在一些特殊情况下由于不知道数库表名时,可以通过mysql的存储过程匹配出我们想要查询的表名
1、首先创建第一存储过程,此过程用于循环体内部进行判断是否匹配查询值,匹配则插入到创建好的临时表
delimiter $$
drop procedure if exists getDataByDbName $$
create procedure getDataByDbName(in baseName varchar(255),in tabName varchar(255),in fieldName varchar(255),in findValue varchar(255))
begin
declare num int;
set @strSQL =concat("select count(*) from ",baseName,".",tabName," where `",fieldName,"` like '%",findValue,"%' into @num;");
prepare strSQL from @strSQL;
execute strSQL;
deallocate prepare strSQL;
if(@num>0) then
set @strSQL = concat("insert into ",baseName,".temp_scliu values ('",tabName,"','",fieldName,"');");
prepare strSQL from @strSQL;
execute strSQL;
deallocate prepare strSQL;
end if;
end$$
delimiter;
2、创建临时存储表,然后对指定库中的所有表和字段进行遍历,查询到对应数据后,删除临时表
delimiter $$
drop procedure if exists findDataByTabName$$
create procedure findDataByTabName(in baseName varchar(255),in findData varchar(255))
begin
declare sign boolean default 0;
declare tab varchar(255);
declare field varchar(255);
declare setData cursor for select TABLE_NAME,COLUMN_NAME from information_schema.columns where TABLE_SCHEMA=baseName;
declare continue handler for sqlstate '02000' set sign=1;
set @strSQL=concat("drop table if exists ",baseName,".temp_scliu;");
prepare strSQL from @strSQL;
execute strSQL;
deallocate prepare strSQL;
set @strSQL=concat("create table ",baseName,".temp_scliu(表名 varchar(255), 字段名 varchar(255));");
prepare strSQL from @strSQL;
execute strSQL;
deallocate prepare strSQL;
open setData;
repeat
fetch setData into tab,field;
call getDataByDbName(baseName,tab,field,findData);
until sign end repeat;
close setData;
set @strSQL=concat("select * from ",baseName,".temp_scliu;");
prepare strSQL from @strSQL;
execute strSQL;
deallocate prepare strSQL;
set @strSQL=concat("drop table if exists ",baseName,".temp_scliu;");
prepare strSQL from @strSQL;
execute strSQL;
deallocate prepare strSQL;
end$$
delimiter;
3、通过调用函数的方式进行快捷查询
call findDataByTabName('要查询库名','要查询的字段值');