如何查看 MySQL 数据库/表/列是什么字符集？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1049728/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How do I see what character set a MySQL database / table / column is?
提问by Rory
What is the (default) charset for:
什么是（默认）字符集：
MySQL database
MySQL table
MySQL column
MySQL数据库
MySQL表
MySQL 列
回答by Zenshai
Here's how I'd do it -
这是我的做法 -
For Schemas (or Databases - they are synonyms):
对于模式（或数据库 - 它们是同义词）：
SELECT default_character_set_name FROM information_schema.SCHEMATA
WHERE schema_name = "schemaname";
For 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";
For Columns:
对于列：
SELECT character_set_name FROM information_schema.`COLUMNS`
WHERE table_schema = "schemaname"
AND table_name = "tablename"
AND column_name = "columnname";
回答by serhat
For columns:
对于列：
SHOW FULL COLUMNS FROM table_name;
回答by J. Polfer
回答by Nabeel Ahmed
For all the databasesyou have on the server:
对于服务器上的所有数据库：
mysql> SELECT SCHEMA_NAME 'database', default_character_set_name 'charset', DEFAULT_COLLATION_NAME 'collation' FROM information_schema.SCHEMATA;
Output:
输出：
+----------------------------+---------+--------------------+
| database | charset | collation |
+----------------------------+---------+--------------------+
| information_schema | utf8 | utf8_general_ci |
| my_database | latin1 | latin1_swedish_ci |
...
+----------------------------+---------+--------------------+
For a single Database:
对于单个数据库：
mysql> USE my_database;
mysql> show variables like "character_set_database";
Output:
输出：
+----------------------------+---------+
| Variable_name | Value |
+----------------------------+---------+
| character_set_database | latin1 |
+----------------------------+---------+
Getting the collation for Tables:
获取Tables的排序规则：
mysql> USE my_database;
mysql> SHOW TABLE STATUS WHERE NAME LIKE 'my_tablename';
OR - will output the complete SQL for create table:
OR - 将输出用于创建表的完整 SQL：
mysql> show create table my_tablename
mysql> show create table my_tablename
Getting the collation of columns:
获取列的整理：
mysql> SHOW FULL COLUMNS FROM my_tablename;
output:
输出：
+---------+--------------+--------------------+ ....
| field | type | collation |
+---------+--------------+--------------------+ ....
| id | int(10) | (NULL) |
| key | varchar(255) | latin1_swedish_ci |
| value | varchar(255) | latin1_swedish_ci |
+---------+--------------+--------------------+ ....
回答by Vex
For tables:
对于表：
SHOW TABLE STATUSwill list all the tables.
SHOW TABLE STATUS将列出所有表。
Filter using:
过滤使用：
SHOW TABLE STATUS where name like 'table_123';
回答by DC-
For databases:
对于数据库：
Just use these commands:
只需使用这些命令：
USE db_name;
SELECT @@character_set_database;
-- or:
-- SELECT @@collation_database;
回答by Eric
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
;
回答by chaos
I always just look at SHOW CREATE TABLE mydatabase.mytable.
我总是只是看着SHOW CREATE TABLE mydatabase.mytable。
For the database, it appears you need to look at SELECT DEFAULT_CHARACTER_SET_NAME FROM information_schema.SCHEMATA.
对于数据库，您似乎需要查看SELECT DEFAULT_CHARACTER_SET_NAME FROM information_schema.SCHEMATA.
回答by user1012513
To see default collation of the database:
要查看数据库的默认排序规则：
USE db_name;
SELECT @@character_set_database, @@collation_database;
To see collation of the table:
要查看表格的整理：
SHOW TABLE STATUS where name like 'table_name';
To see collation of the columns:
要查看列的排序规则：
SHOW FULL COLUMNS FROM table_name;
回答by James
For tablesand columns:
对于表和列：
show create table your_table_name
