Лучший способ удалить значение из поля SET?

Каков наилучший способ обновить поле SQL mysql, чтобы удалить определенное значение из этого поля.

Eg. категории полей со значениями: 1,2,3,4,5? Я хочу удалить "2" из списка:

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

Но что, если "2" - первое или последнее значение из списка?

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

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

Как я могу обрабатывать все 3 случая одним запросом?!

Ответ 1

Если значение, которое необходимо удалить из набора, не может присутствовать более одного раза, вы можете использовать это:

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

см. его здесь. Если значение может присутствовать более одного раза, это приведет к удалению всех его описаний:

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

Ответ 2

update TABLE
set COLUMN = COLUMN & ~NUM
where COLUMN & NUM

Взято из раздела комментариев: http://dev.mysql.com/doc/refman/5.0/en/set.html

Остерегайтесь, однако, NUM не является значением "2", а его внутренним индексом. Поэтому, если вы определили поле типа "set" ( "1", "2", "3", "4", "5" ), то соответствующими индексами этих значений являются (1,2,4,8,16).

Ответ 3

Лучший способ - не сохранять значения, разделенные запятой в таблице.

Но чтобы ответить на ваш вопрос, вы можете использовать CASE для этого,

UPDATE table 
SET categories = CASE WHEN field LIKE '%,2,%'  -- In the middle
                           THEN REPLACE(categories, ',2,', ',')
                      WHEN field LIKE '2,%'    -- At the beginning
                           THEN REPLACE(categories, '2,', '')
                      WHEN field LIKE '%,2'    -- At the end
                           THEN REPLACE(categories, ',2', '') 
                      WHEN field = '2'         -- At whole
                           THEN '' 
                 END
WHERE FIND_IN_SET('2', categories)

Ответ 4

Вот еще один способ сделать это:

UPDATE table
SET categories = CONCAT_WS(',',
    IF(FIND_IN_SET('1', categories), '1', NULL),
    -- Note that '2' is missing here!
    IF(FIND_IN_SET('3', categories), '3', NULL),
    IF(FIND_IN_SET('4', categories), '4', NULL),
    IF(FIND_IN_SET('5', categories), '5', NULL)
);

CONCAT_WS объединяет все свои аргументы (кроме аргумента 1) с первым аргументом (в данном случае ','), если они не являются NULL. Мы просматриваем каждое возможное значение поля SET, если поле содержит его, но пропустите тот, который мы хотим удалить (2 в этом случае). Если да, мы возвращаем это значение, иначе NULL. Это объединяет все значения набора с ',', пропуская тот, который мы хотим удалить, восстанавливая новое значение для поля SET.

Это, конечно, работает только, если вы знаете все возможные значения categories, но поскольку это поле SET, вы это знаете.