执行以下存储过程,全库搜索关键字

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
DELIMITER //
DROP PROCEDURE IF EXISTS `proc_FindStrInAllDataBase`//
# CALL `proc_FindStrInAllDataBase` ('testdb','中');
CREATE PROCEDURE `proc_FindStrInAllDataBase`
(
IN para_databasename VARCHAR(128),
IN para_finstr VARCHAR(128)
)
BEGIN
-- 需要定义接收游标数据的变量
DECLARE tmp_dbname VARCHAR(128);
DECLARE tmp_tbname VARCHAR(128);
DECLARE tmp_colname VARCHAR(128);
-- 遍历数据结束标志
DECLARE done INT DEFAULT FALSE;


-- 游标
DECLARE cur_db_tb CURSOR
FOR
SELECT
#*,
c.table_schema,c.table_name,c.COLUMN_NAME
FROM
information_schema.`COLUMNS` C
INNER JOIN information_schema.`TABLES` t ON c.`TABLE_NAME`=t.`TABLE_NAME`
WHERE
T.`TABLE_TYPE`='BASE TABLE'
AND
(c.data_type LIKE '%char%' OR c.data_type LIKE '%text%')
AND
(C.TABLE_SCHEMA=para_databasename OR IFNULL(para_databasename,'') ='') AND IFNULL(para_finstr,'')<>'';

-- 将结束标志绑定到游标
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
CREATE TEMPORARY TABLE IF NOT EXISTS rstb(dbname VARCHAR(128),tbname VARCHAR(128),colname VARCHAR(128),cnt INT);
-- 打开游标
OPEN cur_db_tb;
-- 开始循环
read_loop: LOOP
-- 提取游标里的数据,这里只有一个,多个的话也一样;
FETCH cur_db_tb INTO tmp_dbname,tmp_tbname,tmp_colname;
-- 声明结束的时候
IF done THEN
LEAVE read_loop;
END IF;
-- 这里做你想做的循环的事件
SET @sqlstr=CONCAT('select count(1) into @rn from ',tmp_dbname,'.',tmp_tbname,' where ',tmp_colname,' like ''%',para_finstr,'%''');

PREPARE str FROM @sqlstr;
EXECUTE str;
DEALLOCATE PREPARE str;
IF IFNULL(@rn,0)>0
THEN
INSERT INTO rstb VALUES(tmp_dbname,tmp_tbname,tmp_colname,@rn);
END IF;

END LOOP;
-- 关闭游标
CLOSE cur_db_tb;

SELECT * FROM rstb;
DROP TABLE rstb;

END
//
DELIMITER ;

执行效果如下:
查询结果