SQL 数据库字段中的逗号分隔值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/738133/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Comma separated values in a database field
提问by Lasse V. Karlsen
I have a products table. Each row in that table corresponds to a single product and it's identified by a unique Id. Now each product can have multiple "codes" associated with that product. For example:
我有一个产品表。该表中的每一行对应一个产品，并由唯一的 ID 标识。现在，每个产品都可以有多个与该产品相关联的“代码”。例如：
Id | Code ---------------------- 0001 | IN,ON,ME,OH 0002 | ON,VI,AC,ZO 0003 | QA,PS,OO,ME
What I'm trying to do is create a stored procedure so that I can pass in a codes like "ON,ME" and have it return every product that contains the "ON" or "ME" code. Since the codes are comma separated, I don't know how I can split those and search them. Is this possible using only TSQL?
我想要做的是创建一个存储过程，以便我可以传入“ON，ME”之类的代码，并让它返回包含“ON”或“ME”代码的每个产品。由于代码是逗号分隔的，我不知道如何拆分和搜索它们。这是否可以仅使用 TSQL？
Edit: It's a mission critical table. I don't have the authority to change it.
编辑：这是一个关键任务表。我没有权力改变它。
回答by Jason Coyne
You should be storing the codes in a separate table, since you have a many to many relationship. If you separate them, then you will easily be able to check.
您应该将代码存储在单独的表中，因为您具有多对多关系。如果将它们分开，那么您将能够轻松检查。
It would be possible to do in the type of system you have now, but would require text searching of the columns, with multiple searches per row to work, which will have huge performance problems as your data grows.
可以在您现在拥有的系统类型中进行，但需要对列进行文本搜索，每行进行多次搜索才能工作，随着数据的增长，这将带来巨大的性能问题。
If you try to go down you current path : You will have to break apart your input string, because nothing guarantees the codes on each record are in the same order (or contiguous) as the input parameter. Then you would have to do a
如果您尝试沿着当前路径走下去：您将不得不拆分输入字符串，因为没有什么能保证每条记录上的代码与输入参数的顺序相同（或连续）。那么你将不得不做一个
Code LIKE '%IN%'
AND Code Like '%QA%'
query with an additional statement for every code you are checking for. Very inefficient.
使用附加语句查询您要检查的每个代码。非常低效。
The UDF idea below is also a good idea. However, depending on the size of your data and the frequency of queries and updates, you may have issues there as well.
下面的UDF想法也是一个好主意。但是，根据数据的大小以及查询和更新的频率，您也可能会遇到问题。
would it be possible to create an additional table that is normalized that is synchronized on a scheduled basis (or based on a trigger) for you to query against?
是否可以创建一个额外的标准化表，该表在预定的基础上（或基于触发器）同步供您查询？
回答by Irawan Soetomo
First, let's make the original table to become like this:
首先，让我们把原来的表变成这样：
Id | Value
-----+------
0001 | IN
0001 | ME
0001 | OH
0001 | ON
0002 | AC
0002 | ON
0002 | VI
0002 | ZO
0003 | ME
0003 | OO
0003 | PS
0003 | QA
It is accomplished by parsing the comma separated values into rows. Then use the powerful CROSS APPLY keyword to join with the original table to retrieve it's Id. Next step is simply to query this CTE.
它是通过将逗号分隔的值解析为行来完成的。然后使用强大的 CROSS APPLY 关键字与原始表连接以检索它的 Id。下一步就是简单地查询这个 CTE。
create function FnSplitToTable
(
@param nvarchar(4000)
)
returns table as
return
with
Num(Pos) as -- list of positions, numbered from 1 to 4000, largest nvarchar
(
select cast(1 as int)
union all
select cast(Pos + 1 as int) from Num where Pos < 4000
)
select substring(@Param, Pos,
charindex(',', @Param + ',', Pos) - Pos) as Value
from Num where Pos <= convert(int, len(@Param))
and substring(',' + @Param, Pos, 1) = ','
go
create proc ProcGetProductId
(
@Codes nvarchar(4000)
)
as
with
Src
(
Id,
Code
)
as
(
select '0001', 'IN,ON,ME,OH'
union all
select '0002', 'ON,VI,AC,ZO'
union all
select '0003', 'QA,PS,OO,ME'
),
Parse as
(
select
s.Id,
f.Value
from
Src as s
cross apply
FnSplitToTable(s.Code) as f
)
select distinct
p.Id
from
Parse as p
join
FnSplitToTable(@Codes) as f
on
p.Value = f.Value
option (maxrecursion 4000)
go
exec ProcGetProductId 'IN,ME' -- returns 0001 & 0003
回答by Lasse V. Karlsen
Everybody else seems very eager to tell you that you should not do this, although I don't see any explicit explanation for why not.
其他人似乎都非常渴望告诉您不应该这样做，尽管我没有看到任何明确的解释说明为什么不这样做。
Apart from breaking the normalization rules, the reason is that you'll do a table-scan through all rows, since you can't have an index on the individual "values" in that column.
除了违反规范化规则之外，原因是您将对所有行进行表扫描，因为您无法对该列中的各个“值”建立索引。
Simply put, there's no way for the database engine to keep some kind of quick-list of which rows contains the code 'AC', unless you either break it up into a separate table, or put it in a column by itself.
简而言之，数据库引擎无法保留某种快速列表，其中包含代码“AC”的行，除非您将其分解为单独的表，或将其单独放在一列中。
Now, if you have other criteria in your SELECT statements that will limit the number of rows down to some manageable number, then perhaps this will be ok, but otherwise I would, if you can, try to avoid this solution and do what others have already told you, split it up into a separate table.
现在，如果您的 SELECT 语句中有其他条件将行数限制为某个可管理的数字，那么这可能没问题，但否则我会，如果可以的话，尽量避免使用此解决方案并执行其他操作已经告诉你了，把它拆分成一个单独的表。
Now, if you're stuck with this design, you can do a search using the following type of query:
现在，如果您坚持使用此设计，则可以使用以下类型的查询进行搜索：
...
WHERE ',' + Code + ',' LIKE '%,AC,%'
This will:
这会：
- Match 'ON,VI,AC,ZO'
- Not match 'ON,VI,TAC,ZO'
- 匹配'ON,VI,AC,ZO'
- 不匹配 'ON,VI,TAC,ZO'
I don't know if the last one is a viable option in your case, if you only have 2-letter codes, then you can use just this:
我不知道最后一个在您的情况下是否可行，如果您只有 2 个字母的代码，那么您可以只使用这个：
...
WHERE Code LIKE '%AC%'
But again, this will perform horribly unless you limit the number of rows using other criteria.
但同样，除非您使用其他条件限制行数，否则这将执行得很糟糕。
回答by Charles Bretana
Although all the previous posters are correct about the normalization of your db schema, you can do what you want using a "Table-Valued UDF" that takes a delimited string and returns a Table, with one row per value in the string... You can use this table as you would any other table in your stored proc , joining to it, etc... this will solve your immediate issue...
尽管之前所有的海报都对 db 模式的规范化是正确的，但您可以使用“Table-Valued UDF”来做您想做的事情，该 UDF 接受一个分隔字符串并返回一个表，字符串中的每个值都有一行...您可以像使用存储过程中的任何其他表一样使用此表，加入它等……这将解决您的直接问题……
Here's a link to such a UDF: FN_Split UDF
这是此类 UDF 的链接： FN_Split UDF
Although the article talks about using it to pass a delimited list of data values in to a stored proc, you can use the same UDF to operate on a delimited string stored in a column of an existing table....
尽管文章谈到使用它来将数据值的分隔列表传递到存储过程，但您可以使用相同的 UDF 对存储在现有表的列中的分隔字符串进行操作......
回答by Mehrdad Afshari
The way you are storing data breaks normalization rules. Only a single atomic value should be stored in each field. You should store each item in a single row.
您存储数据的方式违反了规范化规则。每个字段中只能存储一个原子值。您应该将每个项目存储在一行中。
回答by kumar
More than 1 year old question, but still thought it will be useful. You can use the FIND_IN_SET function of MySql. I am not sure whether other DBMSs support it or not.
超过 1 年的问题，但仍然认为它会有用。你可以使用 MySql 的 FIND_IN_SET 函数。我不确定其他 DBMS 是否支持它。
You can use this function as follows:
您可以按如下方式使用此功能：
SELECT * FROM `table_name` WHERE FIND_IN_SET('AC', `Code`) > 0
回答by JP Emvia
1st Step : Code to create function
第一步：创建函数的代码
<font face="Courier New" size="2">
<font color = "blue">CREATE</font> <font color = "blue">FUNCTION</font> <font color = "maroon">[dbo]</font><font color = "silver">.</font><font color = "#FF0080"><b>[Udflistofids]</b></font> <font color = "maroon">(</font>
<br/><font color = "green"><i>-- Add the parameters for the function here</i></font>
<br/><font color = "#8000FF">@ListOfIDs</font> <font color = "blue">AS</font> <font color = "black"><i>VARCHAR</i></font><font color = "maroon">(</font><font color = "maroon">max</font><font color = "maroon">)</font>
<br/><font color = "green"><i>--, @IDsSeperationChar as varchar(5) = ','</i></font>
<br/><font color = "silver">,</font>
<br/><font color = "#8000FF">@UniqueID1</font> <font color = "black"><i>VARCHAR</i></font><font color = "maroon">(</font><font color = "black">250</font><font color = "maroon">)</font><font color = "silver">,</font>
<br/><font color = "#8000FF">@UniqueID2</font> <font color = "black"><i>VARCHAR</i></font><font color = "maroon">(</font><font color = "black">250</font><font color = "maroon">)</font><font color = "silver">,</font>
<br/><font color = "#8000FF">@UniqueID3</font> <font color = "black"><i>VARCHAR</i></font><font color = "maroon">(</font><font color = "black">250</font><font color = "maroon">)</font><font color = "silver">,</font>
<br/><font color = "#8000FF">@UniqueID4</font> <font color = "black"><i>VARCHAR</i></font><font color = "maroon">(</font><font color = "black">250</font><font color = "maroon">)</font><font color = "silver">,</font>
<br/><font color = "#8000FF">@UniqueID5</font> <font color = "black"><i>VARCHAR</i></font><font color = "maroon">(</font><font color = "black">250</font><font color = "maroon">)</font><font color = "maroon">)</font>
<br/><font color = "maroon">returns</font> <font color = "#8000FF">@TabListOfIDs</font> <font color = "blue">TABLE</font> <font color = "maroon">(</font>
<br/> <font color = "green"><i>-- Add the column definitions for the TABLE variable here</i></font>
<br/> <font color = "maroon">id</font> <font color = "black"><i>VARCHAR</i></font><font color = "maroon">(</font><font color = "black">50</font><font color = "maroon">)</font><font color = "silver">,</font>
<br/> <font color = "maroon">uniqueid1</font> <font color = "black"><i>VARCHAR</i></font><font color = "maroon">(</font><font color = "black">250</font><font color = "maroon">)</font><font color = "silver">,</font>
<br/> <font color = "maroon">uniqueid2</font> <font color = "black"><i>VARCHAR</i></font><font color = "maroon">(</font><font color = "black">250</font><font color = "maroon">)</font><font color = "silver">,</font>
<br/> <font color = "maroon">uniqueid3</font> <font color = "black"><i>VARCHAR</i></font><font color = "maroon">(</font><font color = "black">250</font><font color = "maroon">)</font><font color = "silver">,</font>
<br/> <font color = "maroon">uniqueid4</font> <font color = "black"><i>VARCHAR</i></font><font color = "maroon">(</font><font color = "black">250</font><font color = "maroon">)</font><font color = "silver">,</font>
<br/> <font color = "maroon">uniqueid5</font> <font color = "black"><i>VARCHAR</i></font><font color = "maroon">(</font><font color = "black">250</font><font color = "maroon">)</font><font color = "maroon">)</font>
<br/><font color = "blue">AS</font>
<br/> <font color = "blue">BEGIN</font>
<br/> <font color = "green"><i>-- Fill the table variable with the rows for your result set</i></font>
<br/> <font color = "blue">DECLARE</font> <font color = "#8000FF">@Pos</font> <font color = "blue">AS</font> <font color = "black"><i>INT</i></font>
<br/> <font color = "blue">DECLARE</font> <font color = "#8000FF">@ID</font> <font color = "blue">AS</font> <font color = "black"><i>VARCHAR</i></font><font color = "maroon">(</font><font color = "black">50</font><font color = "maroon">)</font><font color = "silver">,</font>
<br/> <font color = "#8000FF">@IDsSeperationChar</font> <font color = "blue">AS</font> <font color = "black"><i>VARCHAR</i></font><font color = "maroon">(</font><font color = "black">5</font><font color = "maroon">)</font> <font color = "silver">=</font> <font color = "red">','</font>
<br/>
<br/> <font color = "green"><i>--SET @ListOfIDs = REPLACE( @ListOfIDs, @IDsSeperationChar, ',')</i></font>
<br/> <font color = "blue">SET</font> <font color = "#8000FF">@ListOfIDs</font> <font color = "silver">=</font> <font color = "fuchsia"><i>Ltrim</i></font><font color = "maroon">(</font><font color = "fuchsia"><i>Rtrim</i></font><font color = "maroon">(</font><font color = "#8000FF">@ListOfIDs</font><font color = "maroon">)</font><font color = "maroon">)</font>
<br/> <font color = "silver">+</font> <font color = "#8000FF">@IDsSeperationChar</font>
<br/> <font color = "blue">SET</font> <font color = "#8000FF">@Pos</font> <font color = "silver">=</font> <font color = "fuchsia"><i>Patindex</i></font><font color = "maroon">(</font><font color = "red">'%'</font> <font color = "silver">+</font> <font color = "#8000FF">@IDsSeperationChar</font> <font color = "silver">+</font> <font color = "red">'%'</font><font color = "silver">,</font> <font color = "#8000FF">@ListOfIDs</font><font color = "maroon">)</font>
<br/>
<br/> <font color = "green"><i>--SET @Pos = CHARINDEX(@IDsSeperationChar, @ListOfIDs, 1)</i></font>
<br/> <font color = "blue">IF</font> <font color = "fuchsia"><i>Replace</i></font><font color = "maroon">(</font><font color = "#8000FF">@ListOfIDs</font><font color = "silver">,</font> <font color = "#8000FF">@IDsSeperationChar</font><font color = "silver">,</font> <font color = "red">''</font><font color = "maroon">)</font> <font color = "silver"><></font> <font color = "red">''</font>
<br/> <font color = "blue">BEGIN</font>
<br/> <font color = "blue">WHILE</font> <font color = "#8000FF">@Pos</font> <font color = "silver">></font> <font color = "black">0</font>
<br/> <font color = "blue">BEGIN</font>
<br/> <font color = "blue">SET</font> <font color = "#8000FF">@ID</font> <font color = "silver">=</font> <font color = "fuchsia"><i>Ltrim</i></font><font color = "maroon">(</font><font color = "fuchsia"><i>Rtrim</i></font><font color = "maroon">(</font><font color = "fuchsia"><i>LEFT</i></font><font color = "maroon">(</font><font color = "#8000FF">@ListOfIDs</font><font color = "silver">,</font> <font color = "#8000FF">@Pos</font> <font color = "silver">-</font> <font color = "black">1</font><font color = "maroon">)</font><font color = "maroon">)</font><font color = "maroon">)</font>
<br/>
<br/> <font color = "blue">IF</font> <font color = "#8000FF">@ID</font> <font color = "silver"><></font> <font color = "red">''</font>
<br/> <font color = "blue">BEGIN</font>
<br/> <font color = "blue">INSERT</font> <font color = "blue">INTO</font> <font color = "#8000FF">@TabListOfIDs</font>
<br/> <font color = "maroon">(</font><font color = "maroon">id</font><font color = "silver">,</font>
<br/> <font color = "maroon">uniqueid1</font><font color = "silver">,</font>
<br/> <font color = "maroon">uniqueid2</font><font color = "silver">,</font>
<br/> <font color = "maroon">uniqueid3</font><font color = "silver">,</font>
<br/> <font color = "maroon">uniqueid4</font><font color = "silver">,</font>
<br/> <font color = "maroon">uniqueid5</font><font color = "maroon">)</font>
<br/> <font color = "blue">VALUES</font> <font color = "maroon">(</font><font color = "#8000FF">@ID</font><font color = "silver">,</font>
<br/> <font color = "#8000FF">@UniqueID1</font><font color = "silver">,</font>
<br/> <font color = "#8000FF">@UniqueID2</font><font color = "silver">,</font>
<br/> <font color = "#8000FF">@UniqueID3</font><font color = "silver">,</font>
<br/> <font color = "#8000FF">@UniqueID4</font><font color = "silver">,</font>
<br/> <font color = "#8000FF">@UniqueID5</font><font color = "maroon">)</font> <font color = "green"><i>--Use Appropriate conversion</i></font>
<br/> <font color = "blue">END</font>
<br/>
<br/> <font color = "blue">SET</font> <font color = "#8000FF">@ListOfIDs</font> <font color = "silver">=</font> <font color = "fuchsia"><i>RIGHT</i></font><font color = "maroon">(</font><font color = "#8000FF">@ListOfIDs</font><font color = "silver">,</font> <font color = "fuchsia"><i>Len</i></font><font color = "maroon">(</font><font color = "#8000FF">@ListOfIDs</font><font color = "maroon">)</font> <font color = "silver">-</font>
<br/> <font color = "fuchsia"><i>Len</i></font><font color = "maroon">(</font><font color = "#8000FF">@ID</font> <font color = "silver">+</font> <font color = "#8000FF">@IDsSeperationChar</font><font color = "maroon">)</font>
<br/> <font color = "maroon">)</font>
<br/> <font color = "blue">SET</font> <font color = "#8000FF">@Pos</font> <font color = "silver">=</font> <font color = "fuchsia"><i>Patindex</i></font><font color = "maroon">(</font><font color = "red">'%'</font> <font color = "silver">+</font> <font color = "#8000FF">@IDsSeperationChar</font> <font color = "silver">+</font> <font color = "red">'%'</font><font color = "silver">,</font> <font color = "#8000FF">@ListOfIDs</font>
<br/> <font color = "maroon">)</font>
<br/> <font color = "green"><i>--SET @Pos = CHARINDEX(@IDsSeperationChar, @ListOfIDs, 1)</i></font>
<br/> <font color = "blue">END</font>
<br/> <font color = "blue">END</font>
<br/>
<br/> <font color = "blue">RETURN</font>
<br/> <font color = "blue">END</font>
<br/>
<br/><font color = "maroon">go</font>
</font>
**2nd Step : Code to get the result**
<font face="Courier New" size="2">
<font color = "blue">DECLARE</font> <font color = "#8000FF">@udvMax</font> <font color = "black"><i>NVARCHAR</i></font><font color = "maroon">(</font><font color = "maroon">max</font><font color = "maroon">)</font>
<br/>
<br/><font color = "blue">SELECT</font> <font color = "#8000FF">@udvMax</font> <font color = "silver">=</font> <font color = "red">''</font> <font color = "silver">+</font> <font color = "fuchsia"><i>Substring</i></font><font color = "maroon">(</font> <font color = "maroon">(</font> <font color = "blue">SELECT</font> <font color = "red">' Union '</font> <font color = "silver">+</font>
<br/> <font color = "red">'Select * from dbo.udfListOfIDs('''</font> <font color = "silver">+</font>
<br/> <font color = "maroon">tmpu</font><font color = "silver">.</font><font color = "maroon">code</font> <font color = "silver">+</font> <font color = "red">''', '''</font> <font color = "silver">+</font> <font color = "maroon">tmpu</font><font color = "silver">.</font><font color = "maroon">id</font> <font color = "silver">+</font> <font color = "red">''', '''</font> <font color = "silver">+</font> <font color = "maroon">tmpu</font><font color = "silver">.</font><font color = "maroon">code</font> <font color = "silver">+</font>
<br/> <font color = "red">''', null,null,null )'</font> <font color = "blue">FROM</font> <font color = "maroon">tmpu</font> <font color = "blue">FOR</font> <font color = "maroon">xml</font> <font color = "maroon">path</font><font color = "maroon">(</font><font color = "red">''</font><font color = "maroon">)</font><font color = "maroon">)</font><font color = "silver">,</font> <font color = "black">7</font><font color = "silver">,</font>
<br/> <font color = "black">200000</font><font color = "maroon">)</font>
<br/> <font color = "silver">+</font>
<br/> <font color = "red">' Order by UniqueID1, UniqueID2, UniqueID3, UniqueID4, UniqueID5, ID'</font>
<br/>
<br/><font color = "green"><i>--Select @udvMax</i></font>
<br/><font color = "blue">EXECUTE</font> <font color = "#FF0080"><b>Sp_executesql</b></font>
<br/> <font color = "#8000FF">@udvMax</font>
</font>
****May be u may need to add your criteria in select statement in 2nd step.**
****可能是您需要在第二步的选择语句中添加您的条件。**
Hope this will help you.
希望这会帮助你。
JP
J.P
回答by Alex Aza
Although in your case simple LIKEwill work, here is the solution how to parse comma separated strings Table Normalization (Parse comma separated fields into individual records).
尽管在您的情况下 simpleLIKE会起作用，但这里是如何解析逗号分隔的字符串表规范化（将逗号分隔的字段解析为单个记录）的解决方案。
回答by Ahmad Sayeed
if you want to do it with php and mysql it can be any number of keyword no restriction
如果你想用 php 和 mysql 来做，它可以是任意数量的关键字，没有限制
$var = explode(',',"ahmad,sayeed,asmal,babu");
$query = "SELECT * FROM post WHERE post_tags LIKE '%a%' ";
$query1=NULL;
foreach($var as $value)
{
$query1.= " OR post_tags LIKE '%$value%' ";
}
echo "$query $query1";
OUTPUT:
输出：
SELECT * FROM post WHERE post_tags LIKE '%a%' OR post_tags LIKE '%ahmad%' OR post_tags LIKE '%sayeed%' OR post_tags LIKE '%asmal%' OR post_tags LIKE '%babu%'
SELECT * FROM post WHERE post_tags LIKE '%a%' OR post_tags LIKE '%ahmad%' OR post_tags LIKE '%sayeed%' OR post_tags LIKE '%asmal%' OR post_tags LIKE '%babu%'
回答by Andy White
This might not be possible if you're stuck with that database design, but it would be a lot easier to put the codes into separate records in another table:
如果您坚持使用该数据库设计，这可能是不可能的，但是将代码放入另一个表中的单独记录中会容易得多：
ProductCode
-----------
ProductID (FK to Product.ID)
Code (varchar)
The table might look like this:
该表可能如下所示：
ProductID Code
-----------------
0001 IN
0001 ON
0001 ME
...
The query would look something like this (you'd have to pass in the Codes somehow - either as separate variables, or maybe a comma-separated string that you split in the proc):
查询看起来像这样（您必须以某种方式传入代码 - 作为单独的变量，或者您在 proc 中拆分的逗号分隔字符串）：
select ProductID
from ProductCode
where Code in ('ON', 'ME')
