如何查看MySQL数据库/表/列的字符集？

我总是看SHOW CREATE TABLE mydatabase.mytable。

对于数据库，您似乎需要查看SELECT DEFAULT_CHARACTER_SET_NAME FROM information_schema.SCHEMATA。

对于表：

SHOW TABLE STATUS将列出所有表。

过滤器使用：

SHOW TABLE STATUS where name like 'table_123';

对于表和列：

show create table your_table_name

对于数据库：

USE your_database_name;
show variables like "character_set_database";
-- or:
-- show variables like "collation_database";

参考。此页面。并查看MySQL手册

我是这么做的-

对于模式（或数据库-它们是同义词）：

SELECT default_character_set_name FROM information_schema.SCHEMATA
WHERE schema_name = "schemaname";

对于表格：

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";

对于列：

SELECT character_set_name FROM information_schema.`COLUMNS`
WHERE table_schema = "schemaname"
AND table_name = "tablename"
AND column_name = "columnname";

对于列：

SHOW FULL COLUMNS FROM table_name;

SELECT TABLE_SCHEMA,
TABLE_NAME,
CCSA.CHARACTER_SET_NAME AS DEFAULT_CHAR_SET,
COLUMN_NAME,
COLUMN_TYPE,
C.CHARACTER_SET_NAME
FROM information_schema.TABLES AS T
JOIN information_schema.COLUMNS AS C USING (TABLE_SCHEMA, TABLE_NAME)
JOIN information_schema.COLLATION_CHARACTER_SET_APPLICABILITY AS CCSA
ON (T.TABLE_COLLATION = CCSA.COLLATION_NAME)
WHERE TABLE_SCHEMA=SCHEMA()
AND C.DATA_TYPE IN ('enum', 'varchar', 'char', 'text', 'mediumtext', 'longtext')
ORDER BY TABLE_SCHEMA,
TABLE_NAME,
COLUMN_NAME
;

对于数据库：

SHOW CREATE DATABASE "DB_NAME_HERE";

在创建数据库（MySQL）时，默认字符集/排序规则始终是LATIN，而不是在最初创建数据库时选择不同的

对于数据库：

只需使用这些命令：

USE db_name;
SELECT @@character_set_database;
-- or:
-- SELECT @@collation_database;

对于数据库：

SELECT SCHEMA_NAME 'database', default_character_set_name 'charset', DEFAULT_COLLATION_NAME 'collation' FROM information_schema.SCHEMATA;

输出示例：

mysql> SELECT SCHEMA_NAME 'database', default_character_set_name 'charset', DEFAULT_COLLATION_NAME 'collation' FROM information_schema.SCHEMATA;
+----------------------------+---------+--------------------+
| database                   | charset | collation          |
+----------------------------+---------+--------------------+
| information_schema         | utf8    | utf8_general_ci    |
| drupal_demo1               | utf8    | utf8_general_ci    |
| drupal_demo2               | utf8    | utf8_general_ci    |
| drupal_demo3               | utf8    | utf8_general_ci    |
| drupal_demo4               | utf8    | utf8_general_ci    |
| drupal_demo5               | latin1  | latin1_swedish_ci  |


...


+----------------------------+---------+--------------------+
55 rows in set (0.00 sec)


mysql>

#您在服务器上拥有的EYZ0：

mysql> SELECT SCHEMA_NAME 'database', default_character_set_name 'charset', DEFAULT_COLLATION_NAME 'collation' FROM information_schema.SCHEMATA;

输出：

+----------------------------+---------+--------------------+
| database                   | charset | collation          |
+----------------------------+---------+--------------------+
| information_schema         | utf8    | utf8_general_ci    |
| my_database                | latin1  | latin1_swedish_ci  |
...
+----------------------------+---------+--------------------+

对于单一数据库：

mysql> USE my_database;
mysql> show variables like "character_set_database";

输出：

    +----------------------------+---------+
| Variable_name              |  Value  |
+----------------------------+---------+
| character_set_database     |  latin1 |
+----------------------------+---------+

获取排序规则forTables：

mysql> USE my_database;
mysql> SHOW TABLE STATUS WHERE NAME LIKE 'my_tablename';

OR-将输出创建表的完整SQL：

mysql> show create table my_tablename

获取列的排序规则：

mysql> SHOW FULL COLUMNS FROM my_tablename;

输出：

+---------+--------------+--------------------+ ....
| field   | type         | collation          |
+---------+--------------+--------------------+ ....
| id      | int(10)      | (NULL)             |
| key     | varchar(255) | latin1_swedish_ci  |
| value   | varchar(255) | latin1_swedish_ci  |
+---------+--------------+--------------------+ ....

要查看数据库的默认排序规则：

USE db_name;
SELECT @@character_set_database, @@collation_database;

要查看表的排序规则：

SHOW TABLE STATUS where name like 'table_name';

要查看列的排序规则：

SHOW FULL COLUMNS FROM table_name;

查看表的默认字符集

SHOW CREATE TABLE table_name;

正如许多人早些时候写的那样，Show FULL COLUMNS应该是获取列信息的首选方法。 缺少的是一种在不直接到达元数据表的情况下获取字符集的方法：

SHOW FULL COLUMNS FROM my_table WHERE Field = 'my_field'
SHOW COLLATION WHERE Collation = 'collation_you_got'

对于数据库： USE db_name; SELECT @@character_set_database;

显示全局变量，其中variable_namecharacter_set_%或variable_name排序规则%

创建新数据库时，将生成一些必要的表

在“information_schema”这条路上

"COLUMNS"->关于列

"表"->关于表

例如，如果您需要查看表中的所有列名和类型

SELECT COLUMN_NAME,COLUMN_TYPE FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = 'your_schema_name' AND TABLE_NAME = 'your_table_name'