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