mysql event 使用

现在的项目中有个需求，需要每个两天定时将a表中的部分数据保存到一个历史表中，然后将a表中过期的数据删除。同时每隔半年要将历史表中的数据清空。

搜索了下，开始想使用系统的定时计划，后来发现mysql的event提供了上面的机制，还是很方便。
但是在创建event时，开始以为是将表写到了information_schema库中，但是root用户创建时也是没有权限，后来搜索information_schema库的说明发现这个库是一个临时的表的视图库，有关event，triggers等都存在于mysql库的相应表里。呵呵。
以下是摘录的别人的内容：

Information_schema是MySQL自带的信息数据库。里面的“表”保存着服务器当前的实时信息。

最近实现的一个patch，是要在数据库中增加一个统计， 统计结果以表的形式呈现，因此必须在information_schema库中增加一个表，需要了解这些表的存在形式，以及 “维护数据”的方式。

本文描述information_schema库中表的存在形式，访问时的调用方法，以及在该库中增加一个表需要的修改点。



1、特殊的库、特殊的表

稍微试验下会发现，这个库与其他的数据库不同，体现为以下几点：

a) 这个库中，不能创建表，所有的DDL语句都被提示”Access denied”

b) 这些“表”都是只读的。

c) 对应到MySQL的data目录下，没有相应的以information_schema为名的文件夹。

不仅如此，在我们深入到代码中发现，使用内置的open_table函数(sql/sql_base.cc:2531)试图打开这些表的时候，也被提示表不存在。

跟踪select * from information_schema.processlist 这样的语句发现，在查询流程中使用的表名形如 “/tmp/#sql_5c75_0”，是一个临时表。



2、临时表

实际上，整个information_schema及里面的所有表，都可以理解为视图。在需要访问到视图中数据时，通过创建临时表(sql/sql_show.cc: create_schema_table)的方式，填充数据返回给客户端。

可以看到，在没有重启server时，所有对information_schema中的表的查询请求，在查询流程中的使用的表名都是相同的。



3、源码相关—表定义

由于是临时表，也就可以理解为何在使用open_table时，会提示表不存在。要实现在information_schema库中自定义一个表的目的，就必须了解表的描述位置。

整个库的所有表定义入口为schema_tables (sql/sql_show.cc)，这是一个ST_SCHEMA_TABLE的数组，每个元素用于定义一个表。在information_schema中执行show tables，即遍历该数组的所有元素得到。

如果需要新定义一个表，可以在该数组中插入一个ST_SCHEMA_TABLE的单位。注意需要同时在sql/table.h中修改enum enum_schema_tables的定义，它内部宏与schema_tables一一对应。缺少或者位置不对应将会导致MySQL 运行时coredump，原因是在使用schema_tables[i]. fill_table()时，原代码中有些地方并不检验函数指针是否已定义。

ST_SCHEMA_TABLE定义了每个表的字段信息、创建形式、填充数据形式，定义在sql/table.h中。


C代码

1. <span style="font-size: small;">typedef struct st_schema_table
2.
3. {
4.
5. const char* table_name;
6.
7. ST_FIELD_INFO *fields_info;
8.
9. /* Create information_schema table */
10.
11. TABLE *(*create_table) (THD *thd, TABLE_LIST *table_list);
12.
13. /* Fill table with data */
14.
15. int (*fill_table) (THD *thd, TABLE_LIST *tables, COND *cond);
16.
17. ……
18.
19. } ST_SCHEMA_TABLE;
20. </span>

typedef struct st_schema_table

{

const char* table_name;

ST_FIELD_INFO *fields_info;

/* Create information_schema table */

TABLE *(*create_table) (THD *thd, TABLE_LIST *table_list);

/* Fill table with data */

int (*fill_table) (THD *thd, TABLE_LIST *tables, COND *cond);

……

} ST_SCHEMA_TABLE;



说明:

1) table_name 表名

2) fields_info 字段信息数组，结构体ST_FIELD_INFO中定义了字段名、字段类型等信息。

3) create_table 表创建函数，所有的表共享一个创建函数create_schema_table

4) fill_table 内容填充函数，每个表单独定义了一个填充表内容的函数。因为表结构不同，每个表的填充函数不相同。



你可能会疑惑为什么只有填充表内容的函数，而没有修改、删除表中行的函数？原因就是information_schema中的表，不需要更新和删除操作。每一次查询，都是重新生成临时表，将内存中的信息插入到表中。





4、源码相关—表生成流程

在每次需要访问information_schema中的某个表内容时，先调用create_schema_table生成临时表，生成时需要的字段定义等信息来源于fields_info。

临时表生成后，调用fill_table指向的函数填充表内容。看一下我们常见的命令show processlist调用的fill_table函数结构。

C代码

1. <span style="font-size: small;">int fill_schema_processlist(THD* thd, TABLE_LIST* tables, COND* cond)
2. {
3. …….
4. I_List_iterator<THD> it(threads);
5. THD* tmp;
6. while ((tmp= it++))
7. {
8. /* ID */
9. table->field[0]->store((longlong) tmp->thread_id, TRUE);
10. /* USER */
11. table->field[1]->store(val, strlen(val), cs);
12. /* HOST */
13. table->field[2]->store(host, strlen(host), cs);
14. /* DB */
15. table->field[3]->store(tmp->db, strlen(tmp->db), cs);
16. …….
17. schema_table_store_record(thd, table);
18. ……
19. }
20. } </span>

int fill_schema_processlist(THD* thd, TABLE_LIST* tables, COND* cond)
{
…….
I_List_iterator<THD> it(threads);
THD* tmp;
while ((tmp= it++))
{
/* ID */
table->field[0]->store((longlong) tmp->thread_id, TRUE);
/* USER */
table->field[1]->store(val, strlen(val), cs);
/* HOST */
table->field[2]->store(host, strlen(host), cs);
/* DB */
table->field[3]->store(tmp->db, strlen(tmp->db), cs);
…….
schema_table_store_record(thd, table);
……
}
}



说明：

1) 第4行中threads是一个全局变量，记录当前的执行线程。

2) 第6行用一个循环遍历所有线程

3) 第8~16行为填充片段，略去了各种判断语句。可以清楚看到，对于每个字段，都单独进行值填充

4) 第17行的schema_table_store_record是将上面填充完成的table->field数组内容插入到临时表中





5、源码相关—新增自定义表

总结在information_schema库中新增一个表需要的步骤如下：

1) 设计好定义表的结构后，在sql/sql_show.cc中新增一个ST_FIELD_INFO mytable_fields_info结构数组，存放表信息；

2) 在sql/table.h enum_schema_tables中相应增加一个宏定义SCH_MYTABLE

3) 定义一个填充函数fill_schema_mytable， 此函数负责向创建好的临时表中插入数据，需要访问某个全局信息。

4) 在 schema_tables数组中增加一个元素 {“table_name”, mytable_fields_info, create_schema_table, fill_schema_mytable, 0, 0, -1, -1, 0, 0}, 该元素的位置需要与第2步中增加的宏定义位置相对应。

INFORMATION_SCHEMA是信息数据库，其中保存着关于MySQL服务器所维护的所有其他数据库的信息。在 INFORMATION_SCHEMA中，有数个只读表。它们实际上是视图，而不是基本表，因此，你将无法看到与之相关的任何文件。

事实上，尽管不需要生成名为INFORMATION_SCHEMA的文件，我们仍提供了名为INFORMATION_SCHEMA的新数据库。可以使用 USE语句将INFORMATION_SCHEMA选择为默认数据库，但访问该数据库中所含表的唯一方式是使用SELECT语句。不能在其中插入内容，不能更新它们，也不能删除其中的内容。

由于其只能用SELECT，就产生不了create,insert语句，所以就备份不出来。

[url]http://dev.mysql.com/doc/refman/5.1/zh/[/url]
是mysql中文网站