Description\n-----------\n\nThe default character set is utf8mb4 and the default collation is utf8mb4_uca1400_ai_ci.\\\nThis may differ in some distros, see for example Differences in MariaDB in Debian.\n\nThe default character set is latin1 and the default collation is latin1_swedish_ci.\\\nThis may differ in some distros, see for example Differences in MariaDB in Debian.\n\nIn MariaDB 11.6, the default character set changed from latin1 to utf8mb4.\n\nWhen upgrading to 11.6 or above from a previous release series, this can lead to behavior different from what you've been seeing in the old version.\n\nSee this section for details, including the impact on replicating to older MariaDB (or MySQL) slaves.\n\nThe character sets and the collations can be specified from the server right down to the column level, as well as for client-server connections. When changing a character set and not specifying a collation, the default collation for the new character set is always used.\n\nCharacter 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. It's therefore possible to have extremely fine-grained control over all the character sets and collations used in your data.\n\nDefault 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:\n\n``sql\nSHOW COLLATION LIKE 'latin2%';\n+---------------------+---------+----+---------+----------+---------+\n| Collation           | Charset | Id | Default | Compiled | Sortlen |\n+---------------------+---------+----+---------+----------+---------+\n| latin2_czech_cs     | latin2  |  2 |         | Yes      |       4 |\n| latin2_general_ci   | latin2  |  9 | Yes     | Yes      |       1 |\n| latin2_hungarian_ci | latin2  | 21 |         | Yes      |       1 |\n| latin2_croatian_ci  | latin2  | 27 |         | Yes      |       1 |\n| latin2_bin          | latin2  | 77 |         | Yes      |       1 |\n+---------------------+---------+----+---------+----------+---------+\n`\n\nServer Level\n\nThe character_set_server system variable can be used to change the default server character set. It can be set both on startup or dynamically, with the SET command:\n\n`sql\nSET character_set_server = 'latin2';\n`\n\nSimilarly, the collation_server variable is used for setting the default server collation.\n\n`sql\nSET collation_server = 'latin2_czech_cs';\n`\n\nDatabase Level\n\nThe CREATE DATABASE and ALTER DATABASE statements have optional character set and collation clauses. If these are left out, the server defaults are used.\n\n`sql\nCREATE DATABASE czech_slovak_names \n  CHARACTER SET = 'keybcs2'\n  COLLATE = 'keybcs2_bin';\n`\n\n`sql\nALTER DATABASE czech_slovak_names COLLATE = 'keybcs2_general_ci';\n`\n\nTo determine the default character set used by a database, use:\n\n`sql\nSHOW CREATE DATABASE czech_slovak_names;\n+--------------------+--------------------------------------------------------------------------------+\n| Database           | Create Database                                                                |\n+--------------------+--------------------------------------------------------------------------------+\n| czech_slovak_names | CREATE DATABASE czech_slovak_names /!40100 DEFAULT CHARACTER SET keybcs2 / |\n+--------------------+--------------------------------------------------------------------------------+\n`\n\nAlternatively, for the character set and collation:\n\n`sql\nSELECT  FROM INFORMATION_SCHEMA.SCHEMATA;\n+--------------+--------------------+----------------------------+------------------------+----------+\n| CATALOG_NAME | SCHEMA_NAME        | DEFAULT_CHARACTER_SET_NAME | DEFAULT_COLLATION_NAME | SQL_PATH |\n+--------------+--------------------+----------------------------+------------------------+----------+\n| def          | czech_slovak_names | keybcs2                    | keybcs2_general_ci     | NULL     |\n| def          | information_schema | utf8                       | utf8_general_ci        | NULL     |\n| def          | mysql              | latin1                     | latin1_swedish_ci      | NULL     |\n| def          | performance_schema | utf8                       | utf8_general_ci        | NULL     |\n| def          | test               | latin1                     | latin1_swedish_ci      | NULL     |\n+--------------+--------------------+----------------------------+------------------------+----------+\n`\n\nIt 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.\n\n`sql\nCREATE DATABASE danish_names COLLATE 'utf8_danish_ci';\n\nSHOW CREATE DATABASE danish_names;\n+--------------+----------------------------------------------------------------------------------------------+\n| Database     | Create Database                                                                              |\n+--------------+----------------------------------------------------------------------------------------------+\n| danish_names | CREATE DATABASE danish_names /!40100 DEFAULT CHARACTER SET utf8 COLLATE utf8_danish_ci / |\n+--------------+----------------------------------------------------------------------------------------------+\n`\n\nAlthough 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.\n\nTable Level\n\nThe CREATE TABLE and ALTER TABLE statements support optional character set and collation clauses, a MariaDB and MySQL extension to standard SQL.\n\n`sql\nCREATE TABLE english_names (id INT, name VARCHAR(40)) \n  CHARACTER SET 'utf8' \n  COLLATE 'utf8_icelandic_ci';\n`\n\nIf neither character set nor collation is provided, the database default will be used. If only the character set is provided, the default collation for that character set will be used . If only the collation is provided, the associated character set will be used. See Supported Character Sets and Collations.\n\n`sql\nALTER TABLE table_name\n CONVERT TO CHARACTER SET charset_name [COLLATE collation_name];\n`\n\nIf no collation is provided, the collation will be set to the default collation for that character set. See Supported Character Sets and Collations.\n\nFor VARCHAR or TEXT columns, CONVERT TO CHARACTER SET changes the data type if needed to ensure the new column is long enough to store as many characters as the original column.\n\nFor example, an ascii TEXT column requires a single byte per character, so the column can hold up to 65,535 characters. If the column is converted to utf8mb4, 4 bytes can be required for each character, so the column will be converted to MEDIUMTEXT to be able to hold the same number of characters.\n\nCONVERT TO CHARACTER SET binary will convert CHAR, VARCHAR and TEXT columns to BINARY, VARBINARY and BLOB respectively, and from that point will no longer have a character set, or be affected by future CONVERT TO CHARACTER SET statements.\n\nTo avoid data type changes resulting from CONVERT TO CHARACTER SET, use MODIFY on the individual columns instead. For example:\n\n`sql\nALTER TABLE table_name MODIFY ascii_text_column TEXT CHARACTER SET utf8;\nALTER TABLE table_name MODIFY ascii_varchar_column VARCHAR(M) CHARACTER SET utf8;\n`\n\nColumn Level\n\nCharacter sets and collations can also be specified for columns that are character types CHAR, TEXT or VARCHAR. The CREATE TABLE and ALTER TABLE statements support optional character set and collation clauses for this purpose - unlike those at the table level, the column level definitions are standard SQL.\n\n`sql\nCREATE TABLE european_names (\n  croatian_names VARCHAR(40) COLLATE 'cp1250_croatian_ci',\n  greek_names VARCHAR(40) CHARACTER SET 'greek');\n`\n\nIf neither collation nor character set is provided, the table default is used. If only the character set is specified, that character set's default collation is used, while if only the collation is specified, the associated character set is used.\n\nWhen using ALTER TABLE to change a column's character set, you need to ensure the character sets are compatible with your data. MariaDB will map the data as best it can, but it's possible to lose data if care is not taken.\n\nThe SHOW CREATE TABLE statement or INFORMATION SCHEMA database can be used to determine column character sets and collations.\n\n`sql\nSHOW CREATE TABLE european_names\\G\n*********************** 1. row ***********************\n       Table: european_names\nCreate Table: CREATE TABLE european_names (\n  croatian_names varchar(40) CHARACTER SET cp1250 COLLATE cp1250_croatian_ci DEFAULT NULL,\n  greek_names varchar(40) CHARACTER SET greek DEFAULT NULL\n) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_danish_ci\n`\n\n`sql\nSELECT  FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME LIKE 'european%'\\G\n************************ 1. row *********************\n           TABLE_CATALOG: def\n            TABLE_SCHEMA: danish_names\n              TABLE_NAME: european_names\n             COLUMN_NAME: croatian_names\n        ORDINAL_POSITION: 1\n          COLUMN_DEFAULT: NULL\n             IS_NULLABLE: YES\n               DATA_TYPE: varchar\nCHARACTER_MAXIMUM_LENGTH: 40\n  CHARACTER_OCTET_LENGTH: 40\n       NUMERIC_PRECISION: NULL\n           NUMERIC_SCALE: NULL\n      DATETIME_PRECISION: NULL\n      CHARACTER_SET_NAME: cp1250\n          COLLATION_NAME: cp1250_croatian_ci\n             COLUMN_TYPE: varchar(40)\n              COLUMN_KEY: \n                   EXTRA: \n              PRIVILEGES: select,insert,update,references\n          COLUMN_COMMENT: \n********************* 2. row ***********************\n           TABLE_CATALOG: def\n            TABLE_SCHEMA: danish_names\n              TABLE_NAME: european_names\n             COLUMN_NAME: greek_names\n        ORDINAL_POSITION: 2\n          COLUMN_DEFAULT: NULL\n             IS_NULLABLE: YES\n               DATA_TYPE: varchar\nCHARACTER_MAXIMUM_LENGTH: 40\n  CHARACTER_OCTET_LENGTH: 40\n       NUMERIC_PRECISION: NULL\n           NUMERIC_SCALE: NULL\n      DATETIME_PRECISION: NULL\n      CHARACTER_SET_NAME: greek\n          COLLATION_NAME: greek_general_ci\n             COLUMN_TYPE: varchar(40)\n              COLUMN_KEY: \n                   EXTRA: \n              PRIVILEGES: select,insert,update,references\n          COLUMN_COMMENT:\n`\n\nFilenames\n\nThe character_set_filesystem system variable has controlled interpretation of file names that are given as literal strings. This affects the following statements and functions:\n\n SELECT INTO DUMPFILE\n SELECT INTO OUTFILE\n LOAD DATA INFILE\n LOAD XML\n LOAD_FILE()\n\nLiterals\n\nBy default, the character set and collation used for literals is determined by the character_set_connection and collation_connection system variables. However, they can also be specified explicitly:\n\n`sql\n[_charset_name]'string' [COLLATE collation_name]\n`\n\nThe character set of string literals that do not have a character set introducer is determined by the character_set_connection system variable.\n\nThis query always returns the same character set name in both columns.:\n\n`sql\nSELECT CHARSET('a'), @@character_set_connection;\n`\n\ncharacter_set_client and character_set_connection are normally (e.g. during handshake, or after a SET NAMES` query) are set to equal values. However, it's possible to set to different values.\n\nExamples\n--------\n\nCREATE TABLE t1 (a VARCHAR(10)) CHARACTER SET utf8 COLLATE utf8_general_ci;\nINSERT INTO t1 VALUES ('oe'),('ö');\n\nURL: https://mariadb.com/docs/server/reference/data-types/string-data-types/character-sets/setting-character-sets-and-collations