MySQL “IN” 子句中的逗号分隔值

php mysql sql

2022-08-30 15:45:42

我的一个表中有一列，其中存储了由逗号分隔的多个ID。有没有办法在查询的“IN”子句中使用此列的值。

column（）具有如下值：city6,7,8,16,21,2

我需要使用

select * from table where e_ID in (Select city from locations where e_Id=?)

我对克罗津的回答感到满意，但我对建议、观点和选择持开放态度。

随时分享您的观点。

答案 1

基于 @Jeremy Smith 的 FIND_IN_SET（）示例，您可以使用连接来执行此操作，因此您不必运行子查询。

SELECT * FROM table t
JOIN locations l ON FIND_IN_SET(t.e_ID, l.city) > 0
WHERE l.e_ID = ?

众所周知，这执行得非常差，因为它必须执行表扫描，为和 中的每个行组合计算 FIND_IN_SET（）函数。它不能利用索引，也没有办法改进它。tablelocations

我知道你说你正试图充分利用一个糟糕的数据库设计，但你必须明白这是多么糟糕。

解释：假设我要求你在电话簿中查找第一个、中间或最后一个首字母为“J”的每个人。在这种情况下，书籍的排序顺序没有任何帮助，因为无论如何你都必须扫描每一页。

@fthiella给出的解决方案在性能方面也存在类似的问题。无法对它编制索引。LIKE

另请参阅我对在数据库列中存储分隔列表真的那么糟糕吗？的回答，了解这种存储非规范化数据的其他陷阱。

如果可以创建补充表来存储索引，则可以将位置映射到城市列表中的每个条目：

CREATE TABLE location2city (
 location INT,
 city INT,
 PRIMARY KEY (location, city)
);

假设您有一个查找表，用于查找所有可能的城市（而不仅仅是中提到的城市），您可以承受一次效率低下的情况来生成映射：table

INSERT INTO location2city (location, city)
  SELECT l.e_ID, c.e_ID FROM cities c JOIN locations l
  ON FIND_IN_SET(c.e_ID, l.city) > 0;

现在，您可以运行更高效的查询来查找以下列中的条目：table

SELECT * FROM location2city l
JOIN table t ON t.e_ID = l.city
WHERE l.e_ID = ?;

这可以利用索引。现在，您只需要注意，任何 INSERT/UPDATE/DELETE 中的行也会在中插入相应的映射行。locationslocation2city

答案 2

从MySQL的角度来看，你没有存储用逗号分隔的多个ID - 你正在存储一个文本值，它与“Hello World”或“我喜欢蛋糕！”具有完全相同的测量值 - 即它没有任何测量。

您要做的是创建一个单独的表，该表将数据库中的两个对象链接在一起。阅读有关基于 SQL 的数据库中的多对多或一对多（取决于您的要求）关系的详细信息。