MySQL: changing database character set and collate

From lxadm | Linux administration tips, tutorials, HOWTOs and articles
Jump to: navigation, search

my.cnf changes[edit]

You can add these to your my.cnf file:

character_set_server           = utf8mb4
collation-server               = utf8mb4_unicode_ci
init-connect                   = 'SET NAMES utf8mb4'

Also, you might need to add these if converting to a wider character set (i.e. from utf8 to utf8mb4):

innodb_large_prefix            = 1
innodb_file_format             = Barracuda


Setting database character set and collate[edit]

To convert a database character set and collate (this will replace these values in db.opt file), use:

ALTER DATABASE some_db CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

The operation is non-blocking and is instant.

The change will only affect newly created tables in this database.


Setting table character set and collate[edit]

If you have any existing tables and you'd like to change their character set and collate, you can run this:

USE some_db;
ALTER TABLE some_table CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;


Script for setting converting character set and collate in all tables[edit]

To convert all tables in your database, you can run this script - note that the duration will depend on your database size and may block the queries:

#!/bin/bash

DATABASE=some_db

#CHARACTER_SET=utf8
#COLLATE=utf8_unicode_ci
CHARACTER_SET=utf8mb4
COLLATE=utf8mb4_unicode_ci

TABLES=$(echo SHOW TABLES | mysql -s $DATABASE)

for TABLE in $TABLES ; do
    echo "ALTER TABLE $TABLE CONVERT TO CHARACTER SET $CHARACTER_SET COLLATE $COLLATE;"
    echo "ALTER TABLE $TABLE CONVERT TO CHARACTER SET $CHARACTER_SET COLLATE $COLLATE" | mysql $DATABASE
done

The script assumes you can use mysql without specifying a password (i.e. password added to .my.cnf).