代码之家 › 专栏 › 技术社区 › flyingfox

如何用MySQL删除列中的特殊字符?

mysql-5.0 sql mysql

flyingfox · 技术社区 · 7 年前

我参加了一个项目,遇到了别人设计不好的问题,我们有一个任务表,每个任务都有很多用户,任务表如下:

+----------------+---------------+------+-----+---------+-------+
| 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 列,这意味着许多用户ID被放在一列中,我知道这是一个非常糟糕的设计,但是由于它是一个旧项目,所以我不能修改表设计。

现在我有一个问题,如果用户被删除,如何将其从 任务用户 专栏?

用户ID由uuid生成,长度固定,32个字符,因此每个用户ID都是唯一的,例如 40cf5f01eb2f4d2c954412f27b3bf6eb 但问题是用户ID可能会出现在 任务用户 列,所以我不知道如何删除它

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

删除用户ID时,更新的结果如下

   aaa,bbb

我想知道我们是否可以使用一个更新SQL来删除指定的用户ID并保持相同的数据格式?

注意:我在一个MySQL存储过程中执行此操作,附加变量可能会有所帮助,但我仍然希望只使用一个SQL来执行此操作,MySQL版本是五

事先谢谢!

3 回复 | 直到 7 年前

Tim Biegeleisen 7 年前

我想我们可以通过一个查询来实现这一点:

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, '[[:>:]]');

这里有一个演示的链接只有出于测试目的):

Demo

这个答案使用了一个技巧,通过这个技巧,我们在 task_users 字符串。然后,我们将给定的用户ID与它的开始和结束附加逗号进行比较。如果找到匹配项,我们只替换为一个逗号。但是,这将使替换仍然保留其开始和结束逗号,因此我们将使用子字符串操作删除这些逗号。

撇开SQL奥运会不谈,希望您能从这些答案的复杂性中看到,在SQL数据库中使用csv数据可能是一个真正令人头疼的问题。也许你甚至可以用这个页面作为你的同事的证据来证明表格设计需要改变。

Phil 7 年前

试试这个 CASE 表达式Where 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/#!9/1d2baa/1

原件 “四问” 回答如下

-- 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/#!9/8e9b9bb/1

Alexander 7 年前

可以使用以下表达式将指定的用户标识替换为空字符串:

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,'(,|$)')

注释 , the REGEXP_REPLACE() 在mysql 8.0中添加了函数。