MySQL中如何只通过值查询对应的表和字段？

问题描述

有没有这样一种需求：只知道字面值（如：“张三”、“9:00”、“北京”等）想要找出这些值属于哪张表或哪几张表的哪个字段？
答案是，有的。鄙人最近就遇到一个这样的需求：我想在一个数据库中去找存储某个业务的表，但是不知道表名只知道这张表中一定存有某个值。另外，因为这个数据库中的表比较多2000多张表，并且这些表名完全不能顾名思义，所以放弃采用纯人工的方式寻找。

已知

• 数据库名（2000多张表，表名很随意）
• 一个或多个字面值（存在于数据库中）

求

找出字面值所在的表及字段

解决方案

纯人工

因为字面值表示的业务是什么清楚的，所以想通过业务的意思猜测表名，然而打开数据库后一看就蒙了：有很多表，表名让你怎么猜都猜不着里面存的是什么并且还有很大一部分表采用的代号如：abc0001,abc0002等。果断放弃该方案，还是老老实实的写SQL来找，首选存储过程。

编写SQL（存储过程）

整体思路

虽然我们不知道字面值在具体的哪张表的哪个字段，但是如果我们知道整个数据库的所有表及字段的对应关系那就好了，因为这样就可以通过这些表及字段进行查询。如：SELECT COUNT(*) FROM 已知表 WHERE 已知字段 LIKE %字面值%，如果结果大于0说明该表及字段存储有改字面值！那么，就有如下过程：

• 找出所有表及字段的对应关系并存储起来（方便后面取用进行查询）
  其实，MySQL中已经提供了这样一张表存储有数据库中所有表及字段，见如下：（数据库：information_schema,表：COLUMNS）
  

• 根据已知的数据库表名在COLUMNS表中查询出表名及字段名存储在游标中（游标：可理解为高级语言如java等的迭代器）

-- 创建游标用于存取在系统数据库information_schema中COLUMN表中查询出来的数据
DECLARE sys_table_column_information_cursor CURSOR FOR 
SELECT TABLE_NAME,COLUMN_NAME FROM information_schema.`COLUMNS` WHERE TABLE_SCHEMA=databaseName;

• 遍历游标（遍历出表名及字段名），并作为查询的表名及字段名，同时将字面值带入与字段进行匹配

REPEAT
FETCH sys_table_column_information_cursor INTO table_name_cursor,column_name_cursor;
 -- 定义变量result_num:是符合条件的行数
 SET @result_num = 0;
 
 SET @pre_sql = CONCAT("SELECT COUNT(*) FROM `",table_name_cursor,"` WHERE `",column_name_cursor, "` LIKE '%",CheckValue,"%' INTO @result_num;");
 -- SELECT @pre_sql; 可调试sql
 -- 预处理查询
 PREPARE query_sql FROM @pre_sql;
 -- 执行sql
 EXECUTE query_sql;

• 匹配成功则将表名及字段名存入到临时表（临时表：包含表名及字段名，需要提前创建好，用于存储最终的结果）

 -- 判断result_num的值：大于0说明该表名和字段是要查找的值
 IF (@result_num > 0) THEN
   -- 将表名table_name_cursor和字段名column_name_cursor插入临时表table_column_temp中
	 INSERT INTO table_column_temp VALUES(table_name_cursor,column_name_cursor);
	 END IF;
 UNTIL done
 END REPEAT;
 CLOSE sys_table_column_information_cursor;

• 在临时表中取出最终的结果数据

-- 在临时表table_column_temp中查询结果
 SELECT * FROM table_column_temp;
 END

编码实现

下面是完成的存储过程，编写及运行的环境是：Windows 10 /MySQL8.0。可以直接运行：

-- 根据字符串值在数据库中查询对应的表及字段
DROP PROCEDURE IF EXISTS getTableAndColumnNameByCheckValue;
-- databaseName:要查询的数据库名， CheckValue：待查询的值
CREATE PROCEDURE getTableAndColumnNameByCheckValue (IN databaseName VARCHAR(100),IN CheckValue VARCHAR(1000))
BEGIN
-- 声明变量done：使用游标时会用到 默认为0；
DECLARE done BOOLEAN DEFAULT 0;
-- 声明变量: 用于存储从游标中遍历出来的值
DECLARE table_name_cursor VARCHAR(64);
DECLARE column_name_cursor VARCHAR(64);

-- 创建游标用于存取在系统数据库information_schema中COLUMN表中查询出来的数据
DECLARE sys_table_column_information_cursor CURSOR FOR 
SELECT TABLE_NAME,COLUMN_NAME FROM information_schema.`COLUMNS` WHERE TABLE_SCHEMA=databaseName;
-- 遍历游标：将表名和列名作为基础信息，查询是否符合条件 “待查询的值”
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1;

-- 预先创建一张临时表table_column_temp:用于存储查询出来的结果
DROP TABLE IF EXISTS table_column_temp;
-- table_name：表名， column_name：字段名
CREATE TABLE table_column_temp (
table_name VARCHAR(64),
column_name VARCHAR(64)
);

-- 打开游标开始遍历
OPEN sys_table_column_information_cursor;
REPEAT
FETCH sys_table_column_information_cursor INTO table_name_cursor,column_name_cursor;
 -- 定义变量result_num:是符合条件的行数
 SET @result_num = 0;
 
 SET @pre_sql = CONCAT("SELECT COUNT(*) FROM `",table_name_cursor,"` WHERE `",column_name_cursor, "` LIKE '%",CheckValue,"%' INTO @result_num;");
 -- SELECT @pre_sql; 可调试sql
 -- 预处理查询
 PREPARE query_sql FROM @pre_sql;
 -- 执行sql
 EXECUTE query_sql;
 
 -- 判断result_num的值：大于0说明该表名和字段是要查找的值
 IF (@result_num > 0) THEN
   -- 将表名table_name_cursor和字段名column_name_cursor插入临时表table_column_temp中
	 INSERT INTO table_column_temp VALUES(table_name_cursor,column_name_cursor);
	 END IF;
 UNTIL done
 END REPEAT;
 CLOSE sys_table_column_information_cursor;
 
 -- 在临时表table_column_temp中查询结果
 SELECT * FROM table_column_temp;
 END

结果验证

执行以上存储过程

实例验证

输入数据库名：hrm_db, 字面值：项目

得到结果

分别到表document_inf的字段filename及notice_inf的字段CONTENT中查看是否含有 项目 两个字


结果是有的。 （查询值输入 数字 日期 格式也是可以的 ）
完毕。