LLI Wiki — My Digital Knowledge Base

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

Mastodon LLIMastodon SocialFosstodon