处理MySQL中多个数组数据的最佳方法?

I'm working on a project where i will be storing arrays/lists in a MySQL table and make a PHP based search on the information in the lists.
How i basically thought of doing this would be to put in the data like this:

我正在做一个项目，我将在MySQL表中存储数组/列表，并在列表中对信息进行基于PHP的搜索。我主要想做的是把这些数据放入这样的数据中:

column1
,2,6,9,30,
,3,5,8,
,5,6,13,16,17,

column2
,b,g,k,ro,
,c,e,t,
,d,h,i,ar,ez,

then search like this:

然后搜索:

SELECT * FROM table_name WHERE column1 LIKE '%,3,%' OR column2 LIKE '%,h,%'

But i might have to search for five items in column1 and three in column2, which would make the query long and possibly slow?..

但是我可能需要在column1和column2中搜索5个条目，这会使查询变得很长，而且可能会很慢。

What i want to know is, is this a smart way to do it? Is there faster and/or better methods?..

我想知道的是，这是一个聪明的方法吗?有没有更快更好的方法?

Let me know if you have questions or if i'm not clear enough.

如果你有问题或者我不够清楚，请告诉我。

1 个解决方案

#1

If I understand correctly, it would make sense to make the DB structure match the arrays better instead of storing string arrays.

如果我理解正确，那么让DB结构更好地匹配数组，而不是存储字符串数组是有意义的。

For instance, you could use a "LIST" and a "LIST_ITEM" table, with a foreign key from LIST_ITEM.LIST_ID to LIST.LIST_ID. Each entry in List_Item would be an entry in that list. Then queries for those list items could return which lists they were in much faster than having to traverse long strings and compare characters.

例如，您可以使用一个“LIST”和一个“LIST_ITEM”表，其中有一个来自LIST_ITEM的外键。LIST_ID LIST.LIST_ID。List_Item中的每个条目将是该列表中的一个条目。然后，对这些列表项的查询可以返回那些列表，它们比需要遍历长字符串和比较字符要快得多。