MySQLで「ユーザとユーザグループ」のデータを扱いたいが、同じユーザで構成されるグループは作りたくない

Question

実現したいこと

「ユーザA, ユーザB = グループX」のように、複数ユーザを、以下の要件でグループ化したいです。

■要件１
構成要素は順不同でユニークにしたいです。

例えば
「ユーザA, ユーザB = グループY」や
「ユーザB, ユーザA = グループＺ」のように、
構成要素が重複した別グループは作りたくありません。

「ユーザA, ユーザB, ユーザC」なら別グループを作ってOKです。

■要件２
グループ内のユーザ数は最大1万とし、ユーザテーブルのIDカラムは INT(10) としたいです。

発生している問題・分からないこと

要件１をクリアする方法は思いつけます。以下のuser_idsカラムのように、カンマ区切りでユニーク制約をかける方法です。（そして INSERT 前は php 等で user_id をソートします。）

しかし要件２がわかりません。といいますのは、user_idsカラムにインデックスの最大値とされる3068バイトを持たせても、INT(10)のユーザが1万人は入りきらないためです。

該当のソースコード

CREATE TABLE UserGroup (
    group_id INT AUTO_INCREMENT PRIMARY KEY,

    -- "1,2,3,,," のように、User.user_id をカンマ区切りで持つ
    user_ids VARCHAR(3068) NOT NULL UNIQUE
);

CREATE TABLE User (
    user_id INT(10) AUTO_INCREMENT PRIMARY KEY,
    user_name VARCHAR(255) NOT NULL,
    group_id INT,
    FOREIGN KEY (group_id) REFERENCES UserGroup(group_id)
);

試したこと・調べたこと

• teratailやGoogle等で検索した
• ソースコードを自分なりに変更した
• 知人に聞いた
• その他

teratailやGoogle等で検索した

グループという名でネット検索すると、GROUP_CONCAT などの処理が出てしまい、今回の要件のような情報を見つけることはできませんでした。

そして、ChatGPT に聞いてもろくな返答はありませんでした。

ソースコードを自分なりに変更した１

User テーブルに新たにuser_uniqueカラムを置いてそこに数字だけでなく英数記号の値を持たせ、UserGroupテーブルはuser_idsでなくuser_uniquesとでもすれば、数字だけより多人数を管理できますが、やっぱりそれでも要件２を満たすには足りません。

ソースコードを自分なりに変更した２

すると残るは、UserGroup.user_idsという１つのカラムにまとめるのではなく、3068バイトずつ分割して別のテーブルに持たせ、それらのレコードのIDを繋げた値をUserGroup.user_id_pattern_idsとして持たせるとかになるのでしょうか？あまりに不細工というか、ちょっとヘンだと思います。

CREATE TABLE UserGroup (
    group_id INT AUTO_INCREMENT PRIMARY KEY,

    -- "1,2,3,,," のように、UserIdPatterns.user_id_pattern_id をカンマ区切りで持つ
    user_id_pattern_ids VARCHAR(3068) NOT NULL UNIQUE
);

CREATE TABLE UserIdPatterns (
    user_id_pattern_id INT AUTO_INCREMENT PRIMARY KEY,

    -- "1,2,3,,," のように、User.user_id をカンマ区切りで持つ
    user_ids VARCHAR(3068) NOT NULL UNIQUE
);

-- 以下は最初のコードと一緒
CREATE TABLE User (
    user_id INT(10) AUTO_INCREMENT PRIMARY KEY,
    user_name VARCHAR(255) NOT NULL,
    group_id INT,
    FOREIGN KEY (group_id) REFERENCES UserGroup(group_id)
);

もし良い方法があったら教えてください。

補足

実際にユーザが１万人いるサービスを運営しているわけではありませんが、実現方法として興味があっての質問です。

Comments

[sazi]

そういったアプローチだと車輪の再発明になってしまいます。
検索するにしてもキーワードを知らないと意味がありません。
> 実現方法として興味があっての質問です。
という事なら、DB設計に関する知識を先ず得る事だと思います。

[yambejp]

たとえば1000ユーザーで一部順番が違うだけのデータが違うものを別レコードとして管理するのあ運用上課題が大きいでしょうね。

[munekun]

yambejp様、コメントありがとうございます。カンマ区切りの方法へのご指摘ですよね？やっぱりそうですよね。そこもなんか微妙だと思っていました。

[munekun]

sazi様、コメントありがとうございます。この質問に対する一般的な実装がすでにあるとうことでしょうか？よければキーワードだけでも教えてはもらえませんか？

Answer 1

実装をどうするか、という以前に「構成要素が一致した別グループは作{ら,れ}ない」という仕様は実用にならないのでは？ と感じました。
どういう User母体でどういうGroupを作るのかと言うのがわからないので、ごく一般的なものを考えると
学生 と 同好会 という関係を考えてみます。
Aさんが 将棋同好会を立ち上げてメンバー募集した ときは メンバーは A さんだけ
も一つ、卓球同好会も立ち上げよう、としたときに メンバーはAさんだけだから完全一致で作れない。

将棋同好会に A、B 二人が参加した。卓球同好会に A、B、C 3人が参加した。
ここでCさんが卓球同好会から抜けることにした。が、そうなると A、B 二人で将棋同好会と完全一致になるので抜けられない

などなど 身動きできない事態が起きます。

Comments

[ikedas]

私もこれが何かの役に立つのかはわからないですね。普通はグループを主にして考えると思うので、「構成員が同じなら同じグループとみなして区別しない」という制約が必要とされる事情が知りたいですね。
ただ、グループがそれの含む要素の同一性だけで区別されるという構造は例がないわけでもないです。たとえば、数学での「集合の集合」みたいなものを実装しようとしているのかもしれません。

[munekun]

例えばまず「太郎と次郎という構成員のグループ」を花子が作ってグループを保存したとします。次に幸子も同じ構成員でグループを作って保存しようというときに、花子が作ったのと同じ構成員で別のレコードを作る必要はないですよね？

”卓球部”のようにグループに意味を持たせるのはグループの保存者側なんです。つまり花子がグループに”１年のイケメン”と名付けたり、幸子が同じグループに”私より上位成績者”と名付けたりはできます。

こんな事情なんですけど、どうでしょうか…

[winterboum]

一度作ったら構成員に変更がありえない のでしたらOKですね。
ただ
「花子がこのグループに”１年のイケメン”と名付けたり、幸子が同じグループに”私より上位成績者名付けたりはできます」
は
そうしたい という意味なら OK ですが、その様に実装できる、となると厄介ですね。groupに複数の名前をつけられる。それは 誰がアクセスするかでどの名前になるかきまる。 ってことですよね？

[winterboum]

「数学での「集合の集合」」 なるほど！

でも違うみたいですね

[munekun]

> 一度作ったら構成員に変更がありえない のでしたらOKですね。
いえ、変更はよくあると思います。でも（質問したユニーク性の維持以外に）何か問題がなりますか？ちょっとわからなかったです。

> それは 誰がアクセスするかでどの名前になるかきまる。 ってことですよね？
いえ、誰がアクセスするかでは決まりません。保存者が”１年のイケメン”みたいなグループ名を決めます。保存テーブル（グループと保存者のリレーションテーブル）に、グループ名カラムを持たせるだけです。

[winterboum]

質問したユニーク性の維持 に問題があるのです。
今の構成員だと 完全重複は無いが、あの人が 増える・減る と完全一致なグループになってしまう。
回答に書いた例でわかりません？

[ikedas]

そうですね。グループに名前をつけたのなら、そのグループの構成員を変更するのは問題があります。たとえば
・成績が下がっても「私より成績上位者」の構成員を変更できない
・「1年のイケメン」のグループが (自分は削除していないのに) 突然消えてしまった
といった、直感に反する現象が起きます (どういう場合にそうなるかについては、私の回答も見てください)。
そういったことは質問者さんが望んでいないことなのではないかと思います。

質問のようなことを実現したいと思った動機を、もうすこし説明いただきたいです。
(あくまでも例ですが、グループの情報を保存するスペースの節約のために「同じ構成員のグループは一つしか存在しない」という条件をつけたい、などといった動機も考えられるでしょう。)

Answer 2

データ投入時は制限をかけずに追加して、投入完了後ダブったデータがあったときに新しい方のデータを削除するというのが妥協点かなと

参考

create table user(uid int primary key ,name varchar(10));
insert into user values(1,'a'),(2,'b'),(3,'c'),(4,'d'); /* ただしuserテーブルは今回は使わない */

create table users(gid int not null,uid int not null,sort int not null,unique key (gid,uid),unique key (gid,sort));
insert into users values
(1,1,1),
(1,2,2),
(2,2,1),
(2,1,2),
(3,1,1),
(3,2,2),
(4,1,1),
(4,2,2),
(4,3,3),
(5,1,1),
(5,2,2);

というデータを作ったとします。結果こういうデータ

gid	uid	sort
1	1	1
1	2	2
2	2	1
2	1	2
3	1	1
3	2	2
4	1	1
4	2	2
4	3	3
5	1	1
5	2	2

上記1と3と5がダブっていることがわかります。
したがってあとから追加した3と5を探す必要があります

select gid,group_concat(uid order by sort) as uids ,rank() over(partition by group_concat(uid order by sort)  order by gid ) as r from users group by gid

gid	uids	r
1	1,2	1
3	1,2	2
5	1,2	3
4	1,2,3	1
2	2,1	1

上記からこうすると削除されます

delete from users where gid in(
select gid from(
select gid,group_concat(uid order by sort) as uids ,rank() over(partition by group_concat(uid order by sort)  order by gid ) as r from users group by gid
) as sub where r>1
);

Comments

[munekun]

すみません、わからなかったです。
・制限をかけずに追加というのは、どういうテーブルに対してですか？
・そして削除するというのはどういうテーブルに対してですか？
・ダブったデータの判定は、INSERT後に同じトランザクション内で、phpなどでSELECT文をもう一度発行するということですか？

[yambejp]

参考でデータを例示しておきました
重複がある前提でデータ投入してあとから重複を削除する・・・という意味はわかりましたか？

[munekun]

なんじゃこりゃすごすぎます…使っている関数もうまいし、一発クエリですし、発想がやばすぎます
既存確認やロック処理など大変そうだと気が引けていたのですが、最後にまとめて削除するというその発想ならかなり楽ができそうですね
めっちゃ勉強になりました！ありがとうございます

[yambejp]

＞既存確認やロック処理など大変そうだ

そうですね。あとは整合性のために
begin;
select * from users for update;
insert ・・・;
delete ・・・;
commit;

みたいな構造にすればいけるような気がします

[munekun]

SELECT FOR UPDATE というのも知りませんでした！これまた便利なのをありがとうございます
GET LOCK で対処するつもりでしたので助かりました…

Answer 3

本回答は質問で求められた課題を解決しません。

---

ご質問で試された方法というのは、かなりの人が一度は考えたことのあるアンチパターンだと思います。

一般にSQLデータベースはサイズの大きいデータをあまり効率的に扱えません。ですから、ひとつの列を多数の重複しない要素の集合を格納するのに使うことはSQLデータベースではするべきではありません (「集合型」のようなデータ型を持つSQLデータベースもありますが、要素数が数十個以下の集合しか扱えません。余談ですが、SQLデータベースと異なる種類のデータベースシステムの中には、多数の要素の集合を扱えるようなデータ型を持つものもあります)。

SQLデータベースでも、個々のユーザと個々のグループとの関係を表すテーブルを作ることはできます。

CREATE TABLE group_user (
    group_id INT,
    user_id INT,
    PRIMARY KEY (group_id, user_id)
);

これで「あるユーザがあるグループに属している」という情報が一意な行としてテーブルに格納できます。テーブルの制約として実現できるのはここまでだと思います。

所属するユーザが完全に同一のグループが複数あってはならない (グループの構成員を一意にしたい) となるとデータベースの更新処理が単純なものではなくなります。少し考えてみただけでも次のようなケースがありえます。

1. あるユーザをあるグループに追加しようとした場合
   そのユーザが追加された結果と同一の構成員を持つグループがすでに存在しているかどうか検査し、
   (a) あれば追加をしない。
   (b) なければ追加する。
2. 1人以上のユーザを含むグループを新たに追加 (グループ結成) しようとした場合
   同一の構成員を持つグループがすでに存在しているかどうか検査し、
   (a) あれば追加をしない。
   (b) なければ追加する。
3. あるユーザをあるグループから削除しようとした場合
   (a) そのユーザが削除された結果グループが消滅する (構成員がいなくなる) のであれば、削除する。
   そうでなければ、そのユーザが削除された結果と同一の構成員を持つグループがすでに存在しているかどうか検査し、
   (b) あれば、削除して、なんらかの基準でどちらかのグループだけに対し5.と同じ処理をして消滅させる。
   (c) なければ、単に削除する。
4. あるユーザ自身を削除しようとした (ユーザ退会) 場合
   そのユーザが属しているグループ全てに対し、各々3. と同じ処理をする (※各々のグループを処理する順番によって結果が異なり得ることに注意)。
5. あるグループ自身を削除しようとした (グループ解散) 場合
   そのグループのレコードすべてをテーブルから削除する。
6. その他のケース
   ユーザのグループ間の移動は 新グループへ追加 (1.) + 旧グループから削除 (3.) で一応は実現できるでしょう。

上記の各々の処理の中で行なっている「特定の複数のユーザが属しているグループがすでに存在しているかどうか」の検査は、SQLで実行できるでしょう。
しかし、その検査の結果に基づいて行う処理については、データベースの構成だけでどうにかなるものではないと思います。

Comments

[munekun]

丁寧にありがとうございます。テーブルの制約としてはできないのですね…。
ではどういう処理が必要かという分類、とても助かりました。それらに照らしてモレのないよう実装頑張ります。

[munekun]

こちらのコメント欄で失礼いたします。

➀
> 成績が下がっても「私より成績上位者」の構成員を変更できない
大丈夫です。変更できます。構成員を変更したら新しいグループになり、保存グループはその新しいグループに自動で切り替わります。
例えば花子が「私より成績上位者」として「太郎と次郎」（グループID=1）を保存していたが、次郎の成績が落ちて三郎と入れ替わった場合、花子がその構成員を「太郎と三郎」に変えるとグループID=2として作成され、花子の保存グループはこのグループID=2になる感じです。

➁
>「1年のイケメン」のグループが (自分は削除していないのに) 突然消えてしまった
これも大丈夫です。起こりません。グループとしての保存者が0人なって初めてグループは削除されます。
例えば花子の成績がトップになり「私より成績上位者」がいなくなって、花子が保存グループから削除しました。しかしグループ自体は削除されません。あくまでグループと花子のリレーションが削除されるだけです。
そのため幸子の「1年のイケメン」は維持されます。やがて幸子も「よく見たらイケメンじゃない」と思ってグループを削除しました。この時点で「太郎と次郎」のグループの保存者が0人になり、グループが削除されます。

➂
> あくまでも例ですが、グループの情報を保存するスペースの節約のために「同じ構成員のグループは一つしか存在しない」という条件をつけたい、などといった動機も考えられるでしょう。
はい、さすがのご推察で、仰る通りの動機があります。
あともう一つ、グループのランキングをとるという事情もあります。例えば花子と幸子が各々の事情で「太郎と次郎という構成員のグループ」を保存した際に、「太郎と次郎のグループは２人に保存されている」のような感じで、人気の構成員を集計をしたいという動機です。

ながながとすみません。気が向いたら読んでみてください。（そしてもし問題があればいつでもご指摘ください。）

[ikedas]

①②③は質問文を編集して要件の明確化として書かれるべき内容だと思います。

また、下記の理由から私の回答は質問者さんの考えるような挙動を実現しませんので、ベストアンサーを一旦解除してください。
・「グループとユーザのリレーション」について、現在の私の回答では一切考慮していません。
・私の回答では①、②は起きます。

可能なら回答を書き直したいですが、それまでに他の方から回答があればそちらを参照いただいても結構です。現時点では質問は解決に至っていないと考えます。

[munekun]

取り急ぎ、おっしゃる通りベストアンサーを外させていただきました
大変ためになる分類で参考になりましたので改めて御礼申し上げます

Answer 4

データベースではなくアプリケーションレベルで制限すればいい。

UserGroupにuser_idsではなく
UserとGroupの多対多のリレーションで作る。UserGroupは中間テーブル。

一般的な設計パターンは決まってるので今作ろうとしてるものがどのパターンか判断できれば作り方も自動で決まる。

Comments

[munekun]

こうですか？
```
CREATE TABLE UserGroup (
user_id INT(10),
group_id INT(10),
UNIQUE (user_id, group_id),
FOREIGN KEY (user_id) REFERENCES User(user_id),
FOREIGN KEY (group_id) REFERENCES Group(group_id)
);

CREATE TABLE User (
user_id INT(10) AUTO_INCREMENT PRIMARY KEY
);

CREATE TABLE Group (
group_id INT(10) AUTO_INCREMENT PRIMARY KEY
);
```

でもこうできてしまいますよね？
```
INSERT INTO User (user_id)
VALUES (1), (2);

INSERT INTO Group (group_id)
VALUES (101), (102);

INSERT INTO UserGroup (user_id, group_id)
VALUES (1, 101), (2, 101), (1, 102), (2, 102);
```