Impostare i set di caratteri e le collation
In MariaDB, il set di caratteri predefinito è latin1, mentre la collation predefinita è latin1_swedish_ci. Il set di caratteri e la collation possono essere specificati a livello di server e, via via verso i livelli più specifici, fino al livello di colonna, ma anche a livello di connessione client-server. Quando si cambia il set di caratteri senza specificare una collation, viene utilizzata quella di default. Il set di caratteri e la collation possono essere visualizzati con l'istruzione SHOW COLLATION. Per esempio, se si vuole conoscere la collation di default di latin2:
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 | +---------------------+---------+----+---------+----------+---------+
Quando si cambia set di caratteri o collation la modifica si propaga verso il basso, quindi ad esempio una colonna per la quale non è stata specificata una collation utilizzerà quella di default della tabella se esiste, la tabella userà quella del database se esiste, e il database quella del server. E' quindi possibile avere un controllo estremamente granulare sui set di caratteri e le collation utilizzati.
Server level
The 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:
SET character_set_server = 'latin2';
Similarly, the collation_server variable is used for setting the default server collation.
SET collation_server = 'latin2_czech_cs';
Database level
The CREATE DATABASE and ALTER DATABASE statements have optional character set and collation clauses. If these are left out, the server defaults are used.
CREATE DATABASE czech_slovak_names CHARACTER SET = 'keybcs2' COLLATE = 'keybcs2_bin';
ALTER DATABASE czech_slovak_names COLLATE = 'keybcs2_general_ci';
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 */ | +--------------------+--------------------------------------------------------------------------------+
or alternatively, for the character set and collation:
SELECT * FROM INFORMATION_SCHEMA.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 | +--------------+--------------------+----------------------------+------------------------+----------+
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.
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 */ | +--------------+----------------------------------------------------------------------------------------------+
Although 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.
Table level
The CREATE TABLE and ALTER TABLE statements support optional character set and collation clauses, a MariaDB and MySQL extension to standard SQL.
CREATE TABLE english_names (id INT, name VARCHAR(40)) CHARACTER SET 'utf8' COLLATE 'utf8_icelandic_ci';
If 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.
Column level
Character 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.
CREATE TABLE european_names ( croatian_names VARCHAR(40) COLLATE 'cp1250_croatian_ci', greek_names VARCHAR(40) CHARACTER SET 'greek');
If 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.
When 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.
The SHOW CREATE TABLE statement or INFORMATION SCHEMA database can be used to determine column character sets and collations.
SHOW CREATE TABLE european_names\G
*************************** 1. row ***************************
Table: european_names
Create Table: CREATE TABLE `european_names` (
`croatian_names` varchar(40) CHARACTER SET cp1250 COLLATE cp1250_croatian_ci DEFAULT NULL,
`greek_names` varchar(40) CHARACTER SET greek DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_danish_ci
SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME LIKE 'european%'\G
*************************** 1. row ***************************
TABLE_CATALOG: def
TABLE_SCHEMA: danish_names
TABLE_NAME: european_names
COLUMN_NAME: croatian_names
ORDINAL_POSITION: 1
COLUMN_DEFAULT: NULL
IS_NULLABLE: YES
DATA_TYPE: varchar
CHARACTER_MAXIMUM_LENGTH: 40
CHARACTER_OCTET_LENGTH: 40
NUMERIC_PRECISION: NULL
NUMERIC_SCALE: NULL
DATETIME_PRECISION: NULL
CHARACTER_SET_NAME: cp1250
COLLATION_NAME: cp1250_croatian_ci
COLUMN_TYPE: varchar(40)
COLUMN_KEY:
EXTRA:
PRIVILEGES: select,insert,update,references
COLUMN_COMMENT:
*************************** 2. row ***************************
TABLE_CATALOG: def
TABLE_SCHEMA: danish_names
TABLE_NAME: european_names
COLUMN_NAME: greek_names
ORDINAL_POSITION: 2
COLUMN_DEFAULT: NULL
IS_NULLABLE: YES
DATA_TYPE: varchar
CHARACTER_MAXIMUM_LENGTH: 40
CHARACTER_OCTET_LENGTH: 40
NUMERIC_PRECISION: NULL
NUMERIC_SCALE: NULL
DATETIME_PRECISION: NULL
CHARACTER_SET_NAME: greek
COLLATION_NAME: greek_general_ci
COLUMN_TYPE: varchar(40)
COLUMN_KEY:
EXTRA:
PRIVILEGES: select,insert,update,references
COLUMN_COMMENT:
Literals
Character sets and collations can also be specified for character literals.
[_charset_name]'string' [COLLATE collation_name]
For example:
SELECT _latin2 'Müller'; +-----------+ | MĂźller | +-----------+ | MĂźller | +-----------+
SELECT 'Mueller' = 'Müller' COLLATE 'latin1_german2_ci'; +---------------------------------------------------+ | 'Mueller' = 'Müller' COLLATE 'latin1_german2_ci' | +---------------------------------------------------+ | 1 | +---------------------------------------------------+
