MySQL--查出某个特定值在哪些表的哪些字段中出现，并将结果存入新表

引言

在开发中经常出现需要将表的某个字段的值修改，又怕影响其关联表，这时需要对表中的字段值进行评估，由于表的数量很多，于是可以采用SQL语句将某个特定值在所有表中哪个字段出现的位置查出来，进行评估。这篇文章将讲解MySQL数据库如何实现该功能，如果您使用的数据库是Oracle可以查看这篇文章：Oracle—查出某个特定值在哪些表的哪些字段中出现，并将结果存入新表

具体方法

先创建一个表SEARCH_VALUE以保存查出的数据

sql

CREATE TABLE `SEARCH_VALUE` (
  table_name varchar(100) DEFAULT NULL,
  column_name varchar(100) DEFAULT NULL,
  search_val varchar(100) DEFAULT NULL,
  id varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  PRIMARY KEY (`id`)
)

上述SQL语句中，id为表的主键，table_name为查询到的数据库表名，column_name为表中的列名，search_val为本次查询的值。

创建存储过程

接下来是重头戏，采用MySQL存储过程将某个特定值在所有表中哪个字段出现的位置查出来并存入上述的SEARCH_VALUE表中。

sql

-- 存储过程删除语句
DROP procedure search_and_insert;  
-- 创建MySQL存储过程search_and_insert，其中dbname表示数据库的名称，searchValue表示要查的特定值
CREATE PROCEDURE search_and_insert(in dbname VARCHAR(255),in searchValue VARCHAR(255))
BEGIN
	-- 定义变量
	-- 游标标记位
    DECLARE done INT DEFAULT 0;
   	-- 表示数据库表名
    DECLARE tableName VARCHAR(100);
   	-- 表示数据库字段名
    DECLARE columnName VARCHAR(100);
   	-- 表示要查找的特定值 
   	DECLARE valueToSearch VARCHAR(100);
    DECLARE checkNum INT;
	-- 此处采用游标获取数据库中每个表名和每个字段
    DECLARE cur CURSOR FOR 
        SELECT TABLE_NAME, COLUMN_NAME
        FROM information_schema.columns
        WHERE table_schema = dbname
        AND TABLE_NAME NOT LIKE 'ACT_%';  -- 在这里指定一些不需要查找的表，此处是以ACT_开头的工作流
	-- 在游标操作中当找不到更多记录时将done设置为1，以便结束循环
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1; 

    SET valueToSearch = searchValue;
   
   	-- open游标cur
    OPEN cur;                     -- 
    read_loop: LOOP
        FETCH cur INTO tableName, columnName;  
        IF done = 1 THEN
            LEAVE read_loop;
        END IF;

       -- 动态执行SQL语句 该SQL用于查出某个表某一个字段包含要查找的指定值的条数
       	SET @checkSql = CONCAT('SELECT COUNT(*) INTO @result FROM ', tableName, ' WHERE ', columnName, ' LIKE \'%', valueToSearch, '%\' LIMIT 1');
        -- 准备一个SQL语句
       	PREPARE checkStmt FROM @checkSql;
       	-- 执行之前准备的SQL语句
        EXECUTE checkStmt;
       	-- 来释放先前准备的SQL语句，以便释放资源并清理内存。
        DEALLOCATE PREPARE checkStmt;
       	-- 将@checkSql 语句的执行结果保存在checkNum
  		SELECT @result INTO checkNum;
  		-- 如果checkNum大于0，就将这条记录存入Search_value表里
        IF checkNum > 0 THEN    
        	-- 动态SQL将结果插入数据库SEARCH_VALUE，其中id主键用current_timestamp(6)日期表示，精确到毫秒第六位，确保主键唯一
            SET @insertSql = CONCAT('INSERT INTO SEARCH_VALUE (ID, TABLE_NAME, COLUMN_NAME, SEARCH_VAL) VALUES (\'', current_timestamp(6), '\', \'', tableName, '\', \'', columnName, '\', \'', valueToSearch, '\')');
            PREPARE insertStmt FROM @insertSql;
            EXECUTE insertStmt;
            DEALLOCATE PREPARE insertStmt;
        END IF;
    END LOOP read_loop;
	-- 关闭游标cur
    CLOSE cur;
END

要点分析

在MySQL取出数据库中所有的表和字段的名称。

sql

-- 这里的db_name根据需要换成对应的数据库名称
SELECT TABLE_NAME, COLUMN_NAME
FROM information_schema.columns
WHERE table_schema = 'db_name'

如果想查询某个数据库中所有表的详细信息，如表名，表注释，数据行数，存储引擎等，可以用下列SQL：
sql
1
2
-- 老样子，db_name换成你自己的数据库哈
select * from information_schema.tables where table_schema= 'db_name'
.如果想查询某个数据库中某张表的字段的详细信息，如字段名，字段注释，字段数据类型，字段权限，字段是否允许为空值等，可以用下列SQL：
sql
1
2
3
4
5
6
-- db_name为数据库名，table_name为表名
SELECT *
FROM
INFORMATION_SCHEMA.COLUMNS
WHERE
table_schema='db_name' AND table_name = 'table_name'
不同于Oracle使用for循环，在MySQL中我们使用游标（CURSOR ）来获取数据库的表名和字段名，然后判断我们要查找的特定值是否在这个表的这个字段中有记录（即上述的checkNum是否大于0），如果有记录，则用动态SQL插入我们创建的SEARCH_VALUE表中。

执行结果

执行存储过程

sql

1 2	-- 调用存储过程search_and_insert，'Data_Center'为我要查找的数据库，'笨'为我要找到的特定值 call search_and_insert('Data_Center','笨');