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

原创已于 2022-04-20 15:22:40 修改 · 4.1k 阅读

3 ·

本内容遵循CC 4.0 BY-SA版权协议

标签

#mysql查询字段包含特定值

于 2022-04-19 13:53:48 首次发布

数据库专栏收录该内容

32 篇文章

订阅专栏

该博客主要涉及数据库查询的优化，通过创建和调用存储过程实现批量数据处理。内容包括使用`IF EXISTS`和`DROP TABLE`来管理临时表，创建`getDataByDbName`和`processquanjusou`两个存储过程，用于根据给定的数据库名和字段名，查询包含特定字符串的记录，并将结果存储在临时表中。此外，还展示了如何使用游标处理多个值的情况，提高查询效率。

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');