Skip to content

Instantly share code, notes, and snippets.

@poudelmadhav

poudelmadhav/charset-and-collation.md

Last active September 4, 2023 07:18
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save poudelmadhav/a921b27beaab0812a91966ad29454025 to your computer and use it in GitHub Desktop.
Save poudelmadhav/a921b27beaab0812a91966ad29454025 to your computer and use it in GitHub Desktop.
Download ZIP
View or change charset and collation of database and table in sql
Raw
charset-and-collation.md

View all databases charset and collation:

SELECT SCHEMA_NAME 'database', default_character_set_name 'charset', DEFAULT_COLLATION_NAME 'collation' FROM information_schema.SCHEMATA;

View specified database charset and collation:

USE db_name;
SELECT @@character_set_database, @@collation_database;

View specified table charset and collation:

SHOW TABLE STATUS WHERE NAME LIKE 'my_tablename';

OR - will output the complete SQL for create table:

SHOW CREATE TABLE my_tablename

View full columns collation:

SHOW FULL COLUMNS FROM my_tablename;

Change specified database charset and collation to utf8mb4 and utf8mb4_unicode_ci respectively:

ALTER DATABASE databasename CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

Change specified table charset and collation to utf8mb4 and utf8mb4_unicode_ci respectively:

ALTER TABLE tablename CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

References

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment