MySQL: Consultar el charset y collation

Conceptos

El charset es un conjunto de símbolos y codificaciones, es decir, la forma en que la base de datos guarda internamente los datos. Mientras que el collation es el conjunto de reglas que se aplican para comparar caracteres en un charset, es decir, indica a la base de datos como debe comparar los datos.

A continuación hago una traducción libre de un extracto de la documentación de MySQL Character Sets and Collations in General:

Suponga que tenemos un alfabeto de cuatro letras: A, B, a, b. Asignamos a cada letra un número: A = 0, B = 1, a = 2, b = 3. La letra A es el símbolo, el número 0 es el código para A y la combinación de las cuatro letras y sus códigos es el juego de caracteres (charset).

Suponga que queremos comparar dos cadenas de valores, A y B. La forma más sencilla de hacerlo es mirar sus códigos: 0 para A y 1 para B. Como 0 es menor que 1, diremos que A es menor que B. Lo que acabamos de hacer, es aplicar un collation a nuestro charset. El collation es un juego de reglas (en este caso solo una): “campara los códigos”. Llamamos a este collation, el más simple de todos los collation posible, collation binario (binary collation).

Pero, ¿qué ocurre si queremos decir que las minúsculas y las mayúsculas son equivalente? Entonces nosotros tenemos al menos dos reglas: (1) tratar la letras minúsculas a y b como equivalente de A y B; (2) compara los códigos. Llamamos a este collation insensible a mayúsculas y minúsculas (case insensitive) y es un poco más complejo que un binary collation.

Consultar el charset y collation de una base de datos

1
2
3
4
5
6
7
8
SELECT
schema_name AS 'database',
default_character_set_name AS 'charset',
default_collation_name AS 'collation'
FROM
information_schema.SCHEMATA
WHERE
schema_name = "XXXXX";

Donde XXXXX es el nombre de la base de datos sobre la cual se ejecuta la consulta. A continuación un ejemplo para la base de datos nextcloud:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
MariaDB [(none)]> SELECT
-> schema_name AS 'database',
-> default_character_set_name AS 'charset',
-> default_collation_name AS 'collation'
-> FROM
-> information_schema.SCHEMATA
-> WHERE
-> schema_name = "nextcloud";
+-----------+---------+--------------------+
| database | charset | collation |
+-----------+---------+--------------------+
| nextcloud | utf8mb4 | utf8mb4_general_ci |
+-----------+---------+--------------------+
1 row in set (0.00 sec)

Consultar el charset y collate de todas las bases de datos

1
2
3
4
5
6
SELECT
schema_name AS 'database',
default_character_set_name AS 'charset',
default_collation_name 'collation'
FROM
information_schema.SCHEMATA;

A continuación un ejemplo:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
MariaDB [(none)]> SELECT
-> schema_name AS 'database',
-> default_character_set_name AS 'charset',
-> default_collation_name 'collation'
-> FROM
-> information_schema.SCHEMATA;
+--------------------+---------+--------------------+
| database | charset | collation |
+--------------------+---------+--------------------+
| amavisd | utf8 | utf8_general_ci |
| information_schema | utf8 | utf8_general_ci |
| iredadmin | utf8 | utf8_general_ci |
| iredapd | utf8 | utf8_general_ci |
| itopdb | latin1 | latin1_swedish_ci |
| itopdbqa | latin1 | latin1_swedish_ci |
| mysql | latin1 | latin1_swedish_ci |
| nextcloud | utf8mb4 | utf8mb4_general_ci |
| performance_schema | utf8 | utf8_general_ci |
| roundcubemail | utf8 | utf8_general_ci |
| sa_bayes | latin1 | latin1_swedish_ci |
| vmail | utf8 | utf8_general_ci |
+--------------------+---------+--------------------+
12 rows in set (0.00 sec)

Consultar el charset y collation de todas las tablas de una base de datos

1
2
3
4
5
6
7
8
9
10
11
12
SELECT
T.table_schema AS 'database',
T.table_name AS 'table',
CCSA.character_set_name AS 'charset',
CCSA.collation_name AS 'collation'
FROM
information_schema.TABLES T,
information_schema.collation_character_set_applicability CCSA
WHERE
CCSA.collation_name = T.table_collation
AND T.table_schema = 'XXXXX'
AND T.table_name IN (SELECT table_name FROM information_schema.tables WHERE table_schema = 'XXXXX');

Donde XXXXX es el nombre de la base de datos sobre la cual se ejecuta la consulta. A continuación un ejemplo para la base de datos amavisd:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
MariaDB [(none)]> SELECT
-> T.table_schema AS 'database',
-> T.table_name AS 'table',
-> CCSA.character_set_name AS 'charset',
-> CCSA.collation_name AS 'collation'
-> FROM
-> information_schema.TABLES T,
-> information_schema.collation_character_set_applicability CCSA
-> WHERE
-> CCSA.collation_name = T.table_collation
-> AND T.table_schema = 'amavisd'
-> AND T.table_name IN (SELECT table_name FROM information_schema.tables WHERE table_schema = 'amavisd');
+----------+-----------------+---------+-----------------+
| database | table | charset | collation |
+----------+-----------------+---------+-----------------+
| amavisd | maddr | utf8 | utf8_general_ci |
| amavisd | mailaddr | utf8 | utf8_general_ci |
| amavisd | msgrcpt | utf8 | utf8_general_ci |
| amavisd | msgs | utf8 | utf8_general_ci |
| amavisd | outbound_wblist | utf8 | utf8_general_ci |
| amavisd | policy | utf8 | utf8_general_ci |
| amavisd | quarantine | utf8 | utf8_general_ci |
| amavisd | users | utf8 | utf8_general_ci |
| amavisd | wblist | utf8 | utf8_general_ci |
+----------+-----------------+---------+-----------------+
9 rows in set (0.00 sec)

Entradas de interés

Contenidos
  1. 1. Conceptos
  2. 2. Consultar el charset y collation de una base de datos
  3. 3. Consultar el charset y collate de todas las bases de datos
  4. 4. Consultar el charset y collation de todas las tablas de una base de datos