mysql> set @x='[at tach]886[/attach]'; Query OK, 0 rows affected (0.00 sec)mysql> select substring_index(substring_index(@x,']',-4),'[',3); +---------------------------------------------------+ | substring_index(substring_index(@x,']',-4),'[',3) | +---------------------------------------------------+ | [attach]886[/attach] | +---------------------------------------------------+ 1 row in set (0.00 sec)mysql>
MYSQL不支持正则替换,假设格式固定(只有2个[attach]) SET @a='=http://www.aaa.com/viewthread.php?tid=317&page=1#pid5302][attach]886[/attach]'; SELECT RIGHT(@a,LENGTH(@a)-INSTR(@a,'[attach]'))
你导出来成文本的 用awk和sed处理
谢谢大家,我根据大家的建议搞好了 用这样的句子,实际情况比上面的例子稍复杂些update table set message = replace(concat(left(message,15),substring(message,instr(message,'][')+1)),'[/url]','') where message regexp '\.+quote\.*url=http://www.aaa.com/viewthread\.*'
tach]886[/attach]';
Query OK, 0 rows affected (0.00 sec)mysql> select substring_index(substring_index(@x,']',-4),'[',3);
+---------------------------------------------------+
| substring_index(substring_index(@x,']',-4),'[',3) |
+---------------------------------------------------+
| [attach]886[/attach] |
+---------------------------------------------------+
1 row in set (0.00 sec)mysql>
SET @a='=http://www.aaa.com/viewthread.php?tid=317&page=1#pid5302][attach]886[/attach]';
SELECT RIGHT(@a,LENGTH(@a)-INSTR(@a,'[attach]'))
用这样的句子,实际情况比上面的例子稍复杂些update table
set message =
replace(concat(left(message,15),substring(message,instr(message,'][')+1)),'[/url]','')
where message regexp '\.+quote\.*url=http://www.aaa.com/viewthread\.*'
如果使用mysql的处理函数来直接进行更新,那会很慢的,而且无法使用索引。