64

I have a database which now needs to support 4 byte characters (Chinese). Luckily I already have MySQL 5.5 in production.

So I would just like to make all collations which are utf8_bin to utf8mb4_bin.

I believe there is no performance loss/gain with this change other than a bit of storage overhead.

  • Use ALTER TABLE tbl_name [[DEFAULT] CHARACTER SET charset_name] [COLLATE collation_name] – Raihan Nov 22 '11 at 18:31
  • 1
    I think he wants to make everything utf8mb4_bin, meaning tables and columns. – Glen Solsberry Nov 22 '11 at 20:37
84

From my guide How to support full Unicode in MySQL databases, here are the queries you can run to update the charset and collation of a database, a table, or a column:

For each database:

ALTER DATABASE
    database_name
    CHARACTER SET = utf8mb4
    COLLATE = utf8mb4_unicode_ci;

For each table:

ALTER TABLE
    table_name
    CONVERT TO CHARACTER SET utf8mb4
    COLLATE utf8mb4_unicode_ci;

For each column:

ALTER TABLE
    table_name
    CHANGE column_name column_name
    VARCHAR(191)
    CHARACTER SET utf8mb4
    COLLATE utf8mb4_unicode_ci;

(Don’t blindly copy-paste this! The exact statement depends on the column type, maximum length, and other properties. The above line is just an example for a VARCHAR column.)

Note, however, that you cannot fully automate the conversion from utf8 to utf8mb4. As described in step 4 of the abovementioned guide, you’ll need to check the maximum length of columns and index keys, as the number you specify has a different meaning when utf8mb4 is used instead of utf8.

Section 10.1.11 of the MySQL 5.5 Reference Manual has some more information on this.

  • 11
    According to the ALTER TABLE syntax (dev.mysql.com/doc/refman/5.5/en/alter-table.html) CONVERT TO handles conversion between the character sets for each column, modifying the column type where appropriate. I think that means ALTER-ing each column is redundant. Is there some reason each column must be ALTER-ed? – CatShoes Aug 27 '13 at 19:23
  • @CatShoes: your point is supported by this discussion: dchua.com/2014/08/15/migrating-from-utf8-to-utf8mb4-in-mysql – davide Mar 10 '15 at 5:43
  • 1
    @CatShoes I'm a little bit confused. Does that mean ALTER TABLE also convert the charset of the existing data on each column? – Lewis Sep 26 '16 at 19:05
  • 1
    From my work, I saw that ALTER the database and the tables is not enough. I had to go into each table and ALTER each of the text/mediumtext/varchar columns too. Luckily I was able to write a script to detect the metadata of MySQL databases, so it can loop through the tables and columns and ALTER them automatically. There is one thing you must have DBA/SUPER USER privilege to do: Setting the database parameters: ``` innodb_large_prefix : ON innodb_file_format : Barracuda innodb_file_format_max : Barracuda ``` – Châu Hồng Lĩnh Sep 27 '17 at 18:47
  • 1
    Found it. ALTER TABLE table_name DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci; finishes the job. – Alph.Dev Oct 5 '17 at 21:43
27

I have a solution that will convert databases and tables by running a few commands. It also converts all columns of the type varchar, text, tinytext, mediumtext, longtext. You can add other types like char by simply extending the queries in the following code. You should also backup your database in case something breaks.

Copy the following code into a file called it preAlterTables.sql:

use information_schema;
SELECT concat("ALTER DATABASE `",table_schema,"` CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;") as _sql 
FROM `TABLES` where table_schema like "yourDbName" group by table_schema;
SELECT concat("ALTER TABLE `",table_schema,"`.`",table_name,"` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;") as _sql  
FROM `TABLES` where table_schema like "yourDbName" group by table_schema, table_name;
SELECT concat("ALTER TABLE `",table_schema,"`.`",table_name, "` CHANGE `",column_name,"` `",column_name,"` ",data_type,"(",character_maximum_length,") CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;") as _sql 
FROM `COLUMNS` where table_schema like "yourDbName" and data_type in ('varchar','char');
SELECT concat("ALTER TABLE `",table_schema,"`.`",table_name, "` CHANGE `",column_name,"` `",column_name,"` ",data_type," CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;") as _sql 
FROM `COLUMNS` where table_schema like "yourDbName" and data_type in ('text','tinytext','mediumtext','longtext');

Replace all occurences of "yourDbName" with the database you want to convert. Then run:

mysql -uroot < preAlterTables.sql | egrep '^ALTER' > alterTables.sql

This will generate a new file alterTables.sql, with all the queries you need to convert the database. Run the following command to start the conversion:

mysql -uroot < alterTables.sql

You can also adapt this to run through multiple databases, by changing the condition for the table_schema. For example table_schema like "wiki_%" will convert all databases with the name prefix wiki_. To convert all databases replace the condition with table_type!='SYSTEM VIEW'.

An issue that might arise. I had some varchar(255) columns in mysql keys. This causes an error:

ERROR 1071 (42000) at line 2229: Specified key was too long; max key length is 767 bytes

If that happens you can simply change the column to be smaller, like varchar(150), and rerun the command.

Please note: This answer converts the database to utf8mb4_unicode_ci instead of utf8mb4_bin, asked in the question. But you can simply replace this.

  • I got the "too long" error and found that changing my 255s to 150s before running alterTables.sql helped but I had to change the order so that I changed the columns before I changed the table defaults. – Aaron Surrain Mar 15 '17 at 19:21
  • Slide 27 of mysql.rjweb.org/slides/rots.pdf gives 5 workarounds to the problem. – Rick James Apr 28 '17 at 1:13
  • I get this error ERROR 1118 (42000) at line 3: Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs and line 3 is ALTER TABLE "nextcloud"."oc_activity" CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; – azmeuk Nov 13 '17 at 21:17
  • Sounds like there are multiple char / varchar colums and there combined maximum length adds up to more than 65535 bytes after beeing converted to utf8mb4. So, as the message says, you may need to change some columns to text or blobs. – MrJingles87 Nov 14 '17 at 22:33
  • 1
    I also noted an error, in my answer, which causes the columns to allow NULL, even when they were peviousely NOT NULL. will update my answer soon. – MrJingles87 Nov 14 '17 at 22:37
3

I would write a script (in Perl, or whatever) to use information_schema (TABLES and COLUMNS) to walk through all the tables, and do MODIFY COLUMN on every CHAR/VARCHAR/TEXT field. I would collect all the MODIFYs into a single ALTER for each table; this will be more efficient.

I think (but am not sure) that Raihan's suggestion only changes the default for the table.

3

Ran into this situation; here's the approach I used to convert my database:

  1. First, you need to edit my.cnf to make default database connection (between applications and MYSQL) utf8mb4_unicode_ci compliant. Without this characters like emojis and similar submitted by your apps won't make it to your tables in right bytes/encoding (unless your application's DB CNN params specify a utf8mb4 connection).

    Instructions given here.

  2. Execute the following SQL (no need to get prepared SQL to change individual columns, ALTER TABLE statements will do that).

    Before you execute below code replace "DbName" with your actual DB name.

    USE information_schema;
    
    SELECT concat("ALTER DATABASE `",table_schema,
                  "` CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;") as _sql
      FROM `TABLES`
     WHERE table_schema like "DbName"
     GROUP BY table_schema;
    
    SELECT concat("ALTER TABLE `",table_schema,"`.`",table_name,
                  "` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;") as _sql
      FROM `TABLES`
     WHERE table_schema like "DbName"
     GROUP BY table_schema, table_name;
    
  3. Collect and save output of above SQL in a dot sql file and execute it.

  4. If you get an error like #1071 - Specified key was too long; max key length is 1000 bytes. along with the problematic table name, this means index key on some column of that table (which was supposed to be converted to MB4 charstring) will be very big hence that Varchar column should be <= 250 so that its index key will be max 1000 bytes. Check the columns on which you have indexes and if one of them is a varchar > 250 (most likely 255) then

    • Step 1: check data in that column to make sure that max string size in that column is <= 250.

      Example query:

      select `id`,`username`, `email`,
             length(`username`) as l1,
             char_length(`username`) as l2,
             length(`email`) as l3,
             char_length(`email`) as l4
        from jos_users
       order by l4 Desc;
      
    • Step 2: if max charlength of indexed column data <= 250 then change the col length to 250. if that is not possible, remove index on that column

    • Step 3: then run the alter table query for that table again and table should now be converted into utf8mb4 successfully.

Cheers!

  • There is a way to use index for long VARCHAR over 191 characters. You must have DBA/SUPER USER privilege to do: Setting the database parameters: innodb_large_prefix : ON; innodb_file_format : Barracuda; innodb_file_format_max : Barracuda; – Châu Hồng Lĩnh Oct 4 '17 at 4:34
2

I wrote this guide: http://hanoian.com/content/index.php/24-automate-the-converting-a-mysql-database-character-set-to-utf8mb4

From my work, I saw that ALTER the database and the tables is not enough. I had to go into each table and ALTER each of the text/mediumtext/varchar columns too.

Luckily I was able to write a script to detect the metadata of MySQL databases, so it could loop through the tables and columns and ALTERed them automatically.

Long index for MySQL 5.6:

There is one thing you must have DBA/SUPER USER privilege to do: Setting the database parameters:

innodb_large_prefix : ON
innodb_file_format : Barracuda 
innodb_file_format_max : Barracuda

In the answers for this question, there is instruction how to set those parameters above: https://stackoverflow.com/questions/35847015/mysql-change-innodb-large-prefix

Of course, in my article, there are instructions to do that too.

For MySQL version 5.7 or newer, the innodb_large_prefix is ON by default, and the innodb_file_format is also Barracuda by default.

2

For people who might have this problem the best solution is to modify first the columns to a binary type, according to this table:

  1. CHAR => BINARY
  2. TEXT => BLOB
  3. TINYTEXT => TINYBLOB
  4. MEDIUMTEXT => MEDIUMBLOB
  5. LONGTEXT => LONGBLOB
  6. VARCHAR => VARBINARY

And after that modify the column back to its former type and with your desired charset.

Eg.: ALTER TABLE [TABLE_SCHEMA].[TABLE_NAME] MODIFY [COLUMN_NAME] LONGBLOB; 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%');

2

I used the following shell 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 <database> [<charset> <collation>]
# 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
)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.