以下是:
MySQL 数据库的 (默认) 字符集
MySQL 表的 (默认) 字符集
MySQL 列的 (默认) 字符集
以下是:
MySQL 数据库的 (默认) 字符集
MySQL 表的 (默认) 字符集
MySQL 列的 (默认) 字符集
SELECT default_character_set_name FROM information_schema.SCHEMATA
WHERE schema_name = "mydatabasename";
关于表格:
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 = "mydatabasename"
AND T.table_name = "tablename";
对于列:
SELECT character_set_name FROM information_schema.`COLUMNS`
WHERE table_schema = "mydatabasename"
AND table_name = "tablename"
AND column_name = "columnname";
schemaname
感到困惑。实际上它只是一个数据库名称
:) - Paul Basenko对于列:
SHOW FULL COLUMNS FROM table_name;
latin1_swedish_ci
,那么字符集(charset)除了latin1
之外就不可能是其他任何东西了。如果排序规则(collation)是utf8mb4_general_ci
,那么字符集(charset)除了utf8mb4
之外就不可能是其他任何东西了。 - Pacerier对于数据库:
USE your_database_name;
show variables like "character_set_database";
-- or:
-- show variables like "collation_database";
请参阅此页面。并查看MySQL手册。
对于服务器上所有的数据库:
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 |
+----------------------------+---------+
获取表的排序规则:
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 |
+---------+--------------+--------------------+ ....
对于表格:
SHOW TABLE STATUS
将列出所有的表。
可以使用以下筛选条件:
SHOW TABLE STATUS where name like 'table_123';
查看数据库的默认排序规则:
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;
对于数据库:
只需使用这些命令:
USE db_name;
SELECT @@character_set_database;
-- or:
-- SELECT @@collation_database;
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 ;这段代码查询数据库中所有表的信息,包括表名、列名、默认字符集等。它只返回那些数据类型为枚举、字符串、文本等的列,并按照表名和列名进行排序。请注意保留原有的HTML标签。
我通常只查看 SHOW CREATE TABLE mydatabase.mytable
。
对于数据库,似乎需要查看 SELECT DEFAULT_CHARACTER_SET_NAME FROM information_schema.SCHEMATA
。
select default_character_set_name from information_schema.schemata
不够用,因为你无法确定哪一行与哪个数据库相关联。请使用 select default_character_set_name,schema_name from information_schema.schemata
或简单地使用 **select*from information_schema.schemata
**。 - PacerierSELECT * FROM information_schema.SCHEMATA WHERE SCHEMA_NAME = '<database-name>' \G;
它运行得非常好 :)谢谢! - Sankalp对于表和列:
show create table your_table_name
SHOW CREATE TABLE
,如 https://stackoverflow.com/questions/57628217/how-can-i-figure-out-the-default-charset-collation-in-my-mysql 中所讨论的那样。 - Rick James