If no collation is provided, the collation will be set to the default collation for that character set. ALTER TABLE table_name CONVERT TO CHARACTER SET charset_name See Supported Character Sets and Collations. If only the collation is provided, the associated character set will be used. If only the character set is provided, the default collation for that character set will be used. If neither character set nor collation is provided, the database default will be used. CREATE TABLE english_names ( id INT, name VARCHAR ( 40 )) CHARACTER SET 'utf8' COLLATE 'utf8_icelandic_ci' The CREATE TABLE and ALTER TABLE statements support optional character set and collation clauses, a MariaDB and MySQL extension to standard SQL. CREATE DATABASE danish_names COLLATE 'utf8_danish_ci' SHOW CREATE DATABASE danish_names + -+-+ | Database | Create Database | + -+-+ | danish_names | CREATE DATABASE ` danish_names ` /*!40100 DEFAULT CHARACTER SET utf8 COLLATE utf8_danish_ci */ | + -+-+Īlthough there are character_set_database and collation_database system variables which can be set dynamically, these are used for determining the character set and collation for the default database, and should only be set by the server. It is also possible to specify only the collation, and, since each collation only applies to one character set, the associated character set will automatically be specified. SCHEMATA + -+-+-+-+-+ | CATALOG_NAME | SCHEMA_NAME | DEFAULT_CHARACTER_SET_NAME | DEFAULT_COLLATION_NAME | SQL_PATH | + -+-+-+-+-+ | def | czech_slovak_names | keybcs2 | keybcs2_general_ci | NULL | | def | information_schema | utf8 | utf8_general_ci | NULL | | def | mysql | latin1 | latin1_swedish_ci | NULL | | def | performance_schema | utf8 | utf8_general_ci | NULL | | def | test | latin1 | latin1_swedish_ci | NULL | + -+-+-+-+-+ Or alternatively, for the character set and collation: SELECT * FROM INFORMATION_SCHEMA. To determine the default character set used by a database, use: SHOW CREATE DATABASE czech_slovak_names + -+-+ | Database | Create Database | + -+-+ | czech_slovak_names | CREATE DATABASE ` czech_slovak_names ` /*!40100 DEFAULT CHARACTER SET keybcs2 */ | + -+-+ CREATE DATABASE czech_slovak_names CHARACTER SET = 'keybcs2' COLLATE = 'keybcs2_bin' ALTER DATABASE czech_slovak_names COLLATE = 'keybcs2_general_ci' If these are left out, the server defaults are used.
The CREATE DATABASE and ALTER DATABASE statements have optional character set and collation clauses. SET collation_server = 'latin2_czech_cs' Database Level Similarly, the collation_server variable is used for setting the default server collation.
It can be set both on startup or dynamically, with the SET command: SET character_set_server = 'latin2' The character_set_server system variable can be used to change the default server character set. It's therefore possible to have extremely fine-grained control over all the character sets and collations used in your data.ĭefault collations for each character set can be viewed with the SHOW COLLATION statement, for example, to find the default collation for the latin2 character set: SHOW COLLATION LIKE 'latin2%' + -+-+-+-+-+-+ | Collation | Charset | Id | Default | Compiled | Sortlen | + -+-+-+-+-+-+ | latin2_czech_cs | latin2 | 2 | | Yes | 4 | | latin2_general_ci | latin2 | 9 | Yes | Yes | 1 | | latin2_hungarian_ci | latin2 | 21 | | Yes | 1 | | latin2_croatian_ci | latin2 | 27 | | Yes | 1 | | latin2_bin | latin2 | 77 | | Yes | 1 | + -+-+-+-+-+-+ Server Level When changing a character set and not specifying a collation, the default collation for the new character set is always used.Ĭharacter sets and collations always cascade down, so a column without a specified collation will look for the table default, the table for the database, and the database for the server. Both character sets and collations can be specified from the server right down to the column level, as well as for client-server connections. In MariaDB, the default character set is latin1, and the default collation is latin1_swedish_ci (however this may differ in some distros, see for example Differences in MariaDB in Debian).
Example: Changing the Default Character Set To UTF-8.