How to convert all tables in database to one collation

Question

I m getting error      Illegal mix of collations  utf8 general ci IMPLICIT  and  utf8 unicode ci IMPLICIT  for operation        I tried changing both tables manually to utf8 general ci IMPLICIT but I m still getting the error   Is there a way to convert all tables to utf8 general ci IMPLICIT and be done with it

User · Accepted Answer

You need to execute a alter table statement for each table. The statement would follow this form:

ALTER TABLE tbl_name
[[DEFAULT] CHARACTER SET charset_name]
[COLLATE collation_name]

Now to get all the tables in the database you would need to execute the following query:

SELECT * 
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA="YourDataBaseName"
AND TABLE_TYPE="BASE TABLE";

So now let MySQL write the code for you:

SELECT CONCAT("ALTER TABLE ", TABLE_SCHEMA, '.', TABLE_NAME," COLLATE your_collation_name_here;") AS    ExecuteTheString
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA="YourDatabaseName"
AND TABLE_TYPE="BASE TABLE";

You can copy the results and execute them. I have not tested the syntax but you should be able to figure out the rest. Think of it as a little exercise.

Hope That Helps!

User · Answer

This is my version of a bash script  It takes database name as a parameter and converts all tables to another charset and collation  given by another parameters or default value defined in the script       bin bash    mycollate sh  lt database gt    lt charset gt   lt collation gt     changes MySQL MariaDB charset and collation for one database - all tables and   all columns in all tables  DB   1  CHARSET   2  COLL   3     -n   DB       exit 1   -n   CHARSET       CHARSET  utf8mb4    -n   COLL       COLL  utf8mb4 general ci   echo  DB echo  ALTER DATABASE  DB CHARACTER SET  CHARSET COLLATE  COLL     mysql  echo  USE  DB  SHOW TABLES     mysql -s         while read TABLE  do         echo  DB  TABLE         echo  ALTER TABLE  TABLE CONVERT TO CHARACTER SET  CHARSET COLLATE  COLL     mysql  DB     done

User · Answer

For phpMyAdmin I figured this out   SELECT GROUP CONCAT  ALTER TABLE    TABLE SCHEMA       TABLE NAME   CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4 unicode ci   SEPARATOR      AS    OneSQLString FROM INFORMATION SCHEMA TABLES WHERE TABLE SCHEMA  yourtableschemaname  AND TABLE TYPE  BASE TABLE    Just change yourtableschemaname and you re fine

User · Answer

Below is the more accurate query  I am giving example how to convert it to utf8  SELECT CONCAT  ALTER TABLE     TABLE NAME    DEFAULT CHARACTER SET utf8 COLLATE utf8 unicode ci    AS    mySQL FROM INFORMATION SCHEMA TABLES WHERE TABLE SCHEMA  myschema  AND TABLE TYPE  BASE TABLE

User · Answer

Following on from G H I ve added the user and host parameters incase you need to do this on a remote server         bin bash        mycollate sh  lt database gt   lt user gt   lt password gt    lt host gt   lt charset gt   lt collation gt         changes MySQL MariaDB charset and collation for one database - all tables and       all columns in all tables      DB   1      USER   2      PW   3      HOST   4      CHARSET   5      COLL   6         -n   DB       exit 1       -n   USER       exit 1       -n   PW       exit 1       -n   HOST       HOST  localhost        -n   CHARSET       CHARSET  utf8mb4        -n   COLL       COLL  utf8mb4 general ci       PW  --password    PW      HOST  --host    HOST      USER  --user    USER       echo  DB     echo  ALTER DATABASE  DB CHARACTER SET  CHARSET COLLATE  COLL     mysql   HOST    USER    PW       echo  USE  DB  SHOW TABLES     mysql    HOST    USER    PW              while read TABLE  do             echo  DB  TABLE             echo  ALTER TABLE  TABLE CONVERT TO CHARACTER SET  CHARSET COLLATE  COLL     mysql    HOST    USER    PW   DB         done            PW  pleaseEmptyMeNow

User · Answer

You can use this BASH script      bin bash  USER  YOUR DATABASE USER  PASSWORD  YOUR USER PASSWORD  DB NAME  DATABASE NAME  CHARACTER SET  utf8    your default character set COLLATE  utf8 general ci    your default collation  tables  mysql -u  USER -p PASSWORD -e  SELECT tbl TABLE NAME FROM information schema TABLES tbl WHERE tbl TABLE SCHEMA     DB NAME  AND tbl TABLE TYPE  BASE TABLE     for tableName in  tables  do     if      tableName      TABLE NAME       then         mysql -u  USER -p PASSWORD -e  ALTER TABLE  DB NAME  tableName DEFAULT CHARACTER SET  CHARACTER SET COLLATE  COLLATE           echo   tableName - done      fi done

User · Answer

If you re using PhpMyAdmin  you can now   Select the database  Click the  quot Operations quot  tab  Under  quot Collation quot  section  select the desired collation  Click the  quot Change all tables collations quot  checkbox  A new  quot Change all tables columns collations quot  checkbox will appear  Click the  quot Change all tables columns collations quot  checkbox  Click the  quot Go quot  button   I had over 250 tables to convert  It took a little over 5 minutes

User · Answer

Namphibian s suggestion helped me a lot    went a little further though and added columns and views to the script  just enter your schema s name below and it will do the rest  -- set your table name here SET  MY SCHEMA        -- tables SELECT DISTINCT     CONCAT  ALTER TABLE    TABLE NAME   CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4 unicode ci    as queries FROM INFORMATION SCHEMA TABLES WHERE TABLE SCHEMA  MY SCHEMA   AND TABLE TYPE  BASE TABLE   UNION  -- table columns SELECT DISTINCT     CONCAT  ALTER TABLE    C TABLE NAME    CHANGE    C COLUMN NAME       C COLUMN NAME       C COLUMN TYPE    CHARACTER SET utf8mb4 COLLATE utf8mb4 unicode ci    as queries FROM INFORMATION SCHEMA COLUMNS as C     LEFT JOIN INFORMATION SCHEMA TABLES as T         ON C TABLE NAME   T TABLE NAME WHERE C COLLATION NAME is not null     AND C TABLE SCHEMA  MY SCHEMA     AND T TABLE TYPE  BASE TABLE   UNION  -- views SELECT DISTINCT     CONCAT  CREATE OR REPLACE VIEW    V TABLE NAME    AS    V VIEW DEFINITION       as queries FROM INFORMATION SCHEMA VIEWS as V     LEFT JOIN INFORMATION SCHEMA TABLES as T         ON V TABLE NAME   T TABLE NAME WHERE V TABLE SCHEMA  MY SCHEMA     AND T TABLE TYPE  VIEW

User · Answer

Taking the answer from  Petr Stastny a step further by adding a password variable   I d prefer if it actually took it in like a regular password rather than as an argument  but it s working for what I needed      bin bash    mycollate sh  lt database gt   lt password gt    lt charset gt   lt collation gt     changes MySQL MariaDB charset and collation for one database - all tables and   all columns in all tables  DB   1  PW   2  CHARSET   3  COLL   4     -n   DB       exit 1   -n   PW      -n   CHARSET       CHARSET  utf8mb4    -n   COLL       COLL  utf8mb4 bin   PW  --password    PW   echo  DB echo  ALTER DATABASE  DB CHARACTER SET  CHARSET COLLATE  COLL     mysql -u root   PW   echo  USE  DB  SHOW TABLES     mysql -s   PW          while read TABLE  do         echo  DB  TABLE         echo  ALTER TABLE  TABLE CONVERT TO CHARACTER SET  CHARSET COLLATE  COLL     mysql   PW   DB     done    PW  pleaseEmptyMeNow

User · Answer

If you want a copy-paste bash script   var   mysql -e  SELECT CONCAT  ALTER TABLE    TABLE NAME   CONVERT TO CHARACTER SET utf8 COLLATE utf8 czech ci    AS execTabs FROM INFORMATION SCHEMA TABLES WHERE TABLE SCHEMA  zabbix  AND TABLE TYPE  BASE TABLE   -uroot -p    var   ALTER DATABASE zabbix CHARACTER SET utf8 COLLATE utf8 general ci    echo  var   cut -d     -f2-   mysql -uroot -p zabbix   Change zabbix to your database name

User · Answer

Better option to change also collation of varchar columns inside table also  SELECT CONCAT  ALTER TABLE     TABLE NAME    CONVERT TO CHARACTER SET utf8 COLLATE utf8 general ci    AS    mySQL FROM INFORMATION SCHEMA TABLES WHERE TABLE SCHEMA   myschema  AND TABLE TYPE  BASE TABLE    Additionnaly if you have data with forein key on non utf8 column before launch the bunch script use   SET foreign key checks   0    It means global SQL will be for mySQL    SET foreign key checks   0  ALTER TABLE  table1  CONVERT TO CHARACTER SET utf8 COLLATE utf8 unicode ci  ALTER TABLE  table2  CONVERT TO CHARACTER SET utf8 COLLATE utf8 unicode ci  ALTER TABLE  tableXXX  CONVERT TO CHARACTER SET utf8 COLLATE utf8 unicode ci  SET foreign key checks   1    But take care if according mysql documentation http   dev mysql com doc refman 5 1 en charset-column html       If you use ALTER TABLE to convert a column from one character set to another  MySQL attempts to map the data values  but if the character sets are incompatible  there may be data loss      EDIT  Specially with column type enum  it just crash completly enums set  even if there is no special caracters  https   bugs mysql com bug php id 26731

[mysql] How to convert all tables in database to one collation?

Examples related to mysql

Examples related to sql