How to make MySQL handle UTF-8 properly

Question

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

User · Answer

SET NAMES UTF8  This is does the trick

User · Answer

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

User · Answer

SET NAMES UTF8  This is does the trick

User · Answer

The charset is a property of the database  default  and the table  You can have a look  MySQL commands    show create database foo    gt  CREATE DATABASE   foo   foo     40100 DEFAULT CHARACTER SET latin1     show create table foo bar   gt  lots of stuff ending with  gt    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

User · Answer

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

User · Answer

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 gt  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

User · Answer

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

User · Answer

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

User · Answer

The charset is a property of the database  default  and the table  You can have a look  MySQL commands    show create database foo    gt  CREATE DATABASE   foo   foo     40100 DEFAULT CHARACTER SET latin1     show create table foo bar   gt  lots of stuff ending with  gt    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

User · Answer

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   lt meta charset charset UTF-8 gt  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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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     Aaron s comment on this answer How to make MySQL handle UTF-8 properly What  39 s the difference between utf8 general ci and utf8 unicode ci Conversion guide  https   dev mysql com doc refman 5 5 en charset-unicode-conversion html   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    complete UTF-8 migration  cdbaby com  article on UTF-8 readiness of php functions  note some of this information is outdated

User · Answer

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

User · Answer

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     Aaron s comment on this answer How to make MySQL handle UTF-8 properly What  39 s the difference between utf8 general ci and utf8 unicode ci Conversion guide  https   dev mysql com doc refman 5 5 en charset-unicode-conversion html   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    complete UTF-8 migration  cdbaby com  article on UTF-8 readiness of php functions  note some of this information is outdated

User · Answer

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

User · Answer

SET NAMES UTF8  This is does the trick

User · Answer

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

User · Answer

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

User · Answer

SET NAMES UTF8  This is does the trick

User · Answer

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

User · Answer

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

User · Answer

The charset is a property of the database  default  and the table  You can have a look  MySQL commands    show create database foo    gt  CREATE DATABASE   foo   foo     40100 DEFAULT CHARACTER SET latin1     show create table foo bar   gt  lots of stuff ending with  gt    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

User · Answer

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     Aaron s comment on this answer How to make MySQL handle UTF-8 properly What  39 s the difference between utf8 general ci and utf8 unicode ci Conversion guide  https   dev mysql com doc refman 5 5 en charset-unicode-conversion html   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    complete UTF-8 migration  cdbaby com  article on UTF-8 readiness of php functions  note some of this information is outdated

User · Answer

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 gt  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

User · Answer

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

User · Answer

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

User · Answer

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   lt meta charset charset UTF-8 gt  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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

The charset is a property of the database  default  and the table  You can have a look  MySQL commands    show create database foo    gt  CREATE DATABASE   foo   foo     40100 DEFAULT CHARACTER SET latin1     show create table foo bar   gt  lots of stuff ending with  gt    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

User · Answer

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     Aaron s comment on this answer How to make MySQL handle UTF-8 properly What  39 s the difference between utf8 general ci and utf8 unicode ci Conversion guide  https   dev mysql com doc refman 5 5 en charset-unicode-conversion html   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    complete UTF-8 migration  cdbaby com  article on UTF-8 readiness of php functions  note some of this information is outdated

User · Answer

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

User · Answer

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

[mysql] How to make MySQL handle UTF-8 properly

Examples related to mysql

Examples related to utf-8