在mysql中字符串唯一性的类似功能

my question is is there any way to do this functionality in mySQL: $str = implode(',',array_unique(explode(',', $str))); . Since I am doing a table update in a loop, i need to check if the string already exists in the column. Something like

我的问题是在mySQL中有没有办法做这个功能:$ str = implode(',',array_unique(explode(',',$ str))); 。由于我在循环中进行表更新,我需要检查列中是否已存在该字符串。就像是

Item | Numbers
A    | 1,2,4
B    | 6,7,8
C    | 2,7,5

So if an entry comes in which should be added to row B, say 7,8,30 it should only add 30 in the string. so B | 6,7,8,30. I know about selecting the row first and doing it in PHP, I am looking for a way to do directly in mySQL.

因此,如果有一个条目应该添加到B行,比如7,8,30,它应该只在字符串中添加30。所以B | 6,7,8,30。我知道首先选择行并在PHP中执行,我正在寻找一种直接在mySQL中执行的方法。

Note : Unfortunately I wont be able to change the table structure. (Edited)

注意:不幸的是我无法更改表结构。 (编辑)的

Thanks

2 个解决方案

#1

If you had your table in a correct format, then yes. You would need Item to be a key, Number to be a numeric column, and Item,Number to be the primary key. Then you can do this:

如果你的表格格式正确,那么是的。您需要将Item作为键,将Number作为数字列,将Item,Number作为主键。然后你可以这样做:

INSERT IGNORE INTO table VALUES ('B',7), ('B',8), ('B',30)

This will ignore duplicates when adding values. When it comes to getting the data back, you can simply get all rows where Item='B' and implode them.

添加值时,这将忽略重复项。在获取数据时,您可以简单地获取Item ='B'的所有行并将其内爆。

#2

You can do this as:

你可以这样做:

update t
    set Numbers = (case when Numbers = '' then '30'
                        else concat(Numbers, ',', 30)
                   end)
    where item = 'B';

However, the data structure is not very good for what you want to accomplish. You should have a table called ItemNumbers, which would have an item and a number in each row.

但是,数据结构不是很适合您想要完成的任务。你应该有一个名为ItemNumbers的表,它在每一行中都有一个项目和一个数字。

#1