MySQL之information_schema数据库详细讲解

这篇文章主要介绍了MySQL之information_schema数据库详细讲解,本篇文章通过简要的案例,讲解了该项技术的了解与使用,以下就是详细内容,需要的朋友可以参考下

1. 概述

information_schema 数据库跟 performance_schema 一样,都是 MySQL 自带的信息数据库。其中 performance_schema 用于性能分析,而 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

68

69

70

71

72

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)

2. information_schema 库中常用的表

CHARACTER_SETS 表

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

?

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

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 |

| eucjpms | UJIS for Windows Japanese | eucjpms_japanese_ci | 3 |

| gb18030 | China National Standard GB18030 | gb18030_chinese_ci | 4 |

+———-+———————————+———————+——–+

41 rows in set (0.07 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 |

| eucjpms | eucjpms_japanese_ci | UJIS for Windows Japanese | 3 |

| gb18030 | gb18030_chinese_ci | China National Standard GB18030 | 4 |

+——————–+———————-+———————————+——–+

41 rows in set (0.00 sec)

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

22

23

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 | mysql | latin1 | latin1_swedish_ci | NULL |

| def | performance_schema | utf8 | utf8_general_ci | NULL |

| def | sys | utf8 | utf8_general_ci | NULL |

| def | test | utf8 | utf8_unicode_ci | NULL |

+————–+——————–+—————————-+————————+———-+

10 rows in set (0.00 sec)

mysql> show databases;

+——————–+

| Database |

+——————–+

| information_schema |

| mysql |

| performance_schema |

| sys |

| test |

+——————–+

10 rows in set (0.00 sec)

TABLES 表

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

?

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

mysql> SELECT * FROM TABLES;

+—————+——————–+——————————————————+————-+——————–+———+————+————+—————-+————-+——————–+————–+———–+—————-+———————+———————+———————+——————-+———-+——————–+——————————————–+

| 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 | information_schema | CHARACTER_SETS | SYSTEM VIEW | MEMORY | 10 | Fixed | NULL | 384 | 0 | 16434816 | 0 | 0 | NULL | 2018-04-23 11:51:32 | NULL | NULL | utf8_general_ci | NULL | max_rows=43690 | |

| def | information_schema | COLLATIONS | SYSTEM VIEW | MEMORY | 10 | Fixed | NULL | 231 | 0 | 16704765 | 0 | 0 | NULL | 2018-04-23 11:51:32 | NULL | NULL | utf8_general_ci | NULL | max_rows=72628 | |

| def | zentao | zt_usertpl | BASE TABLE | MyISAM | 10 | Dynamic | 0 | 0 | 0 | 281474976710655 | 1024 | 0 | 1 | 2017-08-16 16:36:45 | 2017-08-16 16:36:45 | NULL | utf8_general_ci | NULL | | |

+—————+——————–+——————————————————+————-+——————–+———+————+————+—————-+————-+——————–+————–+———–+—————-+———————+———————+———————+——————-+———-+——————–+——————————————–+

525 rows in set (3.03 sec)

mysql> SHOW TABLES FROM zentao;

+——————-+

| Tables_in_zentao |

+——————-+

| zt_action |

| zt_block |

| zt_branch |

| zt_usertpl |

+——————-+

48 rows in set (0.00 sec)

COLUMNS 表

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

?

1

2

3

4

5

6

7

8

9

10

11

mysql> SELECT * FROM COLUMNS LIMIT 2,5;

+—————+——————–+—————-+——————–+——————+—————-+————-+———–+————————–+————————+——————-+—————+——————–+——————–+—————–+————-+————+——-+————+—————-+———————–+

| 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 | GENERATION_EXPRESSION |

+—————+——————–+—————-+——————–+——————+—————-+————-+———–+————————–+————————+——————-+—————+——————–+——————–+—————–+————-+————+——-+————+—————-+———————–+

| def | information_schema | CHARACTER_SETS | DESCRIPTION | 3 | | NO | varchar | 60 | 180 | NULL | NULL | NULL | utf8 | utf8_general_ci | varchar(60) | | | select | | |

| def | information_schema | CHARACTER_SETS | MAXLEN | 4 | 0 | NO | bigint | NULL | NULL | 19 | 0 | NULL | NULL | NULL | bigint(3) | | | select | | |

| def | information_schema | COLLATIONS | COLLATION_NAME | 1 | | NO | varchar | 32 | 96 | NULL | NULL | NULL | utf8 | utf8_general_ci | varchar(32) | | | select | | |

| def | information_schema | COLLATIONS | CHARACTER_SET_NAME | 2 | | NO | varchar | 32 | 96 | NULL | NULL | NULL | utf8 | utf8_general_ci | varchar(32) | | | select | | |

| def | information_schema | COLLATIONS | ID | 3 | 0 | NO | bigint | NULL | NULL | 19 | 0 | NULL | NULL | NULL | bigint(11) | | | select | | |

+—————+——————–+—————-+——————–+——————+—————-+————-+———–+————————–+————————+——————-+—————+——————–+——————–+—————–+————-+————+——-+————+—————-+———————–+

5 rows in set (0.08 sec)

STATISTICS 表

表索引的信息。SHOW INDEX FROM schemaname.tablename; 命令从这个表获取结果。

?

1

2

3

4

5

6

7

8

9

mysql> SHOW INDEX FROM szhuizhong.users;

+——-+————+—————+————–+————-+———–+————-+———-+——–+——+————+———+—————+

| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |

+——-+————+—————+————–+————-+———–+————-+———-+——–+——+————+———+—————+

| users | 0 | PRIMARY | 1 | UserID | A | 1460 | NULL | NULL | | BTREE | | |

| users | 0 | Account_index | 1 | Account | A | 1460 | NULL | NULL | | BTREE | | |

| users | 1 | CorpID | 1 | FromID | A | 2 | NULL | NULL | YES | BTREE | | |

+——-+————+—————+————–+————-+———–+————-+———-+——–+——+————+———+—————+

3 rows in set (0.00 sec)

USER_PRIVILEGES 表

用户权限表。内容源自 mysql.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

mysql> SELECT * FROM USER_PRIVILEGES;

+————————-+—————+————————-+————–+

| GRANTEE | TABLE_CATALOG | PRIVILEGE_TYPE | IS_GRANTABLE |

+————————-+—————+————————-+————–+

| 'mysql.sys'@'localhost' | def | USAGE | NO |

| 'root'@'%' | def | SELECT | YES |

| 'root'@'%' | def | INSERT | YES |

| 'root'@'%' | def | UPDATE | YES |

| 'root'@'%' | def | DELETE | YES |

| 'root'@'%' | def | CREATE | YES |

| 'root'@'%' | def | DROP | YES |

| 'root'@'%' | def | RELOAD | YES |

| 'root'@'%' | def | SHUTDOWN | YES |

| 'root'@'%' | def | PROCESS | YES |

| 'root'@'%' | def | FILE | YES |

| 'root'@'%' | def | REFERENCES | YES |

| 'root'@'%' | def | INDEX | YES |

| 'root'@'%' | def | ALTER | YES |

| 'root'@'%' | def | SHOW DATABASES | YES |

| 'root'@'%' | def | SUPER | YES |

| 'root'@'%' | def | CREATE TEMPORARY TABLES | YES |

| 'root'@'%' | def | LOCK TABLES | YES |

| 'root'@'%' | def | EXECUTE | YES |

| 'root'@'%' | def | REPLICATION SLAVE | YES |

| 'root'@'%' | def | REPLICATION CLIENT | YES |

| 'root'@'%' | def | CREATE VIEW | YES |

| 'root'@'%' | def | SHOW VIEW | YES |

| 'root'@'%' | def | CREATE ROUTINE | YES |

| 'root'@'%' | def | ALTER ROUTINE | YES |

| 'root'@'%' | def | CREATE USER | YES |

| 'root'@'%' | def | EVENT | YES |

| 'root'@'%' | def | TRIGGER | YES |

| 'root'@'%' | def | CREATE TABLESPACE | YES |

+————————-+—————+————————-+————–+

29 rows in set (0.00 sec)

SCHEMA_PRIVILEGES 表

方案权限表。给出了关于方案(数据库)权限的信息。内容来自 mysql.db 授权表。是非标准表。

?

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

mysql> SELECT * FROM SCHEMA_PRIVILEGES;

+————————-+—————+————–+————————-+————–+

| GRANTEE | TABLE_CATALOG | TABLE_SCHEMA | PRIVILEGE_TYPE | IS_GRANTABLE |

+————————-+—————+————–+————————-+————–+

| 'mysql.sys'@'localhost' | def | sys | TRIGGER | NO |

| 'root'@'%' | def | mysql | SELECT | YES |

| 'root'@'%' | def | mysql | INSERT | YES |

| 'root'@'%' | def | mysql | UPDATE | YES |

| 'root'@'%' | def | mysql | DELETE | YES |

| 'root'@'%' | def | mysql | CREATE | YES |

| 'root'@'%' | def | mysql | DROP | YES |

| 'root'@'%' | def | mysql | REFERENCES | YES |

| 'root'@'%' | def | mysql | INDEX | YES |

| 'root'@'%' | def | mysql | ALTER | YES |

| 'root'@'%' | def | mysql | CREATE TEMPORARY TABLES | YES |

| 'root'@'%' | def | mysql | LOCK TABLES | YES |

| 'root'@'%' | def | mysql | EXECUTE | YES |

| 'root'@'%' | def | mysql | CREATE VIEW | YES |

| 'root'@'%' | def | mysql | SHOW VIEW | YES |

| 'root'@'%' | def | mysql | CREATE ROUTINE | YES |

| 'root'@'%' | def | mysql | ALTER ROUTINE | YES |

| 'root'@'%' | def | mysql | EVENT | YES |

| 'root'@'%' | def | mysql | TRIGGER | YES |

+————————-+—————+————–+————————-+————–+

19 rows in set (0.00 sec)

TABLE_PRIVILEGES 表

表权限表。给出了关于表权限的信息。内容源自 mysql.tables_priv 授权表。是非标准表。

?

1

2

3

4

5

6

7

mysql> SELECT * FROM TABLE_PRIVILEGES;

+————————-+—————+————–+————+—————-+————–+

| GRANTEE | TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | PRIVILEGE_TYPE | IS_GRANTABLE |

+————————-+—————+————–+————+—————-+————–+

| 'mysql.sys'@'localhost' | def | sys | sys_config | SELECT | NO |

+————————-+—————+————–+————+—————-+————–+

1 row in set (0.00 sec)

COLUMN_PRIVILEGES 表

列权限表。给出了关于列权限的信息。内容源自 mysql.columns_priv 授权表。是非标准表。

?

1

2

mysql> SELECT * FROM COLUMN_PRIVILEGES;

Empty set (0.00 sec)

COLLATIONS 表

提供了关于各字符集的对照信息。SHOW COLLATION; 命令从这个表获取结果。

?

1

2

3

4

5

6

7

8

9

10

11

12

13

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 |

| gb18030_bin | gb18030 | 249 | | Yes | 1 |

| gb18030_unicode_520_ci | gb18030 | 250 | | Yes | 8 |

+————————–+——————–+—–+————+————-+———+

222 rows in set (0.03 sec)

COLLATION_CHARACTER_SET_APPLICABILITY 表

指明了可用于校对的字符集。相当于 SHOW COLLATION 命令结果的前两个字段。

?

1

2

3

4

5

6

7

8

9

10

11

12

mysql> SELECT * FROM COLLATION_CHARACTER_SET_APPLICABILITY;

+————————–+——————–+

| COLLATION_NAME | CHARACTER_SET_NAME |

+————————–+——————–+

| big5_chinese_ci | big5 |

| big5_bin | big5 |

| dec8_swedish_ci | dec8 |

| gb18030_bin | gb18030 |

| gb18030_unicode_520_ci | gb18030 |

+————————–+——————–+

222 rows in set (0.00 sec)

TABLE_CONSTRAINTS 表

描述了存在约束的表。以及表的约束类型。

?

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

mysql> SELECT * FROM TABLE_CONSTRAINTS;

+——————–+——————-+——————–+————–+—————————+—————–+

| CONSTRAINT_CATALOG | CONSTRAINT_SCHEMA | CONSTRAINT_NAME | TABLE_SCHEMA | TABLE_NAME | CONSTRAINT_TYPE |

+——————–+——————-+——————–+————–+—————————+—————–+

| def | mysql | PRIMARY | mysql | columns_priv | PRIMARY KEY |

| def | mysql | PRIMARY | mysql | db | PRIMARY KEY |

| def | mysql | PRIMARY | mysql | engine_cost | PRIMARY KEY |

| def | mysql | PRIMARY | mysql | event | PRIMARY KEY |

| def | mysql | PRIMARY | mysql | func | PRIMARY KEY |

| def | mysql | PRIMARY | mysql | gtid_executed | 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 | innodb_index_stats | PRIMARY KEY |

| def | mysql | PRIMARY | mysql | innodb_table_stats | 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 | server_cost | PRIMARY KEY |

| def | mysql | PRIMARY | mysql | servers | PRIMARY KEY |

| def | mysql | PRIMARY | mysql | slave_master_info | PRIMARY KEY |

| def | mysql | PRIMARY | mysql | slave_relay_log_info | PRIMARY KEY |

| def | mysql | PRIMARY | mysql | slave_worker_info | 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 |

| def | sys | PRIMARY | sys | sys_config | PRIMARY KEY |

| def | zentao | PRIMARY | zentao | zt_action | PRIMARY KEY |

| def | zentao | account | zentao | zt_usergroup | UNIQUE |

| def | zentao | PRIMARY | zentao | zt_userquery | PRIMARY KEY |

| def | zentao | PRIMARY | zentao | zt_usertpl | PRIMARY KEY |

+——————–+——————-+——————–+————–+—————————+—————–+

213 rows in set (0.37 sec)

KEY_COLUMN_USAGE 表

描述了具有约束的键列。

?

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

mysql> SELECT * FROM KEY_COLUMN_USAGE;

+——————–+——————-+——————–+—————+————–+—————————+——————–+——————+——————————-+————————-+———————–+————————+

| CONSTRAINT_CATALOG | CONSTRAINT_SCHEMA | CONSTRAINT_NAME | TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | COLUMN_NAME | ORDINAL_POSITION | POSITION_IN_UNIQUE_CONSTRAINT | REFERENCED_TABLE_SCHEMA | REFERENCED_TABLE_NAME | REFERENCED_COLUMN_NAME |

+——————–+——————-+——————–+—————+————–+—————————+——————–+——————+——————————-+————————-+———————–+————————+

| def | mysql | PRIMARY | def | mysql | columns_priv | Host | 1 | NULL | NULL | NULL | NULL |

| def | mysql | PRIMARY | def | mysql | columns_priv | Db | 2 | NULL | NULL | NULL | NULL |

| def | mysql | PRIMARY | def | mysql | columns_priv | User | 3 | NULL | NULL | NULL | NULL |

| def | mysql | PRIMARY | def | mysql | columns_priv | Table_name | 4 | NULL | NULL | NULL | NULL |

| def | mysql | PRIMARY | def | mysql | columns_priv | Column_name | 5 | NULL | NULL | NULL | NULL |

| def | mysql | PRIMARY | def | mysql | time_zone_leap_second | Transition_time | 1 | NULL | NULL | NULL | NULL |

| def | mysql | PRIMARY | def | mysql | time_zone_name | Name | 1 | NULL | NULL | NULL | NULL |

| def | mysql | PRIMARY | def | mysql | time_zone_transition | Time_zone_id | 1 | NULL | NULL | NULL | NULL |

| def | mysql | PRIMARY | def | mysql | time_zone_transition | Transition_time | 2 | NULL | NULL | NULL | NULL |

| def | mysql | PRIMARY | def | mysql | time_zone_transition_type | Time_zone_id | 1 | NULL | NULL | NULL | NULL |

| def | mysql | PRIMARY | def | mysql | time_zone_transition_type | Transition_type_id | 2 | NULL | NULL | NULL | NULL |

| def | mysql | PRIMARY | def | mysql | user | Host | 1 | NULL | NULL | NULL | NULL |

| def | mysql | PRIMARY | def | mysql | user | User | 2 | NULL | NULL | NULL | NULL |

| def | sys | PRIMARY | def | sys | sys_config | variable | 1 | NULL | NULL | NULL | NULL |

+——————–+——————-+——————–+—————+————–+—————————+——————–+——————+——————————-+————————-+———————–+————————+

278 rows in set (0.03 sec)

ROUTINES 表

提供了关于存储子程序(存储程序和函数)的信息。此时,ROUTINES 表不包含自定义函数(UDF)。名为“mysql.proc name”的列指明了对应于 INFORMATION_SCHEMA.ROUTINES 表的 mysql.proc 列。

VIEWS 表

给出了关于数据库中的视图的信息。需要有 show views 权限,否则无法查看视图信息。

?

1

2

3

4

5

6

7

8

9

10

11

12

13

mysql> SELECT * FROM VIEWS LIMIT 1\\G

*************************** 1. row ***************************

TABLE_CATALOG: def

TABLE_SCHEMA: sys

TABLE_NAME: host_summary

VIEW_DEFINITION: select if(isnull(`performance_schema`.`accounts`.`HOST`),'background',`performance_schema`.`accounts`.`HOST`) AS `host`,sum(`stmt`.`total`) AS `statements`,`sys`.`format_time`(sum(`stmt`.`total_latency`)) AS `statement_latency`,`sys`.`format_time`(ifnull((sum(`stmt`.`total_latency`) / nullif(sum(`stmt`.`total`),0)),0)) AS `statement_avg_latency`,sum(`stmt`.`full_scans`) AS `table_scans`,sum(`io`.`ios`) AS `file_ios`,`sys`.`format_time`(sum(`io`.`io_latency`)) AS `file_io_latency`,sum(`performance_schema`.`accounts`.`CURRENT_CONNECTIONS`) AS `current_connections`,sum(`performance_schema`.`accounts`.`TOTAL_CONNECTIONS`) AS `total_connections`,count(distinct `performance_schema`.`accounts`.`USER`) AS `unique_users`,`sys`.`format_bytes`(sum(`mem`.`current_allocated`)) AS `current_memory`,`sys`.`format_bytes`(sum(`mem`.`total_allocated`)) AS `total_memory_allocated` from (((`performance_schema`.`accounts` join `sys`.`x$host_summary_by_statement_latency` `stmt` on((`performance_schema`.`accounts`.`HOST` = `stmt`.`host`))) join `sys`.`x$host_summary_by_file_io` `io` on((`performance_schema`.`accounts`.`HOST` = `io`.`host`))) join `sys`.`x$memory_by_host_by_current_bytes` `mem` on((`performance_schema`.`accounts`.`HOST` = `mem`.`host`))) group by if(isnull(`performance_schema`.`accounts`.`HOST`),'background',`performance_schema`.`accounts`.`HOST`)

CHECK_OPTION: NONE

IS_UPDATABLE: NO

DEFINER: mysql.sys@localhost

SECURITY_TYPE: INVOKER

CHARACTER_SET_CLIENT: utf8

COLLATION_CONNECTION: utf8_general_ci

1 row in set (0.03 sec)

TRIGGERS 表

提供了关于触发程序的信息。必须有 super 权限才能查看该表。

?

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

mysql> SELECT * FROM TRIGGERS LIMIT 1\\G

*************************** 1. row ***************************

TRIGGER_CATALOG: def

TRIGGER_SCHEMA: sys

TRIGGER_NAME: sys_config_insert_set_user

EVENT_MANIPULATION: INSERT

EVENT_OBJECT_CATALOG: def

EVENT_OBJECT_SCHEMA: sys

EVENT_OBJECT_TABLE: sys_config

ACTION_ORDER: 1

ACTION_CONDITION: NULL

ACTION_STATEMENT: BEGIN IF @sys.ignore_sys_config_triggers != true AND NEW.set_by IS NULL THEN SET NEW.set_by = USER(); END IF; END

ACTION_ORIENTATION: ROW

ACTION_TIMING: BEFORE

ACTION_REFERENCE_OLD_TABLE: NULL

ACTION_REFERENCE_NEW_TABLE: NULL

ACTION_REFERENCE_OLD_ROW: OLD

ACTION_REFERENCE_NEW_ROW: NEW

CREATED: 2017-05-27 11:18:43.60

SQL_MODE:

DEFINER: mysql.sys@localhost

CHARACTER_SET_CLIENT: utf8

COLLATION_CONNECTION: utf8_general_ci

DATABASE_COLLATION: utf8_general_ci

1 row in set (0.00 sec)

到此这篇关于MySQL之information_schema数据库详细讲解的文章就介绍到这了,更多相关MySQL之information_schema数据库内容请搜索钦钦技术栈以前的文章或继续浏览下面的相关文章希望大家以后多多支持钦钦技术栈!

原文链接:https://blog.csdn.net/kikajack/article/details/80065753

版权声明:本文(即:原文链接:https://www.qin1qin.com/catagory/5957/)内容由互联网用户自发投稿贡献,该文观点仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌抄袭侵权/违法违规的内容, 请发送邮件至 630367839@qq.com 举报,一经查实,本站将立刻删除。

(0)
上一篇 2022-07-27 9:52:42
下一篇 2022-07-27 9:52:53

软件定制开发公司

相关阅读

发表回复

登录后才能评论
通知:禁止投稿所有关于虚拟货币,币圈类相关文章,发现立即永久封锁账户ID!