Checking and changing character sets and collations in MySQL
MySQL commands to show the character sets of the server, the session and the databases, and to change the default of a database.
These commands show the character set and collation used by the server, the session and each database, and how to change the default character set of a database.
Server and session settings
SHOW VARIABLES LIKE 'character\_set\_%';
Sample output:
| Variable_name | Value |
|---|---|
| character_set_client | utf8mb3 |
| character_set_connection | utf8mb3 |
| character_set_database | utf8mb4 |
| character_set_filesystem | binary |
| character_set_results | utf8mb3 |
| character_set_server | utf8mb4 |
| character_set_system | utf8mb3 |
To read the values of the current session:
SELECT @@character_set_client;
SELECT @@character_set_connection;
SELECT @@character_set_results;
SELECT @@collation_connection;
SELECT @@collation_database;
Character set of each database
SELECT SCHEMA_NAME, CATALOG_NAME, default_character_set_name, default_collation_name
FROM information_schema.schemata
WHERE SCHEMA_NAME = 'AppDev';
Change the default character set of a database
Replace the database name and the character set with your values:
ALTER DATABASE your_db_name CHARACTER SET utf8mb4;
ALTER DATABASE AppDev CHARACTER SET latin1;
ALTER DATABASE AppProd CHARACTER SET latin1;
Available character sets and collations
SHOW CHARACTER SET;
SELECT * FROM information_schema.collations WHERE CHARACTER_SET_NAME = 'utf8';
SHOW COLLATION WHERE charset = 'utf8';
Settings from the shell
mysqladmin -u dba -p variables | grep -E 'character|collation' | tr -s ' '
Notes
ALTER DATABASEchanges the default for tables created later. Existing tables and columns keep their character set.- In MySQL 8.0,
utf8is an alias forutf8mb3, which stores at most 3 bytes per character. Useutf8mb4for the full Unicode range, such as emoji.