mysql 事件 建表_Mysql事件event定时分表

对于日志这种作用的数据库，如果历史记录不太重要，可以选择定期删除日志，比如现在在玩儿的阴阳师的突破记录只保留两天数据，近两年很火的棋牌类游戏，回放也只支持两三天的.但如果数据比较重要，比如玩家充值记录，消耗记录等，那就必须要考虑数据量大了后的影响了，比如查询插入操作太慢带来的负面影响之类的.一种优化方式就是分表备份.现在手上的活就有这需求，经过思考我决定按月分表.

一说到自动创建分表，想到的方法就是，用event来解决这个问题。

event定义

事件可以说是数据库独有的定时器，到点执行一次,可以随时增删改，但不能由其他程序调用(触发).

在创建时你可以创建成一次性或者重复性事件。

启用事件功能，需要设置开关。可以用mysql进入到mysql环境下执行set global event_scheduler=1.但这只是一次的，mysql重启后，就不行了，所以我们用第二种方式，在mysql的配置文件/etc/my.cnf中添加event_scheduler=1语句在[mysqld]下。这样就可以在重启mysql的时候也开启事件功能.

思路

写一个存储过程专门去check分表.如果当前时间应该所存在在的的表是否存在，如果不存在，就备份一下并清空现在正使用存放数据的表，比如有个recharge表，现在时间是2017-06-19，那么应该存在一个表名叫recharge1706的表.

创建一个事件，每天去调用一次

存储过程

预备知识1:获取当前时间的年月

1

2

3

4

5

6

7

8

9set @vNow = now();

set @v1 = month(@vNow);

set @v2 = year(@vNow);

set @v3 = extract(year_month from @vNow);

set @v4 = date_format(@vNow, "%y%m");

select @v1,@v2,@v3,@v4

#----result:

6 2017 201706 1706

这是部分对时间的有趣处理结果.

可以看出，@v4是我们想要的结果.

预备知识2:动态创建表

1

2

3

4set @q = concat('create table if not exists `recharge', 1706,'` (id int not null)');

prepare stmt from @q;

execute stmt;

drop prepare stmt;

这是创建一个表的语句例子，先把整个语句连接成字符串，然后变成sql语句，然后执行，清除。得到的结果是多了一个名为recharge1706的表。

所以最后总的存储过程如下：

1

2

3

4

5

6

7

8

9

10

11

12

13CREATE DEFINER=`rsyslog`@`%` PROCEDURE `p_tst_check_table`()

BEGIN

declare vYearMonth int default date_format(now(), "%y%m");

declare vcnt int default 0;

select vcnt = count(*) from `information_schema`.`tables` where table_name = concat('recharge', vYearMonth);

if vcnt = 0 then

set @q = concat('create table `recharge', vYearMonth,'` select * from recharge');

prepare stmt from @q;

execute stmt;

drop prepare stmt;

truncate table recharge;

end if;

END

先从information_schema.tables中统计一下有多少条名为xxx的表，如果为0条，说明需要新创建，然后就是创建表recharge1706并把recharge表中的数据全部备份过来，清除recharge表的数据.

创建事件

创建事件的语法也非常简单，网上有各种姿势。这里创建一个符合我们的事件，每天check一次(调用上面的存储过程p_tst_check_table)，无限循环

1

2

3create event if not exists event_check_table

on schedule every 1 day

do call p_tst_check_table()

这时候就可以每天调用一次存储过程了，可以用show events查询语句查看已建立的事件.

附上一个官方文档中完整event的属性:

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21CREATE

[DEFINER = { user | CURRENT_USER }]

EVENT

[IF NOT EXISTS]

event_name

ON SCHEDULE schedule

[ON COMPLETION [NOT] PRESERVE]

[ENABLE | DISABLE | DISABLE ON SLAVE]

[COMMENT 'comment']

DO event_body;

schedule:

AT timestamp [+ INTERVAL interval] ...

| EVERY interval

[STARTS timestamp [+ INTERVAL interval] ...]

[ENDS timestamp [+ INTERVAL interval] ...]

interval:

quantity {YEAR | QUARTER | MONTH | DAY | HOUR | MINUTE |

WEEK | SECOND | YEAR_MONTH | DAY_HOUR | DAY_MINUTE |

DAY_SECOND | HOUR_MINUTE | HOUR_SECOND | MINUTE_SECOND}

题外

这只是个例子，细心一点会发现:

创建表备份不应该利用当前时间的年月，应该备份的是上个月的。

事件的触发是在创建的那个点的每一天，不是每一天的0点。

事件触发频率控制到一个月一次，又可以节约好几次调用了.

这些就在具体的逻辑中去细化吧:).