mysql 字段内逗号分割,数据库字段中的逗号分隔值

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 | 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?

Edit: It's a mission critical table. I don't have the authority to change it.

解决方案

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.

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?