information_schema系列二（列，列权限，事件，存储引擎）

这个系列的文章主要是为了能够让自己了解MySQL5.7的一些系统表，统一做一下备注和使用，也希望分享出来让大家能够有一点点的受益。

1：COLUMNS

老规矩。查一下这个表，看一下记录，由于这个是看表的结构的我们就拿qiandai数据库的一张表作为例子。

root@localhost [information_schema]>select * from COLUMNS where TABLE_SCHEMA='qiandai' and TABLE_NAME= 'articles'\G;
*************************** 1. row ***************************
TABLE_CATALOG: def
TABLE_SCHEMA: qiandai
TABLE_NAME: articles
COLUMN_NAME: FTS_DOC_ID
ORDINAL_POSITION: 1
COLUMN_DEFAULT: NULL
IS_NULLABLE: NO
DATA_TYPE: bigint
CHARACTER_MAXIMUM_LENGTH: NULL
CHARACTER_OCTET_LENGTH: NULL
NUMERIC_PRECISION: 20
NUMERIC_SCALE: 0
DATETIME_PRECISION: NULL
CHARACTER_SET_NAME: NULL
COLLATION_NAME: NULL
COLUMN_TYPE: bigint(20) unsigned
COLUMN_KEY: PRI
EXTRA: auto_increment
PRIVILEGES: select,insert,update,references
COLUMN_COMMENT:
GENERATION_EXPRESSION:
*************************** 2. row ***************************

 

我们可以通过TABLE_SCHEMA和TABLE_NAME定位到某一张表进行查看，这样还是很方便能看到一张表的记录的，不过如果要查看表结构的话我们一板也可以通过以下两种方式查看：

show create table tablename

desc tablename

以上两种方式都可以满足基本的查看表结构的需求，为什么还要有一张系统表来记录呢，首先这个表更加的全面，其次MySQL本身内部也是要有一个统计信息来统计列的。我们看一下这个表记录的信息，字段名，所属于的类型，属于哪张表，在表中创建的时候排序是第几位等信息。贴一下官网对于这张表的解释：

 

INFORMATION_SCHEMA Name	SHOW Name	Remarks
TABLE_CATALOG		def
TABLE_SCHEMA
TABLE_NAME
COLUMN_NAME	Field
ORDINAL_POSITION		see notes
COLUMN_DEFAULT	Default
IS_NULLABLE	Null
DATA_TYPE	Type
CHARACTER_MAXIMUM_LENGTH	Type
CHARACTER_OCTET_LENGTH
NUMERIC_PRECISION	Type
NUMERIC_SCALE	Type
DATETIME_PRECISION	Type
CHARACTER_SET_NAME
COLLATION_NAME	Collation
COLUMN_TYPE	Type	MySQL extension
COLUMN_KEY	Key	MySQL extension
EXTRA	Extra	MySQL extension
PRIVILEGES	Privileges	MySQL extension
COLUMN_COMMENT	Comment	MySQL extension
GENERATION_EXPRESSION		MySQL extension

CHARACTER_OCTET_LENGTH 和CHARACTER_MAXIMUM_LENGTH大部分情况是相同的，除非是多字节字符集，一般情况下，了解这些信息也算足够一个DBA是用了，不过个人还是比较建议使用desc来查看表结构。

2：COLUMN_PRIVILEGES

我们看一下授权信息：

第一步：授权

grant select on qiandai.articles(title) to 'replication'@'%';

授权以后就会有信息出现了，一般情况这个表是空的。

root@localhost [information_schema]>select * from COLUMN_PRIVILEGES limit 10;
+------------------------------+---------------+--------------+------------+-------------+----------------+--------------+
| GRANTEE | TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | COLUMN_NAME | PRIVILEGE_TYPE | IS_GRANTABLE |
+------------------------------+---------------+--------------+------------+-------------+----------------+--------------+
| 'replication'@'172.16.88.42' | def | qiandai | articles | title | SELECT | NO |
+------------------------------+---------------+--------------+------------+-------------+----------------+--------------+

 

我们这样看的话很清楚就能看到表授权的用户的对象，那张表那个库以及授予的是什么权限，如果授权的时候加上with grant option的话，我们可以看得到PRIVILEGE_TYPE这个值必须是YES,筛选的时候可以根据TABLE_NAME 和COLUMN_NAME 进行筛选，比如：

select * from COLUMN_PRIVILEGES where TABLE_NAME =‘’ and COLUMN_NAME=‘’;

 

3：ENGINES

老规矩看一下数据

root@localhost [information_schema]>select * from ENGINES limit 10;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| ENGINE | SUPPORT | COMMENT | TRANSACTIONS | XA | SAVEPOINTS |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
| BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO |
| MyISAM | YES | MyISAM storage engine | NO | NO | NO |
| CSV | YES | CSV storage engine | NO | NO | NO |
| ARCHIVE | YES | Archive storage engine | NO | NO | NO |
| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |
| FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+

引擎类型，是否支持这个引擎，描述，是否支持事物，是否支持分布式事务，是否能够支持事物的回滚点。这个就比较简了。不过多的解释了。

4：EVENTS

看一下记录吧筒子们，我只查询一条就可以了，因为这个记录的是MySQL的事件，一般情况我们是很少使用事件的，中间有一些加*因为是涉及到公司的东西，不过会解释一下，在后面标注上汉字解释。

mysql> select * from EVENTS limit 1\G;
*************************** 1. row ***************************
EVENT_CATALOG: def
EVENT_SCHEMA: ******所在的数据库
EVENT_NAME: name 事件的名字
DEFINER: root 哪一个用户定义的这个事件
TIME_ZONE: SYSTEM 该事件的时区，这是用于调度事件的时区，这是有效的事件中，因为它执行。默认值是系统。
EVENT_BODY: SQL MYSQL5.7这个记录统一为SQL
EVENT_DEFINITION: call day_statistic() 表示事件要做什么操作，这个很好理解，就是启动day_statistic这个方法，做一系列的处理
EVENT_TYPE: RECURRING 本次活动的重复类型，是ONE TIME（瞬态）或重复（重复） 。
EXECUTE_AT: NULL 单次的话就是定义的时间或最后一次修改事件，如果事件的时序由一个EVERY子句代替AT子句值就是NULL
INTERVAL_VALUE: 1 对于周期性事件，该列包含事件的EVERY子句的数字部分。
INTERVAL_FIELD: DAY 对于周期性事件，该列包含管理事件的时机了EVERY子句的单位部分。如年月日天等
SQL_MODE: STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION
STARTS: 2016-08-16 01:00:00 对于重复发生的事件，其定义包括STARTS子句，该列包含相应的DATETIME值。
ENDS: NULL
STATUS: ENABLED 有以下三个值 ENABLED, DISABLED, or SLAVESIDE_DISABLED.
ON_COMPLETION: NOT PRESERVE 是保留还是不保留
CREATED: 2016-08-15 05:11:12
LAST_ALTERED: 2016-08-15 05:11:12
LAST_EXECUTED: 2016-08-28 01:00:00
EVENT_COMMENT: 注释文本
ORIGINATOR: 1 在其上创建事件的MySQL服务器的服务器ID，也就是server ID ;在复制中使用。缺省值是0
CHARACTER_SET_CLIENT: utf8
COLLATION_CONNECTION: utf8_general_ci
DATABASE_COLLATION: utf8_general_ci
1 row in set (0.00 sec)

好了，我们已经解释完了，接下来看一下创建一个事件怎么创建吧，也就给个例子：

CREATE DEFINER=`root`@`%` EVENT `money_day_statistic` ON SCHEDULE EVERY 1 DAY STARTS '2016-08-16 01:00:00' ON COMPLETION NOT PRESERVE ENABLE DO call day_statistic()

这个语句就是对应上面的查询语句。

 

 

最后看一下上面的表结构：

COLUMNS，EVENTS的存储引擎是MyISAM，这是为了长久的保存，不能够重启后就没有啊

COLUMN_PRIVILEGES，ENGINES的存储引擎是MEMORY，引擎也是一样的按照版本自动可以生成，至于列为什么会是MEMORY，我想大概是因为这样对于权限的控制都是在MySQL的SQL层。每次重启应该是会从其他user表读取到内存，所以他只是在SQL层做一些控制。大概就是这个样子。