By Ben


2008-10-14 18:09:31 8 Comments

One of the responses to a question I asked yesterday suggested that I should make sure my database can handle UTF-8 characters correctly. How I can do this with MySQL?

14 comments

@Owen 2008-10-14 18:21:03

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:

@Aaron McDaid 2013-09-30 09:32:12

My understanding is that utf8 within MySQL only refers to a small subset of full Unicode. You should use utf8mb4 instead to force full support. See mathiasbynens.be/notes/mysql-utf8mb4 "For a long time, I was using MySQL’s utf8 charset for databases, tables, and columns, assuming it mapped to the UTF-8 encoding described above."

@Animism 2014-01-11 15:57:41

MySQL has never had a default character set of UTF-8. 4.1 and 5.x up to the latest 5.7 all use latin1 and latin1_swedish_ci for the default charset and collation. See the "Server Character Set and Collation" page in the MySQL manual for confirmation: dev.mysql.com/doc/refman/5.1/en/charset-server.html

@Aaron McDaid 2014-05-21 13:43:51

(Tim's comment has disappeared! But I think my response here might still be useful to some. Here it is: ) According to Wikipedia the 5- and 6- bytes encodings have been removed. They were never actually used. Unicode never did define a range of characters that used the 5- or 6- byte encodings. I think this email explains it best

@Tim Tisdall 2014-05-21 13:48:40

@AaronMcDaid Yeah, I deleted it after I read that wikipedia article. ;)

@Tim Tisdall 2014-05-21 13:55:42

The most annoying aspect of utf8 in mysql is that 3 or 4 bytes have to be reserved for every possible character in your table. So, utf8mb4 means you can only save 1/4 the amount of regular English text as latin1 if you're using the maximum row length. I think that's why a lot of programs have given up and used BINARY if they have a lot of text to save. (wikipedia is an example)

@Kevin A. Naudé 2014-10-22 21:15:00

@TimTisdall You need not worry utf8mb4 taking extra storage when most text is ASCII. Although char strings are preallocated, varchar strings are not -- see the last few lines on this documentation page. For example, char(10) will be pessimistically reserve 40 bytes under utf8mb4, but varchar(10) will allocate bytes in keeping with the variable length encoding.

@Tim Tisdall 2014-10-22 22:24:13

@Kevin I think you misread that. I think the maximum row length is 64k. You can only make a utf8mb4 field 1/4 of that because it had to reserve that amount of space. So, even if it's ASCII you can only insert 16k characters.

@Kevin A. Naudé 2014-10-23 08:01:28

@TimTisdall Oh, you're talking about upper bounds. Yes, those are lower. Fortunately, current versions of mysql will automatically upgrade from varchar(n) to the text data type if you attempt to alter a varchar(n) field to larger than the feasible byte size (while issuing a warning). An index will also have a lower worst-case upper bound, and that may present other problems.

@T.W.R. Cole 2015-12-29 17:40:12

The statement "... a default character set of UTF-8" is incorrect, according to MySQL's own documentation: dev.mysql.com/doc/refman/4.1/en/charset-unicode-utf8.html - it does not allow 4-byte characters at all.

@Brendan Byrd 2017-02-11 22:09:32

Do not use utf8. Use utf8mb4. Converting a character set is already costly in terms of table locking and CPU time. Don't bother with an inferior character set that doesn't support all of the characters.

@basic6 2018-09-05 10:55:08

Check if MySQL is currently using utf8[mb4] or latin1 using SHOW VARIABLES, e.g., SHOW VARIABLES WHERE Value LIKE 'latin1%';. Here's another post on how to configure utf8mb4 in my.cnf.

@lingar 2020-06-17 11:34:04

- It will solve the” from now on “ created tables. NOT for EXIST tables. For them you need to do : ALTER TABLE table_name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; Source - digitalocean.com/community/questions/…

@Javier 2008-10-14 18:30:19

To make this 'permanent', in my.cnf:

[client]
default-character-set=utf8
[mysqld]
character-set-server = utf8

To check, go to the client and show some variables:

SHOW VARIABLES LIKE 'character_set%';

Verify that they're all utf8, except ..._filesystem, which should be binary and ..._dir, that points somewhere in the MySQL installation.

@Marek Bar 2012-07-09 11:43:59

It didn't work in my case but I created file my.cf in /etc with given content anyway. I used create table my_name(field_name varchar(25) character set utf8);

@javsmo 2015-02-05 15:47:23

The "SHOW VARIABLES LIKE 'character_set%';" command revealed me the problem with my connection. Thanks!

@T.W.R. Cole 2016-03-17 15:25:52

This is not correct. What MySQL calls utf8 is not "full" UTF-8.

@sunil subramanya 2016-04-14 07:22:33

DATABASE CONNECTION TO UTF-8

$connect = mysql_connect('$localhost','$username','$password') or die(mysql_error());
mysql_set_charset('utf8',$connect);
mysql_select_db('$database_name','$connect') or die(mysql_error());

@Gaurav Lad 2016-01-25 08:01:34

Set your database collation to UTF-8 then apply table collation to database default.

@Nyein Aung 2016-01-25 07:14:19

To change the character set encoding to UTF-8 for the database itself, type the following command at the mysql> prompt. USE ALTER DATABASE.. Replace DBNAME with the database name:

ALTER DATABASE DBNAME CHARACTER SET utf8 COLLATE utf8_general_ci;

This is a duplicate of this question How to convert an entire MySQL database characterset and collation to UTF-8?

@Vipin Jain 2016-01-20 06:04:45

Your answer is you can configure by MySql Settings. In My Answer may be something gone out of context but this is also know is help for you.
how to configure Character Set and Collation.

For applications that store data using the default MySQL character set and collation (latin1, latin1_swedish_ci), no special configuration should be needed. If applications require data storage using a different character set or collation, you can configure character set information several ways:

  • Specify character settings per database. For example, applications that use one database might require utf8, whereas applications that use another database might require sjis.
  • Specify character settings at server startup. This causes the server to use the given settings for all applications that do not make other arrangements.
  • Specify character settings at configuration time, if you build MySQL from source. This causes the server to use the given settings for all applications, without having to specify them at server startup.

The examples shown here for your question to set utf8 character set , here also set collation for more helpful(utf8_general_ci collation`).

Specify character settings per database

  CREATE DATABASE new_db
  DEFAULT CHARACTER SET utf8
  DEFAULT COLLATE utf8_general_ci;

Specify character settings at server startup

[mysqld]
character-set-server=utf8
collation-server=utf8_general_ci

Specify character settings at MySQL configuration time

shell> cmake . -DDEFAULT_CHARSET=utf8 \
           -DDEFAULT_COLLATION=utf8_general_ci

To see the values of the character set and collation system variables that apply to your connection, use these statements:

SHOW VARIABLES LIKE 'character_set%';
SHOW VARIABLES LIKE 'collation%';

This May be lengthy answer but there is all way, you can use. Hopeful my answer is helpful for you. for more information http://dev.mysql.com/doc/refman/5.7/en/charset-applications.html

@Rick James 2016-01-20 01:26:23

The short answer: Use utf8mb4 in 4 places:

  • The bytes in your client are utf8, not latin1/cp1251/etc.
  • SET NAMES utf8mb4 or something equivalent when establishing the client's connection to MySQL
  • CHARACTER SET utf8mb4 on all tables/columns -- except columns that are strictly ascii/hex/country_code/zip_code/etc.
  • <meta charset charset=UTF-8> if you are outputting to HTML. (Yes the spelling is different here.)

More info ;
UTF8 all the way

The above links provide the "detailed canonical answer is required to address all the concerns". -- There is a space limit on this forum.

Edit

In addition to CHARACTER SET utf8mb4 containing "all" the world's characters, COLLATION utf8mb4_unicode_520_ci is arguable the 'best all-around' collation to use. (There are also Turkish, Spanish, etc, collations for those who want the nuances in those languages.)

@Rick James 2016-08-11 18:15:18

My new link on how to debug utf8 problems from the output you get.

@Louis 2018-08-02 02:17:07

Why unicode_520_ci is not the best all around: stackoverflow.com/a/49982378/62202

@Rick James 2018-08-16 07:36:00

@Louis - And as I implied Spanish and Turkish (as well as Polish) users may not happy. "Best all-around" tends to hurt everyone some. MySQL 8.0 has an even newer "best" collation: utf8mb4_0900_ai_ci. Alas, again L=Ł.

@T.W.R. Cole 2015-04-28 20:51:10

MySQL 4.1 and above has a default character set that it calls utf8 but which is actually only a subset of UTF-8 (allows only three-byte characters and smaller).

Use utf8mb4 as your charset if you want "full" UTF-8.

@jibai31 2015-08-27 09:37:35

Definitely agree, this is the only correct answer. utf8 doesn't include chars like emoticons. utf8mb4 does. Check this for more info on how to update : mathiasbynens.be/notes/mysql-utf8mb4

@Rick James 2016-01-20 03:28:18

@Basti -- Mostly correct (latin1 was the default until just recently), and not complete (does not discuss correctly inserting/selecting utf8-encoded data, nor displaying in html).

@T.W.R. Cole 2016-03-17 15:23:52

Respectfully, @RickJames, Basti said "so far" - I don't remember seeing your answer when I posted this.

@Rick James 2016-03-17 15:58:21

Alas, there are about 5 distinctly different symptoms of utf8 problems, and about 4 things that programmers do wrong to cause trouble. Most answers point out only one thing that may need fixing. The original question was broad one, so the answer needed all 4. Perhaps Basti was familiar with one symptom for which your one aspect was the solution.

@Rick James 2016-03-17 16:01:16

Or, to look at another way, "handle UTF-8 characters correctly" can be read two ways... You read it as needing utf8mb4. I read it as not garbling the text on the way in/out of the database. By your, and Basti's, interpretation, your answer is correct and complete.

@T.W.R. Cole 2016-03-17 18:21:34

As an aside, I'd like to pause a moment and give the MySQL team a really good, hard stare. o_o WTF were you guys thinking? Do you realize how much confusion you've sown by creating a codepage in your program called "utf8" that isn't actually UTF-8? Goddamn assholes. </rant>

@Nishant 2015-06-09 07:41:27

Was able to find a solution. Ran the following as specified at http://technoguider.com/2015/05/utf8-set-up-in-mysql/

SET NAMES UTF8;
set collation_server = utf8_general_ci;
set default-character-set = utf8;
set init_connect = ’SET NAMES utf8′;
set character_set_server = utf8;
set character_set_client = utf8;

@DanielM 2015-07-25 09:54:59

The last two lines are redundant, since the first one already includes those: dev.mysql.com/doc/refman/5.0/en/charset-connection.html

@Rick James 2016-01-20 03:30:01

Also not a complete solution. The columns need CHARACTER SET utf8. root will not execute the all-important init_connect.

@fin 2013-08-12 21:44:49

Set your database connection to UTF8:

  if($handle = @mysql_connect(DB_HOST, DB_USER, DB_PASS)){          
         //set to utf8 encoding
         mysql_set_charset('utf8',$handle);
  }

@Rick James 2016-01-20 03:30:57

If running PHP, do not use the deprecated mysql_* interface. Switch to mysqli_* or PDO.

@Claudio 2008-10-14 18:21:46

SET NAMES UTF8

This is does the trick

@basic6 2014-06-30 09:53:15

While using SET NAMES UTF8 (or UTF8mb4) is correct, you don't explain what it does (character set used for this connection). "This does the trick" sounds like it would solve the problem (make MySQL handle UTF-8 properly), but many MySQL databases are set to latin1 by default, so that wouldn't make it a proper solution. I would change the default charset and the table charsets to utf8mb4. Really, this answer is rather incomplete, so I downvoted it.

@Vlad Balan 2012-05-20 12:14:19

I followed Javier's solution, but I added some different lines in my.cnf:

[myslqd]
skip-character-set-client-handshake
collation_server=utf8_unicode_ci
character_set_server=utf8 

I found this idea here: http://dev.mysql.com/doc/refman/5.0/en/charset-server.html in the first/only user comment on the bottom of the page. He mentions that skip-character-set-client-handshake has some importance.

@Marcus 2019-02-20 07:39:50

This unloved, zero-vote answer was the only thing that helped me! So it gets my vote, that's for darn sure. skip-character-set-client-handshake was the key.

@Edward Z. Yang 2008-10-15 05:05:33

These tips on MySQL and UTF-8 may be helpful. Unfortunately, they don't constitute a full solution, just common gotchas.

@extraneon 2008-10-14 18:32:39

The charset is a property of the database (default) and the table. You can have a look (MySQL commands):

show create database foo; 
> CREATE DATABASE  `foo`.`foo` /*!40100 DEFAULT CHARACTER SET latin1 */

show create table foo.bar;
> lots of stuff ending with
> ) ENGINE=InnoDB AUTO_INCREMENT=252 DEFAULT CHARSET=latin1

In other words; it's quite easy to check your database charset or change it:

ALTER TABLE `foo`.`bar` CHARACTER SET utf8;

@T.W.R. Cole 2016-03-17 15:25:43

This is not correct. What MySQL calls utf8 is not "full" UTF-8.

Related Questions

Sponsored Content

48 Answered Questions

[SOLVED] How do I import an SQL file using the command line in MySQL?

7 Answered Questions

[SOLVED] Why does modern Perl avoid UTF-8 by default?

  • 2011-05-28 15:12:36
  • w.k
  • 101275 View
  • 563 Score
  • 7 Answer
  • Tags:   perl unicode utf-8

4 Answered Questions

[SOLVED] TINYTEXT, TEXT, MEDIUMTEXT, and LONGTEXT maximum storage sizes

  • 2012-12-18 12:13:35
  • Lalith B
  • 762305 View
  • 810 Score
  • 4 Answer
  • Tags:   mysql innodb

15 Answered Questions

[SOLVED] UTF-8 all the way through

19 Answered Questions

16 Answered Questions

[SOLVED] How to get the sizes of the tables of a MySQL database?

  • 2012-03-08 15:30:31
  • JPashs
  • 678006 View
  • 920 Score
  • 16 Answer
  • Tags:   mysql

39 Answered Questions

[SOLVED] Should I use the datetime or timestamp data type in MySQL?

15 Answered Questions

[SOLVED] How to get a list of user accounts using the command line in MySQL?

Sponsored Content