3

I would like to convert a MySQL database, which has utf8mb4 character set and utf8mb4_unicode_cicollation, to utf8 and utf8_unicode_ci.

How to do that please?

Thank you in advance for your suggestions.

Improve this question
7
  • 2
    You are aware that UTF8 uses 3 bytes/character and UTF8mb4 uses 4 bytes/character, right? This means you could lose some characters due to the conversion.
    – dartonw
    Mar 9, 2015 at 17:44
  • 1
    Why do you want to change the CHARACTER SET? I ask because there may be a better solution to the underlying question.
    – Rick James
    Mar 10, 2015 at 6:37
  • Not only might you lose a 4-byte character, you might lose the rest of the string.
    – Rick James
    Mar 10, 2015 at 6:38
  • 1
    @dartonw: Thanks. I converted a forum database from latin1 to utf8mb4, using a software. I had two options: utf8 or utf8mb4 (these are the only options to use with the new version of the forum). I chose utf8mb4, but with the converted utf8mb4, I had difficulty to import it to the server using mysqldumper (it either stopped at an internal server error or was stuck at some point). With latin1, it was very fast. Therefore, I think that maybe utf8 is more suitable. But since I did not keep the original latin1 database, my only choice is to convert from utf8 to utf8mb4.
    – f10w
    Mar 10, 2015 at 18:52
  • 1
    Suggest you complain to mysqldumper (about which I know nothing). utf8 and utf8mb4 are very similar. Chinese is the main language that really needs utf8mb4 instead of utf8. Also, some emoticons need utf8mb4. Plan A: Load the dumper into utf8. Plan B: Show us the errors, maybe we can figure it out.
    – Rick James
    Mar 10, 2015 at 19:52

2 Answers 2

Reset to default
4

I just solved the charset problem by using Notepad++.

The problem was that my php version is 4.5.5.1 and the hosting firm's was 3.3.8. Because of that, it could not work with the utf8mb4_unicode_520_ci.

To solve the problem open the exported SQL file, search and replace the utf8mb4 with utf8, after that search and replace the utf8mb4_unicode_520_ci with utf8_general_ci. Save the file and import it into your database. After that, change the wp-config.php charset option to utf8, and the magic starts.

Note that it worked in a Hungarian database.

Improve this answer
1
  • 1
    You should replace the utf8mb4_unicode_520_ci with utf8_general_ci first as if you do the first search and replace with utf8mb4 with utf8 it will overwrite the first replace and cause utf8_unicode_520_ci to be created
    – alchuang
    Dec 30, 2016 at 4:08
1

Run in PhpMyAdmin:

ALTER DATABASE yourdatabasename CHARACTER SET utf8 COLLATE utf8_unicode_ci
Improve this answer
1
  • 2
    This does not change the columns, it only changes the default for newly created tables in that database.
    – Rick James
    Feb 5, 2019 at 18:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.