MySQLGBK → UTF-8 encoding conversion:
Writing a tutorial for the first time is actually not a tutorial. I just want to summarize the conversion notes. If there is an error in the middle or the solution is not ideal, you can paste it back and study it.
In addition, I also hope that our forum will not only serve as a chat place, but also make everyone active in the learning atmosphere of our forum. after all, we all come from a place where we should give us knowledge, no matter how much knowledge you get from it.
Now, let's get down to the truth.
Preparations:
Environment: MySQL 4.1.x or later.
Convertz -- text encoding and conversion tool, which is described in molyx. In fact, there are many such tools.
II. theory:
Since MySQL 4.1, the internal storage character set supports UTF-8, which I have seen these days. During the forum upgrade, the server database environment was 4.0.26, which was unknown at the time and did not support the UTF-8 character set. In this case, you also need to upgrade MySQL to version 4.1 or later if it involves a UTF-8 dump.
The general idea of conversion is: Backup (with backup and no delay) → repair database → mysqldump export → Convertz conversion code → modify the converted file → mysqldump import and restore
Three practices:
1. backup. You can use any conventional backup method as long as you have recovered it.
2. repair. Mysqlcheck-r-u user-p if all OK, then OK, if not all OK, then again. I don't know how to get it.
3. export. Because latin1 is the default storage, you need to determine the encoding format of your database in advance. For example, lncz.net is originally gbk encoded but stored as latin1. in this way, the encoding must be specified as latin1 during export, and the gbk text can be correctly displayed in ANSI format after export.
Export command: mysqldump database_name field> path -- default-character-set = latin1-u user-p
The database needs to be segmented, otherwise the subsequent operations will be very troublesome. I export each table separately. At that time, the idea was relatively simple, because the database had a bad table and only wanted to know which table had an error and fixed it separately during restoration.
4. conversion. Convertz is easy to use.
5. modify. When I tried to directly import and restore the database, I failed N times and garbled every time. After careful consideration, I realized that if you direct back to the database, the default latin1 will be used for storage, however, your current encoding is UTF-8, so it will make an error after another conversion. Here, I am not very clear about how MySQL is handled. At this time, we need to add the statement "set names utf8;" to the converted file, note that it is not UTF-8; and change "CHARSET = latin1;" to "CHARSET = utf8; to specify the storage encoding of the table.
6. restore. The recovery process should be simple, and it is handled by mysqldump. Note that if your database is large, modify the global variable max_allowed_packet to 1 MB by default. check the size of your database table and modify the my. ini file accordingly.
Import command: mysqldump database_name <path-u user-p if the import succeeds, your database encoding will be converted to UTF-8.
Make a comparison next time. if there is an error, please correct it and smile at me. The above is for reference only.
