107

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.

0

10 Answers 10

132

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.

0
53

This solution will generate and then run queries needed to convert databases, tables and columns. It converts all columns of the type varchar, text, tinytext, mediumtext, longtext, char.

You should always backup your database in case something goes wrong.

  1. Copy the following query into gen_queries.sql, replacing the 4 occurrences of YOUR_DATABASE_NAME with the name of the database you wish to convert:

    USE information_schema;
    SELECT CONCAT("ALTER DATABASE `",table_schema,"` CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;") AS _sql
    FROM `TABLES` WHERE table_schema LIKE "YOUR_DATABASE_NAME" AND TABLE_TYPE='BASE TABLE' GROUP BY table_schema UNION
    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 "YOUR_DATABASE_NAME" AND TABLE_TYPE='BASE TABLE' GROUP BY table_schema, table_name UNION
    SELECT CONCAT("ALTER TABLE `",`COLUMNS`.table_schema,"`.`",`COLUMNS`.table_name, "` CHANGE `",column_name,"` `",column_name,"` ",data_type,"(",character_maximum_length,") CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci",IF(is_nullable="YES"," NULL"," NOT NULL"),";") AS _sql 
    FROM `COLUMNS` INNER JOIN `TABLES` ON `TABLES`.table_name = `COLUMNS`.table_name WHERE `COLUMNS`.table_schema like "YOUR_DATABASE_NAME" and data_type in ('varchar','char') AND TABLE_TYPE='BASE TABLE' UNION
    SELECT CONCAT("ALTER TABLE `",`COLUMNS`.table_schema,"`.`",`COLUMNS`.table_name, "` CHANGE `",column_name,"` `",column_name,"` ",data_type," CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci",IF(is_nullable="YES"," NULL"," NOT NULL"),";") AS _sql 
    FROM `COLUMNS` INNER JOIN `TABLES` ON `TABLES`.table_name = `COLUMNS`.table_name WHERE `COLUMNS`.table_schema like "YOUR_DATABASE_NAME" and data_type in ('text','tinytext','mediumtext','longtext') AND TABLE_TYPE='BASE TABLE';
    
  2. Run the following command to generate a new file queries.sql, with all the queries you need to convert the database.:

    mysql -u root -p -s < gen_queries.sql > queries.sql
    
  3. Run the following command to run the queries, performing the conversion:

    mysql -u root -p < queries.sql
    

Notes:

  • To run the conversion on multiple databases adjust the table_schema LIKE "YOUR_DATABASE_NAME" part of the query, for example:
    • Replacing with table_schema LIKE "wiki_%" would convert all databases whose name starts with wiki_
    • Replacing with table_type != 'SYSTEM VIEW' would convert all databases
  • An issue I had with some varchar(255) columns in mysql keys generated the following error:
    ERROR 1071 (42000) at line x: Specified key was too long; max key length is 767 bytes
    If that happens, change the column to be smaller, like varchar(150), and rerun the command.
  • This sets the collation to utf8mb4_unicode_ci instead of utf8mb4_bin as asked in the question. Substitute your preferred collation in the query as required.
4
  • 5
    I had to use "SET foreign_key_checks = 0;", then apply the changes, then "SET foreign_key_checks = 1;". – dfrankow Aug 15 '19 at 19:08
  • Very helpful. The error though about "767 exceeded" isn't fixed in mariadb 5.5.x but is ok when tested in higher version (mariadb 10.2.x and higher). – icasimpan Mar 4 '20 at 23:34
  • Note that this appears to break generated columns (appears only persistent, not virtual), be warned! – syserr0r Oct 21 '20 at 11:52
  • 1
    I've just created a bash script based on mrjingles87's answer: github.com/fleio/utf8mb4-convert – waverider Jan 27 at 17:03
8

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
)
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!

1
  • 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.

1
  • 1
    Your blog article is still there - but the link in the article to your ruby script in Github doesn't work. – Claudio Kuenzler Sep 29 '20 at 8:36
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%');
1

Run the following SQL statements to get the result statements for updating character set and collation:

SET @database = "your_database_name";

SET @charset = "utf8mb4";

SET @collate = "utf8mb4_0900_ai_ci";

SELECT "SET foreign_key_checks = 0;"
UNION ALL
SELECT concat(
  "ALTER DATABASE `",
  `SCHEMA_NAME`,
  "` CHARACTER SET = ",
  @charset,
  " COLLATE = ",
  @collate,
  ";"
) AS `sql`
FROM `information_schema`.`SCHEMATA`
WHERE `SCHEMA_NAME` = @database
  AND (
    `DEFAULT_CHARACTER_SET_NAME` <> @charset
    OR `DEFAULT_COLLATION_NAME` <> @collate
  )
UNION ALL
SELECT concat(
  "ALTER TABLE `",
  `TABLE_SCHEMA`,
  "`.`",
  `TABLE_NAME`,
  "` CONVERT TO CHARACTER SET ",
  @charset,
  " COLLATE ",
  @collate,
  ";"
) AS `sql`
FROM `information_schema`.`TABLES`
WHERE `TABLE_SCHEMA` = @database
  AND `TABLE_TYPE` = "BASE TABLE"
  AND `TABLE_COLLATION` <> @collate
UNION ALL
SELECT concat(
  "ALTER TABLE `",
  c.`TABLE_SCHEMA`,
  "`.`",
  c.`TABLE_NAME`,
  "` CHANGE `",
  c.`COLUMN_NAME`,
  "` `",
  c.`COLUMN_NAME`,
  "` ",
  c.`COLUMN_TYPE`,
  " CHARACTER SET ",
  @charset,
  " COLLATE ",
  @collate,
  if(c.`IS_NULLABLE`="YES", " NULL", " NOT NULL"),
  ";"
) AS `sql`
FROM `information_schema`.`COLUMNS` c,
  `information_schema`.`TABLES` t,
  `information_schema`.`COLLATION_CHARACTER_SET_APPLICABILITY` a
WHERE c.`TABLE_SCHEMA` = t.`TABLE_SCHEMA`
  AND c.`TABLE_NAME` = t.`TABLE_NAME`
  AND t.`TABLE_COLLATION` = a.`COLLATION_NAME`
  AND c.`TABLE_SCHEMA` = @database
  AND c.`DATA_TYPE` IN (
    'varchar',
    'char',
    'text',
    'tinytext',
    'mediumtext',
    'longtext'
  )
  AND (
    c.`CHARACTER_SET_NAME` <> a.`CHARACTER_SET_NAME`
    OR c.`COLLATION_NAME` <> t.`TABLE_COLLATION`
  )
  AND t.`TABLE_TYPE` = "BASE TABLE"
UNION ALL
SELECT "SET foreign_key_checks = 1;";

Notice:

  • It only changes database, tables or columns that are not consistent with the given character set or collation.
  • It doesn't treat views as regular tables.
  • It avoids foreign key violation by turning off related checks.
  • Update variable values accordingly before running the above SQL statements.
    • Default collation utf8mb4_0900_ai_ci may not be friendly to your preferred language. For example, it consider half-width parentheses () the same as full-width parentheses (), and causes trouble in certain cases.
    • Run SHOW COLLATION WHERE CHARSET = 'utf8mb4' AND COLLATION LIKE 'utf8mb4%0900%'; and choose a suitable collation for your needs.
1

If you, like me, do not trust automation, this is how I have handled the problem.

First Stop digging!

Start with altering the default charset of new tables by changing the DB definition(like in all other answers):

ALTER DATABASE database_name 
  CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

Then generate sql to change the default charset for new columns of all existing tables:

SELECT concat("ALTER TABLE `",table_schema,"`.`",table_name,"` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_swedish_ci;") as _sql
  FROM information_schema.TABLES
 WHERE table_schema like "database_name" and TABLE_TYPE="BASE TABLE"
 GROUP BY table_schema, table_name ;

Now we can handle the "legacy"

List character datatypes you are using:

select distinct data_type  from information_schema.columns where table_schema = "database_name" and CHARACTER_SET_NAME is not null;

For me that list was "varchar" and "text"

List character_SETS_ in use:

select distinct character_set_name from information_schema.columns where table_schema = "database_name";

This gives me "utf8", "latin1", and "utf8mb4" which is a reason I do not trust automation, the latin1 columns risk having dirty data.

Now you can make a list of all columns you need to update with:

select table_name, column_name, data_type, character_set_name, collation_name
  from information_schema.columns 
 where table_schema = "database_name" and CHARACTER_SET_NAME is not null AND CHARACTER_SET_NAME <> "utf8mb4"
 group by table_name, data_type, character_set_name, collation_name;

Edit: Original syntax above had an error.

Tables containing only utf8 or utf8mb4 could be converted with "CONVERT TO CHARACTER SET" as Mathias and MrJingles describes above, but then you risk MySQL changing the types for you, so you may be better of running "CHANGE COLUMN" instead since that gives you control of exactly what happens.

If you have non-utf8 columns these questions may give inspiration about checking the columns data: https://stackoverflow.com/q/401771/671282 https://stackoverflow.com/q/9304485/671282

Since you probably know what you expect to have in most of the columns something like this will probably handle most of them after modifying the non-ascii chars allowed to suit your needs:

SELECT distinct section FROM table_name WHERE column_name NOT REGEXP '^([A-Za-z0-9åäöÅÄÖ&.,_ -])*$';

When the above did not fit I used the below that have a bit "fuzzier" maching:

SELECT distinct
  CONVERT(CONVERT(column_name USING BINARY) USING latin1) AS latin1,
  CONVERT(CONVERT(column_name USING BINARY) USING utf8) AS utf8
FROM table_name
WHERE CONVERT(column_name USING BINARY) RLIKE CONCAT('[', UNHEX('C0'), '-', UNHEX('F4'), '][',UNHEX('80'),'-',UNHEX('FF'),']') limit 5;

This query matches any two characters that could start an utf8-character, thus allowing you to inspect those records, it may give you a lot of false positives. The utf8 conversion fails returning null if there is any character it can not convert, so in a large field there is a good chance of it not being useful.

0

I made a script which does this more or less automatically:

<?php
/**
 * Requires php >= 5.5
 * 
 * Use this script to convert utf-8 data in utf-8 mysql tables stored via latin1 connection
 * This is a PHP port from: https://gist.github.com/njvack/6113127
 *
 * BACKUP YOUR DATABASE BEFORE YOU RUN THIS SCRIPT!
 *
 * Once the script ran over your databases, change your database connection charset to utf8:
 *
 * $dsn = 'mysql:host=localhost;port=3306;charset=utf8';
 * 
 * DON'T RUN THIS SCRIPT MORE THAN ONCE!
 *
 * @author hollodotme
 *
 * @author derclops since 2019-07-01
 *
 *         I have taken the liberty to adapt this script to also do the following:
 *
 *         - convert the database to utf8mb4
 *         - convert all tables to utf8mb4
 *         - actually then also convert the data to utf8mb4
 *
 */

header('Content-Type: text/plain; charset=utf-8');

$dsn      = 'mysql:host=localhost;port=3306;charset=utf8';
$user     = 'root';
$password = 'root';
$options  = [
    \PDO::ATTR_CURSOR                   => \PDO::CURSOR_FWDONLY,
    \PDO::MYSQL_ATTR_USE_BUFFERED_QUERY => true,
    \PDO::MYSQL_ATTR_INIT_COMMAND       => "SET CHARACTER SET latin1",
];


$dbManager = new \PDO( $dsn, $user, $password, $options );

$databasesToConvert = [ 'database1',/** database3, ... */ ];
$typesToConvert     = [ 'char', 'varchar', 'tinytext', 'mediumtext', 'text', 'longtext' ];

foreach ( $databasesToConvert as $database )
{
    echo $database, ":\n";
    echo str_repeat( '=', strlen( $database ) + 1 ), "\n";

    $dbManager->exec( "USE `{$database}`" );

    echo "converting database to correct locale too ... \n";

    $dbManager->exec("ALTER DATABASE `{$database}` CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci");


    $tablesStatement = $dbManager->query( "SHOW TABLES" );
    while ( ($table = $tablesStatement->fetchColumn()) )
    {
        echo "Table: {$table}:\n";
        echo str_repeat( '-', strlen( $table ) + 8 ), "\n";

        $columnsToConvert = [ ];

        $columsStatement = $dbManager->query( "DESCRIBE `{$table}`" );

        while ( ($tableInfo = $columsStatement->fetch( \PDO::FETCH_ASSOC )) )
        {
            $column = $tableInfo['Field'];
            echo ' * ' . $column . ': ' . $tableInfo['Type'];

            $type = preg_replace( "#\(\d+\)#", '', $tableInfo['Type'] );

            if ( in_array( $type, $typesToConvert ) )
            {
                echo " => must be converted\n";

                $columnsToConvert[] = $column;
            }
            else
            {
                echo " => not relevant\n";
            }
        }


        //convert table also!!!
        $convert = "ALTER TABLE `{$table}` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci";

        echo "\n", $convert, "\n";
        $dbManager->exec( $convert );
        $databaseErrors = $dbManager->errorInfo();
        if( !empty($databaseErrors[1]) ){
            echo "\n !!!!!!!!!!!!!!!!! ERROR OCCURED ".print_r($databaseErrors, true)." \n";
            exit;
        }


        if ( !empty($columnsToConvert) )
        {
            $converts = array_map(
                function ( $column )
                {
                    //return "`{$column}` = IFNULL(CONVERT(CAST(CONVERT(`{$column}` USING latin1) AS binary) USING utf8mb4),`{$column}`)";
                    return "`{$column}` = CONVERT(BINARY(CONVERT(`{$column}` USING latin1)) USING utf8mb4)";
                },
                $columnsToConvert
            );

            $query = "UPDATE IGNORE `{$table}` SET " . join( ', ', $converts );

            //alternative
            // UPDATE feedback SET reply = CONVERT(BINARY(CONVERT(reply USING latin1)) USING utf8mb4) WHERE feedback_id = 15015;


            echo "\n", $query, "\n";


            $dbManager->exec( $query );

            $databaseErrors = $dbManager->errorInfo();
            if( !empty($databaseErrors[1]) ){
                echo "\n !!!!!!!!!!!!!!!!! ERROR OCCURED ".print_r($databaseErrors, true)." \n";
                exit;
            }
        }

        echo "\n--\n";
    }

    echo "\n";
}

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.