Wulf's Various Things

User Tools

Site Tools

Trace:
howto:mysql-latin1-to-utf8

Differences

This shows you the differences between two versions of the page.

Link to this comparison view

Next revision		Previous revision
howto:mysql-latin1-to-utf8 [2018/01/16 18:29] – created Wulf Rajekhowto:mysql-latin1-to-utf8 [2023/05/29 11:55] (current) – external edit 127.0.0.1
Line 1: Line 1:
 ====== Convert latin1 MySQL db to utf8 ====== ====== Convert latin1 MySQL db to utf8 ======
 +Export db, change charset, convert and import again:
 <code> <code>
 mysqldump -u username -p --add-drop-table database-name | replace CHARSET=latin1 CHARSET=utf8 | iconv -f latin1 -t utf8 | mysql -u username -p -D database-name mysqldump -u username -p --add-drop-table database-name | replace CHARSET=latin1 CHARSET=utf8 | iconv -f latin1 -t utf8 | mysql -u username -p -D database-name
 +</code>
 +
 +Directly via mysql, note utf8 charset is not fully compatible with UTF-8, so better use utf8mb4:
 +<code>
 +#change default only
 +ALTER TABLE tablename CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
 +#convert charset of data
 +ALTER TABLE tablename convert to character set utf8mb4 COLLATE utf8mb4_unicode_ci;
 +
 +#changes for whole database
 +ALTER DATABASE databasename CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
 +ALTER DATABASE databasename convert to character set utf8mb4 COLLATE utf8mb4_unicode_ci;
 </code> </code>
  
Line 18: Line 30:
 default_mimetype = "text/html" default_mimetype = "text/html"
 default_charset = "utf-8" default_charset = "utf-8"
 +</code>
 +
 +my.cnf should have:
 +<code>
 +[client]
 +default-character-set=utf8
 +[myslqd]
 +skip-character-set-client-handshake
 +collation_server=utf8_unicode_ci
 +character_set_server = utf8 
 +</code>
 +alternatively, in php scripts set:
 +<code>
 +@mysql_query("SET NAMES utf8") or die ("Could not set character set to UTF8: ".mysql_error());
 </code> </code>
howto/mysql-latin1-to-utf8.1516127342.txt.gz · Last modified: (external edit)