sql - create - utf8mb4




如何查看MySQL數據庫/表/列的字符集? (8)

什麼是(默認)字符集:

  • MySQL數據庫

  • MySQL表

  • 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 - 將輸出create table的完整SQL:

mysql> show create table my_tablename

獲取的整理:

mysql> SHOW FULL COLUMNS FROM my_tablename;

輸出:

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

以下是我如何去做 -

對於模式:

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

對於數據庫

只需使用這些命令:

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> 

對於數據庫

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

參看 此頁面 。 並檢查MySQL手冊


對於表格

SHOW TABLE STATUS將列出所有表格。

過濾使用:

SHOW TABLE STATUS where name like 'table_123';

我總是只看SHOW CREATE TABLE mydatabase.mytable

對於數據庫,看起來您需要查看SELECT DEFAULT_CHARACTER_SET_NAME FROM information_schema.SCHEMATA


要查看數據庫的默認排序規則:

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

要查看表格的整理:

SHOW TABLE STATUS where name like 'table_name';

要查看列的整理:

SHOW FULL COLUMNS FROM table_name;




collation