从MYSQL数据库在阵列搜索ID [英] MYSQL searching for ID in array from DB
问题描述
我已经做了搜索,但一切我所发现的是扭转我所需要的方式。
I have done searching, but everything I have found is reverse the way I need.
我有一个数据库,其中的一个字段是ID的字符串,我想搜索的数组中的某一ID我在DB了。
I have a database where one of the fields is a string of IDs, I want to search for a certain ID in that array I have in the DB already.
有关实例,在数据库中,它看起来像这样:
For instance, in the database it looks like this:
ID Title Spots Used
1 test 1 1,3,5,8
2 test 2 4,2,5,6,7
3 test 3 3,5,2,1
我需要寻找有多少广告系列中使用有有2 ID现货(应返回两个测试2和测试3)。
I need to search for how many campaigns were used that have the spot that has the ID of 2 (should return both test 2 and test 3).
什么是去了解这一点的最好方法是什么?到目前为止,我可以查询整个事情,运行一行一行,并做了数 strpos
搜索,但问题是,当我最终得到超过10斑点上载,1返回1,10,11,12,13,等等。
What would be the best way to go about this? So far, I can query the entire thing, run row by row, and do a strpos
search for the number, but the problem is that when I end up getting more than 10 spots uploaded, 1 will return 1, 10, 11, 12, 13, etc.
如果我需要提供更多的信息,请让我知道。
If I need to provide more info, please let me know.
推荐答案
如果这些ID存储为逗号分隔那么你可以使用的 FIND_IN_SET()的
If these ids are stored as comma seperated then you can use FIND_IN_SET()
SELECT * FROM `table` WHERE FIND_IN_SET('2',`column_name`) >0
但存储逗号分隔值是坏的主意,如果你能够改变你的模式,然后第一个规范化表结构
But storing comma separated values is bad idea if you are able to change your schema then first normalize your table structure
这篇关于从MYSQL数据库在阵列搜索ID的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!