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

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

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

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

SQL

SQL(Structured Query Language)は、リレーショナルデータベース管理システム (RDBMS)のデータベース言語です。大きく分けて、データ定義言語(DDL)、データ操作言語(DML)、データ制御言語(DCL)の3つで構成されており、プログラム上でSQL文を生成して、RDBMSに命令を出し、RDBに必要なデータを格納できます。また、格納したデータを引き出すことも可能です。

Q&A

解決済

2回答

2069閲覧

sqlのテーブル構成&json型を外部キーにしてjoinする方法

ryosuke24

総合スコア2

MySQL

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

SQL

SQL(Structured Query Language)は、リレーショナルデータベース管理システム (RDBMS)のデータベース言語です。大きく分けて、データ定義言語(DDL)、データ操作言語(DML)、データ制御言語(DCL)の3つで構成されており、プログラム上でSQL文を生成して、RDBMSに命令を出し、RDBに必要なデータを格納できます。また、格納したデータを引き出すことも可能です。

0グッド

0クリップ

投稿2022/04/19 02:54

編集2022/04/19 14:45

2点自己学習のMySQLに関し質問があります。

質問1

userが複数のitemを所有している状態を表したい時、写真のcase1とcase2どちらが推奨されるテーブル構成でしょうか?
【補足】
Case1の場合、usersテーブルにて 複数のitem_idをjson形式で格納しようと思っています。Case2の場合、user_itemテーブルを中間テーブルとし、他対多の関係を築いています。

※case1に関し、中間テーブルを挟む事でデータ量が多くなるのかなと思っています。case2に関し、json型で格納することを推奨しない旨の記事をいくつか見受けられました。
イメージ説明

質問2

case1を実装する際に、json配列を外部キーにしてjoinする方法を生sqlで記述する方法で躓いています。
【やりたいこと】
userテーブルのitem_id(json_array)を外部キーとして、itemテーブルとjoinしたい。
イメージ説明

イメージ説明
【上記のjoinをしたい理由】
最終的にやりたい事は、例えば「user_id=1のユーザーが持っているitem_nameの一覧を表示させる」といった事なので、下の画像のようなテーブルを構成する必要があると考えている為です。
イメージ説明

発生している問題・エラーメッセージ

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''$'' at line 6

該当のソースコード

mysql

1select 2 user.id as user_id, 3 item.name as item_name 4from 5 user 6 LEFT JOIN item on JSON_UNQUOTE(JSON_EXTRACT(user.item_id, '$')) = item.id '$'; 7 -- ↓非jsonの場合のjoin 8 -- left join item on user.item_id = item.id,

試したこと

jsonを扱う関数(json_contains等)を使用して外部キーに設定してみた
【参考】
https://stackoverflow.com/questions/39818296/using-mysql-json-field-to-join-on-a-table

補足情報(FW/ツールのバージョンなど)

mysql8.0

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

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

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

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

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

yambejp

2022/04/19 03:27 編集

質問用のもっと簡易化した状態で、どういうJSON型のデータからどういう結合がしたいのか例示ください そもそもJSON型が効率的な検索には向かないという前提にたってテーブルは構築してください
ryosuke24

2022/04/19 04:13

ご返信ありがとうございます。 ご依頼いただいた修正点を加筆致しました。 >そもそもJSON型が効率的な検索には向かないという前提 また、こちらに関し承知致しました。ご指摘ありがとうございます。
guest

回答2

0

SQL

1create table user ( 2id int primary key , 3item_id json); 4insert into user values(1,'[1,2]'); 5 6create table item ( 7id int primary key , 8name varchar(10)); 9insert into item values(1,'ノート'),(2,'鉛筆'),(3,'消しゴム');

//出力

SQL

1select t1.id,concat('[',group_concat('"',t2.name,'"'),']') from user as t1 2inner join item as t2 3on json_contains(t1.item_id,t2.id) 4group by id;

jsonにこだわらないバージョン

sql

1create table user ( 2id int primary key , 3item_id varchar(20)); 4insert into user values(1,'1,2'); 5 6create table item ( 7id int primary key , 8name varchar(10)); 9insert into item values(1,'ノート'),(2,'鉛筆'),(3,'消しゴム'); 10 11select t1.id,group_concat(t2.name) from user as t1 12inner join item as t2 13on find_in_set(t2.id,t1.item_id) 14group by id;

投稿2022/04/19 05:00

編集2022/04/19 05:45
yambejp

総合スコア114572

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

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

ryosuke24

2022/04/19 05:15

ご回答ありがとうございます。 こちらの出力方法が分からずモヤモヤしていたので非常に助かりました。 json型は使用するべきで無いことが分かったので実装自体は中間テーブルを設ける形にしますが、ご回答頂いた方法はjson型の理解の参考にさせて頂きます。
yambejp

2022/04/19 05:44

jsonにこだわらないならもっと簡単な書き方もできます(追記参照) とはいえ中間テーブルをつくって処理するのが効率的なのでそちらを採用ください
guest

0

ベストアンサー

SQLで多対多を関連テーブルなしに表現したいから、JSON型を使うというのは、邪道中の邪道です。配列(繰り返し項目)は、関係モデルの正規化で一番最初に排除する必要があるものです。ですから、関係モデルとして相応しいのは、CASE2です。
関係モデルは、データすべてを2次元の表で表すという、非常に表現力の乏しいモデルです。が、それと引き換えに、プログラミング言語中立なデータモデルとして多くの支持を集めています。関係モデルとしてのあるべき姿を受け入れないのであれば、初めからRDB以外のDBを選択するべきです。

投稿2022/04/19 04:11

nukasa

総合スコア406

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

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

ryosuke24

2022/04/19 04:19

ご回答ありがとうございます。 CASE2の方はデータ量が増えちゃうのでは・・・と思っていたのですが、こちらの方がやはり王道なんですね。 >配列(繰り返し項目)は、関係モデルの正規化で一番最初に排除する必要があるものです。 こちらの理由としましては、json型にする事でテーブルの操作がややこしくなるといった理由のためでしょうか? もしくは、そもそもRDBにおいてjson型の使用を想定していないといった事ですかね。
nukasa

2022/04/19 04:37

第一正規形で繰り返し項目は排除されます。なぜ、正規化をするべきなのかというと、関係モデルで、データの整合性を保ちやすく、SQLの代数演算でアクセスしやすいデータの形のベストプラクティスが、正規化されたデータモデルだからです。 JSONをJSONのまま格納するだけで、構造を意識した検索や他のJSONとの結合はしないというのなら、RDBMSにとってはただのCLOBですから構いません。が、SQLでJSONの構造を意識した演算をおこなうことは少なくとも標準SQLではできません。
ryosuke24

2022/04/19 05:12

なるほど、構造を意識といった観点でjson型は使用するべきで無いとのこと理解出来ました。 詳細にご説明頂きありがとうございました。
guest

あなたの回答

tips

太字

斜体

打ち消し線

見出し

引用テキストの挿入

コードの挿入

リンクの挿入

リストの挿入

番号リストの挿入

表の挿入

水平線の挿入

プレビュー

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

ただいまの回答率
85.50%

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

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

質問する

関連した質問