update 表名 set 列名=修改后的值 ORDER BY id limit 50
这里的order by 没指明就是默认升序,如是order by 列名 desc即为降序排列,使用时需清楚你要修改哪一部分
不知道有没有用,不过你还是试一试吧
表table1:A列数据,开头都有0,用什么语句可以把0都统一去掉。B列数据,要在数据中倒数第三个字符前加A。
--test库是目标库(做替换前一定要做好备份)--test2是图片名字数据集表所在的库
use test2
-- 建立测试表和数据
create table picName (oldname varchar(200),newname varchar(200))
insert into picName values ('图片.JPG','tupian.JPG'),('博客.JPG','boke.JPG'),('网站.JPG','wangzhan.JPG')
commit
-- 建立存储过程
drop procedure if exists dynamic_cursor
CREATE PROCEDURE dynamic_cursor (IN db_name varchar(100))
BEGIN
DECLARE done INT DEFAULT 0
DECLARE v_updateSql varchar(2000)
DECLARE times INT DEFAULT 0
--
--
declare _err int default 0
DECLARE cur CURSOR for( SELECT updateSql from picName_view)
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION set _err=1
DROP VIEW IF EXISTS picName_view
SET @sqlstr = "CREATE VIEW picName_view as "
-- SET @sqlstr = ""
SET @sqlstr =CONCAT(@sqlstr ,"select concat('update ",db_name,".`',a.table_name,'` set ',a.column_name,'=replace(',a.column_name,',''',b.oldname,''',''',b.newname,''')') updateSql")
SET @sqlstr =CONCAT(@sqlstr ," from information_schema.`COLUMNS` a ,picName b ")
SET @sqlstr =CONCAT(@sqlstr ," where a.TABLE_SCHEMA='",db_name,"' and ")
SET @sqlstr =CONCAT(@sqlstr ," (a.COLUMN_type like '%char%' or a.COLUMN_type like '%text%' or a.COLUMN_type like '%blob%')")
PREPARE stmt FROM @sqlstr
EXECUTE stmt
DEALLOCATE PREPARE stmt
OPEN cur
f_loop:LOOP
FETCH cur INTO v_updateSql
IF done THEN
LEAVE f_loop
END IF
set @updateSql=v_updateSql
PREPARE stmt2 FROM @updateSql
EXECUTE stmt2
set times=times+1
-- SELECT v_updateSql
END LOOP f_loop
CLOSE cur
/* */
COMMIT
select times
END
-- 测试
call dynamic_cursor('test')
欢迎分享,转载请注明来源:内存溢出
评论列表(0条)