mysql遍历所有表字段替换值_fetch cur into t-CSDN博客

本文链接：https://blog.csdn.net/qq_16171233/article/details/107483161

UPDATE eform_lf_directory SET directory_name='集团总部' WHERE directory_name='集团'

UPDATE eform_lf_directory SET directory_name=REPLACE(directory_name,'集团','集团总部') WHERE directory_name LIKE '%集团%';

CREATE PROCEDURE init_replace ( IN orig_str VARCHAR ( 100 ), IN new_str VARCHAR ( 100 ), IN db_name VARCHAR ( 100 ), IN tb_name VARCHAR ( 100 ) ) BEGIN
   DECLARE
       t_name VARCHAR ( 50 );
   DECLARE
       done INT DEFAULT 0;
   DECLARE
       cur CURSOR FOR SELECT DISTINCT
       table_name AS NAME
   FROM
       INFORMATION_SCHEMA.TABLES
   WHERE
       table_schema = db_name
       AND TABLE_TYPE = 'BASE TABLE'
       AND TABLE_NAME LIKE tb_name;
   DECLARE
       CONTINUE HANDLER FOR NOT FOUND
       SET done = 1;
   OPEN cur;
   FETCH cur INTO t_name;
   WHILE
           ( done <> 1 ) DO
           CALL do_replace ( orig_str, new_str, db_name, t_name );
       FETCH cur INTO t_name;
   END WHILE;
END;
-- 2.然后1中调用2中的方法，在查询表中所有的字段，并且指定更新值：
CREATE PROCEDURE do_replace (
   IN orig_str VARCHAR ( 100 ),
   IN new_str VARCHAR ( 100 ),
   IN db_name VARCHAR ( 100 ),
   IN t_name VARCHAR ( 100 )
   ) BEGIN
   DECLARE
       cul_name VARCHAR ( 50 );
   DECLARE
       done INT DEFAULT 0;
   DECLARE
       cur CURSOR FOR SELECT
       COLUMN_NAME
   FROM
       INFORMATION_SCHEMA.COLUMNS
   WHERE
       TABLE_SCHEMA = db_name
       AND TABLE_NAME = t_name;
   DECLARE
       CONTINUE HANDLER FOR NOT FOUND
       SET done = 1;
   OPEN cur;
   FETCH cur INTO cul_name;
   WHILE
           ( done <> 1 ) DO
           SET @update_sql = CONCAT( "Update ", t_name, " SET `", cul_name, "`=replace(`", cul_name, "`,'", orig_str, "','", new_str, "');" );
       PREPARE stmt
       FROM
           @update_sql;
       EXECUTE stmt;
       FETCH cur INTO cul_name;
   END WHILE;
   CLOSE cur;
END;

分表调用了，不然工具会卡掉

call init_replace('集团','集团总部','edoc2v5','eform_lf%');
UPDATE eform_lf_directory SET directory_name='智慧服务集团' WHERE directory_name='智慧服务集团总部';
call init_replace('集团','集团总部','edoc2v5','eform_longfor%');
call init_replace('集团','集团总部','edoc2v5','portal_c%');
call init_replace('集团','集团总部','edoc2v5','portal_d%');
call init_replace('集团','集团总部','edoc2v5','portal_e%');
call init_replace('集团','集团总部','edoc2v5','portal_h%');
call init_replace('集团','集团总部','edoc2v5','portal_k%');
call init_replace('集团','集团总部','edoc2v5','portal_l%');
call init_replace('集团','集团总部','edoc2v5','portal_p%');
call init_replace('集团','集团总部','edoc2v5','portal_sitepage');
call init_replace('集团','集团总部','edoc2v5','portal_sitepagedata');
call init_replace('集团','集团总部','edoc2v5','portal_t%');
call init_replace('集团','集团总部','edoc2v5','portal_w%');