Quel est le charset (par défaut) pour:
Base de données MySQL
Table MySQL
Colonne MySQL
Quel est le charset (par défaut) pour:
Base de données MySQL
Table MySQL
Colonne MySQL
Voici comment je le ferais -
Pour les schémas:
SELECT default_character_set_name FROM information_schema.SCHEMATA
WHERE schema_name = "schemaname";
Pour les tables:
SELECT CCSA.character_set_name FROM information_schema.`TABLES` T,
information_schema.`COLLATION_CHARACTER_SET_APPLICABILITY` CCSA
WHERE CCSA.collation_name = T.table_collation
AND T.table_schema = "schemaname"
AND T.table_name = "tablename";
Pour les colonnes:
SELECT character_set_name FROM information_schema.`COLUMNS`
WHERE table_schema = "schemaname"
AND table_name = "tablename"
AND column_name = "columnname";
Pour colonnes:
SHOW FULL COLUMNS FROM table_name;
Pour des bases:
USE your_database_name;
show variables like "character_set_database";
-- or:
-- show variables like "collation_database";
Cf. cette page. Et consultez le manuel de MySQL
Pour toutes les bases de données vous avez sur le serveur:
mysql> SELECT SCHEMA_NAME 'database', default_character_set_name 'charset', DEFAULT_COLLATION_NAME 'collation' FROM information_schema.SCHEMATA;
Sortie:
+----------------------------+---------+--------------------+
| database | charset | collation |
+----------------------------+---------+--------------------+
| information_schema | utf8 | utf8_general_ci |
| my_database | latin1 | latin1_swedish_ci |
...
+----------------------------+---------+--------------------+
Pour un base de données unique:
mysql> USE my_database;
mysql> show variables like "character_set_database";
Sortie:
+----------------------------+---------+
| Variable_name | Value |
+----------------------------+---------+
| character_set_database | latin1 |
+----------------------------+---------+
Obtenir la collation pour les tables:
mysql> USE my_database;
mysql> SHOW TABLE STATUS WHERE NAME LIKE 'my_tablename';
OU - affichera le code SQL complet pour la table de création:
mysql> show create table my_tablename
Obtenir la collation de colonnes:
mysql> SHOW FULL COLUMNS FROM my_tablename;
sortie:
+---------+---------+--------------------+ ....
| field | type | collation |
+---------+---------+--------------------+ ....
| id | utf8 | (NULL) |
| key | utf8 | latin1_swedish_ci |
| value | utf8 | latin1_swedish_ci |
+---------+---------+--------------------+ ....
Pour les tables:
SHOW TABLE STATUS listera toutes les tables.
Filtrer en utilisant:
SHOW TABLE STATUS where name like 'table_123';
Pour des bases:
Utilisez simplement ces commandes:
USE db_name;
SELECT @@character_set_database;
-- or:
-- SELECT @@collation_database;
SELECT TABLE_SCHEMA,
NOM DE LA TABLE,
CCSA.CHARACTER_SET_NAME AS DEFAULT_CHAR_SET,
NOM DE COLONNE,
COLUMN_TYPE,
C.CHARACTER_SET_NAME
FROM information_schema.TABLES AS T
JOIN informations_schema.COLUMNS EN UTILISANT C (TABLE_SCHEMA, TABLE_NAME)
JOIN information_schema.COLLATION_CHARACTER_SET_APPLICABILITY AS CCSA
ON (T.TABLE_COLLATION = CCSA.COLLATION_NAME)
WHERE TABLE_SCHEMA = SCHEMA ()
ET C.DATA_TYPE IN ('enum', 'varchar', 'char', 'texte', 'mediumtext', 'longtext')
COMMANDE PAR TABLE_SCHEMA,
NOM DE LA TABLE,
NOM DE COLONNE
;
Je regarde toujours SHOW CREATE TABLE mydatabase.mytable.
Pour la base de données, il semble que vous deviez regarder SELECT DEFAULT_CHARACTER_SET_NAME FROM information_schema.SCHEMATA.