mysql - 用另一个字符串替换字符串的一部分，并不总是相同的初始字符串？

Question

我有下表(我知道这些用逗号分隔的行是错误的，但我只是在做这项工作):

表水果:

我需要进行查询来删除所有梨，因此由于用逗号值分隔，我实际上需要一个 UPDATE 查询。

所以我尝试像这样替换:

UPDATE fruits SET fruit_data=REPLACE(fruit_data,',greenPear','');    --that comma needs to
UPDATE fruits SET fruit_data=REPLACE(fruit_data,'greenPear,','');    --be deleted too
UPDATE fruits SET fruit_data=REPLACE(fruit_data,'greenPear','');
DELETE FROM fruits WHERE fruit_data = '';

这可行，但仅适用于“绿色梨”，我需要删除“所有”梨，而不仅仅是绿色梨。有二十种不同的颜色，我需要知道是否有办法不重复上述代码二十次。

Answer 1

如果您的表中有主键，那么下面的 SQL 将对您非常有帮助:

根据我的最佳尝试，你的 SQL 应该是这样的:

update fruits f
left join (
select id,group_concat(fruit) new_fruit_data from(
SELECT id,SUBSTRING_INDEX(SUBSTRING_INDEX(t.fruit_data, ',', x.cifre), ',', -1) AS fruit
,count(1)
FROM (
SELECT id,fruit_data FROM fruits) t
INNER JOIN
(
    SELECT 1 + a.i + b.i * 10  cifre, b.i + a.i * 10  sute
    FROM (SELECT 0 AS i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) a
    CROSS JOIN (SELECT 0 AS i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) b
) x
ON (length(fruit_data)-length(replace(fruit_data,',',''))+1) >= x.cifre
group by id,SUBSTRING_INDEX(SUBSTRING_INDEX(t.fruit_data, ',', x.cifre), ',', -1)) k
where fruit not rlike 'pear'
group by id) n
on f.id = n.id
set f.fruit_data = n.new_fruit_data;

详细说明:

设置表:

create table fruits(id int,fruit_data varchar(500));
insert into fruits values(1,'greenApple,greePear,redApple');
insert into fruits values(2,'greePear');
insert into fruits values(3,'redApple,orangePear');
insert into fruits values(4,'greenApple,redApple');
insert into fruits values(5,'yellowPear,greenApple,greenPear');

您的基表数据。

mysql> select * from fruits;
+------+---------------------------------+
| id   | fruit_data                      |
+------+---------------------------------+
|    1 | greenApple,greePear,redApple    |
|    2 | greePear                        |
|    3 | redApple,orangePear             |
|    4 | greenApple,redApple             |
|    5 | yellowPear,greenApple,greenPear |
+------+---------------------------------+
5 rows in set (0.00 sec)

以下是解决方法和您的解决方案:

mysql> update fruits f
    -> join (
    -> select id,group_concat(fruit) new_fruit_data from(
    -> SELECT id,SUBSTRING_INDEX(SUBSTRING_INDEX(t.fruit_data, ',', x.cifre), ',', -1) AS fruit
    -> ,count(1)
    -> FROM (
    -> SELECT id,fruit_data FROM fruits) t
    -> INNER JOIN
    -> (
    ->     SELECT 1 + a.i + b.i * 10  cifre, b.i + a.i * 10  sute
    ->     FROM (SELECT 0 AS i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) a
    ->     CROSS JOIN (SELECT 0 AS i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) b
    -> ) x
    -> ON (length(fruit_data)-length(replace(fruit_data,',',''))+1) >= x.cifre
    -> group by id,SUBSTRING_INDEX(SUBSTRING_INDEX(t.fruit_data, ',', x.cifre), ',', -1)) k
    -> where fruit not rlike 'pear'
    -> group by id) n
    -> on f.id = n.id
    -> set f.fruit_data = n.new_fruit_data;
Query OK, 3 rows affected (0.05 sec)
Rows matched: 4  Changed: 3  Warnings: 0

更新后输出:

mysql> select * from fruits;
+------+---------------------+
| id   | fruit_data          |
+------+---------------------+
|    1 | greenApple,redApple |
|    2 | NULL                |
|    3 | redApple            |
|    4 | greenApple,redApple |
|    5 | greenApple          |
+------+---------------------+
5 rows in set (0.00 sec)