How to make MySQL handle UTF-8 properly

by

Update:

Short answer – You should almost always be using the utf8mb4 charset and utf8mb4_unicode_ci collation.

To alter database:

ALTER DATABASE dbname CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

See:

Original Answer:

MySQL 4.1 and above has a default character set of UTF-8. You can verify this in your my.cnf file, remember to set both client and server (default-character-set and character-set-server).

If you have existing data that you wish to convert to UTF-8, dump your database, and import it back as UTF-8 making sure:

  • use SET NAMES utf8 before you query/insert into the database
  • use DEFAULT CHARSET=utf8 when creating new tables
  • at this point your MySQL client and server should be in UTF-8 (see my.cnf). remember any languages you use (such as PHP) must be UTF-8 as well. Some versions of PHP will use their own MySQL client library, which may not be UTF-8 aware.

If you do want to migrate existing data remember to backup first! Lots of weird choping of data can happen when things don’t go as planned!

Some resources:

More Related Contents:

  1. Trouble with UTF-8 characters; what I see is not what I stored
  2. What’s the difference between utf8_general_ci and utf8_unicode_ci?
  3. “Incorrect string value” when trying to insert UTF-8 into MySQL via JDBC?
  4. What is the difference between utf8mb4 and utf8 charsets in MySQL?
  5. How to conduct an Accent Sensitive search in MySql
  6. How to display UTF-8 characters in phpMyAdmin?
  7. UTF-8: General? Bin? Unicode?
  8. How to remove accents in MySQL?
  9. Differences between utf8 and latin1
  10. Incorrect string value: ‘\xF0\x9F\x8E\xB6\xF0\x9F…’ MySQL
  11. MySQL VARCHAR Lengths and UTF-8
  12. How to detect UTF-8 characters in a Latin1 encoded column – MySQL
  13. Fetching UTF-8 text from MySQL in R returns “????”
  14. How to fix double-encoded UTF8 characters (in an utf-8 table)
  15. Loading utf-8 encoded text into MySQL table
  16. MySQL command line formatting with UTF8
  17. SQL doesnt differentiate u and ü although collation is utf8mb4_unicode_ci
  18. Detecting utf8 broken characters in MySQL
  19. MySQL throws Incorrect string value error
  20. Mysql server does not support 4-byte encoded utf8 characters
  21. UTF8 problem with MySQL 5
  22. Unknown Column In Where Clause
  23. Left Join not returning all rows
  24. Maximum length for MySQL type text
  25. UPDATE all records with a random number from a range
  26. mysql sorting of version numbers
  27. Alter table if exists or create if doesn’t
  28. AWS Aurora MySQL serverless: how to connect from MySQL Workbench
  29. What happens when I exhaust a bigint generated key? How to handle it?
  30. MySQL Auto increment primary key increases by 10

Leave a Comment