カンマ区切り値を含むテーブルの 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 関数に依存しています。これは大規模なデータセットでは非効率となる可能性があります。さらに、行ごとにカンマ区切りの値が 1 つだけであると想定されますが、常にそうであるとは限りません。

推奨される解決策:

データベースの正規化:

カンマ区切りの別のテーブルを作成してデータベース スキーマを正規化します。価値観。これにより、文字列による検索の非効率性がなくなり、クエリが簡素化されます。

スキーマの例:

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 中国語 Web サイトの他の関連記事を参照してください。