質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

新規登録して質問してみよう
ただいま回答率
85.42%
MySQL

MySQL(マイエスキューエル)は、TCX DataKonsultAB社などが開発するRDBMS(リレーショナルデータベースの管理システム)です。世界で最も人気の高いシステムで、オープンソースで開発されています。MySQLデータベースサーバは、高速性と信頼性があり、Linux、UNIX、Windowsなどの複数のプラットフォームで動作することができます。

Q&A

解決済

4回答

680閲覧

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

munekun

総合スコア47

MySQL

MySQL(マイエスキューエル)は、TCX DataKonsultAB社などが開発するRDBMS(リレーショナルデータベースの管理システム)です。世界で最も人気の高いシステムで、オープンソースで開発されています。MySQLデータベースサーバは、高速性と信頼性があり、Linux、UNIX、Windowsなどの複数のプラットフォームで動作することができます。

0グッド

0クリップ

投稿2024/03/06 16:28

編集2024/03/06 16:33

実現したいこと

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

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

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

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

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

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

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

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

該当のソースコード

SQL

1CREATE TABLE UserGroup ( 2 group_id INT AUTO_INCREMENT PRIMARY KEY, 3 4 -- "1,2,3,,," のように、User.user_id をカンマ区切りで持つ 5 user_ids VARCHAR(3068) NOT NULL UNIQUE 6); 7 8CREATE TABLE User ( 9 user_id INT(10) AUTO_INCREMENT PRIMARY KEY, 10 user_name VARCHAR(255) NOT NULL, 11 group_id INT, 12 FOREIGN KEY (group_id) REFERENCES UserGroup(group_id) 13);

試したこと・調べたこと

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

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

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

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

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

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

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

SQL

1CREATE TABLE UserGroup ( 2 group_id INT AUTO_INCREMENT PRIMARY KEY, 3 4 -- "1,2,3,,," のように、UserIdPatterns.user_id_pattern_id をカンマ区切りで持つ 5 user_id_pattern_ids VARCHAR(3068) NOT NULL UNIQUE 6); 7 8CREATE TABLE UserIdPatterns ( 9 user_id_pattern_id INT AUTO_INCREMENT PRIMARY KEY, 10 11 -- "1,2,3,,," のように、User.user_id をカンマ区切りで持つ 12 user_ids VARCHAR(3068) NOT NULL UNIQUE 13); 14 15-- 以下は最初のコードと一緒 16CREATE TABLE User ( 17 user_id INT(10) AUTO_INCREMENT PRIMARY KEY, 18 user_name VARCHAR(255) NOT NULL, 19 group_id INT, 20 FOREIGN KEY (group_id) REFERENCES UserGroup(group_id) 21);

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

補足

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

気になる質問をクリップする

クリップした質問は、後からいつでもMYページで確認できます。

またクリップした質問に回答があった際、通知やメールを受け取ることができます。

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

sazi

2024/03/07 03:46 編集

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

2024/03/07 04:06

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

2024/03/07 09:30

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

2024/03/07 09:33

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

回答4

0

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

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

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

投稿2024/03/07 11:21

winterboum

総合スコア23464

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

ikedas

2024/03/07 12:13 編集

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

2024/03/07 14:06 編集

例えばまず「太郎と次郎という構成員のグループ」を花子が作ってグループを保存したとします。次に幸子も同じ構成員でグループを作って保存しようというときに、花子が作ったのと同じ構成員で別のレコードを作る必要はないですよね? ”卓球部”のようにグループに意味を持たせるのはグループの保存者側なんです。つまり花子がグループに”1年のイケメン”と名付けたり、幸子が同じグループに”私より上位成績者”と名付けたりはできます。 こんな事情なんですけど、どうでしょうか…
winterboum

2024/03/07 13:38

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

2024/03/07 13:40

「数学での「集合の集合」」 なるほど! でも違うみたいですね
munekun

2024/03/07 14:03

> 一度作ったら構成員に変更がありえない のでしたらOKですね。 いえ、変更はよくあると思います。でも(質問したユニーク性の維持以外に)何か問題がなりますか?ちょっとわからなかったです。 > それは 誰がアクセスするかでどの名前になるかきまる。 ってことですよね? いえ、誰がアクセスするかでは決まりません。保存者が”1年のイケメン”みたいなグループ名を決めます。保存テーブル(グループと保存者のリレーションテーブル)に、グループ名カラムを持たせるだけです。
winterboum

2024/03/07 23:29

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

2024/03/08 04:11 編集

そうですね。グループに名前をつけたのなら、そのグループの構成員を変更するのは問題があります。たとえば ・成績が下がっても「私より成績上位者」の構成員を変更できない ・「1年のイケメン」のグループが (自分は削除していないのに) 突然消えてしまった といった、直感に反する現象が起きます (どういう場合にそうなるかについては、私の回答も見てください)。 そういったことは質問者さんが望んでいないことなのではないかと思います。 質問のようなことを実現したいと思った動機を、もうすこし説明いただきたいです。 (あくまでも例ですが、グループの情報を保存するスペースの節約のために「同じ構成員のグループは一つしか存在しない」という条件をつけたい、などといった動機も考えられるでしょう。)
guest

0

ベストアンサー

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

参考

SQL

1create table user(uid int primary key ,name varchar(10)); 2insert into user values(1,'a'),(2,'b'),(3,'c'),(4,'d'); /* ただしuserテーブルは今回は使わない */ 3 4create table users(gid int not null,uid int not null,sort int not null,unique key (gid,uid),unique key (gid,sort)); 5insert into users values 6(1,1,1), 7(1,2,2), 8(2,2,1), 9(2,1,2), 10(3,1,1), 11(3,2,2), 12(4,1,1), 13(4,2,2), 14(4,3,3), 15(5,1,1), 16(5,2,2);

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

giduidsort
111
122
221
212
311
322
411
422
433
511
522

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

SQL

1select 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
giduidsr
11,21
31,22
51,23
41,2,31
22,11

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

SQL

1delete from users where gid in( 2select gid from( 3select 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 4) as sub where r>1 5);

投稿2024/03/07 04:08

編集2024/03/07 10:20
yambejp

総合スコア115505

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

munekun

2024/03/07 09:35

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

2024/03/07 10:26 編集

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

2024/03/07 10:40

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

2024/03/07 10:56

>既存確認やロック処理など大変そうだ そうですね。あとは整合性のために begin; select * from users for update; insert ・・・; delete ・・・; commit; みたいな構造にすればいけるような気がします
munekun

2024/03/07 12:38

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

0

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


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

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

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

sql

1CREATE TABLE group_user ( 2 group_id INT, 3 user_id INT, 4 PRIMARY KEY (group_id, user_id) 5);

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

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

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

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

投稿2024/03/07 09:38

編集2024/03/10 03:10
ikedas

総合スコア4434

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

munekun

2024/03/07 10:12

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

2024/03/08 09:36

こちらのコメント欄で失礼いたします。 ➀ > 成績が下がっても「私より成績上位者」の構成員を変更できない 大丈夫です。変更できます。構成員を変更したら新しいグループになり、保存グループはその新しいグループに自動で切り替わります。 例えば花子が「私より成績上位者」として「太郎と次郎」(グループID=1)を保存していたが、次郎の成績が落ちて三郎と入れ替わった場合、花子がその構成員を「太郎と三郎」に変えるとグループID=2として作成され、花子の保存グループはこのグループID=2になる感じです。 ➁ >「1年のイケメン」のグループが (自分は削除していないのに) 突然消えてしまった これも大丈夫です。起こりません。グループとしての保存者が0人なって初めてグループは削除されます。 例えば花子の成績がトップになり「私より成績上位者」がいなくなって、花子が保存グループから削除しました。しかしグループ自体は削除されません。あくまでグループと花子のリレーションが削除されるだけです。 そのため幸子の「1年のイケメン」は維持されます。やがて幸子も「よく見たらイケメンじゃない」と思ってグループを削除しました。この時点で「太郎と次郎」のグループの保存者が0人になり、グループが削除されます。 ➂ > あくまでも例ですが、グループの情報を保存するスペースの節約のために「同じ構成員のグループは一つしか存在しない」という条件をつけたい、などといった動機も考えられるでしょう。 はい、さすがのご推察で、仰る通りの動機があります。 あともう一つ、グループのランキングをとるという事情もあります。例えば花子と幸子が各々の事情で「太郎と次郎という構成員のグループ」を保存した際に、「太郎と次郎のグループは2人に保存されている」のような感じで、人気の構成員を集計をしたいという動機です。 ながながとすみません。気が向いたら読んでみてください。(そしてもし問題があればいつでもご指摘ください。)
ikedas

2024/03/08 09:58

①②③は質問文を編集して要件の明確化として書かれるべき内容だと思います。 また、下記の理由から私の回答は質問者さんの考えるような挙動を実現しませんので、ベストアンサーを一旦解除してください。 ・「グループとユーザのリレーション」について、現在の私の回答では一切考慮していません。 ・私の回答では①、②は起きます。 可能なら回答を書き直したいですが、それまでに他の方から回答があればそちらを参照いただいても結構です。現時点では質問は解決に至っていないと考えます。
munekun

2024/03/09 16:46

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

0

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

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

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

投稿2024/03/07 01:21

pcs

総合スコア540

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

munekun

2024/03/07 09:26

こうですか? ``` 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); ```
guest

あなたの回答

tips

太字

斜体

打ち消し線

見出し

引用テキストの挿入

コードの挿入

リンクの挿入

リストの挿入

番号リストの挿入

表の挿入

水平線の挿入

プレビュー

15分調べてもわからないことは
teratailで質問しよう!

ただいまの回答率
85.42%

質問をまとめることで
思考を整理して素早く解決

テンプレート機能で
簡単に質問をまとめる

質問する

関連した質問