How to convert an entire MySQL database characterset and collation to UTF-8

Question

How can I convert entire MySQL database character-set to UTF-8 and collation to UTF-8

User · Answer

On the commandline shell  If you re one the commandline shell  you can do this very quickly  Just fill in  dbname   D  DB  dbname        echo  ALTER DATABASE     DB    CHARACTER SET utf8 COLLATE utf8 general ci       mysql   DB  -e  SHOW TABLES  --batch --skip-column-names         xargs -I   echo  ALTER TABLE        CONVERT TO CHARACTER SET utf8 COLLATE utf8 general ci         mysql   DB    One-liner for simple copy paste  DB  dbname     echo  ALTER DATABASE     DB    CHARACTER SET utf8 COLLATE utf8 general ci    mysql   DB  -e  SHOW TABLES  --batch --skip-column-names   xargs -I   echo  ALTER TABLE        CONVERT TO CHARACTER SET utf8 COLLATE utf8 general ci       mysql   DB

User · Answer

For databases that have a high number of tables you can use a simple php script to update the charset of the database and all of the tables using the following     conn   mysqli connect  host   username   password   database    if   conn- gt connect error      die  Connection failed       conn- gt connect error       alter database charset sql    ALTER DATABASE    database   CHARACTER SET utf8 COLLATE utf8 unicode ci   mysqli query  conn   alter database charset sql     show tables result   mysqli query  conn   SHOW TABLES     tables    mysqli fetch all  show tables result    foreach   tables as  index   gt   table       alter table sql    ALTER TABLE    table 0    CONVERT TO CHARACTER SET utf8  COLLATE utf8 unicode ci      alter table result   mysqli query  conn   alter table sql     echo   lt pre gt      var dump  alter table result     echo   lt  pre gt

User · Answer

Make a backup  Then you need to set the default char sets on the database   This does not convert existing tables  it only sets the default for newly created tables   ALTER DATABASE dbname CHARACTER SET utf8 COLLATE utf8 general ci   Then  you will need to convert the char set on all existing tables and their columns   This assumes that your current data is actually in the current char set   If your columns are set to one char set but your data is really stored in another then you will need to check the MySQL manual on how to handle this   ALTER TABLE tbl name CONVERT TO CHARACTER SET utf8 COLLATE utf8 general ci

User · Answer

The safest way is to modify the columns first to a binary type and then modify it back to it type using the desired charset   Each column type have its respective binary type  as follows    CHAR    BINARY TEXT    BLOB TINYTEXT    TINYBLOB MEDIUMTEXT    MEDIUMBLOB LONGTEXT    LONGBLOB VARCHAR    VARBINARY   Eg    ALTER TABLE  TABLE SCHEMA   TABLE NAME  MODIFY  COLUMN NAME  VARBINARY   ALTER TABLE  TABLE SCHEMA   TABLE NAME  MODIFY  COLUMN NAME  VARCHAR 140  CHARACTER SET utf8mb4    I tried in several latin1 tables and it kept all the diacritics   You can extract this query for all columns doing this   SELECT CONCAT  ALTER TABLE    TABLE SCHEMA      TABLE NAME   MODIFY    COLUMN NAME   VARBINARY     CONCAT  ALTER TABLE    TABLE SCHEMA      TABLE NAME   MODIFY    COLUMN NAME      COLUMN TYPE   CHARACTER SET utf8mb4 COLLATE utf8mb4 general ci    FROM information schema columns WHERE TABLE SCHEMA IN    TABLE SCHEMA    AND COLUMN TYPE LIKE  varchar   AND  COLLATION NAME IS NOT NULL AND COLLATION NAME NOT LIKE  utf       After you do this on all your columns then you do it on all tables   ALTER TABLE  TABLE SCHEMA   TABLE NAME  CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4 general ci    To generate this query for all your table  use the following query   SELECT CONCAT  ALTER TABLE    TABLE SCHEMA       TABLE NAME    CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4 general ci    FROM INFORMATION SCHEMA TABLES WHERE TABLE COLLATION NOT LIKE  utf8   and TABLE SCHEMA in    TABLE SCHEMA       And now that you modified all your columns and tables  do the same on the database   ALTER DATABASE  DATA BASE NAME  CHARSET   utf8mb4 COLLATE   utf8mb4 general ci

User · Answer

In case the data is not in the same character set you might consider this snippet from http   dev mysql com doc refman 5 0 en charset-conversion html     If the column has a nonbinary data type  CHAR  VARCHAR  TEXT   its   contents should be encoded in the column character set  not some other   character set  If the contents are encoded in a different character   set  you can convert the column to use a binary data type first  and   then to a nonbinary column with the desired character set    Here is an example    ALTER TABLE t1 CHANGE c1 c1 BLOB   ALTER TABLE t1 CHANGE c1 c1 VARCHAR 100  CHARACTER SET utf8    Make sure to choose the right collation  or you might get unique key conflicts   e g     leanore and Eleanore might be considered the same in some collations   Aside   I had a situation where certain characters  broke  in emails even though they were stored as UTF-8 in the database   If you are sending emails using utf8 data  you might want to also convert your emails to send in UTF8   In PHPMailer  just update this line  public  CharSet    utf-8

User · Answer

alter table table name charset    utf8    This is a simple query i was able to use for my case  you can change the table name as per your requirement s

User · Answer

You can create the sql to update all tables with  SELECT CONCAT  quot ALTER TABLE  quot  TABLE SCHEMA  quot   quot  TABLE NAME  quot  CHARACTER SET utf8 COLLATE utf8 general ci     quot        quot ALTER TABLE  quot  TABLE SCHEMA  quot   quot  TABLE NAME  quot  CONVERT TO CHARACTER SET utf8 COLLATE utf8 general ci    quot        AS alter sql FROM information schema TABLES WHERE TABLE SCHEMA    quot your database name quot    Capture the output and run it  Arnold Daniels  answer above is more elegant

User · Answer

You can also DB tool Navicat  which does it more easier    Siva    Right Click Your Database  amp  select DB Properties  amp  Change as you desired in Drop Down

User · Answer

The only solution that worked for me  http   docs moodle org 23 en Converting your MySQL database to UTF8  Converting a database containing tables  mysqldump -uusername -ppassword -c -e --default-character-set utf8 --single-transaction --skip-set-charset --add-drop-database -B dbname  gt  dump sql  cp dump sql dump-fixed sql vim dump-fixed sql    s DEFAULT CHARACTER SET latin1 DEFAULT CHARACTER SET utf8 COLLATE utf8 general ci    s DEFAULT CHARSET latin1 DEFAULT CHARSET utf8   wq  mysql -uusername -ppassword  lt  dump-fixed sql

User · Answer

Use HeidiSQL  Its free and a very good db tool   From tools menu  enter Bulk table editor  Select the complete database or pick tables to convert    tick Change default collation  utf8mb4 general ci   tick Convert to charset  utf8   Execute  This converts complete database from latin to utf8 in just a few seconds   Works like a charm     HeidiSQL connects by default as utf8 so any special characters should now be seen as the character            and not as encoded when inspecting the table data   The real pitfall when moving from latin to utf8 is to make sure pdo connects with utf8 charset  If not you will get rubbish data inserted to the utf8 table and question marks all over the place on your web page  making you think the table data is not utf8

User · Answer

Use the ALTER DATABASE and ALTER TABLE commands   ALTER DATABASE databasename CHARACTER SET utf8mb4 COLLATE utf8mb4 unicode ci  ALTER TABLE tablename CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4 unicode ci    Or if you re still on MySQL 5 5 2 or older which didn t support 4-byte UTF-8  use utf8 instead of utf8mb4   ALTER DATABASE databasename CHARACTER SET utf8 COLLATE utf8 unicode ci  ALTER TABLE tablename CONVERT TO CHARACTER SET utf8 COLLATE utf8 unicode ci

User · Answer

mysqldump -uusername -ppassword -c -e --default-character-set utf8 --single-transaction --skip-set-charset --add-drop-database -B dbname  gt  dump sql cp dump sql dump-fixed sql vim dump-fixed sql     s DEFAULT CHARACTER SET latin1 DEFAULT CHARACTER SET utf8 COLLATE utf8 general ci    s DEFAULT CHARSET latin1 DEFAULT CHARSET utf8   wq  mysql -uusername -ppassword  lt  dump-fixed sql

User · Answer

Before proceeding  ensure that you  Have completed a full database backup   Step 1  Database Level Changes   Identifying the Collation and Character set of your database  SELECT DEFAULT CHARACTER SET NAME  DEFAULT COLLATION NAME FROM  information schema SCHEMATA S WHERE schema name    your database name  AND  DEFAULT CHARACTER SET NAME     utf8      OR  DEFAULT COLLATION NAME not like  utf8      Fixing the collation for the database  ALTER DATABASE databasename CHARACTER SET utf8 COLLATE utf8 unicode ci     Step 2  Table Level Changes   Identifying Database Tables with the incorrect character set or collation  SELECT CONCAT   ALTER TABLE     table name    CHARACTER SET utf8 COLLATE utf8 general ci        ALTER TABLE     table name    CONVERT TO CHARACTER SET utf8 COLLATE utf8 general ci      FROM information schema TABLES AS T  information schema  COLLATION CHARACTER SET APPLICABILITY  AS C WHERE C collation name   T table collation AND T table schema    your database name  AND  C CHARACTER SET NAME     utf8      OR  C COLLATION NAME not like  utf8     Adjusting table columns  collation and character set   Capture upper sql output and run it   like following   ALTER TABLE rma CHARACTER SET utf8 COLLATE utf8 general ci ALTER TABLE rma CONVERT TO CHARACTER SET utf8 COLLATE utf8 general ci    ALTER TABLE rma history CHARACTER SET utf8 COLLATE utf8 general ci ALTER TABLE rma history CONVERT TO CHARACTER SET utf8 COLLATE utf8 general ci    ALTER TABLE rma products CHARACTER SET utf8 COLLATE utf8 general ci ALTER TABLE rma products CONVERT TO CHARACTER SET utf8 COLLATE utf8 general ci    ALTER TABLE rma report period CHARACTER SET utf8 COLLATE utf8 general ci ALTER TABLE rma report period CONVERT TO CHARACTER SET utf8 COLLATE utf8 general ci    ALTER TABLE rma reservation CHARACTER SET utf8 COLLATE utf8 general ci ALTER TABLE rma reservation CONVERT TO CHARACTER SET utf8 COLLATE utf8 general ci    ALTER TABLE rma supplier return CHARACTER SET utf8 COLLATE utf8 general ci ALTER TABLE rma supplier return CONVERT TO CHARACTER SET utf8 COLLATE utf8 general ci    ALTER TABLE rma supplier return history CHARACTER SET utf8 COLLATE utf8 general ci ALTER TABLE rma supplier return history CONVERT TO CHARACTER SET utf8 COLLATE utf8 general ci    ALTER TABLE rma supplier return product CHARACTER SET utf8 COLLATE utf8 general ci ALTER TABLE rma supplier return product CONVERT TO CHARACTER SET utf8 COLLATE utf8 general ci     refer to  https   confluence atlassian com display CONFKB How to Fix the Collation and Character Set of a MySQL Database

User · Answer

DELIMITER       CREATE PROCEDURE  databasename   update char set       BEGIN    DECLARE done INT DEFAULT 0     DECLARE t sql VARCHAR 256      DECLARE tableName VARCHAR 128      DECLARE lists CURSOR FOR SELECT table name FROM  information schema   TABLES  WHERE table schema    databasename      DECLARE CONTINUE HANDLER FOR SQLSTATE  02000  SET done   1     OPEN lists     FETCH lists INTO tableName     REPEAT       SET  t sql   CONCAT  ALTER TABLE    tableName    CONVERT TO CHARACTER SET utf8 COLLATE utf8 general ci          PREPARE stmt FROM  t sql        EXECUTE stmt        DEALLOCATE PREPARE stmt     FETCH lists INTO tableName     UNTIL done END REPEAT     CLOSE lists    END      DELIMITER      CALL databasename update char set

User · Answer

If you cannot get your tables to convert or your table is always set to some non-utf8 character set  but you want utf8  your best bet might be to wipe it out and start over again and explicitly specify   create database database name character set utf8

User · Answer

Inspired by  sdfor comment  here is a bash script that does the job     bin bash  printf      Converting MySQL character set     n n   printf  Enter the encoding you want to set    read -r CHARSET    Get the MySQL username printf  Enter mysql username    read -r USERNAME    Get the MySQL password printf  Enter mysql password for user  s     USERNAME  read -rs PASSWORD  DBLIST   mydatabase1 mydatabase2    printf   n    for DB in    DBLIST      do       echo  ALTER DATABASE     DB    CHARACTER SET utf8 COLLATE     CHARSET          mysql   DB  -u  USERNAME  -p  PASSWORD  -e  SHOW TABLES  --batch --skip-column-names         xargs -I   echo  ALTER TABLE        CONVERT TO CHARACTER SET utf8 COLLATE     CHARSET            mysql   DB  -u  USERNAME  -p  PASSWORD   echo   DB database done     done  echo      DONE      exit

User · Answer

To change the character set encoding to UTF-8 follow simple steps in PHPMyAdmin   Select your Database  Go To Operations  In operations tab  on the bottom collation drop down menu  select you desire encoding i e utf8 general ci   and also check the checkbox  1 change all table collations   2  Change all tables columns collations  and hit Go

User · Answer

Command Line Solution and Exclude Views  I am simply completing  Jasny s answer for others like  Brian and I who have views in our database   If you have an error like this    ERROR 1347  HY000  at line 17   dbname table name  is not of type  BASE TABLE    It s because you probably have views and you need to exclude them   But when trying to exclude them  MySQL returns 2 columns instead of 1   SHOW FULL TABLES WHERE Table Type    BASE TABLE   -- table name1  BASE TABLE -- table name2  BASE TABLE   So we have to adapt Jasny s command with awk to extract only the 1st column which contains the table name   DB  dbname        echo  ALTER DATABASE     DB    CHARACTER SET utf8 COLLATE utf8 general ci       mysql   DB  -e  SHOW FULL TABLES WHERE Table Type    BASE TABLE   --batch --skip-column-names         awk   print  1            xargs -I   echo  ALTER TABLE        CONVERT TO CHARACTER SET utf8 COLLATE utf8 general ci         mysql   DB    One-liner for simple copy paste  DB  dbname     echo  ALTER DATABASE     DB    CHARACTER SET utf8 COLLATE utf8 general ci    mysql   DB  -e  SHOW FULL TABLES WHERE Table Type    BASE TABLE   --batch --skip-column-names   awk   print  1      xargs -I   echo  ALTER TABLE        CONVERT TO CHARACTER SET utf8 COLLATE utf8 general ci       mysql   DB

User · Answer

To change the character set encoding to UTF-8 for the database itself  type the following command at the mysql  prompt  Replace DBNAME with the database name   ALTER DATABASE DBNAME CHARACTER SET utf8 COLLATE utf8 general ci

[mysql] How to convert an entire MySQL database characterset and collation to UTF-8?

On the commandline shell

One-liner for simple copy/paste

Examples related to mysql

Examples related to character-encoding