从SET字段中删除值的最佳方法是什么? [英] The best way to remove value from SET field?

问题描述

这是更新mysql SET字段，从字段中删除特定值的最佳方法.

Which is the best way to update a mysql SET field, to remove a specific value from the field.

例如.值分别为1,2,3,4,5的字段类别? 我想从列表中删除"2":

Eg. field categories with values: 1,2,3,4,5? I want to remove '2' from the list:

UPDATE table 
SET categories = REPLACE(categories, ',2,', ',') 
WHERE field LIKE '%,2,%';

但是如果'2'是列表中的第一个或最后一个值怎么办?

But what if '2' is the first or the last value from the list?

UPDATE table 
SET categories = REPLACE(categories, '2,', '') 
WHERE field LIKE '2,%';

UPDATE table 
SET categories = REPLACE(categories, ',2', '') 
WHERE field LIKE ',2%';

如何用一个查询处理所有3种情况?！

How could I handle all 3 cases with one single query?!

推荐答案

如果需要从集合中删除的值不能多次出现，则可以使用以下方法:

If the value you need to remove from the set can't be present more than once, you could use this:

UPDATE yourtable
SET
  categories =
    TRIM(BOTH ',' FROM REPLACE(CONCAT(',', categories, ','), ',2,', ','))
WHERE
  FIND_IN_SET('2', categories)

在此处查看它是否正常运行.如果该值可以多次出现，则将删除所有出现的值:

see it working here. If the value can be present more than once, this will remove all occourences of it:

UPDATE yourtable
SET
  categories =
    TRIM(BOTH ',' FROM
      REPLACE(
        REPLACE(CONCAT(',',REPLACE(col, ',', ',,'), ','),',2,', ''), ',,', ',')
    )
WHERE
  FIND_IN_SET('2', categories)

这篇关于从SET字段中删除值的最佳方法是什么?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！