我应该如何用逗号完全删除数据库中的id？

Question

I have a table tbl_scripts that goes like this:

 allowed_group_ids
+-----------------+
 12343,12343,21342

And I need to remove from this string a group id, for example 12343. Setting delete_group_id to 12343, the below query seems to work correctly:

UPDATE tbl_scripts
SET allowed_group_ids = 
  REPLACE(
    REPLACE(allowed_group_ids, '$delete_group_id,', ''),
    ',$delete_group_id', '')
WHERE
  system_id = {$_SESSION['system_id']}

But if the column only has a single value, it fails:

 allowed_group_ids
+-----------------+
 12343 <-----I'm not able to replace this with blank

I know I should normalize the table, but at the moment I can't change my database structure, so I'm trying to make it work this way. How can I make my query work with single values?

Answer 1

The first replace searches for a trailing comma, the second for a leading comma. The single group id has no trailing comma.

Safest option is to run a second query for the case where the field = value

doing this at any point

REPLACE(allowed_group_ids, '$delete_group_id', '')

May delete parts of longer ids (e.g. 123456 out of 1234567 would leave 7) So don't do that

Alternatively always add a trailing comma even for single ids, then you only need something like

REPLACE(allowed_group_ids, '$delete_group_id,', '')

Adding a trailing comma, is something that you can easily update across the whole db, (as a one off query)

UPDATE table SET allowed_group_ids = CONCAT(allowed_group_ids, ',');

Then check through your code for insert and udpate statement acting on this table