如何最佳化具有逗號分隔值的表的 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中文網其他相關文章！