MySQL触发器解决方案历程

 

最近，项目中用到mysql，由于数据量非常大，每天的数据量在百万级，考虑到mysql数据库处理性能以及数据属性，决定保存足够多的查询数据即可，距当前时间最久远的数据直接删除，无需进行备份。

 

头脑中冒出的第一个想法就是，能否利用mysql中的触发器，检查数据表大小，如果超过预设长度，则删除部分数据。

CREATE TRIGGER tg AFTER INSERT ON traffic_info

FOR EACH ROW BEGIN

...

DELETE FROM traffic_info WHERE time <= "..."

...

END;

 

执行后出现如下错误：

ERROR 1442 (HY000): Can't update table 'traffic_info' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.

 

原来，目前MySQL触发器还不支持删除触发器所在的表的数据，ok，我原始的想法是每个数据表自己管理表大小，现在看来是行不通了。

 

第二个想法是干脆用存储过程得了，手动调用存储过程来更新所有表的大小，考虑到数据表很多，想协议个接受数据表名为参数的存储过程：

CREATE PROCEDURE P(IN table_name VARCHAR(50)， IN max_count INT)

READS SQL DATA

BEGIN

...

SELECT COUNT(1) FROM table_name

...

END;

 

这种做法行不通，mysql会将“table_name”字符串作为表名，搜索后发现，需要用到mysql新特性prepare，不过需要注意的是，就算是利用prepare，还是无法直接传入数据表名，需要利用CONCAT事先连接查询字符串：

CREATE PROCEDURE P(IN table_name VARCHAR(50), IN max_count INT)

READS SQL DATA

BEGIN

...

SET @query_str = CONCAT("SELECT COUNT(1) FROM ", table_name);

PREPARE stmt FROM @query_str

EXECUTE stmt;

...

END;

 

总算是送了一口气，可是又发现，我需要在存储过程中动态获取PREPARE查询的返回结果，但是，目前mysql还无法做到这一点，真是让人失望，看来只能另觅它途了……

 

时间仓促，今是为记