Question

MYSQL替换指定字符串

我现在有有一个表 MAC_VOD 我想替换这个表里的 d_url这个字段里的HTML替换成.youku 但是我只想替换这个MAC_VOD表里 d_playerfrom这个字段值是youku的字段 也就是我只想替换MAC_VOD表里的D_PLAYERFROM这个字段里里是YOUKU的 D_URL这个字段里.HTML替换成.YOUKU

Answer 1

UPDATE mac_vod SET d_playerfrom=REPLACE(d_playerfrom, 'HTML', '.YOUKU');

追问

这样不是 所有的都替换掉了？？？ 我要的是如果D_PLAYERFROM值是YOUKU 把d_URL里的.HTML替换成.YOUKU

追答

UPDATE mac_vod SET d_URL
=REPLACE(d_URL, 'HTML', '.YOUKU')
where d_playerfrom ='YOUKU'

For example:

mysql> create table t4
-> (id int,name varchar(50));
Query OK, 0 rows affected (0.05 sec)

mysql> insert into t4 values(1,'adfahttp'),(2,'asdfd'),(2,'sdhttp'),(1,'xdads');

Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0

mysql> select * from t4;
+------+----------+
| id | name |
+------+----------+
| 1 | adfahttp |
| 2 | asdfd |
| 2 | sdhttp |
| 1 | xdads |
+------+----------+
4 rows in set (0.00 sec)

mysql> update t4 set name=replace(name,'http','_youku_') where id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 2 Changed: 1 Warnings: 0

mysql> select * from t4;
+------+-------------+
| id | name |
+------+-------------+
| 1 | adfa_youku_ |
| 2 | asdfd |
| 2 | sdhttp |
| 1 | xdads |
+------+-------------+
4 rows in set (0.00 sec)

mysql>