如何查看MySQL数据库/表/列的字符集是什么？-Java 学习之路

547

什么是（默认）字符集：

MySQL数据库
MySQL表
MySQL专栏

13 回答

202

我总是只看 SHOW CREATE TABLE mydatabase.mytable .

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

回复于 2024-05-05T17:27:00+08:00
60
对于 tables 和 columns ：
```
show create table your_table_name
```
回复于 2024-05-05T17:27:00+08:00
28
对于 tables ：

SHOW TABLE STATUS 将列出所有表格 .

过滤使用：
```
SHOW TABLE STATUS where name like 'table_123';
```
回复于 2024-05-05T17:27:00+08:00

624

对于 databases ：

只需使用以下命令：

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

回复于 2024-05-05T17:27:00+08:00

418

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
;

回复于 2024-05-05T17:27:00+08:00

这是我怎么做的 -

对于架构：

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

回复于 2024-05-05T17:27:00+08:00

97
对于 columns ：
```
SHOW FULL COLUMNS FROM table_name;
```
回复于 2024-05-05T17:27:00+08:00

对于 databases ：

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

参看this page . 并查看MySQL手册

回复于 2024-05-05T17:27:00+08:00

For all the databases 您在服务器上：

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  |
...
+----------------------------+---------+--------------------+

对于 single Database ：

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

输出：

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

获得整理 for Tables ：

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

或 - 将输出create table的完整SQL：

mysql> show create table my_tablename

得到 columns 的整理：

mysql> SHOW FULL COLUMNS FROM my_tablename;

输出：

+---------+---------+--------------------+ ....
| field   | type    | collation          |
+---------+---------+--------------------+ ....
| id      | utf8    | (NULL)             |
| key     | utf8    | latin1_swedish_ci  |
| value   | utf8    | latin1_swedish_ci  |
+---------+---------+--------------------+ ....

回复于 2024-05-05T17:27:00+08:00

对于 databases ：

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>

回复于 2024-05-05T17:27:00+08:00

17
对于 databases ：
```
SHOW CREATE DATABASE "DB_NAME_HERE";
```
在创建数据库（MySQL）时，默认字符集/排序规则始终是LATIN，而是在最初创建数据库时选择了不同的字符集/排序规则
回复于 2024-05-05T17:27:00+08:00

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

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

要查看表的排序规则：

SHOW TABLE STATUS where name like 'table_name';

要查看列的排序规则：

SHOW FULL COLUMNS FROM table_name;

回复于 2024-05-05T17:27:00+08:00

3
正如之前所写的那样，SHOW FULL COLUMNS应该是获取列信息的首选方法 . 缺少的是一种在没有直接访问元数据表的情况下获取字符集的方法：
```
SHOW FULL COLUMNS FROM my_table WHERE Field = 'my_field'
SHOW COLLATION WHERE Collation = 'collation_you_got'
```
回复于 2024-05-05T17:27:00+08:00

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

13 回答

相关问题