MySQL的information_schema库
2024-08-29 18:07:18
information_schema数据库是MySQL自带的，它提供了访问数据库元数据的方式。
什么是元数据呢？元数据是关于数据的数据，如数据库名或表名、列的数据类型，或访问权限。有些时候用于表述该信息的其他术语包括“数据字典”和“系统目录”。
在MySQL中，把information_schema看作是一个数据库，确切的说是信息数据库。
其中保存着关于MySQL服务器所维护的所有其他数据库的信息。如数据库名、数据库的表、表栏的数据类型与访问权限等。
在information_schema中，有数个只读表。它们实际上是视图，而不是基本表，因此，你将无法看到与之相关的任何文件。
information_schema数据库说明：
+---------------------------------------+
| Tables_in_information_schema |
+---------------------------------------+
| CHARACTER_SETS |
| COLLATIONS |
| COLLATION_CHARACTER_SET_APPLICABILITY |
| COLUMNS |
| COLUMN_PRIVILEGES |
| ENGINES |
| EVENTS |
| FILES |
| GLOBAL_STATUS |
| GLOBAL_VARIABLES |
| KEY_COLUMN_USAGE |
| 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_BUFFER_PAGE |
| INNODB_TRX |
| INNODB_BUFFER_POOL_STATS |
| INNODB_LOCK_WAITS |
| INNODB_CMPMEM |
| INNODB_CMP |
| INNODB_LOCKS |
| INNODB_CMPMEM_RESET |
| INNODB_CMP_RESET |
| INNODB_BUFFER_PAGE_LRU |
+---------------------------------------+
information_schema数据库
SCHEMATA表：提供了当前MySQL实例中所有数据库的信息。show databases;的结果取值于此。
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 | civil | utf8 | utf8_general_ci | NULL |
| def | class_7 | utf8 | utf8_general_ci | NULL |
| def | db1 | utf8 | utf8_general_ci | NULL |
| def | express | utf8 | utf8_general_ci | NULL |
| def | mysql | utf8 | utf8_general_ci | NULL |
| def | performance_schema | utf8 | utf8_general_ci | NULL |
| def | test | utf8 | utf8_general_ci | NULL |
+--------------+--------------------+----------------------------+------------------------+----------+
8 rows in set (0.00 sec)
SCHEMATA表的内容
mysql> desc schemata;
+----------------------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------------------+--------------+------+-----+---------+-------+
| CATALOG_NAME | varchar(512) | NO | | | |
| SCHEMA_NAME | varchar(64) | NO | | | |
| DEFAULT_CHARACTER_SET_NAME | varchar(32) | NO | | | |
| DEFAULT_COLLATION_NAME | varchar(32) | NO | | | |
| SQL_PATH | varchar(512) | YES | | NULL | |
+----------------------------+--------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
SCHEMATA的结构
从表中可以看出，SCHEMATA中包含每个库的名字、字符集等信息。
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 | |
| 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(27) | NO | | | |
| PRIVILEGES | varchar(80) | NO | | | |
| COLUMN_COMMENT | varchar(1024) | NO | | | |
+--------------------------+---------------------+------+-----+---------+-------+
19 rows in set (0.00 sec)
COLUMNS的结构属性
TABLES表：提供了关于数据库中的表的信息（包括视图）。详细表述了某个表属于哪个schema（概述）、表类型、表引擎、创建时间等信息。show tables from schemaname的结果取值于此。
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.00 sec)
TABLES表的结构属性
STATISTICS表：提供了关于表索引的信息。show index from schemaname.tablename的取值于此。
+---------------+---------------+------+-----+---------+-------+
| 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 | | | |
+---------------+---------------+------+-----+---------+-------+
STATISTICS表的数据结构属性
USER_PRIVILEGES（用户权限）表：给出了关于方案（数据库）权限的信息。该信息源来自mysql.user授权表，是非标准表。
+--------------------------------+---------------+-------------------------+--------------+
| 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 |
| 'root'@'localhost.localdomain' | def | SELECT | YES |
| 'root'@'localhost.localdomain' | def | INSERT | YES |
| 'root'@'localhost.localdomain' | def | UPDATE | YES |
| 'root'@'localhost.localdomain' | def | DELETE | YES |
| 'root'@'localhost.localdomain' | def | CREATE | YES |
| 'root'@'localhost.localdomain' | def | DROP | YES |
| 'root'@'localhost.localdomain' | def | RELOAD | YES |
| 'root'@'localhost.localdomain' | def | SHUTDOWN | YES |
| 'root'@'localhost.localdomain' | def | PROCESS | YES |
| 'root'@'localhost.localdomain' | def | FILE | YES |
| 'root'@'localhost.localdomain' | def | REFERENCES | YES |
| 'root'@'localhost.localdomain' | def | INDEX | YES |
| 'root'@'localhost.localdomain' | def | ALTER | YES |
| 'root'@'localhost.localdomain' | def | SHOW DATABASES | YES |
| 'root'@'localhost.localdomain' | def | SUPER | YES |
| 'root'@'localhost.localdomain' | def | CREATE TEMPORARY TABLES | YES |
| 'root'@'localhost.localdomain' | def | LOCK TABLES | YES |
| 'root'@'localhost.localdomain' | def | EXECUTE | YES |
| 'root'@'localhost.localdomain' | def | REPLICATION SLAVE | YES |
| 'root'@'localhost.localdomain' | def | REPLICATION CLIENT | YES |
| 'root'@'localhost.localdomain' | def | CREATE VIEW | YES |
| 'root'@'localhost.localdomain' | def | SHOW VIEW | YES |
| 'root'@'localhost.localdomain' | def | CREATE ROUTINE | YES |
| 'root'@'localhost.localdomain' | def | ALTER ROUTINE | YES |
| 'root'@'localhost.localdomain' | def | CREATE USER | YES |
| 'root'@'localhost.localdomain' | def | EVENT | YES |
| 'root'@'localhost.localdomain' | def | TRIGGER | YES |
| 'root'@'localhost.localdomain' | def | CREATE TABLESPACE | YES |
| 'root'@'127.0.0.1' | def | SELECT | YES |
| 'root'@'127.0.0.1' | def | INSERT | YES |
| 'root'@'127.0.0.1' | def | UPDATE | YES |
| 'root'@'127.0.0.1' | def | DELETE | YES |
| 'root'@'127.0.0.1' | def | CREATE | YES |
| 'root'@'127.0.0.1' | def | DROP | YES |
| 'root'@'127.0.0.1' | def | RELOAD | YES |
| 'root'@'127.0.0.1' | def | SHUTDOWN | YES |
| 'root'@'127.0.0.1' | def | PROCESS | YES |
| 'root'@'127.0.0.1' | def | FILE | YES |
| 'root'@'127.0.0.1' | def | REFERENCES | YES |
| 'root'@'127.0.0.1' | def | INDEX | YES |
| 'root'@'127.0.0.1' | def | ALTER | YES |
| 'root'@'127.0.0.1' | def | SHOW DATABASES | YES |
| 'root'@'127.0.0.1' | def | SUPER | YES |
| 'root'@'127.0.0.1' | def | CREATE TEMPORARY TABLES | YES |
| 'root'@'127.0.0.1' | def | LOCK TABLES | YES |
| 'root'@'127.0.0.1' | def | EXECUTE | YES |
| 'root'@'127.0.0.1' | def | REPLICATION SLAVE | YES |
| 'root'@'127.0.0.1' | def | REPLICATION CLIENT | YES |
| 'root'@'127.0.0.1' | def | CREATE VIEW | YES |
| 'root'@'127.0.0.1' | def | SHOW VIEW | YES |
| 'root'@'127.0.0.1' | def | CREATE ROUTINE | YES |
| 'root'@'127.0.0.1' | def | ALTER ROUTINE | YES |
| 'root'@'127.0.0.1' | def | CREATE USER | YES |
| 'root'@'127.0.0.1' | def | EVENT | YES |
| 'root'@'127.0.0.1' | def | TRIGGER | YES |
| 'root'@'127.0.0.1' | def | CREATE TABLESPACE | YES |
| 'root'@'::1' | def | SELECT | YES |
| 'root'@'::1' | def | INSERT | YES |
| 'root'@'::1' | def | UPDATE | YES |
| 'root'@'::1' | def | DELETE | YES |
| 'root'@'::1' | def | CREATE | YES |
| 'root'@'::1' | def | DROP | YES |
| 'root'@'::1' | def | RELOAD | YES |
| 'root'@'::1' | def | SHUTDOWN | YES |
| 'root'@'::1' | def | PROCESS | YES |
| 'root'@'::1' | def | FILE | YES |
| 'root'@'::1' | def | REFERENCES | YES |
| 'root'@'::1' | def | INDEX | YES |
| 'root'@'::1' | def | ALTER | YES |
| 'root'@'::1' | def | SHOW DATABASES | YES |
| 'root'@'::1' | def | SUPER | YES |
| 'root'@'::1' | def | CREATE TEMPORARY TABLES | YES |
| 'root'@'::1' | def | LOCK TABLES | YES |
| 'root'@'::1' | def | EXECUTE | YES |
| 'root'@'::1' | def | REPLICATION SLAVE | YES |
| 'root'@'::1' | def | REPLICATION CLIENT | YES |
| 'root'@'::1' | def | CREATE VIEW | YES |
| 'root'@'::1' | def | SHOW VIEW | YES |
| 'root'@'::1' | def | CREATE ROUTINE | YES |
| 'root'@'::1' | def | ALTER ROUTINE | YES |
| 'root'@'::1' | def | CREATE USER | YES |
| 'root'@'::1' | def | EVENT | YES |
| 'root'@'::1' | def | TRIGGER | YES |
| 'root'@'::1' | def | CREATE TABLESPACE | YES |
| 'maoxian'@'localhost' | def | USAGE | NO |
| ''@'localhost' | def | USAGE | NO |
| ''@'localhost.localdomain' | def | USAGE | NO |
| 'kebi'@'192.168.%.%' | def | SELECT | NO |
| 'kebi'@'192.168.%.%' | def | INSERT | NO |
| 'kebi'@'192.168.%.%' | def | UPDATE | NO |
| 'kebi'@'192.168.%.%' | def | DELETE | NO |
| 'kebi'@'192.168.%.%' | def | CREATE | NO |
| 'kebi'@'192.168.%.%' | def | DROP | NO |
| 'kebi'@'192.168.%.%' | def | RELOAD | NO |
| 'kebi'@'192.168.%.%' | def | SHUTDOWN | NO |
| 'kebi'@'192.168.%.%' | def | PROCESS | NO |
| 'kebi'@'192.168.%.%' | def | FILE | NO |
| 'kebi'@'192.168.%.%' | def | REFERENCES | NO |
| 'kebi'@'192.168.%.%' | def | INDEX | NO |
| 'kebi'@'192.168.%.%' | def | ALTER | NO |
| 'kebi'@'192.168.%.%' | def | SHOW DATABASES | NO |
| 'kebi'@'192.168.%.%' | def | SUPER | NO |
| 'kebi'@'192.168.%.%' | def | CREATE TEMPORARY TABLES | NO |
| 'kebi'@'192.168.%.%' | def | LOCK TABLES | NO |
| 'kebi'@'192.168.%.%' | def | EXECUTE | NO |
| 'kebi'@'192.168.%.%' | def | REPLICATION SLAVE | NO |
| 'kebi'@'192.168.%.%' | def | REPLICATION CLIENT | NO |
| 'kebi'@'192.168.%.%' | def | CREATE VIEW | NO |
| 'kebi'@'192.168.%.%' | def | SHOW VIEW | NO |
| 'kebi'@'192.168.%.%' | def | CREATE ROUTINE | NO |
| 'kebi'@'192.168.%.%' | def | ALTER ROUTINE | NO |
| 'kebi'@'192.168.%.%' | def | CREATE USER | NO |
| 'kebi'@'192.168.%.%' | def | EVENT | NO |
| 'kebi'@'192.168.%.%' | def | TRIGGER | NO |
| 'kebi'@'192.168.%.%' | def | CREATE TABLESPACE | NO |
+--------------------------------+---------------+-------------------------+--------------+
USER_PRIVILEGES表的内容
+----------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------+--------------+------+-----+---------+-------+
| GRANTEE | varchar(81) | NO | | | |
| TABLE_CATALOG | varchar(512) | NO | | | |
| PRIVILEGE_TYPE | varchar(64) | NO | | | |
| IS_GRANTABLE | varchar(3) | NO | | | |
+----------------+--------------+------+-----+---------+-------+
USER_PRIVILEGES的结构属性
SCHEMA_PRIVILEGES（方案权限）表：给出了关于方案（数据库）权限的信息。该信息来自mysql.db授权表，是非标准表。
+-----------------------+---------------+--------------+-------------------------+--------------+
| GRANTEE | TABLE_CATALOG | TABLE_SCHEMA | PRIVILEGE_TYPE | IS_GRANTABLE |
+-----------------------+---------------+--------------+-------------------------+--------------+
| 'maoxian'@'localhost' | def | class_7 | SELECT | NO |
| 'maoxian'@'localhost' | def | class_7 | INSERT | NO |
| ''@'%' | def | test | SELECT | NO |
| ''@'%' | def | test | INSERT | NO |
| ''@'%' | def | test | UPDATE | NO |
| ''@'%' | def | test | DELETE | NO |
| ''@'%' | def | test | CREATE | NO |
| ''@'%' | def | test | DROP | NO |
| ''@'%' | def | test | REFERENCES | NO |
| ''@'%' | def | test | INDEX | NO |
| ''@'%' | def | test | ALTER | NO |
| ''@'%' | def | test | CREATE TEMPORARY TABLES | NO |
| ''@'%' | def | test | LOCK TABLES | NO |
| ''@'%' | def | test | CREATE VIEW | NO |
| ''@'%' | def | test | SHOW VIEW | NO |
| ''@'%' | def | test | CREATE ROUTINE | NO |
| ''@'%' | def | test | EVENT | NO |
| ''@'%' | def | test | TRIGGER | NO |
| ''@'%' | def | test\_% | SELECT | NO |
| ''@'%' | def | test\_% | INSERT | NO |
| ''@'%' | def | test\_% | UPDATE | NO |
| ''@'%' | def | test\_% | DELETE | NO |
| ''@'%' | def | test\_% | CREATE | NO |
| ''@'%' | def | test\_% | DROP | NO |
| ''@'%' | def | test\_% | REFERENCES | NO |
| ''@'%' | def | test\_% | INDEX | NO |
| ''@'%' | def | test\_% | ALTER | NO |
| ''@'%' | def | test\_% | CREATE TEMPORARY TABLES | NO |
| ''@'%' | def | test\_% | LOCK TABLES | NO |
| ''@'%' | def | test\_% | CREATE VIEW | NO |
| ''@'%' | def | test\_% | SHOW VIEW | NO |
| ''@'%' | def | test\_% | CREATE ROUTINE | NO |
| ''@'%' | def | test\_% | EVENT | NO |
| ''@'%' | def | test\_% | TRIGGER | NO |
+-----------------------+---------------+--------------+-------------------------+--------------+
SCHEMA_PRIVILEGES表的内容
+----------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------+--------------+------+-----+---------+-------+
| GRANTEE | varchar(81) | NO | | | |
| TABLE_CATALOG | varchar(512) | NO | | | |
| TABLE_SCHEMA | varchar(64) | NO | | | |
| PRIVILEGE_TYPE | varchar(64) | NO | | | |
| IS_GRANTABLE | varchar(3) | NO | | | |
+----------------+--------------+------+-----+---------+-------+
SCHEMA_PRIVILEGES表的结构属性
TABLE_PRIVILIGES（表权限）表：给出了关于表权限的信息。该信息源自mysql.tables_priv授权表。表的内容无法查看
mysql> desc table_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 | | | |
| PRIVILEGE_TYPE | varchar(64) | NO | | | |
| IS_GRANTABLE | varchar(3) | NO | | | |
+----------------+--------------+------+-----+---------+-------+
6 rows in set (0.00 sec)
TABLE_PRIVILEGES的结构属性
COLUMN_PRIVILEGES（列权限）表：给出了关于列权限的信息。该信息源自mysql.columns_priv授权表
mysql> 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 | | | |
+----------------+--------------+------+-----+---------+-------+
7 rows in set (0.00 sec) mysql> select * from COLUMN_PRIVILEGES;
Empty set (0.00 sec)
COLUMN_PRIVILEGES表的结构属性
COARACTER_SETS（字符集）表：提供了mysql实例可用字符集的信息。show character set的结果取值于此。
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 |
| 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 |
+--------------------+----------------------+-----------------------------+--------+
39 rows in set (0.00 sec)
CHARACTER_SETS表的内容
mysql> 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 | |
+----------------------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
CHARACTER_SETS表的结构属性
COLLATIONS表：提供了关于各种字符集的对照信息。
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_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_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 |
| 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 |
| 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 |
| 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 |
+--------------------------+--------------------+-----+------------+-------------+---------+
197 rows in set (0.00 sec)
COLLATIONS表的内容
mysql> 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 | |
+--------------------+-------------+------+-----+---------+-------+
6 rows in set (0.00 sec)
COLLATIONS表的属性
COLLATION_CHARACTER_SET_APPLICABILITY表：指明了可用于校对的字符集。这些列表等效于show collation的前两个显示字段。
mysql> select * from COLLATION_CHARACTER_SET_APPLICABILITY;
+--------------------------+--------------------+
| COLLATION_NAME | CHARACTER_SET_NAME |
+--------------------------+--------------------+
| big5_chinese_ci | big5 |
| big5_bin | big5 |
| dec8_swedish_ci | dec8 |
| dec8_bin | dec8 |
| cp850_general_ci | cp850 |
| cp850_bin | cp850 |
| hp8_english_ci | hp8 |
| hp8_bin | hp8 |
| koi8r_general_ci | koi8r |
| koi8r_bin | koi8r |
| latin1_german1_ci | latin1 |
| latin1_swedish_ci | latin1 |
| latin1_danish_ci | latin1 |
| latin1_german2_ci | latin1 |
| latin1_bin | latin1 |
| latin1_general_ci | latin1 |
| latin1_general_cs | latin1 |
| latin1_spanish_ci | latin1 |
| latin2_czech_cs | latin2 |
| latin2_general_ci | latin2 |
| latin2_hungarian_ci | latin2 |
| latin2_croatian_ci | latin2 |
| latin2_bin | latin2 |
| swe7_swedish_ci | swe7 |
| swe7_bin | swe7 |
| ascii_general_ci | ascii |
| ascii_bin | ascii |
| ujis_japanese_ci | ujis |
| ujis_bin | ujis |
| sjis_japanese_ci | sjis |
| sjis_bin | sjis |
| hebrew_general_ci | hebrew |
| hebrew_bin | hebrew |
| tis620_thai_ci | tis620 |
| tis620_bin | tis620 |
| euckr_korean_ci | euckr |
| euckr_bin | euckr |
| koi8u_general_ci | koi8u |
| koi8u_bin | koi8u |
| gb2312_chinese_ci | gb2312 |
| gb2312_bin | gb2312 |
| greek_general_ci | greek |
| greek_bin | greek |
| cp1250_general_ci | cp1250 |
| cp1250_czech_cs | cp1250 |
| cp1250_croatian_ci | cp1250 |
| cp1250_bin | cp1250 |
| cp1250_polish_ci | cp1250 |
| gbk_chinese_ci | gbk |
| gbk_bin | gbk |
| latin5_turkish_ci | latin5 |
| latin5_bin | latin5 |
| armscii8_general_ci | armscii8 |
| armscii8_bin | armscii8 |
| utf8_general_ci | utf8 |
| utf8_bin | utf8 |
| utf8_unicode_ci | utf8 |
| utf8_icelandic_ci | utf8 |
| utf8_latvian_ci | utf8 |
| utf8_romanian_ci | utf8 |
| utf8_slovenian_ci | utf8 |
| utf8_polish_ci | utf8 |
| utf8_estonian_ci | utf8 |
| utf8_spanish_ci | utf8 |
| utf8_swedish_ci | utf8 |
| utf8_turkish_ci | utf8 |
| utf8_czech_ci | utf8 |
| utf8_danish_ci | utf8 |
| utf8_lithuanian_ci | utf8 |
| utf8_slovak_ci | utf8 |
| utf8_spanish2_ci | utf8 |
| utf8_roman_ci | utf8 |
| utf8_persian_ci | utf8 |
| utf8_esperanto_ci | utf8 |
| utf8_hungarian_ci | utf8 |
| utf8_sinhala_ci | utf8 |
| utf8_general_mysql500_ci | utf8 |
| ucs2_general_ci | ucs2 |
| ucs2_bin | ucs2 |
| ucs2_unicode_ci | ucs2 |
| ucs2_icelandic_ci | ucs2 |
| ucs2_latvian_ci | ucs2 |
| ucs2_romanian_ci | ucs2 |
| ucs2_slovenian_ci | ucs2 |
| ucs2_polish_ci | ucs2 |
| ucs2_estonian_ci | ucs2 |
| ucs2_spanish_ci | ucs2 |
| ucs2_swedish_ci | ucs2 |
| ucs2_turkish_ci | ucs2 |
| ucs2_czech_ci | ucs2 |
| ucs2_danish_ci | ucs2 |
| ucs2_lithuanian_ci | ucs2 |
| ucs2_slovak_ci | ucs2 |
| ucs2_spanish2_ci | ucs2 |
| ucs2_roman_ci | ucs2 |
| ucs2_persian_ci | ucs2 |
| ucs2_esperanto_ci | ucs2 |
| ucs2_hungarian_ci | ucs2 |
| ucs2_sinhala_ci | ucs2 |
| ucs2_general_mysql500_ci | ucs2 |
| cp866_general_ci | cp866 |
| cp866_bin | cp866 |
| keybcs2_general_ci | keybcs2 |
| keybcs2_bin | keybcs2 |
| macce_general_ci | macce |
| macce_bin | macce |
| macroman_general_ci | macroman |
| macroman_bin | macroman |
| cp852_general_ci | cp852 |
| cp852_bin | cp852 |
| latin7_estonian_cs | latin7 |
| latin7_general_ci | latin7 |
| latin7_general_cs | latin7 |
| latin7_bin | latin7 |
| utf8mb4_general_ci | utf8mb4 |
| utf8mb4_bin | utf8mb4 |
| utf8mb4_unicode_ci | utf8mb4 |
| utf8mb4_icelandic_ci | utf8mb4 |
| utf8mb4_latvian_ci | utf8mb4 |
| utf8mb4_romanian_ci | utf8mb4 |
| utf8mb4_slovenian_ci | utf8mb4 |
| utf8mb4_polish_ci | utf8mb4 |
| utf8mb4_estonian_ci | utf8mb4 |
| utf8mb4_spanish_ci | utf8mb4 |
| utf8mb4_swedish_ci | utf8mb4 |
| utf8mb4_turkish_ci | utf8mb4 |
| utf8mb4_czech_ci | utf8mb4 |
| utf8mb4_danish_ci | utf8mb4 |
| utf8mb4_lithuanian_ci | utf8mb4 |
| utf8mb4_slovak_ci | utf8mb4 |
| utf8mb4_spanish2_ci | utf8mb4 |
| utf8mb4_roman_ci | utf8mb4 |
| utf8mb4_persian_ci | utf8mb4 |
| utf8mb4_esperanto_ci | utf8mb4 |
| utf8mb4_hungarian_ci | utf8mb4 |
| utf8mb4_sinhala_ci | utf8mb4 |
| cp1251_bulgarian_ci | cp1251 |
| cp1251_ukrainian_ci | cp1251 |
| cp1251_bin | cp1251 |
| cp1251_general_ci | cp1251 |
| cp1251_general_cs | cp1251 |
| utf16_general_ci | utf16 |
| utf16_bin | utf16 |
| utf16_unicode_ci | utf16 |
| utf16_icelandic_ci | utf16 |
| utf16_latvian_ci | utf16 |
| utf16_romanian_ci | utf16 |
| utf16_slovenian_ci | utf16 |
| utf16_polish_ci | utf16 |
| utf16_estonian_ci | utf16 |
| utf16_spanish_ci | utf16 |
| utf16_swedish_ci | utf16 |
| utf16_turkish_ci | utf16 |
| utf16_czech_ci | utf16 |
| utf16_danish_ci | utf16 |
| utf16_lithuanian_ci | utf16 |
| utf16_slovak_ci | utf16 |
| utf16_spanish2_ci | utf16 |
| utf16_roman_ci | utf16 |
| utf16_persian_ci | utf16 |
| utf16_esperanto_ci | utf16 |
| utf16_hungarian_ci | utf16 |
| utf16_sinhala_ci | utf16 |
| cp1256_general_ci | cp1256 |
| cp1256_bin | cp1256 |
| cp1257_lithuanian_ci | cp1257 |
| cp1257_bin | cp1257 |
| cp1257_general_ci | cp1257 |
| utf32_general_ci | utf32 |
| utf32_bin | utf32 |
| utf32_unicode_ci | utf32 |
| utf32_icelandic_ci | utf32 |
| utf32_latvian_ci | utf32 |
| utf32_romanian_ci | utf32 |
| utf32_slovenian_ci | utf32 |
| utf32_polish_ci | utf32 |
| utf32_estonian_ci | utf32 |
| utf32_spanish_ci | utf32 |
| utf32_swedish_ci | utf32 |
| utf32_turkish_ci | utf32 |
| utf32_czech_ci | utf32 |
| utf32_danish_ci | utf32 |
| utf32_lithuanian_ci | utf32 |
| utf32_slovak_ci | utf32 |
| utf32_spanish2_ci | utf32 |
| utf32_roman_ci | utf32 |
| utf32_persian_ci | utf32 |
| utf32_esperanto_ci | utf32 |
| utf32_hungarian_ci | utf32 |
| utf32_sinhala_ci | utf32 |
| binary | binary |
| geostd8_general_ci | geostd8 |
| geostd8_bin | geostd8 |
| cp932_japanese_ci | cp932 |
| cp932_bin | cp932 |
| eucjpms_japanese_ci | eucjpms |
| eucjpms_bin | eucjpms |
+--------------------------+--------------------+
197 rows in set (0.00 sec)
表格的内容
mysql> desc COLLATION_CHARACTER_SET_APPLICABILITY;
+--------------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------------+-------------+------+-----+---------+-------+
| COLLATION_NAME | varchar(32) | NO | | | |
| CHARACTER_SET_NAME | varchar(32) | NO | | | |
+--------------------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
表格的属性
TABLE_CONSTRAINTS表：描述了存在约束的表。以及表的约束类型。
mysql> select * from TABLE_CONSTRAINTS;
+--------------------+-------------------+-----------------+--------------+---------------------------+-----------------+
| CONSTRAINT_CATALOG | CONSTRAINT_SCHEMA | CONSTRAINT_NAME | TABLE_SCHEMA | TABLE_NAME | CONSTRAINT_TYPE |
+--------------------+-------------------+-----------------+--------------+---------------------------+-----------------+
| def | class_7 | PRIMARY | class_7 | score | PRIMARY KEY |
| def | class_7 | PRIMARY | class_7 | test_info | PRIMARY KEY |
| def | express | PRIMARY | express | student_info | PRIMARY KEY |
| def | mysql | PRIMARY | mysql | columns_priv | PRIMARY KEY |
| def | mysql | PRIMARY | mysql | db | PRIMARY KEY |
| def | mysql | PRIMARY | mysql | event | PRIMARY KEY |
| def | mysql | PRIMARY | mysql | func | PRIMARY KEY |
| def | mysql | PRIMARY | mysql | help_category | PRIMARY KEY |
| def | mysql | name | mysql | help_category | UNIQUE |
| def | mysql | PRIMARY | mysql | help_keyword | PRIMARY KEY |
| def | mysql | name | mysql | help_keyword | UNIQUE |
| def | mysql | PRIMARY | mysql | help_relation | PRIMARY KEY |
| def | mysql | PRIMARY | mysql | help_topic | PRIMARY KEY |
| def | mysql | name | mysql | help_topic | UNIQUE |
| def | mysql | PRIMARY | mysql | host | PRIMARY KEY |
| def | mysql | PRIMARY | mysql | ndb_binlog_index | PRIMARY KEY |
| def | mysql | PRIMARY | mysql | plugin | PRIMARY KEY |
| def | mysql | PRIMARY | mysql | proc | PRIMARY KEY |
| def | mysql | PRIMARY | mysql | procs_priv | PRIMARY KEY |
| def | mysql | PRIMARY | mysql | proxies_priv | PRIMARY KEY |
| def | mysql | PRIMARY | mysql | servers | PRIMARY KEY |
| def | mysql | PRIMARY | mysql | tables_priv | PRIMARY KEY |
| def | mysql | PRIMARY | mysql | time_zone | PRIMARY KEY |
| def | mysql | PRIMARY | mysql | time_zone_leap_second | PRIMARY KEY |
| def | mysql | PRIMARY | mysql | time_zone_name | PRIMARY KEY |
| def | mysql | PRIMARY | mysql | time_zone_transition | PRIMARY KEY |
| def | mysql | PRIMARY | mysql | time_zone_transition_type | PRIMARY KEY |
| def | mysql | PRIMARY | mysql | user | PRIMARY KEY |
+--------------------+-------------------+-----------------+--------------+---------------------------+-----------------+
28 rows in set (0.00 sec)
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)
TABLE_CONSTRAINITS表格的属性
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)
KEY_COLUMN_USAGE表的结构属性
ROUTINES表：提供了关于存储子程序（存储程序和函数）信息，不包含自定义函数UDF
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 | int(21) | YES | | NULL | |
| NUMERIC_SCALE | int(21) | 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(77) | NO | | | |
| CHARACTER_SET_CLIENT | varchar(32) | NO | | | |
| COLLATION_CONNECTION | varchar(32) | NO | | | |
| DATABASE_COLLATION | varchar(32) | NO | | | |
+--------------------------+---------------+------+-----+---------------------+-------+
30 rows in set (0.06 sec)
ROUTINES表的结构属性
views表：给出了关于数据库中视图的信息。需要有show ciews权限，否则无法查看视图信息。
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(77) | NO | | | |
| SECURITY_TYPE | varchar(7) | NO | | | |
| CHARACTER_SET_CLIENT | varchar(32) | NO | | | |
| COLLATION_CONNECTION | varchar(32) | NO | | | |
+----------------------+--------------+------+-----+---------+-------+
10 rows in set (0.01 sec)
views表格属性
TRIGGERS表：提供了关于触发程序的信息。必须有super权限才能查看该表。
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 | YES | | NULL | |
| SQL_MODE | varchar(8192) | NO | | | |
| DEFINER | varchar(77) | NO | | | |
| CHARACTER_SET_CLIENT | varchar(32) | NO | | | |
| COLLATION_CONNECTION | varchar(32) | NO | | | |
| DATABASE_COLLATION | varchar(32) | NO | | | |
+----------------------------+---------------+------+-----+---------+-------+
22 rows in set (0.00 sec)
TRIGGERS的结构属性
最新文章
- JavaWeb_day05cookie_session_HttpSession
- Zabbix 监控 Nginx 状态
- [项目机会]citrix 虚拟桌面对于java等高CPU占用率如何解决
- android -- 蓝牙 bluetooth （五）接电话与听音乐
- TCP通讯程序设计---5
- Java,javascript,html,css的关系
- JDK8新特性之接口
- Nginx 代理 jira 和 confluence
- 神器 Sublime Text 3 的一些常用插件
- c#调用钩子
- 自学Python2.6-深浅拷贝
- [bzoj4868][Shoi2017]期末考试
- Sql server not in优化
- CentOS7 网络NAT模式
- 第二章 FFmpeg常用命令
- JS高级-Date- Error-***Function:
- matlab核函数与滑窗
- 转：Uncovering Drupalgeddon 2（cve-2018-7600）漏洞深度解析(附漏洞利用代码地址)
- java获取客户端信息
- HLJU 1221: 高考签到题 （三分求极值）