Как убрать спецсимволы в колонке с MySQL?

Question

Я участвую в проекте и столкнулся с плохим дизайном от других, у нас есть таблица, называемая задачей, и у каждой задачи есть много пользователей, таблица задач, как показано ниже:

+----------------+---------------+------+-----+---------+-------+
| Field          | Type          | Null | Key | Default | Extra |
+----------------+---------------+------+-----+---------+-------+
| id             | varchar(40)   | NO   | PRI | NULL    |       |
| name           | varchar(100)  | YES  |     | NULL    |       |
| task_users     | varchar(1000) | YES  |     | NULL    |       |

и пользователь хранится как aaa,bbb,ccc в task_users столбец, который означает, что многие идентификаторы пользователя помещены в один столбец, я знаю, что это очень плохой дизайн, но так как это старый проект, я не могу изменить дизайн таблицы.

Теперь у меня есть проблема, если пользователь удален, как я могу удалить его из task_users столбец?

идентификатор пользователя генерируется UUID и имеет фиксированную длину с 32 символами, поэтому каждый идентификатор пользователя уникален, например: 40cf5f01eb2f4d2c954412f27b3bf6eb, но проблема в том, что идентификатор пользователя может появиться в любой позиции task_users столбец, так что я не знаю как его убрать

aaa,40cf5f01eb2f4d2c954412f27b3bf6eb,bbb -- in center
40cf5f01eb2f4d2c954412f27b3bf6eb,aaa,bbb -- in head
aaa,bbb,40cf5f01eb2f4d2c954412f27b3bf6eb -- in end

при удалении идентификатора пользователя обновленный результат выглядит как

   aaa,bbb

Я хочу знать, можем ли мы использовать одно обновление SQL для удаления указанного идентификатора пользователя и при этом сохранить тот же формат данных?

Примечание: я делаю это в хранимой процедуре MySQL, может быть полезна дополнительная переменная, но я все же хочу просто использовать один sql для этого, версия MySQL 5.0

Заранее спасибо!

Answer 1

Я думаю, что мы можем сделать это с помощью одного запроса:

UPDATE yourTable
SET task_users = SUBSTRING(
    REPLACE(CONCAT(',', task_users, ','), CONCAT(',', uid, ','), ','),
    2,
    LENGTH(task_users) - LENGTH(uid) - 1)
WHERE task_users REGEXP CONCAT('[[:<:]]', uid, '[[:>:]]');

Вот ссылка на демо (используется только для тестирования):

демонстрация

Этот ответ использует хитрость, с помощью которой мы добавляем запятые в начале и конце task_users строка. Затем мы сравниваем данный идентификатор пользователя, также добавляя запятые к его началу и концу. Если совпадение найдено, мы заменим только одну запятую. Но это оставляет замену с начальными и конечными запятыми, поэтому мы удаляем их с помощью операции подстроки.

Помимо Олимпиады по SQL, мы надеемся, что по сложности этих ответов вы поймете, что работа с данными CSV в базе данных SQL может стать настоящей головной болью. Возможно, вы даже можете использовать эту страницу в качестве доказательства для своих коллег, что дизайн таблицы должен измениться.

Answer 2

Попробуй это CASE выражение где uid аргумент вашей хранимой процедуры...

UPDATE `task` SET `task_users` = CASE
  -- at the start
  WHEN `task_users` LIKE CONCAT(uid, ',%')
    THEN REPLACE(`task_users`, CONCAT(uid, ','), '')
  -- at the end
  WHEN `task_users` LIKE CONCAT('%,', uid)
    THEN REPLACE(`task_users`, CONCAT(',', uid), '')
  -- in the middle
  WHEN `task_users` LIKE CONCAT('%,', uid, ',%')
    THEN REPLACE(`task_users`, CONCAT(',', uid, ','), ',')
  -- only that user
  ELSE ''
END
WHERE `task_users` LIKE CONCAT('%', uid, '%');

Демо ~ http://sqlfiddle.com/

---

Оригинальный ответ "четыре запроса" ниже

-- only that user
UPDATE `task`
SET `task_users` = ''
WHERE `task_users` = uid;

-- at start
UPDATE `task`
SET `task_users` = REPLACE(`task_users`, CONCAT(uid, ','), '')
WHERE `task_users` LIKE CONCAT(uid, ',%');

-- at end
UPDATE `task`
SET `task_users` = REPLACE(`task_users`, CONCAT(',', uid), '')
WHERE `task_users` LIKE CONCAT('%,', uid);

-- in the middle
UPDATE `task`
SET `task_users` = REPLACE(`task_users`, CONCAT(',', uid, ','), ',')
WHERE `task_users` LIKE CONCAT('%,', uid, ',%');

Демо ~ http://sqlfiddle.com/

Answer 3

Вы можете использовать следующее выражение, чтобы заменить указанный идентификатор пользователя пустой строкой:

SET @userID = '40cf5f01eb2f4d2c954412f27b3bf6eb';

UPDATE `task`
SET task_users = REGEXP_REPLACE(task_users, CONCAT('(,', @userID, '|', @userID, ',?)'), '');

Или добавить WHERE предложение для фильтрации записей для обновления:

UPDATE `task`
SET task_users = REGEXP_REPLACE(task_users, CONCAT('(,', @userID, '|', @userID, ',?)'), '');
WHERE task_users RLIKE CONCAT('(^|,)', @userID,'(,|$)')

Обратите внимание, что REGEXP_REPLACE() функция была добавлена ​​в MySQL 8.0.