主页 / dba / 问题 / 69734
Accepted
Slyx
Asked: 2014-07-05 06:11:17 +0800 CST

MYSQL 测试字符串字段包含另一个更改的字符串字段

  • 772

我有这张桌子：

mysql> desc mytab;
+------------+-------------+------+-----+---------+----------------+
| Field      | Type        | Null | Key | Default | Extra          |
+------------+-------------+------+-----+---------+----------------+
| id         | int(11)     | NO   | PRI | NULL    | auto_increment |
| value      | varchar(50) | YES  |     | NULL    |                |
| template   | varchar(50) | YES  |     | NULL    |                |
| difference | tinyint(1)  | YES  |     | NULL    |                |
+------------+-------------+------+-----+---------+----------------+

包含此示例数据：

mysql> select * from mytab;
+----+-------+----------------+------------+
| id | value | template       | difference |
+----+-------+----------------+------------+
|  1 | 7     | |2|5|7|8|9|12| |       NULL |
|  2 | 0     | |5|            |       NULL |
|  3 | 5     | |2|5|7|8|9|12| |       NULL |
+----+-------+----------------+------------+
3 rows in set (0.00 sec)

如果包含“|value|” ，我想将difference字段设置为 FALSE template如果不是，则为 TRUE。

所以结果是：

mysql> select * from mytab;
+----+-------+----------------+------------+
| id | value | template       | difference |
+----+-------+----------------+------------+
|  1 | 7     | |2|5|7|8|9|12| |       0    |
|  2 | 0     | |5|            |       1    |
|  3 | 5     | |2|5|7|8|9|12| |       0    |
+----+-------+----------------+------------+
3 rows in set (0.00 sec)
mysql condition

1 个回答

  1. Best Answer

    我找到了这个解决方案：

    mysql> update mytab set difference=(template not like concat('%|',value,'|%'));
Query OK, 0 rows affected (0.05 sec)
Rows matched: 3  Changed: 0  Warnings: 0

mysql> select * from  mytab;
+----+-------+----------------+------------+
| id | value | template       | difference |
+----+-------+----------------+------------+
|  1 | 7     | |2|5|7|8|9|12| |          0 |
|  2 | 0     | |5|            |          1 |
|  3 | 5     | |2|5|7|8|9|12| |          0 |
+----+-------+----------------+------------+
3 rows in set (0.00

    秒）

    • 2

相关问题