时间：2021-07-01 10:21:17 帮助过：13人阅读
CHARACTER_SETS表提供了有关可用字符集的信息。
> desc CHARACTER_SETS;
+----------------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------------+-------------+------+-----+---------+-------+
| CHARACTER_SET_NAME | varchar(32) | NO | | | |
| DEFAULT_COLLATE_NAME | varchar(32) | NO | | | |
| DESCRIPTION | varchar(60) | NO | | | |
| MAXLEN | bigint(3) | NO | | 0 | |
+----------------------+-------------+------+-----+---------+-------+
> select * from CHARACTER_SETS where CHARACTER_SET_NAME like ‘utf8%‘;
+--------------------+----------------------+---------------+--------+
| CHARACTER_SET_NAME | DEFAULT_COLLATE_NAME | DESCRIPTION | MAXLEN |
+--------------------+----------------------+---------------+--------+
| utf8 | utf8_general_ci | UTF-8 Unicode | 3 |
| utf8mb4 | utf8mb4_general_ci | UTF-8 Unicode | 4 |
+--------------------+----------------------+---------------+--------+
COLLATIONS表提供有关每个字符集的排序规则的信息。
> desc COLLATIONS; +--------------------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------------------+-------------+------+-----+---------+-------+ | COLLATION_NAME | varchar(32) | NO | | | | | CHARACTER_SET_NAME | varchar(32) | NO | | | | | ID | bigint(11) | NO | | 0 | | | IS_DEFAULT | varchar(3) | NO | | | | | IS_COMPILED | varchar(3) | NO | | | | | SORTLEN | bigint(3) | NO | | 0 | | +--------------------+-------------+------+-----+---------+-------+
COLLATION_NAME is the collation name.
CHARACTER_SET_NAME is the name of the character set with which the collation is associated.
ID is the collation ID.
IS_DEFAULT indicates whether the collation is the default for its character set.
IS_COMPILED indicates whether the character set is compiled into the server.
SORTLEN is related to the amount of memory required to sort strings expressed in the character set.
每个character set会对应一定数量的collation：
> select * from COLLATIONS where COLLATION_NAME like ‘utf8%general%‘; +--------------------------+--------------------+-----+------------+-------------+---------+ | COLLATION_NAME | CHARACTER_SET_NAME | ID | IS_DEFAULT | IS_COMPILED | SORTLEN | +--------------------------+--------------------+-----+------------+-------------+---------+ | utf8_general_ci | utf8 | 33 | Yes | Yes | 1 | | utf8_general_mysql500_ci | utf8 | 223 | | Yes | 1 | | utf8mb4_general_ci | utf8mb4 | 45 | Yes | Yes | 1 | +--------------------------+--------------------+-----+------------+-------------+---------+
ci 是 case insensitive 的缩写（大小写敏感）， cs 是 case sensitive 的缩写。即，指定大小写是否敏感。??
COLLATION_CHARACTER_SET_APPLICABILITY表显示了哪种字符集适用于哪种排序方式。这些列相当于我们从SHOW COLLATION获得的前两个字段。
> desc COLLATION_CHARACTER_SET_APPLICABILITY; +--------------------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------------------+-------------+------+-----+---------+-------+ | COLLATION_NAME | varchar(32) | NO | | | | | CHARACTER_SET_NAME | varchar(32) | NO | | | | +--------------------+-------------+------+-----+---------+-------+
COLUMNS表提供表格中列的信息。
> 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.01 sec)
COLUMN_PRIVILEGES表提供有关列权限的信息。 这些信息来自mysql.columns_priv授权表。
> desc COLUMN_PRIVILEGES; +----------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------------+--------------+------+-----+---------+-------+ | GRANTEE | varchar(81) | NO | | | | | TABLE_CATALOG | varchar(512) | NO | | | | | TABLE_SCHEMA | varchar(64) | NO | | | | | TABLE_NAME | varchar(64) | NO | | | | | COLUMN_NAME | varchar(64) | NO | | | | | PRIVILEGE_TYPE | varchar(64) | NO | | | | | IS_GRANTABLE | varchar(3) | NO | | | | +----------------+--------------+------+-----+---------+-------+
PRIVILEGE_TYPE 可以包含一个（且只能有一个）这些值：SELECT，INSERT，UPDATE，REFERENCES。
如果用户具有GRANT OPTION权限，则IS_GRANTABLE为YES。 否则，IS_GRANTABLE 为NO。 输出不会将GRANT OPTION列为单独的权限。
ENGINES表提供了有关存储引擎的信息。
> desc ENGINES; +--------------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------------+-------------+------+-----+---------+-------+ | ENGINE | varchar(64) | NO | | | | | SUPPORT | varchar(8) | NO | | | | | COMMENT | varchar(80) | NO | | | | | TRANSACTIONS | varchar(3) | YES | | NULL | | | XA | varchar(3) | YES | | NULL | | | SAVEPOINTS | varchar(3) | YES | | NULL | | +--------------+-------------+------+-----+---------+-------+
ENGINES表是非标准表，其内容对应于SHOW ENGINES语句的列。
EVENTS表提供了有关定时事件（scheduled events）的信息，events类似与linux crontab计划任务，用于时间触发。通过单独或调用存储过程使用，在某一特定的时间点，触发相关的SQL语句或存储过程。
> desc events; +----------------------+---------------+------+-----+---------------------+-------+ | Field | Type | Null | Key | Default | Extra | +----------------------+---------------+------+-----+---------------------+-------+ | EVENT_CATALOG | varchar(64) | NO | | | | | EVENT_SCHEMA | varchar(64) | NO | | | | | EVENT_NAME | varchar(64) | NO | | | | | DEFINER | varchar(77) | NO | | | | | TIME_ZONE | varchar(64) | NO | | | | | EVENT_BODY | varchar(8) | NO | | | | | EVENT_DEFINITION | longtext | NO | | NULL | | | EVENT_TYPE | varchar(9) | NO | | | | | EXECUTE_AT | datetime | YES | | NULL | | | INTERVAL_VALUE | varchar(256) | YES | | NULL | | | INTERVAL_FIELD | varchar(18) | YES | | NULL | | | SQL_MODE | varchar(8192) | NO | | | | | STARTS | datetime | YES | | NULL | | | ENDS | datetime | YES | | NULL | | | STATUS | varchar(18) | NO | | | | | ON_COMPLETION | varchar(12) | NO | | | | | CREATED | datetime | NO | | 0000-00-00 00:00:00 | | | LAST_ALTERED | datetime | NO | | 0000-00-00 00:00:00 | | | LAST_EXECUTED | datetime | YES | | NULL | | | EVENT_COMMENT | varchar(64) | NO | | | | | ORIGINATOR | bigint(10) | NO | | 0 | | | CHARACTER_SET_CLIENT | varchar(32) | NO | | | | | COLLATION_CONNECTION | varchar(32) | NO | | | | | DATABASE_COLLATION | varchar(32) | NO | | | | +----------------------+---------------+------+-----+---------------------+-------+ 24 rows in set (0.00 sec)
• The EVENTS table is a nonstandard table. # EVENTS表是非标准表
• EVENT_CATALOG: The value of this column is always def.
• EVENT_SCHEMA: # events 所属的库
• EVENT_NAME: # event 名称
• DEFINER: # 创建EVENT的账号，‘user_name‘@‘host_name‘的格式
• TIME_ZONE: The event time zone, which is the time zone used for scheduling the event and that is in effect within the event as it executes. The default value is SYSTEM.
• EVENT_BODY: The language used for the statements in the event‘s DO clause; in MySQL 5.7, this is always SQL. This column is not to be confused with the column of the same name (now named EVENT_DEFINITION) that existed in earlier MySQL versions. # 不要与早期版本的同名项（现在是EVENT_DEFINITION）混淆
• EVENT_DEFINITION: # 时间需要执行的部分，即组成时间的Do子句。
• EVENT_TYPE: #事件重复类型，一次（transient）或重复（repeating）。
• EXECUTE_AT: #对于一次性事件，这是在用于创建事件的CREATE EVENT语句的AT子句中指定的DATETIME值；如果事件的计时由EVERY子句而不是AT子句确定（也就是说，事件类型为repeating），则此列的值为NULL。
• INTERVAL_VALUE: #对于定期事件，此列包含事件的EVERY子句的数字部分。 对于一次性事件（即其定时由AT子句确定的事件），此列为NULL。
• INTERVAL_FIELD: #对于定期事件，此列包含EVERY子句的单位部分，用于管理事件的时间。因此，此列包含“YEAR”，“QUARTER”，“DAY”等值。对于一次性事件（即其定时由AT子句确定的事件），此列为NULL
• SQL_MODE: #在创建或更改事件并在其下执行事件时有效的SQL模式。
• STARTS: #事件定义时包含了STARTS子句的事件，此列包含相应的DATETIME值。 与EXECUTE_AT列一样，此值可解析所使用的任何表达式。 如果没有影响事件时间的STARTS子句，则此列为NULL
• ENDS: #事件定义时包含了ENDS子句的循环事件，此列包含相应的DATETIME值。 与EXECUTE_AT列一样，此值可解析所使用的任何表达式。 如果没有影响事件时间的ENDS子句，则此列为NULL。
• STATUS: #ENABLE, DISABLE或SLAVESIDE_DISABLED 三个值之一。 SLAVESIDE_DISABLED表示事件的创建发生在另一个作为复制主服务器的MySQL服务器上，并被复制到当前作为从服务器的MySQL服务器上，但事件目前不在从服务器上执行。
• ON_COMPLETION: One of the two values PRESERVE or NOT PRESERVE.
• CREATED: #事件创建时间
• LAST_ALTERED: #事件最后修改时间
• LAST_EXECUTED: #事件最后一次执行的时间
• EVENT_COMMENT: #事件的注释
• ORIGINATOR: #在其上创建事件的MySQL服务器的服务器ID; 用于复制。 默认值是0。
• CHARACTER_SET_CLIENT: #事件创建时character_set_client系统变量的session值
• COLLATION_CONNECTION: #事件创建时collation_connection系统变量的session值
• DATABASE_COLLATION: #与事件关联的数据库的排序规则。
MySQL 5.7 INFORMATION_SCHEMA 详解
标签：同名 create 而不是 ref disabled proc nat osi one