如何批量修改mysql的表前缀名称

如何批量修改mysql的表前缀名称,第1张

方法/步骤
命令行登陆MySQL,进入相应的数据库,本例是shopping(注:用其他工具也可以,比如Navicat for MySQL)。
输入查询表语句查看有哪些表:

select table_name FROM information_schematables;
查询结果有许多表,以shopping开头的是我自己建立的,其他的是数据库系统自己建立的。
输入下面语句,查询以shopping开头的数据表:
select table_name FROM information_schematables where table_name like 'shopping_%';
输入下面语句:
Select CONCAT( 'ALTER TABLE ', table_name, ' RENAME TO buy_', substring(table_name,9 ),';' )FROM information_schematablesWhere table_name LIKE 'shopping_%';

注意:9表示从“shopping_”后面开始
拷贝出以alter开头的所有语句,用Navicat for MySQL工具更直观。

ALTER TABLE shopping__admin RENAME TO buy___admin;ALTER TABLE shopping__adminlog RENAME TO buy___adminlog;ALTER TABLE shopping__article RENAME TO buy___article;ALTER TABLE shopping__basket RENAME TO buy___basket;ALTER TABLE shopping__book RENAME TO buy___book;ALTER TABLE shopping__comment RENAME TO buy___comment;ALTER TABLE shopping__gb RENAME TO buy___gb;ALTER TABLE shopping__gou RENAME TO buy___gou;ALTER TABLE shopping__huo RENAME TO buy___huo;ALTER TABLE shopping__hw RENAME TO buy___hw;ALTER TABLE shopping__logo RENAME TO buy___logo;ALTER TABLE shopping__newsnsort RENAME TO buy___newsnsort;ALTER TABLE shopping__newssort RENAME TO buy___newssort;ALTER TABLE shopping__nsort RENAME TO buy___nsort;ALTER TABLE shopping__pay RENAME TO buy___pay;ALTER TABLE shopping__payonline RENAME TO buy___payonline;ALTER TABLE shopping__pinpai RENAME TO buy___pinpai;ALTER TABLE shopping__ps RENAME TO buy___ps;ALTER TABLE shopping__pub RENAME TO buy___pub;ALTER TABLE shopping__quan RENAME TO buy___quan;ALTER TABLE shopping__rep RENAME TO buy___rep;ALTER TABLE shopping__rule RENAME TO buy___rule;ALTER TABLE shopping__search RENAME TO buy___search;ALTER TABLE shopping__sort RENAME TO buy___sort;ALTER TABLE shopping__sub RENAME TO buy___sub;ALTER TABLE shopping__system RENAME TO buy___system;ALTER TABLE shopping__totime RENAME TO buy___totime;ALTER TABLE shopping__type RENAME TO buy___type;ALTER TABLE shopping__user RENAME TO buy___user;ALTER TABLE shopping__usertype RENAME TO buy___usertype;ALTER TABLE shopping__wrzcnet_ad RENAME TO buy___wrzcnet_ad;ALTER TABLE shopping__wrzcnet_link RENAME TO buy___wrzcnet_link;ALTER TABLE shopping__zp RENAME TO buy___zp;
运行拷贝的语句:
执行完后,在用上面的查询语句查询一下:
select table_name FROM information_schematables where table_name like 'shopping_%';
发现没有数据,说明修改成功;修改查询语句:
select table_name FROM information_schematables where table_name like 'buy_%';

您好,update mt2 set name = replace(name, substring(name, locate('<contact>', name),locate('</contact>', name)-locate('<contact>'+10, name)),'');
locate:
LOCATE(substr,str)
POSITION(substr IN str)
返回子串 substr 在字符串 str 中第一次出现的位置。如果子串 substr 在 str 中不存在,返回值为 0:
substring
SUBSTR(str,pos,len): 由<str>中的第<pos>位置开始,选出接下去的<len>个字元。
replace
replace(str1, str2, str3): 在字串 str1 中,当 str2 出现时,将其以 str3 替代。

用replace
update wp_posts set post_content=replace(post_content, 'aaacom', 'bbbcn'),guid=replace(guid,'aaacom','bbbcn')

替换语句使用UPDATE语句,例如:
UPDATE
表名
SET
字段名=修改后的值
WHERE
条件
如果你还是不会写语句,请告诉我你希望进行怎样的替换,我帮你写。

update 表名 set SFDate = DATE_ADD(SFDate,INTERVAL "-1" YEAR) where 你要改哪些行自己写条件
附时间函数大全
>

欢迎分享,转载请注明来源:内存溢出

原文地址: http://outofmemory.cn/yw/13403061.html

(0)
打赏 微信扫一扫 微信扫一扫 支付宝扫一扫 支付宝扫一扫
上一篇 2023-07-29
下一篇 2023-07-29

发表评论

登录后才能评论

评论列表(0条)

保存