在查询表名使用变量时只能使用concat 拼接 哭哭哭。。。。
drop procedure if exists proc_tmp;
create procedure proc_tmp()
BEGIN
declare done int default 0;
declare tablename varchar(255);
declare idCur cursor for select table_name from information_schema.tables where table_schema='record' and table_name like 'user_%';
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
open idCur;
REPEAT
fetch idCur INTO tablename;
if not done THEN
SET @a = tablename;
SET @sql_texta =concat('SELECT max(unix_timestamp(time)) into @maxtime from ',@a);
PREPARE stmta FROM @sql_texta;
EXECUTE stmta;
DEALLOCATE PREPARE stmta;
SET @sql_textb =concat('SELECT * FROM ',@a,' where unix_timestamp(time) <= ',@maxtime,' and unix_timestamp(time) >= ',@maxtime,'- 5');
PREPARE stmtb FROM @sql_textb;
EXECUTE stmtb;
DEALLOCATE PREPARE stmtb;
end if;
until done end repeat;
close idCur;
END;
call proc_tmp();
在使用mysql.exe 命令行执行时 直接 传递编写好的过程sql文件 会出错
cmd脚本如下
rem @echo off
mysql.exe -h 10.80.1.74 -u root -p < query.sql > result.txt
echo "!!!!!!!!!!!!Complete!!!!!!!!!!!!!!!!"
pause
使用 query.sql文件 执行时 需要做如此修改
delimiter //
create procedure proc_tmp()
....
END //