Difference between revisions of "MySQL"
Jump to navigation
Jump to search
(→UTF-8 and Latin1: Conversion steps) |
(→UTF-8 and Latin1: Testing the conversion) |
||
| (One intermediate revision by the same user not shown) | |||
| Line 1: | Line 1: | ||
{{tag|MySQL}} | {{tag|MySQL}} | ||
{{tag|Latin-1}} | {{tag|Latin-1}} | ||
{{tag|UTF | {{tag|UTF-8}} | ||
==UTF-8 and Latin1== | ==UTF-8 and Latin1== | ||
| Line 34: | Line 33: | ||
Now your MediaWiki database is utf8 too, and you can keep all character_set_% in utf8. | Now your MediaWiki database is utf8 too, and you can keep all character_set_% in utf8. | ||
===Testing the conversion=== | |||
mysql> create database mies character set = latin1; | |||
shell% mysql --default-character-set=latin1 mies < wikidb.sql | |||
mysql> set names latin; | |||
mysql> SELECT user_name, user_real_name FROM `cheetah_user` WHERE `user_name` LIKE "%Gaston%" ORDER BY `user_name`; | |||
===External Links=== | ===External Links=== | ||
Latest revision as of 08:45, 1 June 2008
UTF-8 and Latin1
Convert a Latin1 database to UTF-8
To:
- convert an existing latin1 encoded MediaWiki MySQL database to utf8; and
- remove any table_name_prefix.
Do:
shell> mysqldump -default-character-set=latin1 wiki_database | sed > wiki_database-utf8.sql \ -e 's/`table_name_prefix_//' \ -e 's/SET NAMES latin1/SET NAMES utf8/' \ -e 's/DEFAULT CHARSET=latin1;/DEFAULT CHARSET=utf8;/'
Next, load the new database:
shell> mysqladmin drop wiki_database shell> mysqladmin create wiki_database shell> mysql wiki_database < wiki_database-utf8.sql
NB: For some strange (uninvestigated) reason, the "SET NAMES utf8" causes:
ERROR 1062 (23000) at line 300: Duplicate entry for key 1
The error occurs in the values for the math table. I simply deleted the line:
INSERT INTO `math` VALUES (…
This leaves the math table empty. I have found no harm done by this. Creating a brand new MediaWiki (1.12.0) also results in a an empty math table. Perhaps there is some safe way to fill the table with the appropriate entries.
Now your MediaWiki database is utf8 too, and you can keep all character_set_% in utf8.
Testing the conversion
mysql> create database mies character set = latin1;
shell% mysql --default-character-set=latin1 mies < wikidb.sql
mysql> set names latin; mysql> SELECT user_name, user_real_name FROM `cheetah_user` WHERE `user_name` LIKE "%Gaston%" ORDER BY `user_name`;