更新行而不删除mysql中的先前值 [英] update row without deleting previous values in mysql

问题描述

我的一个表中有一个字段user_ids，该字段的值类似2,3

One of my table has a field user_ids and the value of the field like 2,3

group_id| user_ids  
--------|------------   
1       | 2,3  
--------|------------  
2       | 5,8

我想更新该字段而不删除当前值.对于前.如果我需要为group_id id 1添加5，那么2,3应该像2,3,5

I want to update the field without deleting the current value. For ex. If I need to add 5 for group_id id 1, then 2,3 should be like 2,3,5

我正在使用此查询:

UPDATE users_group SET user_ids = CONCAT( SUBSTRING( user_ids, 1, CHAR_LENGTH( user_ids ) -1 ) , ',5' ) WHERE group_id =1

但是它将用逗号删除先前的值.

But it is deleting previous value with comma.

group_id| user_ids  
--------|------------   
1       | ,5  
--------|------------  
2       | 5,8

有人可以为此建议正确的方法吗?

can anyone suggest the right way for this?

推荐答案

您能否将其连接起来，而不是先将其拆分?

Can you not just concatenate it on, rather than trying to split it up first?

UPDATE users_group 
SET user_ids = CONCAT_WS(',', user_ids, '5' ) 
WHERE group_id =1

但这确实表明标准化的数据库设计很差.通常，用逗号分隔的列表应该作为行存储在另一张表上(如列表中的每个值一行)，如Mark Ba​​ker所建议的那样.

But this does suggest a badly normalised database design. Generally a comma separated list should instead be stored as rows on another table (ie, one row per value in the list) as suggested by Mark Baker.

编辑-如果您希望每个user_ids字段中只有一个ID的一个副本，而不论您尝试插入多少次，并且希望一次添加多个ID:-

EDIT - If you want to only have a single copy of any id in each user_ids field, irrespective of how many times you try to insert it, and you want to be able to add multiple ids at once:-

UPDATE users_group a
INNER JOIN
(
    SELECT 3 AS an_id
    UNION 
    SELECT 4
) b
ON FIND_IN_SET(b.an_id, a.user_ids) = 0
SET a.user_ids = CONCAT_WS(',', a.user_ids, b.an_id ) 
WHERE a.group_id =1

再次编辑-如果您有一个包含ID的用户表，则可以从其中选择要添加ID的ID中选择ID.

EDIT again - if you have a table of users containing the ids then you can select the ids from that where the id is one of those you want to add.

类似这样的东西.

UPDATE users_group a
INNER JOIN
(
    SELECT id
    FROM users
    WHERE id IN (3, 4)
) b
ON FIND_IN_SET(b.id, a.user_ids) = 0
SET a.user_ids = CONCAT_WS(',', a.user_ids, b.id ) 
WHERE a.group_id =1

这篇关于更新行而不删除mysql中的先前值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！