【mysql】存储过程，查询/替换指定数据库、具体编码类型含有某字符串的所有表名和列名

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`;

View Code

无注释版

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, ';\n', 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, ';\n', 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`;

posted @ 2020-02-19 12:06 xiaostudy 阅读(973) 评论(0) 收藏举报

刷新页面返回顶部

网站推荐、资源下载等 | 个人网站

xiaostudy

将至而立之年，不忘年少初心！

【mysql】存储过程，查询/替换指定数据库、具体编码类型含有某字符串的所有表名和列名

1、需求：就是想在一个数据库中查找存在某字符串的表和列；

2、例子，我这里my_test数据库有3张表，test_replace的name、password列有张三字符串，t1表的name、city列也有张三字符串，t_user表就没有张三字符串。

3、代码

注：如果数据库很多，建议在存储过程中指定数据库，这样减少查询的数据量。下面的代码，记得把my_test换成自己要查询的数据库名称。

4、替换字符串，把test_database换成自己数据库，把张三换成李四。

公告