如何优化具有逗号分隔值的表的 SQL 查询？

优化具有逗号分隔列值的表的 SQL 查询

在处理列包含逗号分隔值的表时，执行特定的操作查询可能具有挑战性。常见场景是在逗号分隔列中搜索特定值时检索所有相关数据。

问题：

您需要检索与特定关联的所有用户名逗号分隔列中的值。例如，从下面的表结构中，您希望在搜索时找到所有用户名"new1."

表：

CREATE TABLE tblA
(
id int NOT NULL AUTO_INCREMENT ,
user varchar(255),
category int(255),
PRIMARY KEY (id)
);

CREATE TABLE tblB
(
id int NOT NULL AUTO_INCREMENT ,
username varchar(255),
userid int(255),
PRIMARY KEY (id)
);

CREATE TABLE tblC
(
id int NOT NULL AUTO_INCREMENT ,
nname varchar(255),
userids varchar(255),
PRIMARY KEY (id)
);

INSERT INTO tblA (user, category ) VALUES
('1', '1'),
('1', '2'),
('1', '3'),
('1', '1'),
('2', '1'),
('2', '1'),
('2', '1'),
('2', '1'),
('3', '1'),
('2', '1'),
('4', '1'),
('4', '1'),
('2', '1');


INSERT INTO tblB (userid, username ) VALUES
('1', 'A'),
('2', 'B'),
('3', 'C'),
('4', 'D'),
('5', 'E');


INSERT INTO tblC (id, nname,userids ) VALUES
('1', 'new1','1,2'),
('2', 'new2','1,3'),
('3', 'new3','1,4'),
('4', 'new4','3,2'),
('5', 'new5','5,2');

查询：

select * where nname="new1" from  tblC
CROSS JOIN tblB
ON tblB.userid=(SELECT userids FROM substr(tblC.userids,','))

限制：

这个查询依赖于SUBSTR和FIND_IN_SET函数，可以是对于大型数据集效率低下。此外，它假设每行只有一个逗号分隔值，但情况可能并非总是如此。

推荐解决方案：

数据库规范化：

通过为逗号分隔值创建单独的表来规范您的数据库架构。这消除了通过字符串搜索的低效率并简化了查询。

示例架构：

CREATE TABLE tblC (
id int NOT NULL AUTO_INCREMENT ,
nname varchar(255),
PRIMARY KEY (id)
);

CREATE TABLE tblC_user (
c_id int NOT NULL,
userid int NOT NULL
);

INSERT INTO tblC (id, nname) VALUES
('1', 'new1'),
('2', 'new2'),
('3', 'new3'),
('4', 'new4'),
('5', 'new5');

INSERT INTO tblC_user (c_id, userid) VALUES
('1','1'),
('1','2'),
('2','1'),
('2','3'),
('3','1'),
('3','4'),
('4','3'),
('4','2'),
('5','5'),
('5','2');

优化查询：

select *
from tblC c
join tblC_user cu on(c.id = cu.c_id)
join tblB b on (b.userid = cu.userid)
where c.nname="new1"

优点：

• 由于高效的连接操作而提高了性能
• 通过消除字符串操作降低了复杂性功能
• 可维护性和适应未来变化的灵活性数据结构

以上是如何优化具有逗号分隔值的表的 SQL 查询？的详细内容。更多信息请关注PHP中文网其他相关文章！