数据库中有很多这样的数据:read-htm-tid-XXX.html
我怎么把它改成这样:thread-XXX-1-1.html数据库中有很多这样的数据:thread-htm-fid-XXX.html
我怎么把它改成这样:forum-XXX-1.htmlXXX是不确定的数字单个的改我知道,但是怎么批量更改呢?
我怎么把它改成这样:thread-XXX-1-1.html数据库中有很多这样的数据:thread-htm-fid-XXX.html
我怎么把它改成这样:forum-XXX-1.htmlXXX是不确定的数字单个的改我知道,但是怎么批量更改呢?
Query OK, 0 rows affected (0.02 sec)mysql> select replace(replace(@col, "read-htm-tid", "thread"), ".html", "-1-1.html");
+------------------------------------------------------------------------+
| replace(replace(@col, "read-htm-tid", "thread"), ".html", "-1-1.html") |
+------------------------------------------------------------------------+
| thread-XXX-1-1.html |
+------------------------------------------------------------------------+
1 row in set (0.00 sec)
对于更新表,就这样:
update table 表 set 列名 = replace(replace(列名, "read-htm-tid", "thread"), ".html", "-1-1.html");另一个也依样处理。另外也可以用 substring_index 来做,提取出你要的 xxx 后,做 concat。
我怎么把它改成这样:forum-XXX-1.html
update tb set col1=concat('forum-',substring(substring_index(col1,'-',-1),'.',1),'-1.html')思路就是提取xxx 然后拼成forum-XXX-1.html
update tt set f1=IF(INSTR(f1,'read-htm-tid')>0,CONCAT('thread-',REPLACE(SUBSTRING_INDEX(f1,'-',-1),'.html',''),'-1-1.html')
,
IF(INSTR(f1,'thread-htm-fid')>0,CONCAT('forum-',REPLACE(SUBSTRING_INDEX(f1,'-',-1),'.html',''),'-1.html'),''));
set col1=CONCAT('thread-',mid(col1,instr(col1,'-')+1,10000))
where col like '%-%';