MySQL: changing database character set and collate

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

my.cnf changes

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

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

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

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