BEGIN
#Routine body goes here...
#存储过程和c语言很像,都要求先声明变量,才可以再使用,并且声明语句前不应该有其他语句(这句没找到出处,这是各种错误调试总结出来的经验)。
declare v_sql varchar(1024);
declare v_table varchar(64);
declare v_column varchar(64);
declare test varchar(1024);
declare database_name varchar(64)
default "liaoning_cdr";
#需要查询的数据库名称,需要自行修改
declare stop int default 0;
#声明游标,提取数据库中多条数据时就需要用到游标,类似于java中的迭代器
declare cur_table cursor
for
select table_name,column_name
from
information_schema.`COLUMNS`
where
table_schema=database_name
order by
table_name,column_name;
#声明触发器,当游标走到结尾,没有下一个数据以供获取时就会出现not
found错误,被该触发器抓到,然后设置stop变量为1
declare continue handler for not
found set stop=1;
#声明临时数据库,好处就是它是临时的,会自动销毁。
create temporary table if not EXISTS
tmp_table(
tablename varchar(64),
columnname varchar(64)
);
#清空临时数据库,虽然可能没啥用。
truncate TABLE tmp_table;
#启动游标
open cur_table;
#fetch cur_table into
v_table,v_column;
#SELECT v_table,v_column;
#一个循环,具体循环语句介绍想看存储过程的教程
table_loop:LOOP
#获取游标的一条数据,并赋值个v_table,v_column
fetch cur_table into
v_table,v_column;
#select v_table,v_column;
#循环中断条件
IF stop=1
THEN
leave table_loop;
END IF;
#set v_sql = CONCAT("select * from
",v_table," where ",v_column," like '%ZY010000307117%'");
#制定动态sql语句部分----start---
set v_sql = CONCAT("insert into
tmp_table(tablename,columnname) select '",v_table,"','",v_column,"'
from app_t.auth_user where EXISTS(select * from
",database_name,".`",v_table,"` WHERE `",v_column,"` like
'%",column_value,"%')");
set @sql_u=v_sql;
PREPARE sql_p from @sql_u;
EXECUTE sql_p;
DEALLOCATE PREPARE sql_p;
set v_sql = CONCAT("select * from
",database_name,".`",v_table,"` WHERE `",v_column,"` like
'%",column_value,"%'");
set @sql_result_u = v_sql;
PREPARE sql_result_p from
@sql_result_u;
EXECUTE sql_result_p;
DEALLOCATE PREPARE
sql_result_p;
#-----end------
end LOOP table_loop;
close cur_table;
SELECT * from tmp_table;
END