mysql匹配替换_mysql模糊匹配like及批量替换replace

1、mysql 模糊匹配 like 与 not like 用法 ：

SELECT * FROM `user` where `nickname` LIKE '%测试%'

SELECT * FROM `user` where `nickname` not  LIKE '%测试%'

2、mysql 批量替换replace函数用法 ：

替换某个字段，replace可以替换某个字段中的指定的某个部分

替换表一行，如果原表中有该条数据，那么会将该条数据删除；如果原表中没有该条数据，则会插入指定值

mysql> select replace('12_34_56','_','&');

+-----------------------------+

| replace('12_34_56','_','&') |

+-----------------------------+

| 12&34&56                    |

+-----------------------------+

mysql> select * from user;

+----+--------------+---------------+------+-------------+----------+---------+

| ID | user_id_tree | nickname      | NAME | DESCRIPTION | QUANTITY | created |

+----+--------------+---------------+------+-------------+----------+---------+

|  1 | 1_2_3        | 测试_kong_123 | 1111 | NULL        | NULL     | NULL    |

+----+--------------+---------------+------+-------------+----------+---------+

1 row in set

mysql>    UPDATE user set `user_id_tree`= replace( `user_id_tree`, '_2_3', '&2&3')  where `id`=1;

Query OK, 1 row affected

Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from user;

+----+--------------+---------------+------+-------------+----------+---------+

| ID | user_id_tree | nickname      | NAME | DESCRIPTION | QUANTITY | created |

+----+--------------+---------------+------+-------------+----------+---------+

|  1 | 1&2&3        | 测试_kong_123 | 1111 | NULL        | NULL     | NULL    |

+----+--------------+---------------+------+-------------+----------+---------+

1 row in set

mysql> replace into user (name) value (222);

Query OK, 1 row affected

mysql> select * from user;

+----+--------------+---------------+------+-------------+----------+---------+

| ID | user_id_tree | nickname      | NAME | DESCRIPTION | QUANTITY | created |

+----+--------------+---------------+------+-------------+----------+---------+

|  1 | 1&2&3        | 测试_kong_123 | 1111 | NULL        | NULL     | NULL    |

|  9 | NULL         | NULL          | 222  | NULL        | NULL     | NULL    |

+----+--------------+---------------+------+-------------+----------+---------+

2 rows in set

mysql>