1、需求：就是想在一个数据库中查找存在某字符串的表和列；
2、例子，我这里my_test数据库有3张表，test_replace的name、password列有张三字符串，t1表的name、city列也有张三字符串，t_user表就没有张三字符串。
运行结果
t1,name;t1,city;test_replace,name;test_replace,password
符合要求
3、代码
-- 1.将mysql分隔符从;设置为& DELIMITER & -- 2.如果存在存储过程getCount则删除 DROP PROCEDURE IF EXISTS `getCount` & -- 3.定义存储过程，获取特定表列关键词的数量 -- (传入参数table_name字符串类型，为表名；传入参数column_name字符串类型，为列名；传入参数key_name字符串类型，为关键字；传出参数count_date整数类型，为数量) CREATE DEFINER=`root`@`localhost` PROCEDURE getCount(IN table_name CHAR(200), IN column_name CHAR(200), IN key_name CHAR(20), OUT count_date INT) BEGIN -- 4.声明变量 DECLARE $sqltext VARCHAR(1000); -- 5.动态sql，把sql返回值放到@count_date中 SET $sqltext = CONCAT('SELECT COUNT(*) into @count_date FROM ', table_name, ' where ', table_name, '.', column_name, ' LIKE '%', key_name, '%';'); SET @sqlcounts := $sqltext; -- 6.预编释，stmt预编释变量的名称 PREPARE stmt FROM @sqlcounts; -- 7.执行SQL语句 EXECUTE stmt; -- 8.释放资源 DEALLOCATE PREPARE stmt; -- 9.获取动态SQL语句返回值 SET count_date = @count_date; END -- 10.定义存储过程结束 & -- 11.如果存在存储过程getTableColumnNames则删除 DROP PROCEDURE IF EXISTS `getTableColumnNames` & -- 12.定义存储过程，获取指定数据库关键词的表列名 -- (传入参数database_n字符串类型，数据库名；传入参数collation_n字符串类型，具体编码类型；传入参数key_name字符串类型，为关键字；传出参数tableColumnNames字符串类型，表列名) CREATE DEFINER=`root`@`localhost` PROCEDURE getTableColumnNames(IN database_n CHAR(20), IN collation_n CHAR(32), IN key_name CHAR(20), OUT tableColumnNames TEXT) BEGIN -- 13.声明变量。database_name查询出来的数据库，table_name查询出来的表名，column_name查询出来的列名，collation_name查询出来的具体编码类型 DECLARE database_name, table_name, column_name, collation_name CHAR(200); DECLARE tableColumnStr TEXT DEFAULT ''; DECLARE resoult_count INT DEFAULT 0; -- 14.定义游标结束标识，默认为0 DECLARE stopflag INT DEFAULT 0; -- 15.定义游标，其实就是临时存储sql返回的集合 DECLARE sql_resoult CURSOR FOR SELECT t.TABLE_SCHEMA, t.TABLE_NAME, t.COLUMN_NAME, t.COLLATION_NAME FROM information_schema.COLUMNS t; -- 16.游标结束就设置为1 DECLARE CONTINUE HANDLER FOR NOT FOUND SET stopflag=1; -- 17.打开游标 OPEN sql_resoult; -- 18.读取游标中数据，存储到指定变量 FETCH sql_resoult INTO database_name, table_name, column_name, collation_name; -- 19.没有结束继续往下走 WHILE (stopflag=0) DO BEGIN -- 20.判断数据库是否为输入的数据库名称，和，指定具体编码类型，和，不含. IF (database_name=database_n AND collation_name=collation_n AND LOCATE('.', column_name)=0) THEN -- 21.调用存储过程，获取特定表列关键词的数量 CALL getCount(table_name, column_name, key_name, resoult_count); -- 22.如果数量不等于0，那么记录表列名 IF (resoult_count <> 0) THEN -- 23.拼接字符串，不可直接用传出变量设值 IF (tableColumnStr IS NULL OR tableColumnStr='') THEN SET tableColumnStr=CONCAT(table_name, ',', column_name); ELSE SET tableColumnStr=CONCAT(tableColumnStr, ';', table_name, ',', column_name); END IF; END IF; END IF; -- 24.读取游标中数据，存储到指定变量。（和18一样） FETCH sql_resoult INTO database_name, table_name, column_name, collation_name; END; END WHILE; -- 25.关闭游标 CLOSE sql_resoult; -- 26.把数据放到传出参数 SET tableColumnNames=tableColumnStr; END -- 27.定义存储过程结束 & -- 28.将mysql分隔符从&设置为; DELIMITER ; -- 29.设置变量 SET @tableColumnNames=''; SET @database='my_test'; SET @collation_name='utf8_general_ci'; SET @key='张三'; -- 30.调用存储过程 CALL getTableColumnNames(@database, @collation_name, @key, @tableColumnNames); -- 31.打印 SELECT @tableColumnNames; -- 32.如果存在存储过程则删除 DROP PROCEDURE IF EXISTS `getCount`; DROP PROCEDURE IF EXISTS `getTableColumnNames`;
注：如果数据库很多，建议在存储过程中指定数据库，这样减少查询的数据量。下面的代码，记得把my_test换成自己要查询的数据库名称。
-- 1.将mysql分隔符从;设置为& DELIMITER & -- 2.如果存在存储过程getCount则删除 DROP PROCEDURE IF EXISTS `getCount` & -- 3.定义存储过程，获取特定表列关键词的数量 -- (传入参数table_name字符串类型，为表名；传入参数column_name字符串类型，为列名；传入参数key_name字符串类型，为关键字；传出参数count_date整数类型，为数量) CREATE DEFINER=`root`@`localhost` PROCEDURE getCount(IN table_name CHAR(200), IN column_name CHAR(200), IN key_name CHAR(20), OUT count_date INT) BEGIN -- 4.声明变量 DECLARE $sqltext VARCHAR(1000); -- 5.动态sql，把sql返回值放到@count_date中 SET $sqltext = CONCAT('SELECT COUNT(*) into @count_date FROM ', table_name, ' where ', table_name, '.', column_name, ' LIKE '%', key_name, '%';'); SET @sqlcounts := $sqltext; -- 6.预编释，stmt预编释变量的名称 PREPARE stmt FROM @sqlcounts; -- 7.执行SQL语句 EXECUTE stmt; -- 8.释放资源 DEALLOCATE PREPARE stmt; -- 9.获取动态SQL语句返回值 SET count_date = @count_date; END -- 10.定义存储过程结束 & -- 11.如果存在存储过程getTableColumnNames则删除 DROP PROCEDURE IF EXISTS `getTableColumnNames` & -- 12.定义存储过程，获取指定数据库关键词的表列名 -- (传入参数collation_n字符串类型，具体编码类型；传入参数key_name字符串类型，为关键字；传出参数tableColumnNames字符串类型，表列名) CREATE DEFINER=`root`@`localhost` PROCEDURE getTableColumnNames(IN collation_n CHAR(32), IN key_name CHAR(20), OUT tableColumnNames TEXT) BEGIN -- 13.声明变量。table_name查询出来的表名，column_name查询出来的列名，collation_name查询出来的具体编码类型 DECLARE table_name, column_name, collation_name CHAR(200); DECLARE tableColumnStr TEXT DEFAULT ''; DECLARE resoult_count INT DEFAULT 0; -- 14.定义游标结束标识，默认为0 DECLARE stopflag INT DEFAULT 0; -- 15.定义游标，其实就是临时存储sql返回的集合 DECLARE sql_resoult CURSOR FOR SELECT t.TABLE_NAME, t.COLUMN_NAME, t.COLLATION_NAME FROM information_schema.COLUMNS t WHERE t.TABLE_SCHEMA='my_test'; -- 16.游标结束就设置为1 DECLARE CONTINUE HANDLER FOR NOT FOUND SET stopflag=1; -- 17.打开游标 OPEN sql_resoult; -- 18.读取游标中数据，存储到指定变量 FETCH sql_resoult INTO table_name, column_name, collation_name; -- 19.没有结束继续往下走 WHILE (stopflag=0) DO BEGIN -- 20.指定具体编码类型，和，不含. IF (collation_name=collation_n AND LOCATE('.', column_name)=0) THEN -- 21.调用存储过程，获取特定表列关键词的数量 CALL getCount(table_name, column_name, key_name, resoult_count); -- 22.如果数量不等于0，那么记录表列名 IF (resoult_count <> 0) THEN -- 23.拼接字符串，不可直接用传出变量设值 IF (tableColumnStr IS NULL OR tableColumnStr='') THEN SET tableColumnStr=CONCAT(table_name, ',', column_name); ELSE SET tableColumnStr=CONCAT(tableColumnStr, ';', table_name, ',', column_name); END IF; END IF; END IF; -- 24.读取游标中数据，存储到指定变量。（和18一样） FETCH sql_resoult INTO table_name, column_name, collation_name; END; END WHILE; -- 25.关闭游标 CLOSE sql_resoult; -- 26.把数据放到传出参数 SET tableColumnNames=tableColumnStr; END -- 27.定义存储过程结束 & -- 28.将mysql分隔符从&设置为; DELIMITER ; -- 29.设置变量 SET @tableColumnNames=''; SET @collation_name='utf8_general_ci'; SET @key='张三'; -- 30.调用存储过程 CALL getTableColumnNames(@collation_name, @key, @tableColumnNames); -- 31.打印 SELECT @tableColumnNames; -- 32.如果存在存储过程则删除 DROP PROCEDURE IF EXISTS `getCount`; DROP PROCEDURE IF EXISTS `getTableColumnNames`;
无注释版
DELIMITER & DROP PROCEDURE IF EXISTS `getCount` & CREATE DEFINER=`root`@`localhost` PROCEDURE getCount(IN table_name CHAR(200), IN column_name CHAR(200), IN key_name CHAR(40), OUT count_date INT) BEGIN DECLARE $sqltext VARCHAR(1000); SET $sqltext = CONCAT('SELECT COUNT(*) into @count_date FROM ', table_name, ' where ', table_name, '.', column_name, ' LIKE '%', key_name, '%';'); SET @sqlcounts := $sqltext; PREPARE stmt FROM @sqlcounts; EXECUTE stmt; DEALLOCATE PREPARE stmt; SET count_date = @count_date; END & DROP PROCEDURE IF EXISTS `getTableColumnNames` & CREATE DEFINER=`root`@`localhost` PROCEDURE getTableColumnNames(IN collation_n CHAR(32), IN key_name CHAR(40), OUT tableColumnNames TEXT) BEGIN DECLARE table_name, column_name, collation_name CHAR(200); DECLARE tableColumnStr TEXT DEFAULT ''; DECLARE resoult_count INT DEFAULT 0; DECLARE stopflag INT DEFAULT 0; DECLARE sql_resoult CURSOR FOR SELECT t.TABLE_NAME, t.COLUMN_NAME, t.COLLATION_NAME FROM information_schema.COLUMNS t WHERE t.TABLE_SCHEMA='my_test'; DECLARE CONTINUE HANDLER FOR NOT FOUND SET stopflag=1; OPEN sql_resoult; FETCH sql_resoult INTO table_name, column_name, collation_name; WHILE (stopflag=0) DO BEGIN IF (collation_name=collation_n AND LOCATE('.', column_name)=0) THEN CALL getCount(table_name, column_name, key_name, resoult_count); IF (resoult_count <> 0) THEN IF (tableColumnStr IS NULL OR tableColumnStr='') THEN SET tableColumnStr=CONCAT(table_name, ',', column_name); ELSE SET tableColumnStr=CONCAT(tableColumnStr, '; ', table_name, ',', column_name); END IF; END IF; END IF; FETCH sql_resoult INTO table_name, column_name, collation_name; END; END WHILE; CLOSE sql_resoult; SET tableColumnNames=tableColumnStr; END & DELIMITER ; SET @tableColumnNames=''; SET @collation_name='utf8_general_ci'; SET @key='张三'; CALL getTableColumnNames(@collation_name, @key, @tableColumnNames); SELECT @tableColumnNames; DROP PROCEDURE IF EXISTS `getCount`; DROP PROCEDURE IF EXISTS `getTableColumnNames`;
4、替换字符串，把test_database换成自己数据库，把张三换成李四。
1 DELIMITER & 2 DROP PROCEDURE IF EXISTS `updateKey` & 3 CREATE DEFINER=`root`@`localhost` PROCEDURE updateKey(IN table_name CHAR(200), IN column_name CHAR(200), IN key_name CHAR(40), IN replace_key_name CHAR(40), OUT count_date INT) 4 BEGIN 5 DECLARE $sqltext VARCHAR(1000); 6 SET $sqltext = CONCAT('SELECT COUNT(*) into @count_date FROM ', table_name, ' where ', table_name, '.', column_name, ' LIKE '%', key_name, '%';'); 7 SET @sqlcounts := $sqltext; 8 PREPARE stmt FROM @sqlcounts; 9 EXECUTE stmt; 10 DEALLOCATE PREPARE stmt; 11 12 IF (@count_date <> 0) THEN 13 SET $sqltext = CONCAT('UPDATE ', table_name, ' SET ', column_name, ' = REPLACE(', column_name, ', '', key_name, '', '', replace_key_name, '') WHERE ', column_name, ' LIKE '%', key_name, '%';'); 14 SET @sqlcounts := $sqltext; 15 PREPARE stmt FROM @sqlcounts; 16 EXECUTE stmt; 17 DEALLOCATE PREPARE stmt; 18 END IF; 19 20 SET count_date = @count_date; 21 END 22 & 23 24 DROP PROCEDURE IF EXISTS `getTableColumnNames` & 25 CREATE DEFINER=`root`@`localhost` PROCEDURE getTableColumnNames(IN collation_n CHAR(64), IN key_name CHAR(40), IN replace_key_name CHAR(40), OUT tableColumnNames TEXT) 26 BEGIN 27 DECLARE table_name, column_name, collation_name CHAR(200); 28 DECLARE tableColumnStr TEXT DEFAULT ''; 29 DECLARE resoult_count INT DEFAULT 0; 30 DECLARE stopflag INT DEFAULT 0; 31 DECLARE sql_resoult CURSOR FOR SELECT t.TABLE_NAME, t.COLUMN_NAME, t.COLLATION_NAME FROM information_schema.COLUMNS t WHERE t.TABLE_SCHEMA='test_database'; 32 DECLARE CONTINUE HANDLER FOR NOT FOUND SET stopflag=1; 33 OPEN sql_resoult; 34 FETCH sql_resoult INTO table_name, column_name, collation_name; 35 WHILE (stopflag=0) DO 36 BEGIN 37 IF (FIND_IN_SET(collation_name, collation_n)>0 AND LOCATE('.', column_name)=0) THEN 38 CALL updateKey(table_name, column_name, key_name, replace_key_name, resoult_count); 39 IF (resoult_count <> 0) THEN 40 IF (tableColumnStr IS NULL OR tableColumnStr='') THEN 41 SET tableColumnStr=CONCAT(table_name, ',', column_name); 42 ELSE 43 SET tableColumnStr=CONCAT(tableColumnStr, '; ', table_name, ',', column_name); 44 END IF; 45 END IF; 46 END IF; 47 FETCH sql_resoult INTO table_name, column_name, collation_name; 48 END; 49 END WHILE; 50 CLOSE sql_resoult; 51 SET tableColumnNames=tableColumnStr; 52 END 53 & 54 55 DELIMITER ; 56 SET @tableColumnNames=''; 57 SET @collation_name='utf8_general_ci,utf8mb4_general_ci'; 58 SET @key='张三'; 59 SET @replace_key='李四'; 60 CALL getTableColumnNames(@collation_name, @key, @replace_key, @tableColumnNames); 61 SELECT @tableColumnNames; 62 DROP PROCEDURE IF EXISTS `updateKey`; 63 DROP PROCEDURE IF EXISTS `getTableColumnNames`;