Change MySQL default character set to UTF-8 in my cnf

Question

Currently we are using the following commands in PHP to set the character set to UTF-8 in our application    Since this is a bit of overhead  we d like to set this as the default setting in MySQL  Can we do this in  etc my cnf or in another location   SET NAMES  utf8  SET CHARACTER SET utf8   I ve looked for a default charset in  etc my cnf  but there s nothing there about charsets   At this point  I did the following to set the MySQL charset and collation variables to UTF-8   skip-character-set-client-handshake character set client utf8 character set server utf8   Is that a correct way to handle this

User · Answer

I also have found out that after setting default-character-set   utf8 under  mysqld  title  MySQL 5 5 x would not start under Ubuntu nbsp 12 04  Precise Pangolin

User · Answer

For the recent version of MySQL   default-character-set   utf8   causes a problem  It s deprecated I think   As Justin Ball says in  Upgrade to MySQL 5 5 12 and now MySQL won   t start  you should    Remove that directive and you should be good   Then your configuration file    etc my cnf  for example  should look like that    mysqld  collation-server   utf8 unicode ci init-connect  SET NAMES utf8  character-set-server   utf8  Restart MySQL  For making sure  your MySQL is UTF-8  run the following queries in your MySQL prompt    First query    mysql gt  show variables like  char      The output should look like     -------------------------- ---------------------------------     Variable name              Value                               -------------------------- ---------------------------------     character set client       utf8                                 character set connection   utf8                                 character set database     utf8                                 character set filesystem   binary                               character set results      utf8                                 character set server       utf8                                 character set system       utf8                                 character sets dir          usr local mysql share charsets     -------------------------- ---------------------------------   Second query    mysql gt  show variables like  collation      And the query output is     ---------------------- -----------------     Variable name          Value               ---------------------- -----------------     collation connection   utf8 general ci      collation database     utf8 unicode ci      collation server       utf8 unicode ci     ---------------------- -----------------

User · Answer

Under Xubuntu 12 04 I simply added   mysqld  character set server   utf8   to  etc mysql my cnf  And the result is  mysql gt  show variables like   character   show variables like   collation     -------------------------- ----------------------------    Variable name              Value                         -------------------------- ----------------------------    character set client       utf8                           character set connection   utf8                           character set database     utf8                           character set filesystem   binary                         character set results      utf8                           character set server       utf8                           character set system       utf8                           character sets dir          usr share mysql charsets     -------------------------- ----------------------------  8 rows in set  0 00 sec    ---------------------- -----------------    Variable name          Value              ---------------------- -----------------    collation connection   utf8 general ci     collation database     utf8 general ci     collation server       utf8 general ci    ---------------------- -----------------  3 rows in set  0 00 sec    Also take a look at http   dev mysql com doc refman 5 6 en charset-server html

User · Answer

If you re having trouble confirming the client s character-set support using MySQL Workbench  then keep the following note in mind   Important All connections opened by MySQL Workbench automatically set the client character set to utf8  Manually changing the client character set  such as using SET NAMES      may cause MySQL Workbench to not correctly display the characters  For additional information about client character sets  see Connection Character Sets and Collations   Thus I was unable to override MySQL Workbench s character sets with my cnf changes  e g   set names utf8mb4

User · Answer

The directive has changed to character-set-system utf8  http   dev mysql com doc refman 5 6 en charset-configuration html

User · Answer

Change MySQL character   Client  default-character-set utf8   mysqld  character set server utf8   We should not write default-character-set utf8 in mysqld  because that could result in an error like      start  Job failed to start   At last     -------------------------- ----------------------------     Variable name              Value                          -------------------------- ----------------------------     character set client       utf8                            character set connection   utf8                            character set database     utf8                            character set filesystem   binary                          character set results      utf8                            character set server       utf8                            character set system       utf8                            character sets dir          usr share mysql charsets      -------------------------- ----------------------------

User · Answer

Note  my cnf file is located at  etc mysql    After adding these lines    mysqld  collation-server   utf8 unicode ci init-connect  SET NAMES utf8  character-set-server   utf8 skip-character-set-client-handshake   client  default-character-set     utf8   mysql  default-character-set     utf8   Don t forget to restart server   sudo service mysql restart

User · Answer

MySQL v5 5 3 and greater   Just add three lines only in the  mysqld  section    mysqld  character-set-server   utf8 collation-server   utf8 unicode ci skip-character-set-client-handshake   Note  Including skip-character-set-client-handshake here obviates the need to include both init-connect in  mysqld  and default-character-set in the  client  and  mysql  sections

User · Answer

You can do it the way it does  and if it doesn t work  you need to restart mysql

User · Answer

MySQL versions and Linux distributions may matter when making configurations   However  the changes under  mysqld  section is encouraged   I want to give a short explanation of tomazzlender s answer    mysqld   init connect  SET collation connection   utf8 unicode ci   init connect  SET NAMES utf8   character-set-server utf8  collation-server utf8 unicode ci  skip-character-set-client-handshake    mysqld    This will change collation connection to utf8 unicode ci  init connect  SET collation connection   utf8 unicode ci    Using SET NAMES   init connect  SET NAMES utf8    The SET NAMES will influence three characters  that is    character set client character set results character set connection   This will set character set database  amp  character set server  character-set-server utf8   This will only affect collation database  amp  collation server  collation-server utf8 unicode ci   Sorry  I m not so sure what is this for  I don t use it however   skip-character-set-client-handshake

User · Answer

NijaCat was close  but specified overkill   To set the default to UTF-8  you want to add the following to my cnf   client  default-character-set utf8   mysqld  default-character-set   utf8   Then  to verify   mysql gt  show variables like   character   show variables like   collation      -------------------------- ----------------------------    Variable name              Value                         -------------------------- ----------------------------    character set client       utf8                           character set connection   utf8                           character set database     utf8                           character set filesystem   binary                         character set results      utf8                           character set server       utf8                           character set system       utf8                           character sets dir          usr share mysql charsets     -------------------------- ----------------------------  8 rows in set  0 00 sec    ---------------------- -----------------    Variable name          Value              ---------------------- -----------------    collation connection   utf8 general ci     collation database     utf8 general ci     collation server       utf8 general ci    ---------------------- -----------------  3 rows in set  0 00 sec

User · Answer

If you are confused by your setting for client and conn is reseted after restart mysql service  Try these steps  which worked for me     vi  etc my cnf add the contents blow and  wq  client  character-sets-dir  usr local mysql share mysql charsets restart mysql and login mysql   use database  input command status   you ll find the character-set for  client  and  conn  is set to  utf8     Check the reference for more info

User · Answer

This question already has a lot of answers  but Mathias Bynens mentioned that  utf8mb4  should be used instead of  utf8  in order to have better UTF-8 support   utf8  does not support 4 byte characters  fields are truncated on insert   I consider this to be an important difference  So here is yet another answer on how to set the default character set and collation  One that ll allow you to insert a pile of poo      This works on MySQL 5 5 35   Note  that some of the settings may be optional  As I m not entirely sure that I haven t forgotten anything  I ll make this answer a community wiki   Old Settings  mysql gt  SHOW VARIABLES LIKE  char    SHOW VARIABLES LIKE  collation     -------------------------- ----------------------------    Variable name              Value                         -------------------------- ----------------------------    character set client       utf8                           character set connection   utf8                           character set database     latin1                         character set filesystem   binary                         character set results      utf8                           character set server       latin1                         character set system       utf8                           character sets dir          usr share mysql charsets     -------------------------- ----------------------------  8 rows in set  0 00 sec    ---------------------- -------------------    Variable name          Value                ---------------------- -------------------    collation connection   utf8 general ci       collation database     latin1 swedish ci     collation server       latin1 swedish ci    ---------------------- -------------------  3 rows in set  0 00 sec    Config        UTF-8 should be used instead of Latin1  Obviously    NOTE  utf8  in MySQL is NOT full UTF-8  http   mathiasbynens be notes mysql-utf8mb4   client  default-character-set   utf8mb4   mysqld  character-set-server   utf8mb4 collation-server   utf8mb4 unicode ci   mysql  default-character-set   utf8mb4   New Settings  mysql gt  SHOW VARIABLES LIKE  char    SHOW VARIABLES LIKE  collation     -------------------------- ----------------------------    Variable name              Value                         -------------------------- ----------------------------    character set client       utf8mb4                        character set connection   utf8mb4                        character set database     utf8mb4                        character set filesystem   binary                         character set results      utf8mb4                        character set server       utf8mb4                        character set system       utf8                           character sets dir          usr share mysql charsets     -------------------------- ----------------------------  8 rows in set  0 00 sec    ---------------------- --------------------    Variable name          Value                 ---------------------- --------------------    collation connection   utf8mb4 general ci     collation database     utf8mb4 unicode ci     collation server       utf8mb4 unicode ci    ---------------------- --------------------  3 rows in set  0 00 sec      character set system is always utf8   This won t affect existing tables  it s just the default setting  used for new tables   The following ALTER code can be used to convert an existing table  without the dump-restore workaround    ALTER DATABASE databasename CHARACTER SET utf8mb4 COLLATE utf8mb4 unicode ci  ALTER TABLE tablename CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4 unicode ci      Edit   On a MySQL 5 0 server  character set client  character set connection  character set results  collation connection remain at latin1  Issuing SET NAMES utf8  utf8mb4 not available in that version  sets those to utf8 as well     Caveat  If you had a utf8 table with an index column of type VARCHAR 255   it can t be converted in some cases  because the maximum key length is exceeded  Specified key was too long  max key length is 767 bytes    If possible  reduce the column size from 255 to 191  because 191   4   764  lt  767  lt  192   4   768   After that  the table can be converted

User · Answer

To set the default to UTF-8  you want to add the following to my cnf my ini  client  default-character-set utf8mb4   mysql  default-character-set utf8mb4    mysqld  collation-server   utf8mb4 unicode 520 ci init-connect  SET NAMES utf8mb4  character-set-server   utf8mb4  If you want to change the character set for an existing DB  let me know    your question didn t specify it directly so I am not sure if that s what you want to do  Edit  I replaced utf8 with utf8mb4 in the original answer due to utf8 only being a subset of UTF-8  MySQL and MariaDB both call UTF-8 utf8mb4

User · Answer

MySQL 5 5  all you need is    mysqld  character set client utf8 character set server utf8 collation server utf8 unicode ci   collation server is optional   mysql gt  show variables like  char     -------------------------- ----------------------------    Variable name              Value                         -------------------------- ----------------------------    character set client       utf8                           character set connection   utf8                           character set database     utf8                           character set filesystem   binary                         character set results      utf8                           character set server       utf8                           character set system       utf8                           character sets dir          usr share mysql charsets     -------------------------- ----------------------------  8 rows in set  0 00 sec

User · Answer

All settings listed here are correct  but here are the most optimal and sufficient solution    mysqld  init connect  SET collation connection   utf8 unicode ci  character-set-server   utf8 collation-server   utf8 unicode ci   client  default-character-set   utf8   Add these to  etc mysql my cnf   Please note  I choose utf8 unicode ci type of collation due to the performance issue   The result is   mysql gt  SHOW VARIABLES LIKE  character     -------------------------- ----------------------------    Variable name              Value                         -------------------------- ----------------------------    character set client       utf8                           character set connection   utf8                           character set database     utf8                           character set filesystem   binary                         character set results      utf8                           character set server       utf8                           character set system       utf8                           character sets dir          usr share mysql charsets     -------------------------- ----------------------------   mysql gt  SHOW VARIABLES LIKE  collation     ---------------------- -----------------    Variable name          Value              ---------------------- -----------------    collation connection   utf8 unicode ci     collation database     utf8 unicode ci     collation server       utf8 unicode ci    ---------------------- -----------------    And this is when you connect as non-SUPER user   For example  the difference between connection as SUPER and non-SUPER user  of course in case of utf8 unicode ci collation    user with SUPER priv      mysql gt  SHOW VARIABLES LIKE  collation     ---------------------- -----------------    Variable name          Value              ---------------------- -----------------    collation connection   utf8 general ci    lt ---   collation database     utf8 unicode ci     collation server       utf8 unicode ci    ---------------------- -----------------    user with non-SUPER priv    mysql gt  SHOW VARIABLES LIKE  collation     ---------------------- -----------------    Variable name          Value              ---------------------- -----------------    collation connection   utf8 unicode ci     collation database     utf8 unicode ci     collation server       utf8 unicode ci    ---------------------- -----------------    I wrote a comprehensive article  rus  explaining in details why you should use one or the other option  All types of Character Sets and Collations are considered  for server  for database  for connection  for table and even for column   I hope this and the article will help to clarify unclear moments

User · Answer

On MySQL 5 5 I have in my cnf   mysqld   init connect  SET collation connection   utf8 unicode ci   init connect  SET NAMES utf8   character-set-server utf8  collation-server utf8 unicode ci  skip-character-set-client-handshake   Result is   mysql gt  show variables like   character   show variables like   collation      -------------------------- ----------------------------    Variable name              Value                         -------------------------- ----------------------------    character set client       utf8                           character set connection   utf8                           character set database     utf8                           character set filesystem   binary                         character set results      utf8                           character set server       utf8                           character set system       utf8                           character sets dir          usr share mysql charsets     -------------------------- ----------------------------  8 rows in set  0 00 sec    ---------------------- -----------------    Variable name          Value              ---------------------- -----------------    collation connection   utf8 unicode ci     collation database     utf8 unicode ci     collation server       utf8 unicode ci    ---------------------- -----------------  3 rows in set  0 00 sec

User · Answer

On Fedora 21    vi  etc my cnf   Add follow    client  default-character-set utf8   mysql  default-character-set utf8   mysqld  init connect  SET collation connection   utf8 unicode ci  init connect  SET NAMES utf8  character-set-server utf8 collation-server utf8 unicode ci  skip-character-set-client-handshake   Save and exit    Final remember restart service mysqld with service mysqld restart

[mysql] Change MySQL default character set to UTF-8 in my.cnf?

Examples related to mysql

Examples related to character-encoding