mysql数据库把表中字段的值_mysql数据库中指定值在所有表中所有字段中的替换...

MySQL数据库:

指定值在数据库中所有表所有字段值的替换(存储过程):

1.写一个存储过程,查指定数据库中所有的表名:

CREATE PROCEDURE init_replace(in orig_str varchar(100),in new_str varchar(100),in db_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;

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;

3.调用1的方法:

启动存储过程:call init_replace('http://localhost:5005','https://www.wqbol.net','给数据库名称');

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值