MySQL: changing database character set and collate
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).