数据库系统原理与应用教程（082）—— MySQL 系统数据库分析 —— information_schema

数据库系统原理与应用教程（082）—— MySQL 系统数据库分析（information_schema）

MySQL 系统数据库有三个：information_schema、performance_schema 与 mysql。其中 performance_schema 用于性能分析，而 information_schema 用于存储数据库元数据，如：数据库名、表名、列的数据类型、访问权限等。 数据库information_schema 中的表实际上是视图，而不是基本表。information_schema 数据库包含的表如下：

mysql> use information_schema;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+---------------------------------------+
| Tables_in_information_schema          |
+---------------------------------------+
| CHARACTER_SETS                        |
| COLLATIONS                            |
| COLLATION_CHARACTER_SET_APPLICABILITY |
| COLUMNS                               |
| COLUMN_PRIVILEGES                     |
| ENGINES                               |
| EVENTS                                |
| FILES                                 |
| GLOBAL_STATUS                         |
| GLOBAL_VARIABLES                      |
| KEY_COLUMN_USAGE                      |
| OPTIMIZER_TRACE                       |
| PARAMETERS                            |
| PARTITIONS                            |
| PLUGINS                               |
| PROCESSLIST                           |
| PROFILING                             |
| REFERENTIAL_CONSTRAINTS               |
| ROUTINES                              |
| SCHEMATA                              |
| SCHEMA_PRIVILEGES                     |
| SESSION_STATUS                        |
| SESSION_VARIABLES                     |
| STATISTICS                            |
| TABLES                                |
| TABLESPACES                           |
| TABLE_CONSTRAINTS                     |
| TABLE_PRIVILEGES                      |
| TRIGGERS                              |
| USER_PRIVILEGES                       |
| VIEWS                                 |
| INNODB_LOCKS                          |
| INNODB_TRX                            |
| INNODB_SYS_DATAFILES                  |
| INNODB_FT_CONFIG                      |
| INNODB_SYS_VIRTUAL                    |
| INNODB_CMP                            |
| INNODB_FT_BEING_DELETED               |
| INNODB_CMP_RESET                      |
| INNODB_CMP_PER_INDEX                  |
| INNODB_CMPMEM_RESET                   |
| INNODB_FT_DELETED                     |
| INNODB_BUFFER_PAGE_LRU                |
| INNODB_LOCK_WAITS                     |
| INNODB_TEMP_TABLE_INFO                |
| INNODB_SYS_INDEXES                    |
| INNODB_SYS_TABLES                     |
| INNODB_SYS_FIELDS                     |
| INNODB_CMP_PER_INDEX_RESET            |
| INNODB_BUFFER_PAGE                    |
| INNODB_FT_DEFAULT_STOPWORD            |
| INNODB_FT_INDEX_TABLE                 |
| INNODB_FT_INDEX_CACHE                 |
| INNODB_SYS_TABLESPACES                |
| INNODB_METRICS                        |
| INNODB_SYS_FOREIGN_COLS               |
| INNODB_CMPMEM                         |
| INNODB_BUFFER_POOL_STATS              |
| INNODB_SYS_COLUMNS                    |
| INNODB_SYS_FOREIGN                    |
| INNODB_SYS_TABLESTATS                 |
+---------------------------------------+
61 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72

下面对 information_schema 数据库中常用的数据表进行分析：

1、SCHEMATA

该表保存了当前 mysql 实例中所有数据库的信息。SHOW DATABASES 命令从这个表获取数据。

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| aaa                |
| mydb               |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
6 rows in set (0.00 sec)

mysql> select * from schemata;
+----------+-------------------+----------------------------+------------------------+----------+
| CATALOG_NAME | SCHEMA_NAME| DEFAULT_CHARACTER_SET_NAME | DEFAULT_COLLATION_NAME | SQL_PATH |
+--------------+---------------+----------------------------+------------------------+----------+
| def          | information_schema | utf8          | utf8_general_ci        | NULL     |
| def          | aaa                | utf8          | utf8_general_ci        | NULL     |
| def          | mydb               | utf8          | utf8_general_ci        | NULL     |
| def          | mysql              | utf8          | utf8_general_ci        | NULL     |
| def          | performance_schema | utf8          | utf8_general_ci        | NULL     |
| def          | sys                | utf8          | utf8_general_ci        | NULL     |
+--------------+--------------------+------------------------+------------------------+----------+
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

2、TABLES

该表保存数据库中的表（视图）信息，包括表属于哪个数据库，表的类型、存储引擎、创建时间等信息。SHOW TABLES FROM XX 命令从这个表获取结果。

mysql> desc TABLES;
+-----------------+---------------------+------+-----+---------+-------+
| Field           | Type                | Null | Key | Default | Extra |
+-----------------+---------------------+------+-----+---------+-------+
| TABLE_CATALOG   | varchar(512)        | NO   |     |         |       |
| TABLE_SCHEMA    | varchar(64)         | NO   |     |         |       |
| TABLE_NAME      | varchar(64)         | NO   |     |         |       |
| TABLE_TYPE      | varchar(64)         | NO   |     |         |       |
| ENGINE          | varchar(64)         | YES  |     | NULL    |       |
| VERSION         | bigint(21) unsigned | YES  |     | NULL    |       |
| ROW_FORMAT      | varchar(10)         | YES  |     | NULL    |       |
| TABLE_ROWS      | bigint(21) unsigned | YES  |     | NULL    |       |
| AVG_ROW_LENGTH  | bigint(21) unsigned | YES  |     | NULL    |       |
| DATA_LENGTH     | bigint(21) unsigned | YES  |     | NULL    |       |
| MAX_DATA_LENGTH | bigint(21) unsigned | YES  |     | NULL    |       |
| INDEX_LENGTH    | bigint(21) unsigned | YES  |     | NULL    |       |
| DATA_FREE       | bigint(21) unsigned | YES  |     | NULL    |       |
| AUTO_INCREMENT  | bigint(21) unsigned | YES  |     | NULL    |       |
| CREATE_TIME     | datetime            | YES  |     | NULL    |       |
| UPDATE_TIME     | datetime            | YES  |     | NULL    |       |
| CHECK_TIME      | datetime            | YES  |     | NULL    |       |
| TABLE_COLLATION | varchar(32)         | YES  |     | NULL    |       |
| CHECKSUM        | bigint(21) unsigned | YES  |     | NULL    |       |
| CREATE_OPTIONS  | varchar(255)        | YES  |     | NULL    |       |
| TABLE_COMMENT   | varchar(2048)       | NO   |     |         |       |
+-----------------+---------------------+------+-----+---------+-------+
21 rows in set (0.08 sec)

select table_schema, table_name, table_type, engine, version,
       table_rows, data_free, create_time
from tables where table_schema = 'mydb';

mysql> select table_schema, table_name, table_type, engine, version,
    ->        table_rows, data_free, create_time
    -> from tables where table_schema = 'mydb';
+--------------+-----------------+------------+--------+---------+------------+----------------+
|table_schema| table_name | table_type| engine| version| table_rows| data_free| create_time    |
+--------------+-----------------+------------+--------+---------+------------+----------------+
| mydb    | course     | BASE TABLE | InnoDB |   10 |    10 |         0 | 2022-08-19 09:13:39 |
| mydb    | president  | VIEW       | NULL   | NULL |  NULL |      NULL | NULL                |
| mydb    | score      | BASE TABLE | InnoDB |   10 |    60 |         0 | 2022-08-19 09:13:39 |
| mydb    | student    | BASE TABLE | InnoDB |   10 |    14 |         0 | 2022-08-19 09:13:39 |
| mydb    | teacher    | BASE TABLE | InnoDB |   10 |     7 |         0 | 2022-08-19 09:13:39 |
| mydb    | v_score    | VIEW       | NULL   | NULL |  NULL |      NULL | NULL                |
| mydb    | v_stu001   | VIEW       | NULL   | NULL |  NULL |      NULL | NULL                |
| mydb    | v_stu002   | VIEW       | NULL   | NULL |  NULL |      NULL | NULL                |
| mydb    | v_stu003   | VIEW       | NULL   | NULL |  NULL |      NULL | NULL                |
| mydb    | v_stu004   | VIEW       | NULL   | NULL |  NULL |      NULL | NULL                |
| mydb    | v_stu_avg_score| VIEW   | NULL   | NULL |  NULL |      NULL | NULL                |
| mydb    | v_stu_zz   | VIEW       | NULL   | NULL |  NULL |      NULL | NULL                |
+--------------+-----------------+------------+--------+---------+------------+------------------+
12 rows in set, 1 warning (0.00 sec)

mysql> show tables from mydb;
+-----------------+
| Tables_in_mydb  |
+-----------------+
| course          |
| president       |
| score           |
| student         |
| teacher         |
| v_score         |
| v_stu001        |
| v_stu002        |
| v_stu003        |
| v_stu004        |
| v_stu_avg_score |
| v_stu_zz        |
+-----------------+
12 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71

3、COLUMNS

该表保存表中的列信息，包括表有多少列、每个列的类型等。SHOW COLUMNS FROM schemaname.tablename 命令从该表获取结果。

mysql> desc columns;
+--------------------------+---------------------+------+-----+---------+-------+
| Field                    | Type                | Null | Key | Default | Extra |
+--------------------------+---------------------+------+-----+---------+-------+
| TABLE_CATALOG            | varchar(512)        | NO   |     |         |       |
| TABLE_SCHEMA             | varchar(64)         | NO   |     |         |       |
| TABLE_NAME               | varchar(64)         | NO   |     |         |       |
| COLUMN_NAME              | varchar(64)         | NO   |     |         |       |
| ORDINAL_POSITION         | bigint(21) unsigned | NO   |     | 0       |       |
| COLUMN_DEFAULT           | longtext            | YES  |     | NULL    |       |
| IS_NULLABLE              | varchar(3)          | NO   |     |         |       |
| DATA_TYPE                | varchar(64)         | NO   |     |         |       |
| CHARACTER_MAXIMUM_LENGTH | bigint(21) unsigned | YES  |     | NULL    |       |
| CHARACTER_OCTET_LENGTH   | bigint(21) unsigned | YES  |     | NULL    |       |
| NUMERIC_PRECISION        | bigint(21) unsigned | YES  |     | NULL    |       |
| NUMERIC_SCALE            | bigint(21) unsigned | YES  |     | NULL    |       |
| DATETIME_PRECISION       | bigint(21) unsigned | YES  |     | NULL    |       |
| CHARACTER_SET_NAME       | varchar(32)         | YES  |     | NULL    |       |
| COLLATION_NAME           | varchar(32)         | YES  |     | NULL    |       |
| COLUMN_TYPE              | longtext            | NO   |     | NULL    |       |
| COLUMN_KEY               | varchar(3)          | NO   |     |         |       |
| EXTRA                    | varchar(30)         | NO   |     |         |       |
| PRIVILEGES               | varchar(80)         | NO   |     |         |       |
| COLUMN_COMMENT           | varchar(1024)       | NO   |     |         |       |
| GENERATION_EXPRESSION    | longtext            | NO   |     | NULL    |       |
+--------------------------+---------------------+------+-----+---------+-------+
21 rows in set (0.00 sec)

/*
select table_schema, table_name, column_name, data_type, 
       column_type, column_key
from columns
where table_schema = 'mydb' and table_name = 'student';
*/
mysql> select table_schema, table_name, column_name, data_type, 
    ->        column_type, column_key
    -> from columns
    -> where table_schema = 'mydb' and table_name = 'student';
+--------------+------------+-------------+-----------+--------------+------------+
| table_schema | table_name | column_name | data_type | column_type  | column_key |
+--------------+------------+-------------+-----------+--------------+------------+
| mydb         | student    | s_id        | char      | char(5)      | PRI        |
| mydb         | student    | s_name      | char      | char(20)     |            |
| mydb         | student    | birth       | datetime  | datetime     |            |
| mydb         | student    | phone       | char      | char(20)     |            |
| mydb         | student    | addr        | varchar   | varchar(100) |            |
+--------------+------------+-------------+-----------+--------------+------------+
5 rows in set (0.00 sec)

mysql> show columns from mydb.student;
+--------+--------------+------+-----+---------+-------+
| Field  | Type         | Null | Key | Default | Extra |
+--------+--------------+------+-----+---------+-------+
| s_id   | char(5)      | NO   | PRI | NULL    |       |
| s_name | char(20)     | YES  |     | NULL    |       |
| birth  | datetime     | YES  |     | NULL    |       |
| phone  | char(20)     | YES  |     | NULL    |       |
| addr   | varchar(100) | YES  |     | NULL    |       |
+--------+--------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60

4、STATISTICS

该表保存表索引的信息。SHOW INDEX FROM schemaname.tablename 命令从该表获取结果。

mysql> desc statistics;
+---------------+---------------+------+-----+---------+-------+
| Field         | Type          | Null | Key | Default | Extra |
+---------------+---------------+------+-----+---------+-------+
| TABLE_CATALOG | varchar(512)  | NO   |     |         |       |
| TABLE_SCHEMA  | varchar(64)   | NO   |     |         |       |
| TABLE_NAME    | varchar(64)   | NO   |     |         |       |
| NON_UNIQUE    | bigint(1)     | NO   |     | 0       |       |
| INDEX_SCHEMA  | varchar(64)   | NO   |     |         |       |
| INDEX_NAME    | varchar(64)   | NO   |     |         |       |
| SEQ_IN_INDEX  | bigint(2)     | NO   |     | 0       |       |
| COLUMN_NAME   | varchar(64)   | NO   |     |         |       |
| COLLATION     | varchar(1)    | YES  |     | NULL    |       |
| CARDINALITY   | bigint(21)    | YES  |     | NULL    |       |
| SUB_PART      | bigint(3)     | YES  |     | NULL    |       |
| PACKED        | varchar(10)   | YES  |     | NULL    |       |
| NULLABLE      | varchar(3)    | NO   |     |         |       |
| INDEX_TYPE    | varchar(16)   | NO   |     |         |       |
| COMMENT       | varchar(16)   | YES  |     | NULL    |       |
| INDEX_COMMENT | varchar(1024) | NO   |     |         |       |
+---------------+---------------+------+-----+---------+-------+
16 rows in set (0.00 sec)

/*
select table_schema, table_name, index_name, 
       index_schema, column_name
from statistics
where table_schema = 'mydb' and table_name = 'student';
*/
mysql> select table_schema, table_name, index_name, 
    ->        index_schema, column_name
    -> from statistics
    -> where table_schema = 'mydb' and table_name = 'student';
+--------------+------------+------------+--------------+-------------+
| table_schema | table_name | index_name | index_schema | column_name |
+--------------+------------+------------+--------------+-------------+
| mydb         | student    | PRIMARY    | mydb         | s_id        |
+--------------+------------+------------+--------------+-------------+
1 row in set (0.00 sec)

mysql> show index from mydb.student\G
*************************** 1. row ***************************
        Table: student
   Non_unique: 0
     Key_name: PRIMARY
 Seq_in_index: 1
  Column_name: s_id
    Collation: A
  Cardinality: 14
     Sub_part: NULL
       Packed: NULL
         Null: 
   Index_type: BTREE
      Comment: 
Index_comment: 
1 row in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56

5、TABLE_CONSTRAINTS

该表保存了存在约束的表、以及表的约束类型。

mysql> DESC TABLE_CONSTRAINTS;
+--------------------+--------------+------+-----+---------+-------+
| Field              | Type         | Null | Key | Default | Extra |
+--------------------+--------------+------+-----+---------+-------+
| CONSTRAINT_CATALOG | varchar(512) | NO   |     |         |       |
| CONSTRAINT_SCHEMA  | varchar(64)  | NO   |     |         |       |
| CONSTRAINT_NAME    | varchar(64)  | NO   |     |         |       |
| TABLE_SCHEMA       | varchar(64)  | NO   |     |         |       |
| TABLE_NAME         | varchar(64)  | NO   |     |         |       |
| CONSTRAINT_TYPE    | varchar(64)  | NO   |     |         |       |
+--------------------+--------------+------+-----+---------+-------+
6 rows in set (0.00 sec)

/*
select *
from TABLE_CONSTRAINTS
where table_schema = 'mydb';
*/
mysql> select *
    -> from TABLE_CONSTRAINTS
    -> where table_schema = 'mydb';
+--------------------+-----+-----------------+--------------+------------+-----------------+
| CONSTRAINT_CATALOG | CONSTRAINT_SCHEMA | CONSTRAINT_NAME | TABLE_SCHEMA | TABLE_NAME | CONSTRAINT_TYPE |
+--------------------+-----+-----------------+--------------+------------+-----------------+
| def      | mydb        | PRIMARY         | mydb         | course     | PRIMARY KEY     |
| def      | mydb        | PRIMARY         | mydb         | score      | PRIMARY KEY     |
| def      | mydb        | score_ibfk_1    | mydb         | score      | FOREIGN KEY     |
| def      | mydb        | score_ibfk_2    | mydb         | score      | FOREIGN KEY     |
| def      | mydb        | PRIMARY         | mydb         | student    | PRIMARY KEY     |
| def      | mydb        | PRIMARY         | mydb         | teacher    | PRIMARY KEY     |
+--------------------+-----+-----------------+--------------+------------+-----------------+
6 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32

6、KEY_COLUMN_USAGE

该表保存具有约束的键列。

mysql> desc KEY_COLUMN_USAGE;
+-------------------------------+--------------+------+-----+---------+-------+
| Field                         | Type         | Null | Key | Default | Extra |
+-------------------------------+--------------+------+-----+---------+-------+
| CONSTRAINT_CATALOG            | varchar(512) | NO   |     |         |       |
| CONSTRAINT_SCHEMA             | varchar(64)  | NO   |     |         |       |
| CONSTRAINT_NAME               | varchar(64)  | NO   |     |         |       |
| TABLE_CATALOG                 | varchar(512) | NO   |     |         |       |
| TABLE_SCHEMA                  | varchar(64)  | NO   |     |         |       |
| TABLE_NAME                    | varchar(64)  | NO   |     |         |       |
| COLUMN_NAME                   | varchar(64)  | NO   |     |         |       |
| ORDINAL_POSITION              | bigint(10)   | NO   |     | 0       |       |
| POSITION_IN_UNIQUE_CONSTRAINT | bigint(10)   | YES  |     | NULL    |       |
| REFERENCED_TABLE_SCHEMA       | varchar(64)  | YES  |     | NULL    |       |
| REFERENCED_TABLE_NAME         | varchar(64)  | YES  |     | NULL    |       |
| REFERENCED_COLUMN_NAME        | varchar(64)  | YES  |     | NULL    |       |
+-------------------------------+--------------+------+-----+---------+-------+
12 rows in set (0.00 sec)

/*
select constraint_schema, constraint_name, table_schema,
       table_name, column_name
from  KEY_COLUMN_USAGE
where table_schema = 'mydb';
*/
mysql> select constraint_schema, constraint_name, table_schema,
    ->        table_name, column_name
    -> from  KEY_COLUMN_USAGE
    -> where table_schema = 'mydb';
+-------------------+-----------------+--------------+------------+-------------+
| constraint_schema | constraint_name | table_schema | table_name | column_name |
+-------------------+-----------------+--------------+------------+-------------+
| mydb              | PRIMARY         | mydb         | course     | c_id        |
| mydb              | PRIMARY         | mydb         | score      | s_id        |
| mydb              | PRIMARY         | mydb         | score      | c_id        |
| mydb              | score_ibfk_1    | mydb         | score      | s_id        |
| mydb              | score_ibfk_2    | mydb         | score      | c_id        |
| mydb              | PRIMARY         | mydb         | student    | s_id        |
| mydb              | PRIMARY         | mydb         | teacher    | t_id        |
+-------------------+-----------------+--------------+------------+-------------+
7 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41

7、VIEWS

该表保存了视图信息。

mysql> desc views;
+----------------------+--------------+------+-----+---------+-------+
| Field                | Type         | Null | Key | Default | Extra |
+----------------------+--------------+------+-----+---------+-------+
| TABLE_CATALOG        | varchar(512) | NO   |     |         |       |
| TABLE_SCHEMA         | varchar(64)  | NO   |     |         |       |
| TABLE_NAME           | varchar(64)  | NO   |     |         |       |
| VIEW_DEFINITION      | longtext     | NO   |     | NULL    |       |
| CHECK_OPTION         | varchar(8)   | NO   |     |         |       |
| IS_UPDATABLE         | varchar(3)   | NO   |     |         |       |
| DEFINER              | varchar(93)  | NO   |     |         |       |
| SECURITY_TYPE        | varchar(7)   | NO   |     |         |       |
| CHARACTER_SET_CLIENT | varchar(32)  | NO   |     |         |       |
| COLLATION_CONNECTION | varchar(32)  | NO   |     |         |       |
+----------------------+--------------+------+-----+---------+-------+
10 rows in set (0.00 sec)

/*
select table_schema, table_name, view_definition, check_option, definer
from views
where table_schema = 'mydb'\G
*/
mysql> select table_schema, table_name, view_definition, check_option, definer
    -> from views
    -> where table_schema = 'mydb'\G
*************************** 1. row ***************************
   table_schema: mydb
     table_name: president
view_definition: select `mydb`.`stu`.`s_id` AS `s_id`,`mydb`.`stu`.`s_name` AS `s_name`,`mydb`.`stu`.`birth` AS `birth`,`mydb`.`stu`.`phone` AS `phone`,`mydb`.`stu`.`presid
ent` AS `president` from `mydb`.`stu`   check_option: NONE
        definer: root@localhost
*************************** 2. row ***************************
   table_schema: mydb
     table_name: v_score
view_definition: select `s`.`s_id` AS `s_id`,`s`.`s_name` AS `s_name`,`c`.`c_id` AS `c_id`,`c`.`c_name` AS `c_name`,`sc`.`score` AS `score` from ((`mydb`.`student` `s` join
 `mydb`.`score` `sc` on((`s`.`s_id` = `sc`.`s_id`))) join `mydb`.`course` `c` on((`sc`.`c_id` = `c`.`c_id`)))   check_option: NONE
        definer: root@localhost
*************************** 3. row ***************************
   table_schema: mydb
     table_name: v_stu001
view_definition: select distinct `mydb`.`student`.`s_id` AS `s_id`,`mydb`.`student`.`s_name` AS `s_name` from `mydb`.`student`
   check_option: NONE
        definer: root@localhost
*************************** 4. row ***************************
   table_schema: mydb
     table_name: v_stu002
view_definition: select `mydb`.`student`.`addr` AS `addr`,count(0) AS `cnt` from `mydb`.`student` group by `mydb`.`student`.`addr` having (`cnt` > 2)
   check_option: NONE
        definer: root@localhost
*************************** 5. row ***************************
   table_schema: mydb
     table_name: v_stu003
view_definition: select `mydb`.`student`.`s_id` AS `s_id`,`mydb`.`student`.`s_name` AS `s_name`,`mydb`.`student`.`birth` AS `birth`,`mydb`.`student`.`phone` AS `phone`,`myd
b`.`student`.`addr` AS `addr` from `mydb`.`student` where (`mydb`.`student`.`birth` > (select `mydb`.`student`.`birth` from `mydb`.`student` where (`mydb`.`student`.`s_id` = 'S2021')))   check_option: NONE
        definer: root@localhost
*************************** 6. row ***************************
   table_schema: mydb
     table_name: v_stu004
view_definition: select `mydb`.`student`.`s_id` AS `s_id`,`mydb`.`student`.`s_name` AS `s_name`,`mydb`.`student`.`birth` AS `birth`,`mydb`.`student`.`phone` AS `phone`,`myd
b`.`student`.`addr` AS `addr` from `mydb`.`student` where (`mydb`.`student`.`phone` like '136%')   check_option: NONE
        definer: root@localhost
*************************** 7. row ***************************
   table_schema: mydb
     table_name: v_stu_avg_score
view_definition: select `s`.`s_id` AS `sid`,`s`.`s_name` AS `sname`,avg(`sc`.`score`) AS `avg_score` from (`mydb`.`student` `s` join `mydb`.`score` `sc` on((`s`.`s_id` = `s
c`.`s_id`))) group by `s`.`s_id`,`s`.`s_name`   check_option: NONE
        definer: root@localhost
*************************** 8. row ***************************
   table_schema: mydb
     table_name: v_stu_zz
view_definition: select `mydb`.`student`.`s_id` AS `s_id`,`mydb`.`student`.`s_name` AS `s_name`,`mydb`.`student`.`birth` AS `birth`,`mydb`.`student`.`phone` AS `phone`,`myd
b`.`student`.`addr` AS `addr` from `mydb`.`student` where (`mydb`.`student`.`addr` = '郑州市')   check_option: CASCADED
        definer: root@localhost
8 rows in set (0.01 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74

8、ROUTINES

该表保存存储子程序（存储过程和函数）的信息。

mysql> desc routines;
+--------------------------+---------------------+------+-----+---------------------+-------+
| Field                    | Type                | Null | Key | Default             | Extra |
+--------------------------+---------------------+------+-----+---------------------+-------+
| SPECIFIC_NAME            | varchar(64)         | NO   |     |                     |       |
| ROUTINE_CATALOG          | varchar(512)        | NO   |     |                     |       |
| ROUTINE_SCHEMA           | varchar(64)         | NO   |     |                     |       |
| ROUTINE_NAME             | varchar(64)         | NO   |     |                     |       |
| ROUTINE_TYPE             | varchar(9)          | NO   |     |                     |       |
| DATA_TYPE                | varchar(64)         | NO   |     |                     |       |
| CHARACTER_MAXIMUM_LENGTH | int(21)             | YES  |     | NULL                |       |
| CHARACTER_OCTET_LENGTH   | int(21)             | YES  |     | NULL                |       |
| NUMERIC_PRECISION        | bigint(21) unsigned | YES  |     | NULL                |       |
| NUMERIC_SCALE            | int(21)             | YES  |     | NULL                |       |
| DATETIME_PRECISION       | bigint(21) unsigned | YES  |     | NULL                |       |
| CHARACTER_SET_NAME       | varchar(64)         | YES  |     | NULL                |       |
| COLLATION_NAME           | varchar(64)         | YES  |     | NULL                |       |
| DTD_IDENTIFIER           | longtext            | YES  |     | NULL                |       |
| ROUTINE_BODY             | varchar(8)          | NO   |     |                     |       |
| ROUTINE_DEFINITION       | longtext            | YES  |     | NULL                |       |
| EXTERNAL_NAME            | varchar(64)         | YES  |     | NULL                |       |
| EXTERNAL_LANGUAGE        | varchar(64)         | YES  |     | NULL                |       |
| PARAMETER_STYLE          | varchar(8)          | NO   |     |                     |       |
| IS_DETERMINISTIC         | varchar(3)          | NO   |     |                     |       |
| SQL_DATA_ACCESS          | varchar(64)         | NO   |     |                     |       |
| SQL_PATH                 | varchar(64)         | YES  |     | NULL                |       |
| SECURITY_TYPE            | varchar(7)          | NO   |     |                     |       |
| CREATED                  | datetime            | NO   |     | 0000-00-00 00:00:00 |       |
| LAST_ALTERED             | datetime            | NO   |     | 0000-00-00 00:00:00 |       |
| SQL_MODE                 | varchar(8192)       | NO   |     |                     |       |
| ROUTINE_COMMENT          | longtext            | NO   |     | NULL                |       |
| DEFINER                  | varchar(93)         | NO   |     |                     |       |
| CHARACTER_SET_CLIENT     | varchar(32)         | NO   |     |                     |       |
| COLLATION_CONNECTION     | varchar(32)         | NO   |     |                     |       |
| DATABASE_COLLATION       | varchar(32)         | NO   |     |                     |       |
+--------------------------+---------------------+------+-----+---------------------+-------+
31 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37

9、TRIGGERS

该表保存触发器的信息。

mysql> desc triggers;
+----------------------------+---------------+------+-----+---------+-------+
| Field                      | Type          | Null | Key | Default | Extra |
+----------------------------+---------------+------+-----+---------+-------+
| TRIGGER_CATALOG            | varchar(512)  | NO   |     |         |       |
| TRIGGER_SCHEMA             | varchar(64)   | NO   |     |         |       |
| TRIGGER_NAME               | varchar(64)   | NO   |     |         |       |
| EVENT_MANIPULATION         | varchar(6)    | NO   |     |         |       |
| EVENT_OBJECT_CATALOG       | varchar(512)  | NO   |     |         |       |
| EVENT_OBJECT_SCHEMA        | varchar(64)   | NO   |     |         |       |
| EVENT_OBJECT_TABLE         | varchar(64)   | NO   |     |         |       |
| ACTION_ORDER               | bigint(4)     | NO   |     | 0       |       |
| ACTION_CONDITION           | longtext      | YES  |     | NULL    |       |
| ACTION_STATEMENT           | longtext      | NO   |     | NULL    |       |
| ACTION_ORIENTATION         | varchar(9)    | NO   |     |         |       |
| ACTION_TIMING              | varchar(6)    | NO   |     |         |       |
| ACTION_REFERENCE_OLD_TABLE | varchar(64)   | YES  |     | NULL    |       |
| ACTION_REFERENCE_NEW_TABLE | varchar(64)   | YES  |     | NULL    |       |
| ACTION_REFERENCE_OLD_ROW   | varchar(3)    | NO   |     |         |       |
| ACTION_REFERENCE_NEW_ROW   | varchar(3)    | NO   |     |         |       |
| CREATED                    | datetime(2)   | YES  |     | NULL    |       |
| SQL_MODE                   | varchar(8192) | NO   |     |         |       |
| DEFINER                    | varchar(93)   | NO   |     |         |       |
| CHARACTER_SET_CLIENT       | varchar(32)   | NO   |     |         |       |
| COLLATION_CONNECTION       | varchar(32)   | NO   |     |         |       |
| DATABASE_COLLATION         | varchar(32)   | NO   |     |         |       |
+----------------------------+---------------+------+-----+---------+-------+
22 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28

10、CHARACTER_SETS

该表提供了 mysql 可用字符集的信息。SHOW CHARACTER SET 命令从这个表获取结果。

mysql> SHOW CHARACTER SET;
+----------+---------------------------------+---------------------+--------+
| Charset  | Description                     | Default collation   | Maxlen |
+----------+---------------------------------+---------------------+--------+
| big5     | Big5 Traditional Chinese        | big5_chinese_ci     |      2 |
| dec8     | DEC West European               | dec8_swedish_ci     |      1 |
| cp850    | DOS West European               | cp850_general_ci    |      1 |
| hp8      | HP West European                | hp8_english_ci      |      1 |
| koi8r    | KOI8-R Relcom Russian           | koi8r_general_ci    |      1 |
| latin1   | cp1252 West European            | latin1_swedish_ci   |      1 |
| latin2   | ISO 8859-2 Central European     | latin2_general_ci   |      1 |
| swe7     | 7bit Swedish                    | swe7_swedish_ci     |      1 |
| ascii    | US ASCII                        | ascii_general_ci    |      1 |
| ujis     | EUC-JP Japanese                 | ujis_japanese_ci    |      3 |
| sjis     | Shift-JIS Japanese              | sjis_japanese_ci    |      2 |
| hebrew   | ISO 8859-8 Hebrew               | hebrew_general_ci   |      1 |
| tis620   | TIS620 Thai                     | tis620_thai_ci      |      1 |
| euckr    | EUC-KR Korean                   | euckr_korean_ci     |      2 |
| koi8u    | KOI8-U Ukrainian                | koi8u_general_ci    |      1 |
| gb2312   | GB2312 Simplified Chinese       | gb2312_chinese_ci   |      2 |
| greek    | ISO 8859-7 Greek                | greek_general_ci    |      1 |
| cp1250   | Windows Central European        | cp1250_general_ci   |      1 |
| gbk      | GBK Simplified Chinese          | gbk_chinese_ci      |      2 |
| latin5   | ISO 8859-9 Turkish              | latin5_turkish_ci   |      1 |
| armscii8 | ARMSCII-8 Armenian              | armscii8_general_ci |      1 |
| utf8     | UTF-8 Unicode                   | utf8_general_ci     |      3 |
| ucs2     | UCS-2 Unicode                   | ucs2_general_ci     |      2 |
| cp866    | DOS Russian                     | cp866_general_ci    |      1 |
| keybcs2  | DOS Kamenicky Czech-Slovak      | keybcs2_general_ci  |      1 |
| macce    | Mac Central European            | macce_general_ci    |      1 |
| macroman | Mac West European               | macroman_general_ci |      1 |
| cp852    | DOS Central European            | cp852_general_ci    |      1 |
| latin7   | ISO 8859-13 Baltic              | latin7_general_ci   |      1 |
| utf8mb4  | UTF-8 Unicode                   | utf8mb4_general_ci  |      4 |
| cp1251   | Windows Cyrillic                | cp1251_general_ci   |      1 |
| utf16    | UTF-16 Unicode                  | utf16_general_ci    |      4 |
| utf16le  | UTF-16LE Unicode                | utf16le_general_ci  |      4 |
| cp1256   | Windows Arabic                  | cp1256_general_ci   |      1 |
| cp1257   | Windows Baltic                  | cp1257_general_ci   |      1 |
| utf32    | UTF-32 Unicode                  | utf32_general_ci    |      4 |
| binary   | Binary pseudo charset           | binary              |      1 |
| geostd8  | GEOSTD8 Georgian                | geostd8_general_ci  |      1 |
| cp932    | SJIS for Windows Japanese       | cp932_japanese_ci   |      2 |
| eucjpms  | UJIS for Windows Japanese       | eucjpms_japanese_ci |      3 |
| gb18030  | China National Standard GB18030 | gb18030_chinese_ci  |      4 |
+----------+---------------------------------+---------------------+--------+
41 rows in set (0.03 sec)

mysql> select * from CHARACTER_SETS;
+--------------------+----------------------+---------------------------------+--------+
| CHARACTER_SET_NAME | DEFAULT_COLLATE_NAME | DESCRIPTION                     | MAXLEN |
+--------------------+----------------------+---------------------------------+--------+
| big5               | big5_chinese_ci      | Big5 Traditional Chinese        |      2 |
| dec8               | dec8_swedish_ci      | DEC West European               |      1 |
| cp850              | cp850_general_ci     | DOS West European               |      1 |
| hp8                | hp8_english_ci       | HP West European                |      1 |
| koi8r              | koi8r_general_ci     | KOI8-R Relcom Russian           |      1 |
| latin1             | latin1_swedish_ci    | cp1252 West European            |      1 |
| latin2             | latin2_general_ci    | ISO 8859-2 Central European     |      1 |
| swe7               | swe7_swedish_ci      | 7bit Swedish                    |      1 |
| ascii              | ascii_general_ci     | US ASCII                        |      1 |
| ujis               | ujis_japanese_ci     | EUC-JP Japanese                 |      3 |
| sjis               | sjis_japanese_ci     | Shift-JIS Japanese              |      2 |
| hebrew             | hebrew_general_ci    | ISO 8859-8 Hebrew               |      1 |
| tis620             | tis620_thai_ci       | TIS620 Thai                     |      1 |
| euckr              | euckr_korean_ci      | EUC-KR Korean                   |      2 |
| koi8u              | koi8u_general_ci     | KOI8-U Ukrainian                |      1 |
| gb2312             | gb2312_chinese_ci    | GB2312 Simplified Chinese       |      2 |
| greek              | greek_general_ci     | ISO 8859-7 Greek                |      1 |
| cp1250             | cp1250_general_ci    | Windows Central European        |      1 |
| gbk                | gbk_chinese_ci       | GBK Simplified Chinese          |      2 |
| latin5             | latin5_turkish_ci    | ISO 8859-9 Turkish              |      1 |
| armscii8           | armscii8_general_ci  | ARMSCII-8 Armenian              |      1 |
| utf8               | utf8_general_ci      | UTF-8 Unicode                   |      3 |
| ucs2               | ucs2_general_ci      | UCS-2 Unicode                   |      2 |
| cp866              | cp866_general_ci     | DOS Russian                     |      1 |
| keybcs2            | keybcs2_general_ci   | DOS Kamenicky Czech-Slovak      |      1 |
| macce              | macce_general_ci     | Mac Central European            |      1 |
| macroman           | macroman_general_ci  | Mac West European               |      1 |
| cp852              | cp852_general_ci     | DOS Central European            |      1 |
| latin7             | latin7_general_ci    | ISO 8859-13 Baltic              |      1 |
| utf8mb4            | utf8mb4_general_ci   | UTF-8 Unicode                   |      4 |
| cp1251             | cp1251_general_ci    | Windows Cyrillic                |      1 |
| utf16              | utf16_general_ci     | UTF-16 Unicode                  |      4 |
| utf16le            | utf16le_general_ci   | UTF-16LE Unicode                |      4 |
| cp1256             | cp1256_general_ci    | Windows Arabic                  |      1 |
| cp1257             | cp1257_general_ci    | Windows Baltic                  |      1 |
| utf32              | utf32_general_ci     | UTF-32 Unicode                  |      4 |
| binary             | binary               | Binary pseudo charset           |      1 |
| geostd8            | geostd8_general_ci   | GEOSTD8 Georgian                |      1 |
| cp932              | cp932_japanese_ci    | SJIS for Windows Japanese       |      2 |
| eucjpms            | eucjpms_japanese_ci  | UJIS for Windows Japanese       |      3 |
| gb18030            | gb18030_chinese_ci   | China National Standard GB18030 |      4 |
+--------------------+----------------------+---------------------------------+--------+
41 rows in set (0.06 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95

11、COLLATIONS

该表保存各字符集的校对信息。SHOW COLLATION 命令从这个表获取结果。

mysql> select * from COLLATIONS;
+--------------------------+--------------------+-----+------------+-------------+---------+
| COLLATION_NAME           | CHARACTER_SET_NAME | ID  | IS_DEFAULT | IS_COMPILED | SORTLEN |
+--------------------------+--------------------+-----+------------+-------------+---------+
| big5_chinese_ci          | big5               |   1 | Yes        | Yes         |       1 |
| big5_bin                 | big5               |  84 |            | Yes         |       1 |
| dec8_swedish_ci          | dec8               |   3 | Yes        | Yes         |       1 |
| dec8_bin                 | dec8               |  69 |            | Yes         |       1 |
| cp850_general_ci         | cp850              |   4 | Yes        | Yes         |       1 |
| cp850_bin                | cp850              |  80 |            | Yes         |       1 |
| hp8_english_ci           | hp8                |   6 | Yes        | Yes         |       1 |
| hp8_bin                  | hp8                |  72 |            | Yes         |       1 |
| koi8r_general_ci         | koi8r              |   7 | Yes        | Yes         |       1 |
| koi8r_bin                | koi8r              |  74 |            | Yes         |       1 |
| latin1_german1_ci        | latin1             |   5 |            | Yes         |       1 |
| latin1_swedish_ci        | latin1             |   8 | Yes        | Yes         |       1 |
| latin1_danish_ci         | latin1             |  15 |            | Yes         |       1 |
| latin1_german2_ci        | latin1             |  31 |            | Yes         |       2 |
| latin1_bin               | latin1             |  47 |            | Yes         |       1 |
| latin1_general_ci        | latin1             |  48 |            | Yes         |       1 |
| latin1_general_cs        | latin1             |  49 |            | Yes         |       1 |
| latin1_spanish_ci        | latin1             |  94 |            | Yes         |       1 |
| latin2_czech_cs          | latin2             |   2 |            | Yes         |       4 |
| latin2_general_ci        | latin2             |   9 | Yes        | Yes         |       1 |
| latin2_hungarian_ci      | latin2             |  21 |            | Yes         |       1 |
| latin2_croatian_ci       | latin2             |  27 |            | Yes         |       1 |
| latin2_bin               | latin2             |  77 |            | Yes         |       1 |
| swe7_swedish_ci          | swe7               |  10 | Yes        | Yes         |       1 |
| swe7_bin                 | swe7               |  82 |            | Yes         |       1 |
| ascii_general_ci         | ascii              |  11 | Yes        | Yes         |       1 |
| ascii_bin                | ascii              |  65 |            | Yes         |       1 |
| ujis_japanese_ci         | ujis               |  12 | Yes        | Yes         |       1 |
| ujis_bin                 | ujis               |  91 |            | Yes         |       1 |
| sjis_japanese_ci         | sjis               |  13 | Yes        | Yes         |       1 |
| sjis_bin                 | sjis               |  88 |            | Yes         |       1 |
| hebrew_general_ci        | hebrew             |  16 | Yes        | Yes         |       1 |
| hebrew_bin               | hebrew             |  71 |            | Yes         |       1 |
| tis620_thai_ci           | tis620             |  18 | Yes        | Yes         |       4 |
| tis620_bin               | tis620             |  89 |            | Yes         |       1 |
| euckr_korean_ci          | euckr              |  19 | Yes        | Yes         |       1 |
| euckr_bin                | euckr              |  85 |            | Yes         |       1 |
| koi8u_general_ci         | koi8u              |  22 | Yes        | Yes         |       1 |
| koi8u_bin                | koi8u              |  75 |            | Yes         |       1 |
| gb2312_chinese_ci        | gb2312             |  24 | Yes        | Yes         |       1 |
| gb2312_bin               | gb2312             |  86 |            | Yes         |       1 |
| greek_general_ci         | greek              |  25 | Yes        | Yes         |       1 |
| greek_bin                | greek              |  70 |            | Yes         |       1 |
| cp1250_general_ci        | cp1250             |  26 | Yes        | Yes         |       1 |
| cp1250_czech_cs          | cp1250             |  34 |            | Yes         |       2 |
| cp1250_croatian_ci       | cp1250             |  44 |            | Yes         |       1 |
| cp1250_bin               | cp1250             |  66 |            | Yes         |       1 |
| cp1250_polish_ci         | cp1250             |  99 |            | Yes         |       1 |
| gbk_chinese_ci           | gbk                |  28 | Yes        | Yes         |       1 |
| gbk_bin                  | gbk                |  87 |            | Yes         |       1 |
| latin5_turkish_ci        | latin5             |  30 | Yes        | Yes         |       1 |
| latin5_bin               | latin5             |  78 |            | Yes         |       1 |
| armscii8_general_ci      | armscii8           |  32 | Yes        | Yes         |       1 |
| armscii8_bin             | armscii8           |  64 |            | Yes         |       1 |
| utf8_general_ci          | utf8               |  33 | Yes        | Yes         |       1 |
| utf8_bin                 | utf8               |  83 |            | Yes         |       1 |
| utf8_unicode_ci          | utf8               | 192 |            | Yes         |       8 |
| utf8_icelandic_ci        | utf8               | 193 |            | Yes         |       8 |
| utf8_latvian_ci          | utf8               | 194 |            | Yes         |       8 |
| utf8_romanian_ci         | utf8               | 195 |            | Yes         |       8 |
| utf8_slovenian_ci        | utf8               | 196 |            | Yes         |       8 |
| utf8_polish_ci           | utf8               | 197 |            | Yes         |       8 |
| utf8_estonian_ci         | utf8               | 198 |            | Yes         |       8 |
| utf8_spanish_ci          | utf8               | 199 |            | Yes         |       8 |
| utf8_swedish_ci          | utf8               | 200 |            | Yes         |       8 |
| utf8_turkish_ci          | utf8               | 201 |            | Yes         |       8 |
| utf8_czech_ci            | utf8               | 202 |            | Yes         |       8 |
| utf8_danish_ci           | utf8               | 203 |            | Yes         |       8 |
| utf8_lithuanian_ci       | utf8               | 204 |            | Yes         |       8 |
| utf8_slovak_ci           | utf8               | 205 |            | Yes         |       8 |
| utf8_spanish2_ci         | utf8               | 206 |            | Yes         |       8 |
| utf8_roman_ci            | utf8               | 207 |            | Yes         |       8 |
| utf8_persian_ci          | utf8               | 208 |            | Yes         |       8 |
| utf8_esperanto_ci        | utf8               | 209 |            | Yes         |       8 |
| utf8_hungarian_ci        | utf8               | 210 |            | Yes         |       8 |
| utf8_sinhala_ci          | utf8               | 211 |            | Yes         |       8 |
| utf8_german2_ci          | utf8               | 212 |            | Yes         |       8 |
| utf8_croatian_ci         | utf8               | 213 |            | Yes         |       8 |
| utf8_unicode_520_ci      | utf8               | 214 |            | Yes         |       8 |
| utf8_vietnamese_ci       | utf8               | 215 |            | Yes         |       8 |
| utf8_general_mysql500_ci | utf8               | 223 |            | Yes         |       1 |
| ucs2_general_ci          | ucs2               |  35 | Yes        | Yes         |       1 |
| ucs2_bin                 | ucs2               |  90 |            | Yes         |       1 |
| ucs2_unicode_ci          | ucs2               | 128 |            | Yes         |       8 |
| ucs2_icelandic_ci        | ucs2               | 129 |            | Yes         |       8 |
| ucs2_latvian_ci          | ucs2               | 130 |            | Yes         |       8 |
| ucs2_romanian_ci         | ucs2               | 131 |            | Yes         |       8 |
| ucs2_slovenian_ci        | ucs2               | 132 |            | Yes         |       8 |
| ucs2_polish_ci           | ucs2               | 133 |            | Yes         |       8 |
| ucs2_estonian_ci         | ucs2               | 134 |            | Yes         |       8 |
| ucs2_spanish_ci          | ucs2               | 135 |            | Yes         |       8 |
| ucs2_swedish_ci          | ucs2               | 136 |            | Yes         |       8 |
| ucs2_turkish_ci          | ucs2               | 137 |            | Yes         |       8 |
| ucs2_czech_ci            | ucs2               | 138 |            | Yes         |       8 |
| ucs2_danish_ci           | ucs2               | 139 |            | Yes         |       8 |
| ucs2_lithuanian_ci       | ucs2               | 140 |            | Yes         |       8 |
| ucs2_slovak_ci           | ucs2               | 141 |            | Yes         |       8 |
| ucs2_spanish2_ci         | ucs2               | 142 |            | Yes         |       8 |
| ucs2_roman_ci            | ucs2               | 143 |            | Yes         |       8 |
| ucs2_persian_ci          | ucs2               | 144 |            | Yes         |       8 |
| ucs2_esperanto_ci        | ucs2               | 145 |            | Yes         |       8 |
| ucs2_hungarian_ci        | ucs2               | 146 |            | Yes         |       8 |
| ucs2_sinhala_ci          | ucs2               | 147 |            | Yes         |       8 |
| ucs2_german2_ci          | ucs2               | 148 |            | Yes         |       8 |
| ucs2_croatian_ci         | ucs2               | 149 |            | Yes         |       8 |
| ucs2_unicode_520_ci      | ucs2               | 150 |            | Yes         |       8 |
| ucs2_vietnamese_ci       | ucs2               | 151 |            | Yes         |       8 |
| ucs2_general_mysql500_ci | ucs2               | 159 |            | Yes         |       1 |
| cp866_general_ci         | cp866              |  36 | Yes        | Yes         |       1 |
| cp866_bin                | cp866              |  68 |            | Yes         |       1 |
| keybcs2_general_ci       | keybcs2            |  37 | Yes        | Yes         |       1 |
| keybcs2_bin              | keybcs2            |  73 |            | Yes         |       1 |
| macce_general_ci         | macce              |  38 | Yes        | Yes         |       1 |
| macce_bin                | macce              |  43 |            | Yes         |       1 |
| macroman_general_ci      | macroman           |  39 | Yes        | Yes         |       1 |
| macroman_bin             | macroman           |  53 |            | Yes         |       1 |
| cp852_general_ci         | cp852              |  40 | Yes        | Yes         |       1 |
| cp852_bin                | cp852              |  81 |            | Yes         |       1 |
| latin7_estonian_cs       | latin7             |  20 |            | Yes         |       1 |
| latin7_general_ci        | latin7             |  41 | Yes        | Yes         |       1 |
| latin7_general_cs        | latin7             |  42 |            | Yes         |       1 |
| latin7_bin               | latin7             |  79 |            | Yes         |       1 |
| utf8mb4_general_ci       | utf8mb4            |  45 | Yes        | Yes         |       1 |
| utf8mb4_bin              | utf8mb4            |  46 |            | Yes         |       1 |
| utf8mb4_unicode_ci       | utf8mb4            | 224 |            | Yes         |       8 |
| utf8mb4_icelandic_ci     | utf8mb4            | 225 |            | Yes         |       8 |
| utf8mb4_latvian_ci       | utf8mb4            | 226 |            | Yes         |       8 |
| utf8mb4_romanian_ci      | utf8mb4            | 227 |            | Yes         |       8 |
| utf8mb4_slovenian_ci     | utf8mb4            | 228 |            | Yes         |       8 |
| utf8mb4_polish_ci        | utf8mb4            | 229 |            | Yes         |       8 |
| utf8mb4_estonian_ci      | utf8mb4            | 230 |            | Yes         |       8 |
| utf8mb4_spanish_ci       | utf8mb4            | 231 |            | Yes         |       8 |
| utf8mb4_swedish_ci       | utf8mb4            | 232 |            | Yes         |       8 |
| utf8mb4_turkish_ci       | utf8mb4            | 233 |            | Yes         |       8 |
| utf8mb4_czech_ci         | utf8mb4            | 234 |            | Yes         |       8 |
| utf8mb4_danish_ci        | utf8mb4            | 235 |            | Yes         |       8 |
| utf8mb4_lithuanian_ci    | utf8mb4            | 236 |            | Yes         |       8 |
| utf8mb4_slovak_ci        | utf8mb4            | 237 |            | Yes         |       8 |
| utf8mb4_spanish2_ci      | utf8mb4            | 238 |            | Yes         |       8 |
| utf8mb4_roman_ci         | utf8mb4            | 239 |            | Yes         |       8 |
| utf8mb4_persian_ci       | utf8mb4            | 240 |            | Yes         |       8 |
| utf8mb4_esperanto_ci     | utf8mb4            | 241 |            | Yes         |       8 |
| utf8mb4_hungarian_ci     | utf8mb4            | 242 |            | Yes         |       8 |
| utf8mb4_sinhala_ci       | utf8mb4            | 243 |            | Yes         |       8 |
| utf8mb4_german2_ci       | utf8mb4            | 244 |            | Yes         |       8 |
| utf8mb4_croatian_ci      | utf8mb4            | 245 |            | Yes         |       8 |
| utf8mb4_unicode_520_ci   | utf8mb4            | 246 |            | Yes         |       8 |
| utf8mb4_vietnamese_ci    | utf8mb4            | 247 |            | Yes         |       8 |
| cp1251_bulgarian_ci      | cp1251             |  14 |            | Yes         |       1 |
| cp1251_ukrainian_ci      | cp1251             |  23 |            | Yes         |       1 |
| cp1251_bin               | cp1251             |  50 |            | Yes         |       1 |
| cp1251_general_ci        | cp1251             |  51 | Yes        | Yes         |       1 |
| cp1251_general_cs        | cp1251             |  52 |            | Yes         |       1 |
| utf16_general_ci         | utf16              |  54 | Yes        | Yes         |       1 |
| utf16_bin                | utf16              |  55 |            | Yes         |       1 |
| utf16_unicode_ci         | utf16              | 101 |            | Yes         |       8 |
| utf16_icelandic_ci       | utf16              | 102 |            | Yes         |       8 |
| utf16_latvian_ci         | utf16              | 103 |            | Yes         |       8 |
| utf16_romanian_ci        | utf16              | 104 |            | Yes         |       8 |
| utf16_slovenian_ci       | utf16              | 105 |            | Yes         |       8 |
| utf16_polish_ci          | utf16              | 106 |            | Yes         |       8 |
| utf16_estonian_ci        | utf16              | 107 |            | Yes         |       8 |
| utf16_spanish_ci         | utf16              | 108 |            | Yes         |       8 |
| utf16_swedish_ci         | utf16              | 109 |            | Yes         |       8 |
| utf16_turkish_ci         | utf16              | 110 |            | Yes         |       8 |
| utf16_czech_ci           | utf16              | 111 |            | Yes         |       8 |
| utf16_danish_ci          | utf16              | 112 |            | Yes         |       8 |
| utf16_lithuanian_ci      | utf16              | 113 |            | Yes         |       8 |
| utf16_slovak_ci          | utf16              | 114 |            | Yes         |       8 |
| utf16_spanish2_ci        | utf16              | 115 |            | Yes         |       8 |
| utf16_roman_ci           | utf16              | 116 |            | Yes         |       8 |
| utf16_persian_ci         | utf16              | 117 |            | Yes         |       8 |
| utf16_esperanto_ci       | utf16              | 118 |            | Yes         |       8 |
| utf16_hungarian_ci       | utf16              | 119 |            | Yes         |       8 |
| utf16_sinhala_ci         | utf16              | 120 |            | Yes         |       8 |
| utf16_german2_ci         | utf16              | 121 |            | Yes         |       8 |
| utf16_croatian_ci        | utf16              | 122 |            | Yes         |       8 |
| utf16_unicode_520_ci     | utf16              | 123 |            | Yes         |       8 |
| utf16_vietnamese_ci      | utf16              | 124 |            | Yes         |       8 |
| utf16le_general_ci       | utf16le            |  56 | Yes        | Yes         |       1 |
| utf16le_bin              | utf16le            |  62 |            | Yes         |       1 |
| cp1256_general_ci        | cp1256             |  57 | Yes        | Yes         |       1 |
| cp1256_bin               | cp1256             |  67 |            | Yes         |       1 |
| cp1257_lithuanian_ci     | cp1257             |  29 |            | Yes         |       1 |
| cp1257_bin               | cp1257             |  58 |            | Yes         |       1 |
| cp1257_general_ci        | cp1257             |  59 | Yes        | Yes         |       1 |
| utf32_general_ci         | utf32              |  60 | Yes        | Yes         |       1 |
| utf32_bin                | utf32              |  61 |            | Yes         |       1 |
| utf32_unicode_ci         | utf32              | 160 |            | Yes         |       8 |
| utf32_icelandic_ci       | utf32              | 161 |            | Yes         |       8 |
| utf32_latvian_ci         | utf32              | 162 |            | Yes         |       8 |
| utf32_romanian_ci        | utf32              | 163 |            | Yes         |       8 |
| utf32_slovenian_ci       | utf32              | 164 |            | Yes         |       8 |
| utf32_polish_ci          | utf32              | 165 |            | Yes         |       8 |
| utf32_estonian_ci        | utf32              | 166 |            | Yes         |       8 |
| utf32_spanish_ci         | utf32              | 167 |            | Yes         |       8 |
| utf32_swedish_ci         | utf32              | 168 |            | Yes         |       8 |
| utf32_turkish_ci         | utf32              | 169 |            | Yes         |       8 |
| utf32_czech_ci           | utf32              | 170 |            | Yes         |       8 |
| utf32_danish_ci          | utf32              | 171 |            | Yes         |       8 |
| utf32_lithuanian_ci      | utf32              | 172 |            | Yes         |       8 |
| utf32_slovak_ci          | utf32              | 173 |            | Yes         |       8 |
| utf32_spanish2_ci        | utf32              | 174 |            | Yes         |       8 |
| utf32_roman_ci           | utf32              | 175 |            | Yes         |       8 |
| utf32_persian_ci         | utf32              | 176 |            | Yes         |       8 |
| utf32_esperanto_ci       | utf32              | 177 |            | Yes         |       8 |
| utf32_hungarian_ci       | utf32              | 178 |            | Yes         |       8 |
| utf32_sinhala_ci         | utf32              | 179 |            | Yes         |       8 |
| utf32_german2_ci         | utf32              | 180 |            | Yes         |       8 |
| utf32_croatian_ci        | utf32              | 181 |            | Yes         |       8 |
| utf32_unicode_520_ci     | utf32              | 182 |            | Yes         |       8 |
| utf32_vietnamese_ci      | utf32              | 183 |            | Yes         |       8 |
| binary                   | binary             |  63 | Yes        | Yes         |       1 |
| geostd8_general_ci       | geostd8            |  92 | Yes        | Yes         |       1 |
| geostd8_bin              | geostd8            |  93 |            | Yes         |       1 |
| cp932_japanese_ci        | cp932              |  95 | Yes        | Yes         |       1 |
| cp932_bin                | cp932              |  96 |            | Yes         |       1 |
| eucjpms_japanese_ci      | eucjpms            |  97 | Yes        | Yes         |       1 |
| eucjpms_bin              | eucjpms            |  98 |            | Yes         |       1 |
| gb18030_chinese_ci       | gb18030            | 248 | Yes        | Yes         |       2 |
| gb18030_bin              | gb18030            | 249 |            | Yes         |       1 |
| gb18030_unicode_520_ci   | gb18030            | 250 |            | Yes         |       8 |
+--------------------------+--------------------+-----+------------+-------------+---------+
222 rows in set (0.00 sec)

mysql> SHOW COLLATION;
+--------------------------+----------+-----+---------+----------+---------+
| Collation                | Charset  | Id  | Default | Compiled | Sortlen |
+--------------------------+----------+-----+---------+----------+---------+
| big5_chinese_ci          | big5     |   1 | Yes     | Yes      |       1 |
| big5_bin                 | big5     |  84 |         | Yes      |       1 |
| dec8_swedish_ci          | dec8     |   3 | Yes     | Yes      |       1 |
| dec8_bin                 | dec8     |  69 |         | Yes      |       1 |
| cp850_general_ci         | cp850    |   4 | Yes     | Yes      |       1 |
| cp850_bin                | cp850    |  80 |         | Yes      |       1 |
| hp8_english_ci           | hp8      |   6 | Yes     | Yes      |       1 |
| hp8_bin                  | hp8      |  72 |         | Yes      |       1 |
| koi8r_general_ci         | koi8r    |   7 | Yes     | Yes      |       1 |
| koi8r_bin                | koi8r    |  74 |         | Yes      |       1 |
| latin1_german1_ci        | latin1   |   5 |         | Yes      |       1 |
| latin1_swedish_ci        | latin1   |   8 | Yes     | Yes      |       1 |
| latin1_danish_ci         | latin1   |  15 |         | Yes      |       1 |
| latin1_german2_ci        | latin1   |  31 |         | Yes      |       2 |
| latin1_bin               | latin1   |  47 |         | Yes      |       1 |
| latin1_general_ci        | latin1   |  48 |         | Yes      |       1 |
| latin1_general_cs        | latin1   |  49 |         | Yes      |       1 |
| latin1_spanish_ci        | latin1   |  94 |         | Yes      |       1 |
| latin2_czech_cs          | latin2   |   2 |         | Yes      |       4 |
| latin2_general_ci        | latin2   |   9 | Yes     | Yes      |       1 |
| latin2_hungarian_ci      | latin2   |  21 |         | Yes      |       1 |
| latin2_croatian_ci       | latin2   |  27 |         | Yes      |       1 |
| latin2_bin               | latin2   |  77 |         | Yes      |       1 |
| swe7_swedish_ci          | swe7     |  10 | Yes     | Yes      |       1 |
| swe7_bin                 | swe7     |  82 |         | Yes      |       1 |
| ascii_general_ci         | ascii    |  11 | Yes     | Yes      |       1 |
| ascii_bin                | ascii    |  65 |         | Yes      |       1 |
| ujis_japanese_ci         | ujis     |  12 | Yes     | Yes      |       1 |
| ujis_bin                 | ujis     |  91 |         | Yes      |       1 |
| sjis_japanese_ci         | sjis     |  13 | Yes     | Yes      |       1 |
| sjis_bin                 | sjis     |  88 |         | Yes      |       1 |
| hebrew_general_ci        | hebrew   |  16 | Yes     | Yes      |       1 |
| hebrew_bin               | hebrew   |  71 |         | Yes      |       1 |
| tis620_thai_ci           | tis620   |  18 | Yes     | Yes      |       4 |
| tis620_bin               | tis620   |  89 |         | Yes      |       1 |
| euckr_korean_ci          | euckr    |  19 | Yes     | Yes      |       1 |
| euckr_bin                | euckr    |  85 |         | Yes      |       1 |
| koi8u_general_ci         | koi8u    |  22 | Yes     | Yes      |       1 |
| koi8u_bin                | koi8u    |  75 |         | Yes      |       1 |
| gb2312_chinese_ci        | gb2312   |  24 | Yes     | Yes      |       1 |
| gb2312_bin               | gb2312   |  86 |         | Yes      |       1 |
| greek_general_ci         | greek    |  25 | Yes     | Yes      |       1 |
| greek_bin                | greek    |  70 |         | Yes      |       1 |
| cp1250_general_ci        | cp1250   |  26 | Yes     | Yes      |       1 |
| cp1250_czech_cs          | cp1250   |  34 |         | Yes      |       2 |
| cp1250_croatian_ci       | cp1250   |  44 |         | Yes      |       1 |
| cp1250_bin               | cp1250   |  66 |         | Yes      |       1 |
| cp1250_polish_ci         | cp1250   |  99 |         | Yes      |       1 |
| gbk_chinese_ci           | gbk      |  28 | Yes     | Yes      |       1 |
| gbk_bin                  | gbk      |  87 |         | Yes      |       1 |
| latin5_turkish_ci        | latin5   |  30 | Yes     | Yes      |       1 |
| latin5_bin               | latin5   |  78 |         | Yes      |       1 |
| armscii8_general_ci      | armscii8 |  32 | Yes     | Yes      |       1 |
| armscii8_bin             | armscii8 |  64 |         | Yes      |       1 |
| utf8_general_ci          | utf8     |  33 | Yes     | Yes      |       1 |
| utf8_bin                 | utf8     |  83 |         | Yes      |       1 |
| utf8_unicode_ci          | utf8     | 192 |         | Yes      |       8 |
| utf8_icelandic_ci        | utf8     | 193 |         | Yes      |       8 |
| utf8_latvian_ci          | utf8     | 194 |         | Yes      |       8 |
| utf8_romanian_ci         | utf8     | 195 |         | Yes      |       8 |
| utf8_slovenian_ci        | utf8     | 196 |         | Yes      |       8 |
| utf8_polish_ci           | utf8     | 197 |         | Yes      |       8 |
| utf8_estonian_ci         | utf8     | 198 |         | Yes      |       8 |
| utf8_spanish_ci          | utf8     | 199 |         | Yes      |       8 |
| utf8_swedish_ci          | utf8     | 200 |         | Yes      |       8 |
| utf8_turkish_ci          | utf8     | 201 |         | Yes      |       8 |
| utf8_czech_ci            | utf8     | 202 |         | Yes      |       8 |
| utf8_danish_ci           | utf8     | 203 |         | Yes      |       8 |
| utf8_lithuanian_ci       | utf8     | 204 |         | Yes      |       8 |
| utf8_slovak_ci           | utf8     | 205 |         | Yes      |       8 |
| utf8_spanish2_ci         | utf8     | 206 |         | Yes      |       8 |
| utf8_roman_ci            | utf8     | 207 |         | Yes      |       8 |
| utf8_persian_ci          | utf8     | 208 |         | Yes      |       8 |
| utf8_esperanto_ci        | utf8     | 209 |         | Yes      |       8 |
| utf8_hungarian_ci        | utf8     | 210 |         | Yes      |       8 |
| utf8_sinhala_ci          | utf8     | 211 |         | Yes      |       8 |
| utf8_german2_ci          | utf8     | 212 |         | Yes      |       8 |
| utf8_croatian_ci         | utf8     | 213 |         | Yes      |       8 |
| utf8_unicode_520_ci      | utf8     | 214 |         | Yes      |       8 |
| utf8_vietnamese_ci       | utf8     | 215 |         | Yes      |       8 |
| utf8_general_mysql500_ci | utf8     | 223 |         | Yes      |       1 |
| ucs2_general_ci          | ucs2     |  35 | Yes     | Yes      |       1 |
| ucs2_bin                 | ucs2     |  90 |         | Yes      |       1 |
| ucs2_unicode_ci          | ucs2     | 128 |         | Yes      |       8 |
| ucs2_icelandic_ci        | ucs2     | 129 |         | Yes      |       8 |
| ucs2_latvian_ci          | ucs2     | 130 |         | Yes      |       8 |
| ucs2_romanian_ci         | ucs2     | 131 |         | Yes      |       8 |
| ucs2_slovenian_ci        | ucs2     | 132 |         | Yes      |       8 |
| ucs2_polish_ci           | ucs2     | 133 |         | Yes      |       8 |
| ucs2_estonian_ci         | ucs2     | 134 |         | Yes      |       8 |
| ucs2_spanish_ci          | ucs2     | 135 |         | Yes      |       8 |
| ucs2_swedish_ci          | ucs2     | 136 |         | Yes      |       8 |
| ucs2_turkish_ci          | ucs2     | 137 |         | Yes      |       8 |
| ucs2_czech_ci            | ucs2     | 138 |         | Yes      |       8 |
| ucs2_danish_ci           | ucs2     | 139 |         | Yes      |       8 |
| ucs2_lithuanian_ci       | ucs2     | 140 |         | Yes      |       8 |
| ucs2_slovak_ci           | ucs2     | 141 |         | Yes      |       8 |
| ucs2_spanish2_ci         | ucs2     | 142 |         | Yes      |       8 |
| ucs2_roman_ci            | ucs2     | 143 |         | Yes      |       8 |
| ucs2_persian_ci          | ucs2     | 144 |         | Yes      |       8 |
| ucs2_esperanto_ci        | ucs2     | 145 |         | Yes      |       8 |
| ucs2_hungarian_ci        | ucs2     | 146 |         | Yes      |       8 |
| ucs2_sinhala_ci          | ucs2     | 147 |         | Yes      |       8 |
| ucs2_german2_ci          | ucs2     | 148 |         | Yes      |       8 |
| ucs2_croatian_ci         | ucs2     | 149 |         | Yes      |       8 |
| ucs2_unicode_520_ci      | ucs2     | 150 |         | Yes      |       8 |
| ucs2_vietnamese_ci       | ucs2     | 151 |         | Yes      |       8 |
| ucs2_general_mysql500_ci | ucs2     | 159 |         | Yes      |       1 |
| cp866_general_ci         | cp866    |  36 | Yes     | Yes      |       1 |
| cp866_bin                | cp866    |  68 |         | Yes      |       1 |
| keybcs2_general_ci       | keybcs2  |  37 | Yes     | Yes      |       1 |
| keybcs2_bin              | keybcs2  |  73 |         | Yes      |       1 |
| macce_general_ci         | macce    |  38 | Yes     | Yes      |       1 |
| macce_bin                | macce    |  43 |         | Yes      |       1 |
| macroman_general_ci      | macroman |  39 | Yes     | Yes      |       1 |
| macroman_bin             | macroman |  53 |         | Yes      |       1 |
| cp852_general_ci         | cp852    |  40 | Yes     | Yes      |       1 |
| cp852_bin                | cp852    |  81 |         | Yes      |       1 |
| latin7_estonian_cs       | latin7   |  20 |         | Yes      |       1 |
| latin7_general_ci        | latin7   |  41 | Yes     | Yes      |       1 |
| latin7_general_cs        | latin7   |  42 |         | Yes      |       1 |
| latin7_bin               | latin7   |  79 |         | Yes      |       1 |
| utf8mb4_general_ci       | utf8mb4  |  45 | Yes     | Yes      |       1 |
| utf8mb4_bin              | utf8mb4  |  46 |         | Yes      |       1 |
| utf8mb4_unicode_ci       | utf8mb4  | 224 |         | Yes      |       8 |
| utf8mb4_icelandic_ci     | utf8mb4  | 225 |         | Yes      |       8 |
| utf8mb4_latvian_ci       | utf8mb4  | 226 |         | Yes      |       8 |
| utf8mb4_romanian_ci      | utf8mb4  | 227 |         | Yes      |       8 |
| utf8mb4_slovenian_ci     | utf8mb4  | 228 |         | Yes      |       8 |
| utf8mb4_polish_ci        | utf8mb4  | 229 |         | Yes      |       8 |
| utf8mb4_estonian_ci      | utf8mb4  | 230 |         | Yes      |       8 |
| utf8mb4_spanish_ci       | utf8mb4  | 231 |         | Yes      |       8 |
| utf8mb4_swedish_ci       | utf8mb4  | 232 |         | Yes      |       8 |
| utf8mb4_turkish_ci       | utf8mb4  | 233 |         | Yes      |       8 |
| utf8mb4_czech_ci         | utf8mb4  | 234 |         | Yes      |       8 |
| utf8mb4_danish_ci        | utf8mb4  | 235 |         | Yes      |       8 |
| utf8mb4_lithuanian_ci    | utf8mb4  | 236 |         | Yes      |       8 |
| utf8mb4_slovak_ci        | utf8mb4  | 237 |         | Yes      |       8 |
| utf8mb4_spanish2_ci      | utf8mb4  | 238 |         | Yes      |       8 |
| utf8mb4_roman_ci         | utf8mb4  | 239 |         | Yes      |       8 |
| utf8mb4_persian_ci       | utf8mb4  | 240 |         | Yes      |       8 |
| utf8mb4_esperanto_ci     | utf8mb4  | 241 |         | Yes      |       8 |
| utf8mb4_hungarian_ci     | utf8mb4  | 242 |         | Yes      |       8 |
| utf8mb4_sinhala_ci       | utf8mb4  | 243 |         | Yes      |       8 |
| utf8mb4_german2_ci       | utf8mb4  | 244 |         | Yes      |       8 |
| utf8mb4_croatian_ci      | utf8mb4  | 245 |         | Yes      |       8 |
| utf8mb4_unicode_520_ci   | utf8mb4  | 246 |         | Yes      |       8 |
| utf8mb4_vietnamese_ci    | utf8mb4  | 247 |         | Yes      |       8 |
| cp1251_bulgarian_ci      | cp1251   |  14 |         | Yes      |       1 |
| cp1251_ukrainian_ci      | cp1251   |  23 |         | Yes      |       1 |
| cp1251_bin               | cp1251   |  50 |         | Yes      |       1 |
| cp1251_general_ci        | cp1251   |  51 | Yes     | Yes      |       1 |
| cp1251_general_cs        | cp1251   |  52 |         | Yes      |       1 |
| utf16_general_ci         | utf16    |  54 | Yes     | Yes      |       1 |
| utf16_bin                | utf16    |  55 |         | Yes      |       1 |
| utf16_unicode_ci         | utf16    | 101 |         | Yes      |       8 |
| utf16_icelandic_ci       | utf16    | 102 |         | Yes      |       8 |
| utf16_latvian_ci         | utf16    | 103 |         | Yes      |       8 |
| utf16_romanian_ci        | utf16    | 104 |         | Yes      |       8 |
| utf16_slovenian_ci       | utf16    | 105 |         | Yes      |       8 |
| utf16_polish_ci          | utf16    | 106 |         | Yes      |       8 |
| utf16_estonian_ci        | utf16    | 107 |         | Yes      |       8 |
| utf16_spanish_ci         | utf16    | 108 |         | Yes      |       8 |
| utf16_swedish_ci         | utf16    | 109 |         | Yes      |       8 |
| utf16_turkish_ci         | utf16    | 110 |         | Yes      |       8 |
| utf16_czech_ci           | utf16    | 111 |         | Yes      |       8 |
| utf16_danish_ci          | utf16    | 112 |         | Yes      |       8 |
| utf16_lithuanian_ci      | utf16    | 113 |         | Yes      |       8 |
| utf16_slovak_ci          | utf16    | 114 |         | Yes      |       8 |
| utf16_spanish2_ci        | utf16    | 115 |         | Yes      |       8 |
| utf16_roman_ci           | utf16    | 116 |         | Yes      |       8 |
| utf16_persian_ci         | utf16    | 117 |         | Yes      |       8 |
| utf16_esperanto_ci       | utf16    | 118 |         | Yes      |       8 |
| utf16_hungarian_ci       | utf16    | 119 |         | Yes      |       8 |
| utf16_sinhala_ci         | utf16    | 120 |         | Yes      |       8 |
| utf16_german2_ci         | utf16    | 121 |         | Yes      |       8 |
| utf16_croatian_ci        | utf16    | 122 |         | Yes      |       8 |
| utf16_unicode_520_ci     | utf16    | 123 |         | Yes      |       8 |
| utf16_vietnamese_ci      | utf16    | 124 |         | Yes      |       8 |
| utf16le_general_ci       | utf16le  |  56 | Yes     | Yes      |       1 |
| utf16le_bin              | utf16le  |  62 |         | Yes      |       1 |
| cp1256_general_ci        | cp1256   |  57 | Yes     | Yes      |       1 |
| cp1256_bin               | cp1256   |  67 |         | Yes      |       1 |
| cp1257_lithuanian_ci     | cp1257   |  29 |         | Yes      |       1 |
| cp1257_bin               | cp1257   |  58 |         | Yes      |       1 |
| cp1257_general_ci        | cp1257   |  59 | Yes     | Yes      |       1 |
| utf32_general_ci         | utf32    |  60 | Yes     | Yes      |       1 |
| utf32_bin                | utf32    |  61 |         | Yes      |       1 |
| utf32_unicode_ci         | utf32    | 160 |         | Yes      |       8 |
| utf32_icelandic_ci       | utf32    | 161 |         | Yes      |       8 |
| utf32_latvian_ci         | utf32    | 162 |         | Yes      |       8 |
| utf32_romanian_ci        | utf32    | 163 |         | Yes      |       8 |
| utf32_slovenian_ci       | utf32    | 164 |         | Yes      |       8 |
| utf32_polish_ci          | utf32    | 165 |         | Yes      |       8 |
| utf32_estonian_ci        | utf32    | 166 |         | Yes      |       8 |
| utf32_spanish_ci         | utf32    | 167 |         | Yes      |       8 |
| utf32_swedish_ci         | utf32    | 168 |         | Yes      |       8 |
| utf32_turkish_ci         | utf32    | 169 |         | Yes      |       8 |
| utf32_czech_ci           | utf32    | 170 |         | Yes      |       8 |
| utf32_danish_ci          | utf32    | 171 |         | Yes      |       8 |
| utf32_lithuanian_ci      | utf32    | 172 |         | Yes      |       8 |
| utf32_slovak_ci          | utf32    | 173 |         | Yes      |       8 |
| utf32_spanish2_ci        | utf32    | 174 |         | Yes      |       8 |
| utf32_roman_ci           | utf32    | 175 |         | Yes      |       8 |
| utf32_persian_ci         | utf32    | 176 |         | Yes      |       8 |
| utf32_esperanto_ci       | utf32    | 177 |         | Yes      |       8 |
| utf32_hungarian_ci       | utf32    | 178 |         | Yes      |       8 |
| utf32_sinhala_ci         | utf32    | 179 |         | Yes      |       8 |
| utf32_german2_ci         | utf32    | 180 |         | Yes      |       8 |
| utf32_croatian_ci        | utf32    | 181 |         | Yes      |       8 |
| utf32_unicode_520_ci     | utf32    | 182 |         | Yes      |       8 |
| utf32_vietnamese_ci      | utf32    | 183 |         | Yes      |       8 |
| binary                   | binary   |  63 | Yes     | Yes      |       1 |
| geostd8_general_ci       | geostd8  |  92 | Yes     | Yes      |       1 |
| geostd8_bin              | geostd8  |  93 |         | Yes      |       1 |
| cp932_japanese_ci        | cp932    |  95 | Yes     | Yes      |       1 |
| cp932_bin                | cp932    |  96 |         | Yes      |       1 |
| eucjpms_japanese_ci      | eucjpms  |  97 | Yes     | Yes      |       1 |
| eucjpms_bin              | eucjpms  |  98 |         | Yes      |       1 |
| gb18030_chinese_ci       | gb18030  | 248 | Yes     | Yes      |       2 |
| gb18030_bin              | gb18030  | 249 |         | Yes      |       1 |
| gb18030_unicode_520_ci   | gb18030  | 250 |         | Yes      |       8 |
+--------------------------+----------+-----+---------+----------+---------+
222 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457

12、USER_PRIVILEGES

用户权限表。内容来自：mysql.user 授权表。

mysql> select * from USER_PRIVILEGES;
+-----------------------------+---------------+-------------------------+--------------+
| GRANTEE                     | TABLE_CATALOG | PRIVILEGE_TYPE          | IS_GRANTABLE |
+-----------------------------+---------------+-------------------------+--------------+
| 'root'@'localhost'          | def           | SELECT                  | YES          |
| 'root'@'localhost'          | def           | INSERT                  | YES          |
| 'root'@'localhost'          | def           | UPDATE                  | YES          |
| 'root'@'localhost'          | def           | DELETE                  | YES          |
| 'root'@'localhost'          | def           | CREATE                  | YES          |
| 'root'@'localhost'          | def           | DROP                    | YES          |
| 'root'@'localhost'          | def           | RELOAD                  | YES          |
| 'root'@'localhost'          | def           | SHUTDOWN                | YES          |
| 'root'@'localhost'          | def           | PROCESS                 | YES          |
| 'root'@'localhost'          | def           | FILE                    | YES          |
| 'root'@'localhost'          | def           | REFERENCES              | YES          |
| 'root'@'localhost'          | def           | INDEX                   | YES          |
| 'root'@'localhost'          | def           | ALTER                   | YES          |
| 'root'@'localhost'          | def           | SHOW DATABASES          | YES          |
| 'root'@'localhost'          | def           | SUPER                   | YES          |
| 'root'@'localhost'          | def           | CREATE TEMPORARY TABLES | YES          |
| 'root'@'localhost'          | def           | LOCK TABLES             | YES          |
| 'root'@'localhost'          | def           | EXECUTE                 | YES          |
| 'root'@'localhost'          | def           | REPLICATION SLAVE       | YES          |
| 'root'@'localhost'          | def           | REPLICATION CLIENT      | YES          |
| 'root'@'localhost'          | def           | CREATE VIEW             | YES          |
| 'root'@'localhost'          | def           | SHOW VIEW               | YES          |
| 'root'@'localhost'          | def           | CREATE ROUTINE          | YES          |
| 'root'@'localhost'          | def           | ALTER ROUTINE           | YES          |
| 'root'@'localhost'          | def           | CREATE USER             | YES          |
| 'root'@'localhost'          | def           | EVENT                   | YES          |
| 'root'@'localhost'          | def           | TRIGGER                 | YES          |
| 'root'@'localhost'          | def           | CREATE TABLESPACE       | YES          |
| 'mysql.session'@'localhost' | def           | SUPER                   | NO           |
| 'mysql.sys'@'localhost'     | def           | USAGE                   | NO           |
| 'wgx'@'localhost'           | def           | USAGE                   | NO           |
| 'admin'@'192.168.1.%'       | def           | SELECT                  | NO           |
| 'admin'@'192.168.1.%'       | def           | INSERT                  | NO           |
| 'admin'@'192.168.1.%'       | def           | UPDATE                  | NO           |
| 'admin'@'192.168.1.%'       | def           | DELETE                  | NO           |
| 'admin'@'192.168.1.%'       | def           | CREATE                  | NO           |
| 'admin'@'192.168.1.%'       | def           | DROP                    | NO           |
| 'admin'@'192.168.1.%'       | def           | RELOAD                  | NO           |
| 'admin'@'192.168.1.%'       | def           | SHUTDOWN                | NO           |
| 'admin'@'192.168.1.%'       | def           | PROCESS                 | NO           |
| 'admin'@'192.168.1.%'       | def           | FILE                    | NO           |
| 'admin'@'192.168.1.%'       | def           | REFERENCES              | NO           |
| 'admin'@'192.168.1.%'       | def           | INDEX                   | NO           |
| 'admin'@'192.168.1.%'       | def           | ALTER                   | NO           |
| 'admin'@'192.168.1.%'       | def           | SHOW DATABASES          | NO           |
| 'admin'@'192.168.1.%'       | def           | SUPER                   | NO           |
| 'admin'@'192.168.1.%'       | def           | CREATE TEMPORARY TABLES | NO           |
| 'admin'@'192.168.1.%'       | def           | LOCK TABLES             | NO           |
| 'admin'@'192.168.1.%'       | def           | EXECUTE                 | NO           |
| 'admin'@'192.168.1.%'       | def           | REPLICATION SLAVE       | NO           |
| 'admin'@'192.168.1.%'       | def           | REPLICATION CLIENT      | NO           |
| 'admin'@'192.168.1.%'       | def           | CREATE VIEW             | NO           |
| 'admin'@'192.168.1.%'       | def           | SHOW VIEW               | NO           |
| 'admin'@'192.168.1.%'       | def           | CREATE ROUTINE          | NO           |
| 'admin'@'192.168.1.%'       | def           | ALTER ROUTINE           | NO           |
| 'admin'@'192.168.1.%'       | def           | CREATE USER             | NO           |
| 'admin'@'192.168.1.%'       | def           | EVENT                   | NO           |
| 'admin'@'192.168.1.%'       | def           | TRIGGER                 | NO           |
| 'admin'@'192.168.1.%'       | def           | CREATE TABLESPACE       | NO           |
+-----------------------------+---------------+-------------------------+--------------+
59 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65

13、SCHEMA_PRIVILEGES

数据库权限表。内容来自：mysql.db 授权表。

mysql> select * from SCHEMA_PRIVILEGES;
+-----------------------------+---------------+--------------------+----------------+--------------+
| GRANTEE                     | TABLE_CATALOG | TABLE_SCHEMA       | PRIVILEGE_TYPE | IS_GRANTABLE |
+-----------------------------+---------------+--------------------+----------------+--------------+
| 'mysql.sys'@'localhost'     | def           | sys                | TRIGGER        | NO           |
| 'mysql.session'@'localhost' | def           | performance_schema | SELECT         | NO           |
+-----------------------------+---------------+--------------------+----------------+--------------+
2 rows in set (0.00 sec)
1
2
3
4
5
6
7
8

14、TABLE_PRIVILEGES

表权限表。内容来自：mysql.tables_priv 授权表。

mysql> select * from TABLE_PRIVILEGES;
+----------------------------------+--------------+------------+----------------+--------------+
| GRANTEE          | TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | PRIVILEGE_TYPE | IS_GRANTABLE |
+------------------+---------------+--------------+------------+----------------+--------------+
| 'mysql.session'@'localhost' | def    | mysql    | user       | SELECT         | NO           |
| 'mysql.sys'@'localhost'     | def    | sys      | sys_config | SELECT         | NO           |
+-----------------------------+-----------------+-----------+----------------+--------------+
2 rows in set (0.00 sec)
1
2
3
4
5
6
7
8

15、COLUMN_PRIVILEGES

列权限表。内容来自：mysql.columns_priv 授权表。

mysql> select * from COLUMN_PRIVILEGES;
Empty set (0.00 sec)
1
2