先创建一个表来保存每个表的最大id值
CREATE TABLE table_max_id` (
`tablename` varchar(60) DEFAULT NULL,
`max_id` int(11) DEFAULT NULL
);
存储过程如下:
CREATE PROCEDURE get_all_max_ids(IN data_name varchar(50))
BEGIN
— 本存储过程实现查询库里的每个表的id最大值并写入到table_max_id表。本存储过程只适用于MySql数据库,表的主键名称需要是id
— 需要定义接收游标数据的变量
DECLARE tb_name varchar(50);
DECLARE done boolean default 0;
— 游标
DECLARE cur CURSOR FOR SELECT table_name
FROM information_schema.columns WHERE table_schema = data_name AND column_name =’id’ ORDER BY table_name DESC;
— 将结束标志绑定到游标
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
TRUNCATE table_max_id; — 清空数据库表
— 打开游标
OPEN cur;
— 开始循环
REPEAT
— 提取游标里的数据,这里只有一个,多个的话也一样;
FETCH cur INTO tb_name;
— 查询单个表的id最大值并插入表
set @sql=concat(‘INSERT INTO table_max_id(tablename, max_id) SELECT \”, tb_name, ‘\’, (SELECT MAX(id) FROM ‘, tb_name, ‘)’);
PREPARE stmt1 FROM @sql;
EXECUTE stmt1 ;
DEALLOCATE PREPARE stmt1;
until done end repeat;
— 关闭游标
CLOSE cur;
END;
调用方法:call get_all_max_ids(‘数据库名称’);