mysql查询某个特定值在整个数据库中所在的表和字段的方法

DROP TABLE  if EXISTS temp;
CREATE TABLE temp(tablename VARCHAR(255),LieName VARCHAR(255));
DROP TABLE  if EXISTS temp_sql;
CREATE TABLE temp_sql(t_sql VARCHAR(2000));

DROP procedure IF EXISTS getDataByDbName;
CREATE procedure getDataByDbName(in tbName VARCHAR(255),IN ziduan VARCHAR(255),IN str VARCHAR(255))
BEGIN
DECLARE num INT DEFAULT 0;
-- SET @selectSql =CONCAT('SELECT COUNT(1) from sys_station where stationpic like ''%12345%'' ;');
-- SET @STMT =CONCAT('SELECT COUNT(1) FROM ',tbName,'where ',ziduan,' LIKE ''%12345%'' INTO @num;');
SET @selectSql =CONCAT('SELECT COUNT(1) FROM ',tbName,' where ',ziduan,' LIKE ''%',str,'%'' INTO @num;');
insert into temp_sql values (@selectSql);
PREPARE stmt FROM @selectSql;
EXECUTE stmt;
IF @num>0 THEN 
INSERT INTO temp VALUES (tbName,ziduan);

END IF;
END;

DROP PROCEDURE IF EXISTS processquanjusou;
CREATE PROCEDURE processquanjusou(in dbName VARCHAR(255),IN str VARCHAR(255))
BEGIN
    DECLARE done BOOLEAN DEFAULT 0;
    DECLARE biao VARCHAR (255);
    DECLARE ziduan VARCHAR (255);
    DECLARE indexss CURSOR
    FOR
    SELECT TABLE_NAME,COLUMN_NAME FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=dbName and column_name <>'constraint';
    DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1;
    OPEN indexss;
    repeat
    FETCH indexss INTO biao,ziduan;
    call  getDataByDbName(biao,ziduan,str);
    UNTIL done END repeat;
    CLOSE indexss;
end;

CALL processquanjusou('dbName','12345');

 如果需要判断多个值，可以使用游标改写存储过程

DROP TABLE  if EXISTS temp;
CREATE TABLE temp(tablename VARCHAR(255),LieName VARCHAR(255));
DROP TABLE  if EXISTS temp_sql;
CREATE TABLE temp_sql(t_sql VARCHAR(2000));

DROP procedure IF EXISTS getDataByDbName;
CREATE procedure getDataByDbName(in tbName VARCHAR(255),IN ziduan VARCHAR(255))
BEGIN
    DECLARE num INT DEFAULT 0;
    DECLARE str VARCHAR(50);
    -- 定义循环条件
    DECLARE flag INT DEFAULT 0;
    
    -- 声明游标，取机构id
    DECLARE cur CURSOR FOR select distinct id from bladex.blade_dept where is_deleted=0; 
    -- 退出循环
    DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET flag = 1;
    OPEN cur;
    outer_label:  BEGIN  #设置一个标记
    WHILE flag <> 1 DO
    FETCH cur INTO str;
    
      -- SET @STMT =CONCAT('SELECT COUNT(1) FROM ',tbName,'where ',ziduan,' LIKE ''%12345%'' INTO @num;');
        SET @selectSql =CONCAT('SELECT COUNT(1) FROM ',tbName,' where ',ziduan,' LIKE ''%',str,'%'' INTO @num;');        
        PREPARE stmt FROM @selectSql;
        EXECUTE stmt;
        IF @num>0 THEN 
            -- insert into temp_sql values (@selectSql);
            INSERT INTO temp VALUES (tbName,ziduan);
            LEAVE  outer_label;  #满足条件，终止循环，跳转到end outer_label标记
        END IF;
    END WHILE;
    END outer_label; 
    CLOSE cur;
END;

DROP PROCEDURE IF EXISTS processquanjusou;
CREATE PROCEDURE processquanjusou(in dbName VARCHAR(255))
BEGIN
    DECLARE done BOOLEAN DEFAULT 0;
    DECLARE biao VARCHAR (255);
    DECLARE ziduan VARCHAR (255);
    DECLARE indexss CURSOR
    FOR
    SELECT TABLE_NAME,COLUMN_NAME FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=dbName 
    and length(table_name) = char_length(table_name) and column_name <>'constraint';
    DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1;
    OPEN indexss;
    repeat
    FETCH indexss INTO biao,ziduan;
    call  getDataByDbName(biao,ziduan);
    UNTIL done END repeat;
    CLOSE indexss;
end;

CALL processquanjusou('dbName');