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

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?

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

Try this:

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

This will remove every entry of $delete_group_id then cleanup leading and trailing commas with the TRIM function.

Try this, not sure how feasible this is in terms of syntax wise..

UPDATE tbl_scripts SET allowed_group_ids = 
CASE WHEN INSTR(allowed_group_ids, '$delete_group_id,') > 0 THEN
REPLACE(REPLACE(allowed_group_ids, '$delete_group_id,', ''), ',$delete_group_id', '')
ELSE NULL END
WHERE system_id = {$_SESSION['system_id']}
;


UPDATE tbl_scripts SET allowed_group_ids = 
CASE WHEN INSTR(allowed_group_ids, ',') > 0 THEN
REPLACE(REPLACE(allowed_group_ids + ',', '$delete_group_id,', ''), ',$delete_group_id', ''
ELSE
REPLACE(REPLACE(allowed_group_ids, '$delete_group_id,', ''), ',$delete_group_id', '')
END
AND system_id = {$_SESSION['system_id']}
;

I think this should do what you are looking for:

UPDATE tbl_scripts
SET allowed_group_ids =
  TRIM( both ',' from
  REPLACE(
    REPLACE(
      CONCAT(',',
        REPLACE(allowed_group_ids, ',', ',,')
           , ','), ',12343,', '')
    , ',,', ','))

• first I double every , to ,,
• then I add a , before the string and a , after, to make it easyer to match the group to delete
• then I replace all ,delete_group_id, (with leading and trailing commas) with an empty string
• then I replace again all ,, back to single ,
• with TRIM, i remove leading and trailing ,