MySQL中的Information_schema介绍

[TOC]

##介绍
information_schema数据库是在mysql>5.0版本之后产生的一个虚拟数据库,物理上并不存在。
information_schema数据库类似于“数据字典”,提供了访问数据库元数据的方式,即数据的数据。比如数据库名或表名、列类型、访问权限(更细化的访问方式)。

##information_schema所有数据表一揽

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
mysql> use information_schema;
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_LOCK_WAITS |
| INNODB_SYS_TABLESTATS |
| INNODB_CMP |
| INNODB_METRICS |
| INNODB_CMP_RESET |
| INNODB_CMP_PER_INDEX |
| INNODB_CMPMEM_RESET |
| INNODB_FT_DELETED |
| INNODB_BUFFER_PAGE_LRU |
| INNODB_SYS_FOREIGN |
| INNODB_SYS_COLUMNS |
| INNODB_SYS_INDEXES |
| INNODB_FT_DEFAULT_STOPWORD |
| INNODB_SYS_FIELDS |
| INNODB_CMP_PER_INDEX_RESET |
| INNODB_BUFFER_PAGE |
| INNODB_CMPMEM |
| INNODB_FT_INDEX_TABLE |
| INNODB_FT_BEING_DELETED |
| INNODB_SYS_TABLESPACES |
| INNODB_FT_INDEX_CACHE |
| INNODB_SYS_FOREIGN_COLS |
| INNODB_SYS_TABLES |
| INNODB_BUFFER_POOL_STATS |
| INNODB_FT_CONFIG |
+---------------------------------------+
59 rows in set (0.00 sec)

共有59张表,但是我们在SQL注入过程中,主要用到其中7张表,最重要的是SCHEMATABLESCOLUMNS这三张表。

##重要表结构

###SCHEMATA表
information_schema.SCHEMATA表提供了当前mysql实例中所有数据库的信息。是show databases的结果。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
mysql> select * from information_schema.schemata;
+--------------+--------------------+----------------------------+------------------------+----------+
| CATALOG_NAME | SCHEMA_NAME | DEFAULT_CHARACTER_SET_NAME | DEFAULT_COLLATION_NAME | SQL_PATH |
+--------------+--------------------+----------------------------+------------------------+----------+
| def | information_schema | utf8 | utf8_general_ci | NULL |
| def | mysql | latin1 | latin1_swedish_ci | NULL |
| def | performance_schema | utf8 | utf8_general_ci | NULL |
| def | test | latin1 | latin1_swedish_ci | NULL |
+--------------+--------------------+----------------------------+------------------------+----------+
4 rows in set (0.00 sec)

mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
+--------------------+
4 rows in set (0.00 sec)

###TABLES表
information_schema.TABLES表提供了关于数据库中的的信息(包括视图)。是show tables from [schemaname]的结果。

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
mysql> desc information_schema.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.01 sec)

其中,table_schema为数据库名称;table_name为表名;table_rows为该表中的数据行数.
如想通过information_schema查询名为mysql的数据库中所有表:

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
mysql> select * from information_schema.tables where table_schema='mysql';
+---------------+--------------+---------------------------+------------+--------+---------+------------+------------+----------------+-------------+--------------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-------------------+----------+--------------------+-----------------------------------------+
| TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | TABLE_TYPE | ENGINE | VERSION | ROW_FORMAT | TABLE_ROWS | AVG_ROW_LENGTH | DATA_LENGTH | MAX_DATA_LENGTH | INDEX_LENGTH | DATA_FREE | AUTO_INCREMENT | CREATE_TIME | UPDATE_TIME | CHECK_TIME | TABLE_COLLATION | CHECKSUM | CREATE_OPTIONS | TABLE_COMMENT |
+---------------+--------------+---------------------------+------------+--------+---------+------------+------------+----------------+-------------+--------------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-------------------+----------+--------------------+-----------------------------------------+
| def | mysql | columns_priv | BASE TABLE | MyISAM | 10 | Fixed | 0 | 0 | 0 | 227994731135631359 | 4096 | 0 | NULL | 2016-08-30 19:14:49 | 2016-08-30 19:14:49 | NULL | utf8_bin | NULL | | Column privileges |
| def | mysql | db | BASE TABLE | MyISAM | 10 | Fixed | 0 | 0 | 0 | 123848989752688639 | 2048 | 0 | NULL | 2016-08-30 19:14:48 | 2016-08-30 19:14:48 | NULL | utf8_bin | NULL | | Database privileges |
| def | mysql | event | BASE TABLE | MyISAM | 10 | Dynamic | 0 | 0 | 0 | 281474976710655 | 2048 | 0 | NULL | 2016-08-30 19:14:49 | 2016-08-30 19:14:49 | NULL | utf8_general_ci | NULL | | Events |
| def | mysql | func | BASE TABLE | MyISAM | 10 | Fixed | 0 | 0 | 0 | 162974011515469823 | 1024 | 0 | NULL | 2016-08-30 19:14:48 | 2016-08-30 19:14:48 | NULL | utf8_bin | NULL | | User defined functions |
| def | mysql | general_log | BASE TABLE | CSV | 10 | Dynamic | 2 | 0 | 0 | 0 | 0 | 0 | NULL | NULL | NULL | NULL | utf8_general_ci | NULL | | General log |
| def | mysql | help_category | BASE TABLE | MyISAM | 10 | Dynamic | 40 | 28 | 1120 | 281474976710655 | 3072 | 0 | NULL | 2016-08-30 19:14:53 | 2016-08-30 19:14:53 | NULL | utf8_general_ci | NULL | | help categories |
| def | mysql | help_keyword | BASE TABLE | MyISAM | 10 | Fixed | 611 | 197 | 120367 | 55450570411999231 | 21504 | 0 | NULL | 2016-08-30 19:14:53 | 2016-08-30 19:14:53 | NULL | utf8_general_ci | NULL | | help keywords |
| def | mysql | help_relation | BASE TABLE | MyISAM | 10 | Fixed | 1218 | 9 | 10962 | 2533274790395903 | 21504 | 0 | NULL | 2016-08-30 19:14:53 | 2016-08-30 19:14:53 | NULL | utf8_general_ci | NULL | | keyword-topic relation |
| def | mysql | help_topic | BASE TABLE | MyISAM | 10 | Dynamic | 583 | 975 | 568664 | 281474976710655 | 22528 | 0 | NULL | 2016-08-30 19:14:53 | 2016-08-30 19:14:53 | NULL | utf8_general_ci | NULL | | help topics |
| def | mysql | innodb_index_stats | BASE TABLE | InnoDB | 10 | Compact | 3 | 5461 | 16384 | 0 | 0 | 0 | NULL | 2017-04-19 16:55:05 | NULL | NULL | utf8_bin | NULL | stats_persistent=0 | |
| def | mysql | innodb_table_stats | BASE TABLE | InnoDB | 10 | Compact | 1 | 16384 | 16384 | 0 | 0 | 0 | NULL | 2017-04-19 16:55:05 | NULL | NULL | utf8_bin | NULL | stats_persistent=0 | |
| def | mysql | ndb_binlog_index | BASE TABLE | MyISAM | 10 | Dynamic | 0 | 0 | 0 | 281474976710655 | 1024 | 0 | NULL | 2016-08-30 19:14:49 | 2016-08-30 19:14:49 | NULL | latin1_swedish_ci | NULL | | |
| def | mysql | plugin | BASE TABLE | MyISAM | 10 | Dynamic | 0 | 0 | 0 | 281474976710655 | 1024 | 0 | NULL | 2016-08-30 19:14:48 | 2016-08-30 19:14:48 | NULL | utf8_general_ci | NULL | | MySQL plugins |
| def | mysql | proc | BASE TABLE | MyISAM | 10 | Dynamic | 0 | 0 | 0 | 281474976710655 | 2048 | 0 | NULL | 2016-08-30 19:14:49 | 2016-08-30 19:14:49 | NULL | utf8_general_ci | NULL | | Stored Procedures |
| def | mysql | procs_priv | BASE TABLE | MyISAM | 10 | Fixed | 0 | 0 | 0 | 239253730204057599 | 4096 | 0 | NULL | 2016-08-30 19:14:49 | 2016-08-30 19:14:49 | NULL | utf8_bin | NULL | | Procedure privileges |
| def | mysql | proxies_priv | BASE TABLE | MyISAM | 10 | Fixed | 2 | 693 | 1386 | 195062158860484607 | 5120 | 0 | NULL | 2016-08-30 19:14:53 | 2016-08-30 19:14:53 | 2016-08-30 19:14:53 | utf8_bin | NULL | | User proxy privileges |
| def | mysql | servers | BASE TABLE | MyISAM | 10 | Fixed | 0 | 0 | 0 | 433752939111120895 | 1024 | 0 | NULL | 2016-08-30 19:14:48 | 2016-08-30 19:14:48 | NULL | utf8_general_ci | NULL | | MySQL Foreign Servers table |
| def | mysql | slave_master_info | BASE TABLE | InnoDB | 10 | Compact | 0 | 0 | 16384 | 0 | 0 | 0 | NULL | 2017-04-19 16:55:05 | NULL | NULL | utf8_general_ci | NULL | stats_persistent=0 | Master Information |
| def | mysql | slave_relay_log_info | BASE TABLE | InnoDB | 10 | Compact | 0 | 0 | 16384 | 0 | 0 | 0 | NULL | 2017-04-19 16:55:05 | NULL | NULL | utf8_general_ci | NULL | stats_persistent=0 | Relay Log Information |
| def | mysql | slave_worker_info | BASE TABLE | InnoDB | 10 | Compact | 0 | 0 | 16384 | 0 | 0 | 0 | NULL | 2017-04-19 16:55:05 | NULL | NULL | utf8_general_ci | NULL | stats_persistent=0 | Worker Information |
| def | mysql | slow_log | BASE TABLE | CSV | 10 | Dynamic | 2 | 0 | 0 | 0 | 0 | 0 | NULL | NULL | NULL | NULL | utf8_general_ci | NULL | | Slow log |
| def | mysql | tables_priv | BASE TABLE | MyISAM | 10 | Fixed | 0 | 0 | 0 | 239535205180768255 | 4096 | 0 | NULL | 2016-08-30 19:14:48 | 2016-08-30 19:14:48 | NULL | utf8_bin | NULL | | Table privileges |
| def | mysql | time_zone | BASE TABLE | MyISAM | 10 | Fixed | 0 | 0 | 0 | 1970324836974591 | 1024 | 0 | 1 | 2016-08-30 19:14:49 | 2016-08-30 19:14:49 | NULL | utf8_general_ci | NULL | | Time zones |
| def | mysql | time_zone_leap_second | BASE TABLE | MyISAM | 10 | Fixed | 0 | 0 | 0 | 3659174697238527 | 1024 | 0 | NULL | 2016-08-30 19:14:49 | 2016-08-30 19:14:49 | NULL | utf8_general_ci | NULL | | Leap seconds information for time zones |
| def | mysql | time_zone_name | BASE TABLE | MyISAM | 10 | Fixed | 0 | 0 | 0 | 55450570411999231 | 1024 | 0 | NULL | 2016-08-30 19:14:49 | 2016-08-30 19:14:49 | NULL | utf8_general_ci | NULL | | Time zone names |
| def | mysql | time_zone_transition | BASE TABLE | MyISAM | 10 | Fixed | 0 | 0 | 0 | 4785074604081151 | 1024 | 0 | NULL | 2016-08-30 19:14:49 | 2016-08-30 19:14:49 | NULL | utf8_general_ci | NULL | | Time zone transitions |
| def | mysql | time_zone_transition_type | BASE TABLE | MyISAM | 10 | Fixed | 0 | 0 | 0 | 10696049115004927 | 1024 | 0 | NULL | 2016-08-30 19:14:49 | 2016-08-30 19:14:49 | NULL | utf8_general_ci | NULL | | Time zone transition types |
| def | mysql | user | BASE TABLE | MyISAM | 10 | Dynamic | 5 | 70 | 352 | 281474976710655 | 2048 | 0 | NULL | 2016-08-30 19:14:48 | 2016-08-30 19:14:55 | NULL | utf8_bin | NULL | | Users and global privileges |
+---------------+--------------+---------------------------+------------+--------+---------+------------+------------+----------------+-------------+--------------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-------------------+----------+--------------------+-----------------------------------------+
28 rows in set (0.00 sec)

###COLUMNS表
information_schema.COLUMNS表提供了表中的的信息,详细表述了某张表的所有列以及每个列的信息,是show columns from [schemaname].[tablename]的结果。

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
mysql> desc information_schema.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 | | | |
+--------------------------+---------------------+------+-----+---------+-------+
20 rows in set (0.00 sec)

其中,对应information_schema.Tables,多出来的COLUMN_NAME为对应表中的列名。
如想查询表名为user的表中的所有列名:

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
mysql> select * from information_schema.columns where table_name='user';
+---------------+--------------+------------+------------------------+------------------+-----------------------+-------------+-----------+--------------------------+------------------------+-------------------+---------------+--------------------+--------------------+-----------------+-----------------------------------+------------+-------+---------------------------------+----------------+
| TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | COLUMN_NAME | ORDINAL_POSITION | COLUMN_DEFAULT | IS_NULLABLE | DATA_TYPE | CHARACTER_MAXIMUM_LENGTH | CHARACTER_OCTET_LENGTH | NUMERIC_PRECISION | NUMERIC_SCALE | DATETIME_PRECISION | CHARACTER_SET_NAME | COLLATION_NAME | COLUMN_TYPE | COLUMN_KEY | EXTRA | PRIVILEGES | COLUMN_COMMENT |
+---------------+--------------+------------+------------------------+------------------+-----------------------+-------------+-----------+--------------------------+------------------------+-------------------+---------------+--------------------+--------------------+-----------------+-----------------------------------+------------+-------+---------------------------------+----------------+
| def | mysql | user | Host | 1 | | NO | char | 60 | 180 | NULL | NULL | NULL | utf8 | utf8_bin | char(60) | PRI | | select,insert,update,references | |
| def | mysql | user | User | 2 | | NO | char | 16 | 48 | NULL | NULL | NULL | utf8 | utf8_bin | char(16) | PRI | | select,insert,update,references | |
| def | mysql | user | Password | 3 | | NO | char | 41 | 41 | NULL | NULL | NULL | latin1 | latin1_bin | char(41) | | | select,insert,update,references | |
| def | mysql | user | Select_priv | 4 | N | NO | enum | 1 | 3 | NULL | NULL | NULL | utf8 | utf8_general_ci | enum('N','Y') | | | select,insert,update,references | |
| def | mysql | user | Insert_priv | 5 | N | NO | enum | 1 | 3 | NULL | NULL | NULL | utf8 | utf8_general_ci | enum('N','Y') | | | select,insert,update,references | |
| def | mysql | user | Update_priv | 6 | N | NO | enum | 1 | 3 | NULL | NULL | NULL | utf8 | utf8_general_ci | enum('N','Y') | | | select,insert,update,references | |
| def | mysql | user | Delete_priv | 7 | N | NO | enum | 1 | 3 | NULL | NULL | NULL | utf8 | utf8_general_ci | enum('N','Y') | | | select,insert,update,references | |
| def | mysql | user | Create_priv | 8 | N | NO | enum | 1 | 3 | NULL | NULL | NULL | utf8 | utf8_general_ci | enum('N','Y') | | | select,insert,update,references | |
| def | mysql | user | Drop_priv | 9 | N | NO | enum | 1 | 3 | NULL | NULL | NULL | utf8 | utf8_general_ci | enum('N','Y') | | | select,insert,update,references | |
| def | mysql | user | Reload_priv | 10 | N | NO | enum | 1 | 3 | NULL | NULL | NULL | utf8 | utf8_general_ci | enum('N','Y') | | | select,insert,update,references | |
| def | mysql | user | Shutdown_priv | 11 | N | NO | enum | 1 | 3 | NULL | NULL | NULL | utf8 | utf8_general_ci | enum('N','Y') | | | select,insert,update,references | |
| def | mysql | user | Process_priv | 12 | N | NO | enum | 1 | 3 | NULL | NULL | NULL | utf8 | utf8_general_ci | enum('N','Y') | | | select,insert,update,references | |
| def | mysql | user | File_priv | 13 | N | NO | enum | 1 | 3 | NULL | NULL | NULL | utf8 | utf8_general_ci | enum('N','Y') | | | select,insert,update,references | |
| def | mysql | user | Grant_priv | 14 | N | NO | enum | 1 | 3 | NULL | NULL | NULL | utf8 | utf8_general_ci | enum('N','Y') | | | select,insert,update,references | |
| def | mysql | user | References_priv | 15 | N | NO | enum | 1 | 3 | NULL | NULL | NULL | utf8 | utf8_general_ci | enum('N','Y') | | | select,insert,update,references | |
| def | mysql | user | Index_priv | 16 | N | NO | enum | 1 | 3 | NULL | NULL | NULL | utf8 | utf8_general_ci | enum('N','Y') | | | select,insert,update,references | |
| def | mysql | user | Alter_priv | 17 | N | NO | enum | 1 | 3 | NULL | NULL | NULL | utf8 | utf8_general_ci | enum('N','Y') | | | select,insert,update,references | |
| def | mysql | user | Show_db_priv | 18 | N | NO | enum | 1 | 3 | NULL | NULL | NULL | utf8 | utf8_general_ci | enum('N','Y') | | | select,insert,update,references | |
| def | mysql | user | Super_priv | 19 | N | NO | enum | 1 | 3 | NULL | NULL | NULL | utf8 | utf8_general_ci | enum('N','Y') | | | select,insert,update,references | |
| def | mysql | user | Create_tmp_table_priv | 20 | N | NO | enum | 1 | 3 | NULL | NULL | NULL | utf8 | utf8_general_ci | enum('N','Y') | | | select,insert,update,references | |
| def | mysql | user | Lock_tables_priv | 21 | N | NO | enum | 1 | 3 | NULL | NULL | NULL | utf8 | utf8_general_ci | enum('N','Y') | | | select,insert,update,references | |
| def | mysql | user | Execute_priv | 22 | N | NO | enum | 1 | 3 | NULL | NULL | NULL | utf8 | utf8_general_ci | enum('N','Y') | | | select,insert,update,references | |
| def | mysql | user | Repl_slave_priv | 23 | N | NO | enum | 1 | 3 | NULL | NULL | NULL | utf8 | utf8_general_ci | enum('N','Y') | | | select,insert,update,references | |
| def | mysql | user | Repl_client_priv | 24 | N | NO | enum | 1 | 3 | NULL | NULL | NULL | utf8 | utf8_general_ci | enum('N','Y') | | | select,insert,update,references | |
| def | mysql | user | Create_view_priv | 25 | N | NO | enum | 1 | 3 | NULL | NULL | NULL | utf8 | utf8_general_ci | enum('N','Y') | | | select,insert,update,references | |
| def | mysql | user | Show_view_priv | 26 | N | NO | enum | 1 | 3 | NULL | NULL | NULL | utf8 | utf8_general_ci | enum('N','Y') | | | select,insert,update,references | |
| def | mysql | user | Create_routine_priv | 27 | N | NO | enum | 1 | 3 | NULL | NULL | NULL | utf8 | utf8_general_ci | enum('N','Y') | | | select,insert,update,references | |
| def | mysql | user | Alter_routine_priv | 28 | N | NO | enum | 1 | 3 | NULL | NULL | NULL | utf8 | utf8_general_ci | enum('N','Y') | | | select,insert,update,references | |
| def | mysql | user | Create_user_priv | 29 | N | NO | enum | 1 | 3 | NULL | NULL | NULL | utf8 | utf8_general_ci | enum('N','Y') | | | select,insert,update,references | |
| def | mysql | user | Event_priv | 30 | N | NO | enum | 1 | 3 | NULL | NULL | NULL | utf8 | utf8_general_ci | enum('N','Y') | | | select,insert,update,references | |
| def | mysql | user | Trigger_priv | 31 | N | NO | enum | 1 | 3 | NULL | NULL | NULL | utf8 | utf8_general_ci | enum('N','Y') | | | select,insert,update,references | |
| def | mysql | user | Create_tablespace_priv | 32 | N | NO | enum | 1 | 3 | NULL | NULL | NULL | utf8 | utf8_general_ci | enum('N','Y') | | | select,insert,update,references | |
| def | mysql | user | ssl_type | 33 | | NO | enum | 9 | 27 | NULL | NULL | NULL | utf8 | utf8_general_ci | enum('','ANY','X509','SPECIFIED') | | | select,insert,update,references | |
| def | mysql | user | ssl_cipher | 34 | NULL | NO | blob | 65535 | 65535 | NULL | NULL | NULL | NULL | NULL | blob | | | select,insert,update,references | |
| def | mysql | user | x509_issuer | 35 | NULL | NO | blob | 65535 | 65535 | NULL | NULL | NULL | NULL | NULL | blob | | | select,insert,update,references | |
| def | mysql | user | x509_subject | 36 | NULL | NO | blob | 65535 | 65535 | NULL | NULL | NULL | NULL | NULL | blob | | | select,insert,update,references | |
| def | mysql | user | max_questions | 37 | 0 | NO | int | NULL | NULL | 10 | 0 | NULL | NULL | NULL | int(11) unsigned | | | select,insert,update,references | |
| def | mysql | user | max_updates | 38 | 0 | NO | int | NULL | NULL | 10 | 0 | NULL | NULL | NULL | int(11) unsigned | | | select,insert,update,references | |
| def | mysql | user | max_connections | 39 | 0 | NO | int | NULL | NULL | 10 | 0 | NULL | NULL | NULL | int(11) unsigned | | | select,insert,update,references | |
| def | mysql | user | max_user_connections | 40 | 0 | NO | int | NULL | NULL | 10 | 0 | NULL | NULL | NULL | int(11) unsigned | | | select,insert,update,references | |
| def | mysql | user | plugin | 41 | mysql_native_password | YES | char | 64 | 192 | NULL | NULL | NULL | utf8 | utf8_bin | char(64) | | | select,insert,update,references | |
| def | mysql | user | authentication_string | 42 | NULL | YES | text | 65535 | 65535 | NULL | NULL | NULL | utf8 | utf8_bin | text | | | select,insert,update,references | |
| def | mysql | user | password_expired | 43 | N | NO | enum | 1 | 3 | NULL | NULL | NULL | utf8 | utf8_general_ci | enum('N','Y') | | | select,insert,update,references | |
+---------------+--------------+------------+------------------------+------------------+-----------------------+-------------+-----------+--------------------------+------------------------+-------------------+---------------+--------------------+--------------------+-----------------+-----------------------------------+------------+-------+---------------------------------+----------------+
43 rows in set (0.00 sec)

###权限表
权限表主要有三个,分别为USER_PRIVILEGES表SCHEMA_PRIVILEGES表TABLE_PRIVILEGES表COLUMN_PRIVILEGES表。学习方法同上!

##总结
通过information_schema表,我们可以在不知道数据库名、表名、列名的情况下进行数据查询。