「DB」の版間の差分

提供：senooken JP Wiki
 
(同じ利用者による、間の75版が非表示)
1行目: 1行目:
== Other ==
== Other ==


==== 画像データのパス/BLOBでの格納 ====
=== Performance ===
 
==== SQL vs. app ====
[https://gihyo.jp/dev/serial/01/game_mysql/0005 第5回　DB側でやること、アプリ側でやることを見極めよう | gihyo.jp]
 
DB側ですべき処理とアプリ側ですべき処理の見極め。


* [https://stackoverflow.com/questions/1347461/saving-images-files-or-blobs database - Saving images: files or blobs? - Stack Overflow]
ネットワーク上でデータの受け渡しがあるので、ソート以外はSQLですべてしたほうがいい。
* [https://stackoverflow.com/questions/3748/storing-images-in-db-yea-or-nay database - Storing Images in DB - Yea or Nay? - Stack Overflow]
* [https://www.microsoft.com/en-us/research/publication/to-blob-or-not-to-blob-large-object-storage-in-a-database-or-a-filesystem/ To BLOB or Not To BLOB: Large Object Storage in a Database or a Filesystem - Microsoft Research]
* [https://teratail.com/questions/296506 データベースに画像を保存するメリット・デメリットと必要性について]
* [https://teratail.com/questions/81233 データベースに画像を保存するのはありでしょうか？]


画像のファイルサイズが1MB以下の場合は問題ない。が、それ以外だと問題があるので、ファイルパスで扱うのがいいらしい。
コンピューターが処理することに変わりはなく、


=== カラム順序 ===
==== カラム順序 ====
テーブルのカラム・列の順序。これに意味があるのか？カラムを追加する場合などに影響がある。
テーブルのカラム・列の順序。これに意味があるのか？カラムを追加する場合などに影響がある。


26行目: 27行目:


ディスクスペースの利用率、使用するデータの効率などに影響がある模様。
ディスクスペースの利用率、使用するデータの効率などに影響がある模様。
=== マスター ===
[https://jsol-datamanagement.com/solution/solution-575/ マスタデータって何？マスタデータについて詳しく解説！ - J-MDM／Snowflake]
ユーザー、顧客や拠点の情報など、ベースとなるデータのこと。
マスターの他に、トランザクションというのもある。これは、売買などシステムや業務の稼働に伴って生じるデータ。
[https://www.praha-inc.com/lab/posts/database-normalization 【データベース正規化完全ガイド】実務観点での正規化手順やポイントを詳しく解説   | PrAhaENGINEERLAB]
トランザクションテーブルで、マスターテーブルを直接参照するのはまずい。
マスターテーブルを更新したら、トランザクションの情報も変わってしまうから。
登録時にマスターテーブルのデータをコピーしていれる。
=== Sharding ===
[https://e-words.jp/w/%E3%82%B7%E3%83%A3%E3%83%BC%E3%83%87%E3%82%A3%E3%83%B3%E3%82%B0.html シャーディングとは - 意味をわかりやすく - IT用語辞典 e-Words]
シャーディング。DBの負荷分散の手法。
データをレコード・行単位で水平分割して、複数のデータベースサーバーに分散して記録する。
分散したDBの単位をシャードと呼んでいる。データが増大しても台数を増やせば問題ない。
ただし、どのデータがどのサーバーで記録しているかを把握する必要がある。ハッシュ値などから計算する。


=== NULL ===
=== 親子関係 ===
Ref: [https://qiita.com/daichi_yamazaki/items/ea3516080948e95bbf26 NOT NULL 制約をつけるべきカラムとは #PostgreSQL - Qiita].
親子関係、ツリー構造、階層構造を表現したいことがある。
 
[https://teratail.com/questions/281348 １つのテーブルで親子関係を管理する方法]


絶対に必要な項目にNOT NULL制約をつける。NULLを許容する列は、最後のほうに集めると、ストレージなどの効率がいい。
基本的に、親コードのカラムをどこかのテーブルに持たせて対応させる。


例えば、取得に失敗した場合に、値をどうするのか？などを考えるとよい。
* 隣接リスト (Adjacency List): 親子関係が再帰的になっている場合、途中データの削除はやっかいになる。親子の取得が少々複雑で何回もSQLが必要になり厄介。階層構造が2-3階程度なら問題ない。
* 経路列挙:


=== int/string ===
=== Standard ===
[[wikipedia:ISO/IEC_9075|ISO/IEC 9075 - Wikipedia]]


* [https://stackoverflow.com/questions/747802/integer-vs-string-in-database language agnostic - Integer vs String in database - Stack Overflow]
ISO/IEC 9075がSQLの標準。標準外のSQL/関数は独自実装なので扱いには注意が必要。標準のみの使用が望ましい。
* [https://teratail.com/questions/259465 データベースの値の持ち方は文字列にするか数字にするか迷っています]


データベースのカラムでIDやコード類を定義する場合、型をintなどの数値系、charなどの文字列系にするかの議論がある。
ANSI SQLと呼んだりするらしい。


* WHEREの範囲、算術計算など数値計算が伴うなら数値。データを何に使うかの意味論が重要。
* [[wikipedia:List_of_SQL_reserved_words|List of SQL reserved words - Wikipedia]]: 標準SQLの予約語一覧
* 数値のほうが高速。
* [[wikipedia:SQL_syntax|SQL syntax - Wikipedia]]: 標準SQLの構文。
* 郵便番号のように先頭が0つく場合、文字列。
* 数値だと、auto incrementで登録時の重複除去を保証できる。
* 値を変更する可能性がないなら数値。
* 文字列型だと文字数の検討が必要。


=== INT/BIGINT ===
=== 制約 ===


* [https://qiita.com/tk20211228/items/fbed24e7c97bf10112a0 MySQLにおけるint型IDの限界とクラッシュ回避のための対策 #MySQL - Qiita]
* [https://qiita.com/MuscleProgramer/items/b34c6d83037bae3b5070 ユニークキーはNULL許容します（圧） #MySQL - Qiita]
* [https://gihyo.jp/article/2022/08/mysql-rcn0178 第178回　MySQLのAUTO_INCREMENTなINTEGERってどのくらいで使い切るの？ | gihyo.jp]
* [https://qiita.com/s_ryota/items/d04cbc633f1b1f4958f9 データベースにおける制約の種類 #DB - Qiita]


int型は4バイトでsignedなら2,147,483,647 (21億) が最大値。1日1万件増加するとして588年。10万件で58年。増加追加量次第だが、中小規模なら通常はint型で問題ない。
3のキー制約がある。


BIGINTなら、1日100万件で25269512429年。BIGINTならほぼ問題ない。
# NOT NULL。
# UNIQUE制約: ユニークキー NULL許容
# PRIMARY KEY制約: プライマリーキー非NULL。UK AND NOT NULL相当。
# FOREIGN KEY制約: 外部キー非NULL。
# CHECK制約


ただ、BIGINTはINTの2倍のサイズになるのでストレージも消費する。よく考えておく。
=== Error ===


BIGINTでも足りない場合、プライマリーキーを文字列にして、UUIDやhash値を使う。
==== SQLSTATE[01000]: Warning: 1265 Data truncated for column ====
ALTER TABLEでデータ型を変更して、文字数を切り詰めるときに、元データがあって、切り詰められないときに出るエラー。


=== マスター ===
=== Transfer ===
[https://jsol-datamanagement.com/solution/solution-575/ マスタデータって何？マスタデータについて詳しく解説！ - J-MDM／Snowflake]


ユーザー、顧客や拠点の情報など、ベースとなるデータのこと。
==== import ====
主キーが文字列型のテーブルに、外部からインポートする場合、主キーの生成方法が問題になる。


マスターの他に、トランザクションというのもある。これは、売買などシステムや業務の稼働に伴って生じるデータ。
データを削除して取り込む想定なら、自分で連番を作ればいい。SQLで生成してもいい。


[https://www.praha-inc.com/lab/posts/database-normalization 【データベース正規化完全ガイド】実務観点での正規化手順やポイントを詳しく解説   | PrAhaENGINEERLAB]
COUNT/ROW/ROWS/ROWNUM/ROW_NUMBERなど。


トランザクションテーブルで、マスターテーブルを直接参照するのはまずい。
ROW_NUMBERが使えないなら変数で行えるらしい ([https://qiita.com/sand_bash/items/851f08a36381a4f01b75 MySQLのSELECT結果に行番号を振る。 #mariadb - Qiita])。


マスターテーブルを更新したら、トランザクションの情報も変わってしまうから。
== Design ==


登録時にマスターテーブルのデータをコピーしていれる。
=== ID ===


=== DB ID ===
==== DB ID ====
DBのIDの採番規則。非常に重要。全体のパフォーマンスにも影響する。
DBのIDの採番規則。非常に重要。全体のパフォーマンスにも影響する。


[https://zenn.dev/praha/articles/3c84e3818891c3 idをautoincrementして何が悪いの？]
* [https://zenn.dev/j5ik2o/articles/a085ab3e3d0f197f6559 ID生成方法についてあれこれ]
* [https://zenn.dev/praha/articles/3c84e3818891c3 idをautoincrementして何が悪いの？]
 
Instagramの方式が、後発なだけあって優れているように感じる。


[https://instagram-engineering.com/sharding-ids-at-instagram-1cf5a71e5a5c Sharding & IDs at Instagram. With more than 25 photos and 90 likes… | by Instagram Engineering | Instagram Engineering]
[https://instagram-engineering.com/sharding-ids-at-instagram-1cf5a71e5a5c Sharding & IDs at Instagram. With more than 25 photos and 90 likes… | by Instagram Engineering | Instagram Engineering]
93行目: 126行目:
この3要素で構成して算出している。これでバッティングせずに時間ソートで64ビットで収まる。よく考えられている。
この3要素で構成して算出している。これでバッティングせずに時間ソートで64ビットで収まる。よく考えられている。


=== Sharding ===
==== 議論 ====
[https://e-words.jp/w/%E3%82%B7%E3%83%A3%E3%83%BC%E3%83%87%E3%82%A3%E3%83%B3%E3%82%B0.html シャーディングとは - 意味をわかりやすく - IT用語辞典 e-Words]


シャーディング。DBの負荷分散の手法。
* [https://nippondanji.blogspot.com/2013/12/id.html 漢(オトコ)のコンピュータ道: IDの設計についてのさらに突っ込んだ議論]


データをレコード・行単位で水平分割して、複数のデータベースサーバーに分散して記録する。
==== IDとコード ====
* [https://atmarkit.itmedia.co.jp/bbs/phpBB/viewtopic.php?topic=30818&forum=26 「列名の付け方（～CD or ～ID ？）」（1） Database Expert － ＠IT]
* [https://irof.hateblo.jp/entry/2021/10/26/181920 コード、区分、フラグ、IDと名前。 - 日々常々]


分散したDBの単位をシャードと呼んでいる。データが増大しても台数を増やせば問題ない。
コードは広い概念。


ただし、どのデータがどのサーバーで記録しているかを把握する必要がある。ハッシュ値などから計算する。
IDはコードの内、一意なもの。


=== 親子関係 ===
==== Naming ====
親子関係、ツリー構造、階層構造を表現したいことがある。


[https://teratail.com/questions/281348 １つのテーブルで親子関係を管理する方法]
* [https://softwareengineering.stackexchange.com/questions/114728/why-is-naming-a-tables-primary-key-column-id-considered-bad-practice sql - Why is naming a table's Primary Key column "Id" considered bad practice? - Software Engineering Stack Exchange]
* [https://stackoverflow.com/questions/1369593/primary-key-foreign-key-naming-convention sql - Primary key/foreign Key naming convention - Stack Overflow]
* [https://stackoverflow.com/questions/208580/naming-of-id-columns-in-database-tables/ sql - Naming of ID columns in database tables - Stack Overflow]
* [https://stackoverflow.com/questions/465136/is-prefixing-each-field-name-in-a-table-with-abbreviated-table-name-a-good-pract database design - Is prefixing each field name in a table with abbreviated table name a good practice? - Stack Overflow]
* [https://stackoverflow.com/questions/529863/do-you-prefer-verbose-naming-when-it-comes-to-database-columns sql - Do you prefer verbose naming when it comes to database columns? - Stack Overflow]
* [https://qiita.com/ayayo/items/ba38853bca0c2cc2acb7 SQLアンチパターン勉強会　第三回：IDリクワイアド #SQL - Qiita]


基本的に、親コードのカラムをどこかのテーブルに持たせて対応させる。
テーブルの主キーの命名規則にいろいろ議論がある。


* 隣接リスト (Adjacency List): 親子関係が再帰的になっている場合、途中データの削除はやっかいになる。親子の取得が少々複雑で何回もSQLが必要になり厄介。階層構造が2-3階程度なら問題ない。
ケース1=idとすべきか、ケース2=table_idとすべきか。それぞれ言い分がある。
* 経路列挙:


=== Standard ===
* ケース1=id
[[wikipedia:ISO/IEC_9075|ISO/IEC 9075 - Wikipedia]]
** 主キー列、外部キー列が明確。
** テーブル名の反復がなくDRY原則に従う。
* ケース2=table_id
** USINGで指定可能。
** 複数テーブルで同じ列を同名にできる。


ISO/IEC 9075がSQLの標準。標準外のSQL/関数は独自実装なので扱いには注意が必要。標準のみの使用が望ましい。
ケース1がいいと思う。テーブル名で十分コンテキストがある。ケース2を採用するならば、全部の列にテーブル名を前置しないと一貫性がない。


ANSI SQLと呼んだりするらしい。
USINGはシンプルな結合しかできない。ONは条件を増やせる。
 
[[wikipedia:List_of_SQL_reserved_words|List of SQL reserved words - Wikipedia]]
 
ここに標準SQLの予約語一覧がある。
 
== Design ==


=== 正規化 ===
=== 正規化 ===
277行目: 312行目:
|4
|4
|}
|}
残ったIDだけのテーブル。IDを片方に残して、複合主キーとすることもできる。
残ったIDだけのテーブル。関連テーブル、中間テーブルと呼ぶ。IDを片方に残して、複合主キーとすることもできる。


第２正規形の解決課題。
第２正規形の解決課題。
287行目: 322行目:


マスターテーブルじゃないなら、複合主キーも悪くないか。複合主キーを使わないなら、代理キーでIDを生成するだけだから。
マスターテーブルじゃないなら、複合主キーも悪くないか。複合主キーを使わないなら、代理キーでIDを生成するだけだから。
この関連テーブルには注意が必要。実装の都合で複合主キーではなく、代理キーを主キーにするなら、複合ユニーク制約をつける「[https://qiita.com/buzzkuri/items/c704efcbdf6c0ab03b4d 中間テーブルに複合ユニーク制約をつける理由 #SQL - Qiita]」。そうしないと、同じデータが登録できてしまう。


==== 第3正規形 ====
==== 第3正規形 ====
337行目: 374行目:
|山根
|山根
|}
|}


=== Naming ===
=== Naming ===
==== IDとコード ====
* [https://atmarkit.itmedia.co.jp/bbs/phpBB/viewtopic.php?topic=30818&forum=26 「列名の付け方（～CD or ～ID ？）」（1） Database Expert － ＠IT]
* [https://irof.hateblo.jp/entry/2021/10/26/181920 コード、区分、フラグ、IDと名前。 - 日々常々]
コードは広い概念。
IDはコードの内、一意なもの。


==== 命名規則 ====
==== 命名規則 ====
382行目: 410行目:


構文強調がないと、formなどと見分けがつきにくいので、大文字にしたほうがいい。可読性優先。
構文強調がないと、formなどと見分けがつきにくいので、大文字にしたほうがいい。可読性優先。
=== Data ===


==== enum/string ====
==== enum/string ====
392行目: 422行目:


数値で必要ならマスターの主キーにする。基本は文字列でいいと思う。
数値で必要ならマスターの主キーにする。基本は文字列でいいと思う。
==== 画像データのパス/BLOBでの格納 ====
* [https://stackoverflow.com/questions/1347461/saving-images-files-or-blobs database - Saving images: files or blobs? - Stack Overflow]
* [https://stackoverflow.com/questions/3748/storing-images-in-db-yea-or-nay database - Storing Images in DB - Yea or Nay? - Stack Overflow]
* [https://www.microsoft.com/en-us/research/publication/to-blob-or-not-to-blob-large-object-storage-in-a-database-or-a-filesystem/ To BLOB or Not To BLOB: Large Object Storage in a Database or a Filesystem - Microsoft Research]
* [https://teratail.com/questions/296506 データベースに画像を保存するメリット・デメリットと必要性について]
* [https://teratail.com/questions/81233 データベースに画像を保存するのはありでしょうか？]
画像のファイルサイズが1MB以下の場合は問題ない。が、それ以外だと問題があるので、ファイルパスで扱うのがいいらしい。
==== NULL ====
Ref: [https://qiita.com/daichi_yamazaki/items/ea3516080948e95bbf26 NOT NULL 制約をつけるべきカラムとは #PostgreSQL - Qiita].
絶対に必要な項目にNOT NULL制約をつける。NULLを許容する列は、最後のほうに集めると、ストレージなどの効率がいい。
例えば、取得に失敗した場合に、値をどうするのか？などを考えるとよい。
==== int/string ====
* [https://stackoverflow.com/questions/747802/integer-vs-string-in-database language agnostic - Integer vs String in database - Stack Overflow]
* [https://teratail.com/questions/259465 データベースの値の持ち方は文字列にするか数字にするか迷っています]
データベースのカラムでIDやコード類を定義する場合、型をintなどの数値系、charなどの文字列系にするかの議論がある。
* WHEREの範囲、算術計算など数値計算が伴うなら数値。データを何に使うかの意味論が重要。
* 数値のほうが高速。
* 郵便番号のように先頭が0つく場合、文字列。
* 数値だと、auto incrementで登録時の重複除去を保証できる。
* 値を変更する可能性がないなら数値。
* 文字列型だと文字数の検討が必要。
==== INT/BIGINT ====
* [https://qiita.com/tk20211228/items/fbed24e7c97bf10112a0 MySQLにおけるint型IDの限界とクラッシュ回避のための対策 #MySQL - Qiita]
* [https://gihyo.jp/article/2022/08/mysql-rcn0178 第178回　MySQLのAUTO_INCREMENTなINTEGERってどのくらいで使い切るの？ | gihyo.jp]
型
* TINYINT
** 符号付き: -128から127まで
** 符号なし: 0から255まで
** 1バイト（8ビット）を使用
* SMALLINT
** 符号付き: -32,768から32,767まで
** 符号なし: 0から65,535まで
** 2バイト（16ビット）を使用
* MEDIUMINT
** 符号付き: -8,388,608から8,388,607まで
** 符号なし: 0から16,777,215まで
** 3バイト（24ビット）を使用
* INT
** 符号付き: -2,147,483,648から2,147,483,647まで
** 符号なし: 0から4,294,967,295まで
** 4バイト（32ビット）を使用
* BIGINT
** 符号付き: -9,223,372,036,854,775,808から9,223,372,036,854,775,807まで
** 符号なし: 0から18,446,744,073,709,551,615まで
** 8バイト（64ビット）を使用
int型は4バイトでsignedなら2,147,483,647 (21億) が最大値。1日1万件増加するとして588年。10万件で58年。増加追加量次第だが、中小規模なら通常はint型で問題ない。
BIGINTなら、1日100万件で25269512429年。BIGINTならほぼ問題ない。
ただ、BIGINTはINTの2倍のサイズになるのでストレージも消費する。よく考えておく。
BIGINTでも足りない場合、プライマリーキーを文字列にして、UUIDやhash値を使う。
==== NULLと空文字 ====
* [https://stackoverflow.com/questions/5618357/sql-server-null-vs-empty-string SQL Server: Null VS Empty String - Stack Overflow]
* [https://softwareengineering.stackexchange.com/questions/32578/sql-empty-string-vs-null-value design - SQL: empty string vs NULL value - Software Engineering Stack Exchange]
住所などのようなテキスト系のデータで、NULLを許容するべきか？空文字とNULLで意味を区別すべきか？
NULLと空文字の意味論
* NULL=値がない、何であるか不明な状態。わからないという状態。
* 空文字=値がわかっていて、空なことが分かっているという状態。
例えば、人名のミドルネームの扱いがある。
* ミドルネームなし=<nowiki>''</nowiki>
* ミドルネームの有無不明=NULL
これを区別するにはNULLしかない。空の文字列は値。
NULLがないほうがSQLはシンプルになる。が、値が空なのか、不定なのか判別できない。判別したいなら、別のカラムが必要になる ([https://zenn.dev/pei0804/articles/donot-use-nullable-in-star-schema 分析用途のテーブルにnullはいらない])。
[カラム] なしと不明を区別する必要があるかどうか？不明=なしで問題ないなら空文字でいいと思われる。実務上違いがない。
文字列型なら空文字でいいが、数値型ならNULLを使うしかない。
データの要件を考えて判断する。基本は、NULLは使わない。文字列の選択肢なども。
ただ、他の日付などは空=NULLにするしかない。他のデータ型と合わせるなら、空=NULLでもいい気がする。
==== NULL ====
[https://oss-db.jp/dojo/dojo_08 第8回　NULLって何？]
そもそもNULLを理解する必要がある。
NULLは、「値が入っていない、値なし、値不明」を意味する。
空文字は「空という値が入っている」とみなす。値なしとは意味が違う。
==== VARCHARの文字数定義 ====
* [https://stackoverflow.com/questions/8295131/best-practices-for-sql-varchar-column-length mysql - Best practices for SQL varchar column length - Stack Overflow]
* [https://dev.mysql.com/doc/refman/8.4/en/char.html MySQL :: MySQL 8.4 リファレンスマニュアル :: 13.3.2 CHAR 型と VARCHAR 型]
varcharで最大文字数を定義できる。この最大文字数の考え方。
DB上は最大文字数は、定義するだけだとあまり意味がない。実際に使用された文字数でデータがカウントされる。varchar(2)でもvarchar(255)でも同じ。
ただし、varchar(255)までは1バイトだが、256文字目からは長さプレフィックスに2バイト使用する。なので、不必要に256文字のカラムにしない。
VARCHARの長さは、技術的・物理的なものというより、一種の制約、ビジネスルールとして考えたほうがいい。
無駄に長い文字数を許容すると、アプリ側でその幅を考慮必要になる。UI上の制約になるので注意したほうがよく感じる。
==== 外部キー制約 ====
[https://kagamihoge.hatenablog.com/entry/20130513/1368449162 整合性制約の有無によるINSERTの速度差 - kagamihogeの日記]
外部キー制約をいれると、データの整合性が保証できる。代わりに速度が落ちる。ないほうがいいか。
=== Relation ===
==== 多対多 ====
原則として中間テーブルを使う。
中間テーブルなしでやる場合、1列に複数レコードを入れるしかない。PostgreSQLなどの配列、JSON、カンマ区切り文字列。やらないほうがいい。
2個のテーブルにそれぞれのID列をもたせるだけでは正しく管理できない。例えば以下の問題が生じる。
# データの重複: それぞれに相手のID列を渡す必要があり、同じデータを何度も追加必要になる。
# 管理の煩雑: あるIDを削除する場合、もう片方のテーブルにもIDが残る。
# 性能: 両方のテーブルの更新が必要で性能が低下する。
中間テーブルの命名規則。
2のテーブルを_でつなげる。2テーブルの関係が明確なら関係の名前をつけるといいが、これは考えるのが難しい。
多対多のデータの登録画面の実装方法は2種類。
* [https://rails.densan-labs.net/form/many_to_many_form.html 1.4. 多対多の関連を持つオブジェクトを編集可能なフォーム — Railsアプリケーション構築ガイド]
* [https://rails.densan-labs.net/form/many_to_many_checkbox_form.html 1.5. 多対多の関連を持つオブジェクトをチェックボックスで編集可能なフォーム — Railsアプリケーション構築ガイド]
追加ボタンで関連を追加できるようにするか、チェックボックス、ドロップダウンリストで複数選択できるようにする。
==== ポリモーフィック ====
* [https://zenn.dev/ukiyocreate_dev/articles/b184ea1c3c39be ORM のポリモーフィック関連は使うべきか？]
* [https://techlife.cookpad.com/entry/2023/04/13/141200 ポリモーフィック関連を活用し、森羅万象の「いいね」を実現する手法 - クックパッド開発者ブログ]
* [https://qiita.com/onosi/items/270e87740a4c3e24eae6 SQLアンチパターン6章　ポリモーフィック関連 #SQLアンチパターン - Qiita]
* [https://qiita.com/dai329/items/1db8fbe37f43a465d801 SQLアンチパターン勉強会　第6回：ポリモーフィック関連 #SQLアンチパターン - Qiita]
1個のテーブルで複数のテーブルに関連させるパターン。
画像テーブルを、投稿テーブルとプロフィールテーブルに関連させる場合。
1テーブルだけなら、画像テーブルにプロフィールテーブルの外部キーを持たせれば済んでいた。これを1テーブルで投稿テーブルにも対応させるパターン。
外部キー列に追加してテーブル名の列も追加する。これで複数テーブルを特定する。一見柔軟だが、外部参照制約をつけられなかったり、SQLが複雑になるなどの欠点がある。SQLが複雑になるということは性能も悪くなる。
ポリモーフィック関連を使わなくても、中間テーブルを使って親子を関連付けることはできる。ただし、テーブル数は増える
* 交差テーブル: 1レコードが共通で呼ばれる場合に対処できない。
* 共通の親テーブル: 親idを全部で持つ。1レコードが共通で呼ばれる場合に対処できない。
親テーブルを持つパターンが理にかなっている気がする。オブジェクト指向の継承になっており、余計なテーブル数も少なくシンプル。
ポリモーフィック関連は使わないほうがよさそう。
== Function/Operator ==
[https://dev.mysql.com/doc/refman/8.0/ja/functions.html MySQL :: MySQL 8.0 リファレンスマニュアル :: 12 関数と演算子]
=== Flow control function ===
'''表 12.7 「フロー制御演算子」'''
{| class="wikitable"
!名前
!説明
|-
|[[/dev.mysql.com/doc/refman/8.0/ja/flow-control-functions.html#operator%20case|<code>CASE</code>]]
|CASE 演算子
|-
|[[/dev.mysql.com/doc/refman/8.0/ja/flow-control-functions.html#function%20if|<code>IF()</code>]]
|If/else 構文
|-
|[[/dev.mysql.com/doc/refman/8.0/ja/flow-control-functions.html#function%20ifnull|<code>IFNULL()</code>]]
|Null if/else 構文
|-
|[[/dev.mysql.com/doc/refman/8.0/ja/flow-control-functions.html#function%20nullif|<code>NULLIF()</code>]]
|expr1 = expr2 の場合に NULL を返します
|}
CASEとIFはSQL文としても同名が存在する。ただし、使うタイミングがSQL文だとストアドプロシージャであまり使うことはない。演算子と関数をよく使う。


== SQL ==
== SQL ==


=== JOIN ===
=== DML ===
[https://products.sint.co.jp/topsic/blog/sql-bootcamp-07 SQLのテーブル結合（JOIN, UNION, NATURAL）を覚えよう！基本構文と使用例を解説]
DML (Data Manipulation Language)。データ操作用言語。
 
INSERT/SELECT/UPDATE/DELETEが該当。
 
==== DELETE ====
[https://products.sint.co.jp/topsic/blog/sql-delete DELETE文（SQLを基本から学ぶシリーズ）]
DELETE FROM [table] [条件]
構文がシンプル。
 
==== INSERT ====
 
===== basic =====
* [https://detail.chiebukuro.yahoo.co.jp/qa/question_detail/q14150336179 SQL文についての質問です。INSERT文でINTOの後のカラム名指定のところ... - Yahoo!知恵袋]
* [https://products.sint.co.jp/topsic/blog/sql-insert INSERT文（SQLを基本から学ぶシリーズ）]
 
INSERT INTO テーブル名 (列名1, 列名2,...) VALUES (値1, 値2,...), (), ();
 
INSERT INTO テーブル名 VALUES (値1, 値2,...);
列名と値の順番があっていれば、順番はテーブルカラム定義の順番じゃなくてもOK。カラム名を省略した場合、全カラムが定義の順番で必要。
 
基本はカラム名を指定したほうがいい。
 
VALUESの後、<code>,()</code>で複数レコード一度に追加できる。
 
[https://qiita.com/shakechi/items/c5be910d924b9661c216 INSERT文に（SELECT結果＋入力結果）を入れる #MySQL - Qiita]
 
SELECTの結果を値に使うこともできる。その場合、構文が若干異なる。
INSERT INTO `テーブル名１` (col1, col2, ...)
  SELECT col1, col2, ... FROM `テーブル名２`
VALUESが不要。
 
例えば、データベースに登録されていない商品をIDが重複しないように、データを追加したい場合だとこんな感じに書きます。
INSERT INTO `テーブル名` (id,item_name)
  SELECT MAX(id)+1,"商品名" FROM `テーブル名`
 
===== bulk =====
[https://blog.yuyat.jp/archives/2018 PHP/MySQL でレコードを N 件ずつバルクインサート | Born Too Late]
 
一括INSERTする場合、一度の件数が大きすぎると問題がある模様。
 
1000件ずつくらいに分割するといいらしい。
 
==== UPDATE ====
 
===== Basic =====
[https://products.sint.co.jp/topsic/blog/sql-update UPDATE文（SQLを基本から学ぶシリーズ）]
UPDATE (表名) SET (カラム名1) = (値1) WHERE (条件);
UPDATE (表名) SET (カラム名1) = (値1), (カラム名2) = (値2) WHERE (条件);
一括で更新する。
 
==== 別のテーブルで更新 ====
[https://products.sint.co.jp/topsic/blog/sql-update#toc-7 UPDATE文（SQLを基本から学ぶシリーズ）]
 
2種類の方法がある。
UPDATE employees SET employees.manager_name =
( SELECT manager.manager_name FROM manager
  WHERE employees.emp_no = manager.emp_no );
服問い合わせ、サブクエリー。
UPDATE employees JOIN manager
ON employees.emp_no = manager.emp_no
SET employees.manager_name = manager.manager_name
JOIN。JOINのほうがシンプルかもしれない。
 
==== UPSERT ====
Ref: [https://resanaplaza.com/2023/01/22/%e3%80%90%e7%b7%8f%e3%81%be%e3%81%a8%e3%82%81%e3%80%91upsert%e3%81%a8%e3%81%af%ef%bc%9fdb%e3%81%94%e3%81%a8%e3%81%ae%e9%81%95%e3%81%84%e3%81%af%ef%bc%9f/ 【総まとめ】UPSERTとは？DBごとの違いは？ | 初心者DIYプログラミング入門].
 
INSERTするかUPDATEするか、一度SELECTで値の有無を確認してから本来しないといけない。SQLを2回実行必要で、処理が無駄になるので、これを1回で行う。これがUPSERTと呼ばれるもの。ただし、UPSERTというSQLがあるわけではない。MERGE文が標準だが、実装していないものがあって、DBごとの独自拡張になる。
 
===== MySQL =====
 
====== INSERT ... ON DUPULICATE KEY UPDATE ======
MySQLにMERGE文はない ([https://stackoverflow.com/questions/42663074/is-merge-statement-available-in-mysql sql - Is merge statement available in MySQL - Stack Overflow])。代わりにINSERT ... ON DUPULICATE KEY UPDATEという文を使う。
 
「[https://stackoverflow.com/questions/9168928/what-are-practical-differences-between-replace-and-insert-on-duplicate-ke sql - What are practical differences between `REPLACE` and `INSERT ... ON DUPLICATE KEY UPDATE` in MySQL? - Stack Overflow]」によると、REPLACE文も使えるが、内部的に一度削除して、副作用があるので、INSERT ... ON DUPULICATE KEY UPDATEがいい模様。
 
[https://dev.mysql.com/doc/refman/8.4/en/insert-on-duplicate.html MySQL :: MySQL 8.4 Reference Manual :: 15.2.7.2 INSERT ... ON DUPLICATE KEY UPDATE Statement]
INSERT INTO t1 (a,b,c) VALUES (1,2,3)
  ON DUPLICATE KEY UPDATE c=c+1;
複数レコードを一括で更新したい場合。「[https://journal.lampetty.net/entry/mysql-bulk-insert-and-on-duplicate-key-update MySQLでbulk insert + on duplicate key updateしたい - oinume journal]」VALUESを使う。
INSERT INTO users VALUES
  ('akuwano', 15), ('oinume', 14), ('oranie', 13)
  ON DUPLICATE KEY UPDATE age = VALUES(age);
[https://stackoverflow.com/questions/11235501/mysql-insert-row-on-duplicate-key-update-multiple-columns MySQL insert row on duplicate key update multiple columns - Stack Overflow]
INSERT INTO tb (name, date, stat1, stat2, stat3)
VALUES (?, ?, ?, ?, ?)
ON DUPLICATE KEY UPDATE stat1 = stat1 + VALUES(stat1), stat2 = stat2 + VALUES(stat2), stat3 = stat3 + VALUES(stat3)
複数列指定する場合、列名 = values(列名), で複数列挙する模様。
 
====== AUTO_INCREMENT ======
* [https://stackoverflow.com/questions/23516958/on-duplicate-key-auto-increment-issue-mysql ON DUPLICATE KEY + AUTO INCREMENT issue mysql - Stack Overflow]
* [https://ameblo.jp/stylagy/entry-12434472506.html 【MySQL】DUPLICATE で自動採番が飛ぶケースについて（２） | 株式会社スタイラジーの「居心地の良い」ブログ]
* [https://commerce-engineer.rakuten.careers/entry/tech/0005 MySQL で ON DUPLICATE KEY UPDATE を使ってたら、カンスト(カウンターストップ)した話 - R-Hack（楽天グループ株式会社）]
* [https://tech.mobilefactory.jp/entry/2020/12/12/000000 MySQLでのINSERT ON DUPLICATE構文との付き合い方 - Mobile Factory Tech Blog]
* [https://qiita.com/QBMK_IQU/items/4515c935b48acd0c953e INSERT ON DUPLICATE KEY UPDATEの利点と注意点 #MySQL - Qiita]
 
MySQLのINSERT ... ON DUPULICATE KEY UPDATEは扱いに注意が必要。AUTO_INCREMENTがあると、更新でも増加する。
 
基本的に最初にINSERTを実行するから。
 
挙動として、一度のINSERT ... ON DUPULICATE KEY UPDATE中は連番が保証される。終わったらUPDATE回数分AIが増分される。
 
大量更新前提のテーブルの場合、増分値がすごい勢いで増加する。いくつか対策がある。
 
* INSERT ... ON DUPULICATE KEY UPDATEを使わず、昔ながらのSELECTの結果を見てINSERT/UPDATEする。
* AUTO_INCREMENTをそもそも使わない、idを使わない削除する。
* idをbigintにする。
* ストレージエンジンをInnoDBではなくMyIsamを使う。
 
最初2個が回答になっている。実行中は連番なので、ON DUPLICATE KEY UPDATEの使用後に、ALTER TABLEでAUTO_INCREMENTの初期値をリセットする。
INSERT INTO `table_blah` (`the_col`) VALUES("the_value")
ON DUPLICATE KEY UPDATE `the_col` = "the_value";
SET @NEW_AI = (SELECT MAX(`the_id`)+1 FROM `table_blah`);
SET @ALTER_SQL = CONCAT('ALTER TABLE `table_blah` AUTO_INCREMENT =', @NEW_AI);
PREPARE NEWSQL FROM @ALTER_SQL;
EXECUTE NEWSQL;
これでOK。
 
なお、実行中の連番は注意が必要。1回のSQL内で同じ行に重複してINSERT/UPDATEしている場合、SQL実行中にAIが増加する。重複していなかったら連番。SQL実行前に事前に重複を除去したらいい。SQLの用意に工夫が必要。
 
====== UNIQUE ======
 
* [https://ramble.impl.co.jp/4159/ 【MySQL】Nullable カラムを含めて複合ユニーク制約を設ける方法 | ramble - ランブル -]
* [https://tgk.hatenadiary.org/entry/20120913/1347506946 「NULLがUNIQUE制約に縛られないことを利用する」のは、正当なNULLの使い方 - 極北データモデリング]
 
UPSERTで挿入する際のUNIQUE制約には注意が必要。複合UNIQUEでNULLを許容する場合、NULLは固有扱いになるので、実質重複キーが生まれる。回避したければ。
 
# 複合UNIQUEにはNOT NULL
# 文字列結合、ハッシュなど算出可能な参照用のカラムを追加する。
 
====== 注意すべき挙動の整理 ======
AUTO_INCREMENTとUNIQUE制約に由来する注意すべき挙動がある。(MySQL 5.7で確認。
 
# PKの指定がなく、UNIQUEで行特定の場合、PKがAIで更新→更新ならPKは常に必要。事前にSELECTでPKを取得しておく。
## 重複を削除するので、そもそもUNIQUE制約は不要かもと思ったが、重複は1SQL内で複数ある場合の話。重複が生じるのでUNIQUEは必要。
# UPDATE発生時、UPSERT後にAIがUPDATE回数分増加→UPSERT後にAIを最大値で初期化。
# UPDATE発生時、実行中にもAI増加→PKなしの複数UPDATE時はUPSERT実行前のSQLで一番最後を残して重複レコード除去。
# マスター系テーブルで固有でINSERTする場合、重複除去のためにNOT NULL+UNIQUE制約をつけておく。
## NULL != NULLが成立するので、UNIQUE制約をつける場合はNOT NULLもつけておく。
 
==== GROUP BY ====
 
===== Basic =====
[https://amg-solution.jp/blog/64244 【SQL入門】複数のデータをグループ化するGROUP BYの使い方 | 株式会社AMG Solution]
 
テーブルの値でデータをグループ化する句。
 
基本構文
SELECT 要素名 FROM テーブル名 GROUP BY グループ化したい要素名;
SELECT部分でGROUP BYで指定したカラム名の指定が必須？
 
GROUP BYで指定したカラム以外は、集約関数を使わないと選択できない。テキストのように集約不能な場合でも。その場合は集約の意味はない。
 
===== 連番 =====
[https://qiita.com/kyamadahoge/items/fdbc04790f620dddbf3c MySQLでSQL一発でグループごとに連番を振る #MySQL8.0 - Qiita]
 
MySQL 5.7だと変数で実現。
mysql> select * from history;
+----+----------+--------------------+------------+
| id | group_id | name              | created_at |
+----+----------+--------------------+------------+
|  1 |        1 | Cupcake            | 2020-01-01 |
|  2 |        1 | Donut              | 2020-01-02 |
|  3 |        2 | Eclair            | 2020-01-03 |
|  4 |        2 | Froyo              | 2020-01-04 |
|  5 |        2 | Gingerbread        | 2020-01-05 |
|  6 |        3 | Honeycomb          | 2020-01-06 |
|  7 |        4 | Ice Cream Sandwich | 2020-01-07 |
|  8 |        4 | Jelly Bean        | 2020-01-08 |
|  9 |        4 | KitKat            | 2020-01-09 |
+----+----------+--------------------+------------+
9 rows in set (0.00 sec)
 
SELECT
<nowiki> </nowiki> @rownum := IF(@prev_group_id = group_id, @rownum + 1, 1) AS rownum, -- 連番の列
<nowiki> </nowiki> history.id,
<nowiki> </nowiki> @prev_group_id := group_id AS group_id,
<nowiki> </nowiki> history.name,
<nowiki> </nowiki> history.created_at
FROM
<nowiki> </nowiki> history,
<nowiki> </nowiki> (select @rownum := 0) hoge,
<nowiki> </nowiki> (select @prev_group_id := <nowiki>''</nowiki>) fuga
ORDER BY
<nowiki> </nowiki> group_id,
<nowiki> </nowiki> created_at DESC
 
+--------+----+----------+--------------------+------------+
| rownum | id | group_id | name              | created_at |
+--------+----+----------+--------------------+------------+
|      1 |  2 |        1 | Donut              | 2020-01-02 |
|      2 |  1 |        1 | Cupcake            | 2020-01-01 |
|      1 |  5 |        2 | Gingerbread        | 2020-01-05 |
|      2 |  4 |        2 | Froyo              | 2020-01-04 |
|      3 |  3 |        2 | Eclair            | 2020-01-03 |
|      1 |  6 |        3 | Honeycomb          | 2020-01-06 |
|      1 |  9 |        4 | KitKat            | 2020-01-09 |
|      2 |  8 |        4 | Jelly Bean        | 2020-01-08 |
|      3 |  7 |        4 | Ice Cream Sandwich | 2020-01-07 |
+--------+----+----------+--------------------+------------+
9 rows in set (0.00 sec)
MySQL8でROW_NUMBERが使えるならもっと簡単。
SELECT
  ROW_NUMBER() OVER(PARTITION BY group_id ORDER BY group_id, created_at DESC) AS rownum,
  id,
  group_id,
  name,
  created_at
FROM
  history
ORDER BY
  group_id,
  created_at DESC
 
==== SELECT ====
[https://dev.mysql.com/doc/refman/8.0/ja/select.html MySQL :: MySQL 8.0 リファレンスマニュアル :: 13.2.10 SELECT ステートメント]
 
====== JOIN ======
 
* [https://products.sint.co.jp/topsic/blog/sql-bootcamp-07 SQLのテーブル結合（JOIN, UNION, NATURAL）を覚えよう！基本構文と使用例を解説]
* [https://zenn.dev/argi_berri/articles/74e518f2f703c1 SQL の内部結合(INNER JOIN)と内部結合 (OUTER JOIN)]


外部テーブルと外部キーで結合の方法がいくつかある。
外部テーブルと外部キーで結合の方法がいくつかある。
403行目: 846行目:
** SELECT ... FROM ... INNER JOIN テーブル名 ON 項目A = 項目B
** SELECT ... FROM ... INNER JOIN テーブル名 ON 項目A = 項目B
* OUTER JOIN: OR。片方がなくても。
* OUTER JOIN: OR。片方がなくても。
** LEFT OUTER JOIN: 左を基準。右側はNULLありえる。
** RIGHT OUTER JOIN: 右を基準。左側はNULLありえる。
* CROSS JOIN: 全組み合わせ。
* CROSS JOIN: 全組み合わせ。
* NATURAL: 同名項目を取得。
* NATURAL: 同名項目を取得。
411行目: 856行目:


結合する場合、同じ値だから通常は問題ない。
結合する場合、同じ値だから通常は問題ない。
==== 列名が重複する場合の対応 ====
addSelectで列名をASで地道に追加する。これが確実。プリペアードステートメントなどを使うと、プログラム側でできなくはない。


[https://zenn.dev/kasamifutoshi/scraps/8c8cf445f50445 【Laravel】目的別クエリビルダの記述方法まとめ（記事を書く前の整理用）]
[https://zenn.dev/kasamifutoshi/scraps/8c8cf445f50445 【Laravel】目的別クエリビルダの記述方法まとめ（記事を書く前の整理用）]


addSelectで列名を地道に追加する。これが確実。
[https://rimane.net/inner-join-1037/ 結合したテーブルに同じ名前のカラムがあっても正しく取り出す方法【MySQL+PHP】 - りまねどっとねっと]


[https://rimane.net/inner-join-1037/ 結合したテーブルに同じ名前のカラムがあっても正しく取り出す方法【MySQL+PHP】 - りまねどっとねっと]
PHP PDOだったら列番号で取得するオプションなどがあるのでそれで対応できる。


GroupConcatで文字列で結合する。
他にGroupConcatで文字列で結合する。


[https://stackoverflow.com/questions/329931/sql-select-join-is-it-possible-to-prefix-all-columns-as-prefix SQL select join: is it possible to prefix all columns as 'prefix.*'? - Stack Overflow]
[https://stackoverflow.com/questions/329931/sql-select-join-is-it-possible-to-prefix-all-columns-as-prefix SQL select join: is it possible to prefix all columns as 'prefix.*'? - Stack Overflow]
424行目: 872行目:
ダミーの空列を追加すれば、処理はしやすい。が、そもそも同じ列名を取得できるのか？
ダミーの空列を追加すれば、処理はしやすい。が、そもそも同じ列名を取得できるのか？


=== JOINとWHERE ===
[https://qiita.com/tomoya_ozawa/items/0cc147623d6cfe0ce0b8 同じテーブルを複数回Joinしたい場合 #MySQL - Qiita]
SELECT * FROM table
INNER JOIN join_table AS join1
ON table.id = join1.id
INNER JOIN join_table AS join2
ON table.another_id = join2.another_id
ASで別名をつける。


====== JOINとWHERE ======
* [https://blog.mosuke.tech/entry/2014/02/28/232846/ INNER JOIN とWHERE結合の違いについて理解する]
* [https://blog.mosuke.tech/entry/2014/02/28/232846/ INNER JOIN とWHERE結合の違いについて理解する]
* [https://www.ritolab.com/posts/258 テーブル結合は JOIN と FROM どちらで行うべきか。両者の違いと注意点 | Ritolabo]
* [https://www.ritolab.com/posts/258 テーブル結合は JOIN と FROM どちらで行うべきか。両者の違いと注意点 | Ritolabo]
452行目: 907行目:
複数テーブルが登場するなら、基本はJOINがよいだろう。
複数テーブルが登場するなら、基本はJOINがよいだろう。


=== 一括更新 ===
Ref: [https://zenn.dev/rescuenow/articles/5b16ab6c4d9cf9 MySQLで複数行を一括更新するクエリの実装方法].


ELT/FIELDを併用すれば一括更新可能とのこと。


=== UPSERT ===
=== information schema ===
Ref: [https://resanaplaza.com/2023/01/22/%e3%80%90%e7%b7%8f%e3%81%be%e3%81%a8%e3%82%81%e3%80%91upsert%e3%81%a8%e3%81%af%ef%bc%9fdb%e3%81%94%e3%81%a8%e3%81%ae%e9%81%95%e3%81%84%e3%81%af%ef%bc%9f/ 【総まとめ】UPSERTとは？DBごとの違いは？ | 初心者DIYプログラミング入門].
[[wikipedia:Information_schema|Information schema - Wikipedia]]
 
DBのテーブル情報を格納する特殊なテーブルが [information schema]。
 
標準化されているが、SQLiteやOracle Databaseなどで未対応。
 
MySQL/PostgreSQLなどでは対応されている。が、SQLほど標準化されていないので扱いには注意。
 
「[https://www.quora.com/Why-doesnt-SQLite-support-INFORMATION_SCHEMA Why doesn't SQLite support INFORMATION_SCHEMA? - Quora]」にあるように、SQLiteのような軽量な実装にすると、このメタデータのテーブル実装はコストが大きいのだと思われる。
=== VIEW ===
 
* [https://qiita.com/ryo-ka-notti/items/341310a6f0673fa2a7b5 ビュー(DB)について #SQL - Qiita]
* [https://style.potepan.com/articles/25504.html SQLのビュー(VIEW)って何？基本的な使い方理解しよう【サンプル有り】 | ポテパンスタイル]
* [https://and-engineer.com/articles/YRsqCxAAAPDr7vrW SQLのビューとは？考え方から実際にできることまで解説！ | アンドエンジニア]
 
SELECT文の結果に別名をつけるようなこと。
 
何回も呼び出すSELECT文を省略でき、シンプルにできる。また、テーブルを直接使わないこととで、機密情報へのアクセスを制御できる。
 
* SELECT文なので、ストレージを消費しない。
* SELECT文呼び出しなので、複雑だと時間がかかる。
 


INSERTするかUPDATEするか、一度SELECTで値の有無を確認してから本来しないといけない。SQLを2回実行必要で、処理が無駄になるので、これを1回で行う。これがUPSERTと呼ばれるもの。ただし、UPSERTというSQLがあるわけではなく、DBごとの独自拡張になる。


MERGE文が一応あるが、MySQLにはない ([https://stackoverflow.com/questions/42663074/is-merge-statement-available-in-mysql sql - Is merge statement available in MySQL - Stack Overflow])。代わりにINSERT ... ON DUPULICATE KEY UPDATEという文を使う。


=== 一括更新 ===
Ref: [https://zenn.dev/rescuenow/articles/5b16ab6c4d9cf9 MySQLで複数行を一括更新するクエリの実装方法].
ELT/FIELDを併用すれば一括更新可能とのこと。
=== NULLを含むLIKE ===
=== NULLを含むLIKE ===
[https://stackoverflow.com/questions/3924400/like-does-not-accept-null-value sql - like '%' does not accept NULL value - Stack Overflow]
[https://stackoverflow.com/questions/3924400/like-does-not-accept-null-value sql - like '%' does not accept NULL value - Stack Overflow]
598行目: 1,073行目:


引用識別子は、テーブル名、カラム名など、DB中のオブジェクト。
引用識別子は、テーブル名、カラム名など、DB中のオブジェクト。
SELECTのASなどで指定するリテラルは文字列定数扱い。あくまで定義済みのオブジェクトかどうかで変わる模様。


=== 引用識別子 ===
=== 引用識別子 ===
617行目: 1,094行目:


なお、文字列定数は常に一重引用符が必要。数字などは囲み不要。
なお、文字列定数は常に一重引用符が必要。数字などは囲み不要。
=== COMMENT ===
[https://analytics-okinawa.jp/sql/1076/ SQL記述文内でコメントアウトする。単一行・複数行用の書き方 | アナリティクス沖縄│DataAnalytics]
SQLのコメントアウト。癖がある。
* [-- ]: ハイフン2個+スペースの後がコメント扱い。
* /* */: ブロックコメントアウト。
=== 行番号 ===
* [https://qiita.com/sand_bash/items/851f08a36381a4f01b75 MySQLのSELECT結果に行番号を振る。 #mariadb - Qiita]
* [https://qiita.com/taka777n/items/f473450b8623dd1f43d2 MySQL8.0で便利なROW_NUMBER()を使ってみた #MySQL8.0 - Qiita]
MySQL 8.0からROW_NUMBERが使える。8.0未満だと変数を使って実現する。
SET @rownum=0; SELECT @rownum:=@rownum+1 as ROW_NUM, TABLE_NAME FROM information_schema.TABLES LIMIT 10;
SET @row=0; SELECT @row:=@row+1 as ROW_NUM, t.* FROM [table] t;
=== Table ===
==== CREATE TABLE ====
[https://letitride.hatenablog.com/entry/20120907/1347013609 複合unique制約のつけ方 - テックメモ跡地]
CREATE TABLE example(
    column1    SERIAL      PRIMARY KEY,
    column2    INTEGER      NOT NULL,
    column3    INTEGER      NOT NULL,
    UNIQUE ( column2, column3 )
);
==== SHOW tables ====
* [https://stackoverflow.com/questions/16179578/check-if-table-exist-using-pdo php - Check if table exist using PDO - Stack Overflow]
* [https://stackoverflow.com/questions/1227921/portable-sql-to-determine-if-a-table-exists-or-not portability - Portable SQL to determine if a table exists or not? - Stack Overflow]
テーブル一覧やテーブルの確認方法。
完全に標準的な方法はない。
* information_schema.tables
* MySQL: SHOW TABLES
* SQLite: .tables/SELECT * FROM dbname.sqlite_master WHERE type='table' [[https://stackoverflow.com/questions/82875/how-can-i-list-the-tables-in-a-sqlite-database-file-that-was-opened-with-attach sql - How can I list the tables in a SQLite database file that was opened with ATTACH? - Stack Overflow]];
* [https://stackoverflow.com/questions/769683/how-to-show-tables-in-postgresql database - How to show tables in PostgreSQL? - Stack Overflow]
一番確実なのはテーブルに対してSELECTしてエラーが出るかを確認。
SELECT count(*) FROM $table;
他に、information_schema.tablesを使う方法がある。MySQL/PostgreSQLなどSQLiteがないならこれでもOK。
select count(*) from information_schema.tables where table_name = 'tablename';
==== ALTER TABLE ====
テーブル定義を変更する文。ほぼ標準化されていないので、DB毎の独自構文を理解する必要がある。
[https://analytics-okinawa.jp/sql/1010/#index_id19 SQL│テーブルの定義を変更する。列のデータ型や名前、順番の変更 | アナリティクス沖縄│DataAnalytics]
-- テーブル名変更
ALTER TABLE [before table name] RENAME TO [after table name];
-- 列名変更
ALTER TABLE [table name] RENAME COLUMN [before column name] TO [after column name];
テーブル名と列名の変更だけシンプルで全体共通の模様。
===== COLUMN =====
DB製品ごとに微妙に構文が違う。
RENAMEだけ共通の模様。RENAMEとALTERはPostgreSQLと共通？
,で区切って、同じテーブルへの複数のサブ文？を1文で書ける。
PostgreSQL
[https://www.postgresql.jp/document/16/html/sql-altertable.html ALTER TABLE]
ALTER TABLE [ IF EXISTS ] [ ONLY ] name [ * ]
    ALTER [ COLUMN ] column_name [ SET DATA ] TYPE data_type [ COLLATE collation ] [ USING expression ]
====== MySQL ======
[https://dev.mysql.com/doc/refman/8.4/en/alter-table.html#alter-table-redefine-column MySQL :: MySQL 8.4 Reference Manual :: 15.1.9 ALTER TABLE Statement]
* CHANGE: 改名と定義。MySQLの独自拡張。カラム名を常に2回書かないといけないが、改名と定義変更を同時に行える。
* MODIFY: Oracleとの互換用。定義のみ。カラム名1回のみ。単一カラムの定義変更用。
* RENAME Oracleとの互換用。COLUMN: 改名のみ。
* ALTER: デフォルト値の変更。
ALTER TABLE t1 MODIFY a TINYINT NOT NULL, CHANGE b c CHAR(20);
ALTER TABLE t1 RENAME COLUMN b TO a;
==== 削除 ====
[https://www.earthlink.co.jp/engineerblog/technology-engineerblog/2680/ DELETE文 TRUNCATE文 DROP文の違い(SQL構文) | 株式会社アースリンク]
テーブルのデータ削除の方法がいくつかある。
* DELETE: COMMITしてなければロールバック可能。WHEREで行を指定して削除。そのため多少時間かかる。AUTO_INCREMENT維持。
* TRUNCATE: 全削除して再作成。DELETEより速い。ロールバック不能。AUTO_INCREMENTもリセット。
* DROP: ロールバックも不能でテーブル構造も消える。AUTO_INCREMENTもリセット。
DELETE FROM table_name;
TRUNCATE TABLE table_name;
基本はTRUNCATEでOK。
==== AUTO_INCREMENT ====
[https://dev.mysql.com/doc/refman/8.0/ja/example-auto-increment.html MySQL :: MySQL 8.0 リファレンスマニュアル :: 3.6.9 AUTO_INCREMENT の使用]
レコードの挿入時に番号を自動で増分してくれる仕組み。DB側でIDの一意性を保証してくれるので、同時実行などをアプリ側で考えなくていいので使うと楽。
増分のルールがある。
* INSERT時にカラムを未指定または、値が0かNULLの場合に増分。
* 値は1から始まる。
* 値を指定してINSERTした場合、既存の行の最大値+1が次の値。
次の値を指定も可能。
  ALTER TABLE tbl AUTO_INCREMENT = 100;
=== Function ===
==== String ====
===== LPAD/RPAD =====
SQL-2023標準
LPAD(カラム, 桁数, 埋め文字)
RPAD(カラム, 桁数, 埋め文字)
===== LEFT/RIGHT =====
SQL-2023標準。
指定した文字数で切り取る。
===== CONCAT =====
* [https://www.oreilly.com/library/view/sql-in-a/9780596155322/re92.html Concatenation Operator - SQL in a Nutshell, 3rd Edition [Book]]
* [https://dev.mysql.com/doc/refman/8.0/ja/logical-operators.html MySQL :: MySQL 8.0 リファレンスマニュアル :: 12.4.3 論理演算子]
* [https://proengineer.internous.co.jp/content/columnfeature/6836#section200 MySQLで使える文字列結合方法 - プロエンジニア]
* [https://stackoverflow.com/questions/1373238/string-concatenation-operator-in-oracle-postgres-and-sql-server String concatenation operator in Oracle, Postgres and SQL Server - Stack Overflow]
カラム同士を結合したいことがある。いくつか方法がある。
* ||: 標準SQL (2008 5.2)。ただし、MySQLはデフォルトだとOR扱いで使用不能。PIPES_AS_CONCAT モードだとOK。
* CONCAT(): 非標準。MySQLなどデフォルトで||未対応なDBの場合こちら。
SELECT 'a' || 'b'
SELECT CONCAT(C1, C2) AS C1C2 FROM [table_name];
=== Other ===
==== Variable ====
SQL内で変数を使う構文が用意されている。
MySQLの場合。
* [https://dev.mysql.com/doc/refman/8.0/ja/user-variables.html MySQL :: MySQL 8.0 リファレンスマニュアル :: 9.4 ユーザー定義変数]
* [https://dev.mysql.com/doc/refman/8.0/ja/set-variable.html MySQL :: MySQL 8.0 リファレンスマニュアル :: 13.7.6.1 変数代入の SET 構文]
ユーザー変数という呼び名？
* 書式=@var_name。var_nameが変数名。
* 変数名の使用可能文字=には英数字_$が使用可能。加えて、<nowiki>''</nowiki>で囲めば (@'var-name') 文字には何でも使える。
* 制限=大文字小文字無視。最大64文字。
* スコープ=ユーザー変数はセッション固有。
変数割当
SET @var_name = expr [, @var_name = expr] ...
SET文で行う。=の他に:=も使える。なお、@var_name := exprでSETを省略することも可能だが、これは廃止予定。
ただ、短縮時に便利なことがある ([https://stackoverflow.com/questions/54979058/getting-values-from-previous-rows-using-user-defined-variable-in-mysql sql - Getting values from previous rows using user-defined variable in MySQL - Stack Overflow])。
SET @a = 1;
SELECT @a, @a:=@a+1;
SELECT内で割り当てと使用を同時にする場合、SETは構文エラーになる。それに、ユーザー変数を含む式の評価順序は未定義。やめたほうがいい。
ただし、直前の行を参照する場合などもこの構文を使うしかない。MySQL 8以上ならWIndow関数でこの構文は不要になる。MySQL 8未満の考慮が必要ならやむを得ない。
文を分けずに1文で変数定義と使用を同時に行う場合。FROMで定義するしかない ([https://qiita.com/kyamadahoge/items/fdbc04790f620dddbf3c MySQLでSQL一発でグループごとに連番を振る #MySQL8.0 - Qiita])。
SELECT
<nowiki> </nowiki> @rownum := IF(@prev_group_id = group_id, @rownum + 1, 1) AS rownum, -- 連番の列
<nowiki> </nowiki> history.id,
<nowiki> </nowiki> @prev_group_id := group_id AS group_id,
<nowiki> </nowiki> history.name,
<nowiki> </nowiki> history.created_at
FROM
<nowiki> </nowiki> history,
<nowiki> </nowiki> (select @rownum := 0) hoge,
<nowiki> </nowiki> (select @prev_group_id := <nowiki>''</nowiki>) fuga
ORDER BY
<nowiki> </nowiki> group_id,
<nowiki> </nowiki> created_at DESC
変数は定義しないと型が認識されなくて、値が更新されないらしい。
==== 階層テーブルの取得 ====
[https://medium.com/geekculture/sql-interview-question8-parent-child-relationship-use-case-of-self-join-4fe88146dd85 SQL Interview Question8: Parent Child Relationship-Use Case of Self Join | by Deeksha Singh | Geek Culture | Medium]
select a.son as grandson,
c.son as grandfather,c.dead_or_alive as dead_or_alive
from family_relation as a
left join family_relation as b on a.father=b.son
left join family_relation as c on b.father=c.son
こんな感じで、同じテーブルに対して、別名をつけてそれで連結して取得する。これで子と親を横に並べられる。
==== ピボットテーブル ====
行データの列データへの変換をピボットと呼ぶらしい。ピボットテーブルの作成という方法で実現できる模様。
いくつか方法がある。
# CASE文とGROUP BY句
# 独自拡張: PIVOT演算子、WITH (MySQL)
CASE文とGROUP BY句で実現するのが基本。
{| class="wikitable"
|+sales
!product_id
!month
!sale_amount
|-
|1
|Jan
|100
|-
|1
|Feb
|150
|-
|1
|Mar
|120
|-
|2
|Jan
|200
|-
|2
|Feb
|250
|-
|2
|Mar
|210
|}
SELECT
    product_id,
    MAX(CASE WHEN month = 'Jan' THEN sale_amount END) AS Jan,
    MAX(CASE WHEN month = 'Feb' THEN sale_amount END) AS Feb,
    MAX(CASE WHEN month = 'Mar' THEN sale_amount END) AS Mar
FROM
    sales
GROUP BY
    product_id;
{| class="wikitable"
!product_id
!Jan
!Feb
!Mar
|-
|1
|100
|150
|120
|-
|2
|200
|250
|210
|}
値が決まっているならこの方法でもいい。
==== 複数行データの複数列 ====
* [http://oraclemaster.boo.jp/%E5%90%8C%E3%81%98%E3%82%AD%E3%83%BC%E9%A0%85%E7%9B%AE%E6%AF%8E%E3%81%AE%E7%B8%A6%E8%A4%87%E6%95%B0%E8%A1%8C%E3%83%87%E3%83%BC%E3%82%BF%E3%82%92%E6%A8%AA%E8%A4%87%E6%95%B0%E5%88%97%E3%83%87%E3%83%BC/ 同じキー項目毎の縦複数行データを横複数列データに変換するSQL | オラクルマスター11g ブロンズ＆シルバー ～取得のためにすべきこと～]
* [https://qiita.com/UMA9626/items/4eef67d7c57e6cd89e6e 複数レコードを単一レコード・複数カラムにする #SQL - Qiita]
ピボットターブルに近いが、値が決まっていない場合の行の列への変換。
peopleテーブル    telテーブル
| id | name |    | id | people_id | tel          |
|----|------|    |----|-----------|---------------|
| 1  | hoge |    | 1  | 1        | 090-0000-1111 |
| 2  | fuga |    | 2  | 1        | 090-0000-2222 |
| 3  | foo  |    | 3  | 2        | 090-0000-3333 |
| 4  | bar  |    | 4  | 3        | 090-0000-4444 |
| id | tel1          | tel2          |
|----|---------------|---------------|
| 1  | 090-0000-1111 | 090-0000-2222 |
| 2  | 090-0000-3333 | null          |
| 3  | 090-0000-4444 | null          |
| 4  | null          | null          |
上記のように複数行を複数列にしたい。
考え方としては、階層テーブルの取得に近いと思われる。
SELECT *
FROM `cross_table` AS a
LEFT JOIN `cross_table` AS b ON a.括りオーナーコード = b.括りオーナーコード
limit 50
こんな感じで結合する。すると、重複するのが2行になってクロスに近い表示になる。ただし2個。ここからUNIQUEする？
{| class="wikitable"
|括りオーナーコード
|代表オーナーコード1
|代表オーナーコード2
|-
|1
|693
|693
|-
|2
|728
|728
|-
|3
|2676
|2676
|-
|5
|1357
|1357
|-
|6
|677
|677
|-
|7
|2675
|2675
|-
|8
|1187
|1187
|-
|9
|2231
|2231
|-
|10
|124
|124
|-
|11
|2486
|2486
|-
|12
|505
|505
|-
|13
|1677
|1677
|-
|14
|21
|21
|-
|15
|3567
|3567
|-
|16
|758
|758
|-
|17
|3167
|3167
|-
|18
|3404
|3404
|-
|18
|3404
|6412
|-
|18
|6412
|3404
|-
|18
|6412
|6412
|-
|19
|2295
|2295
|-
|20
|2968
|2968
|-
|21
|2771
|2771
|-
|22
|2414
|2414
|-
|23
|324
|324
|-
|24
|320
|320
|-
|25
|187
|187
|-
|26
|4043
|4043
|-
|27
|3613
|3613
|-
|28
|1853
|1853
|-
|29
|550
|550
|-
|30
|9
|9
|-
|31
|2639
|2639
|-
|31
|2639
|6413
|-
|31
|6413
|2639
|-
|31
|6413
|6413
|-
|32
|2946
|2946
|-
|33
|321
|321
|-
|34
|4262
|4262
|-
|35
|145
|145
|-
|36
|4270
|4270
|-
|37
|779
|779
|-
|38
|3383
|3383
|-
|38
|3383
|6414
|-
|38
|6414
|3383
|-
|38
|6414
|6414
|-
|39
|4076
|4076
|-
|40
|955
|955
|-
|41
|688
|688
|-
|42
|2011
|2011
|}
DISTINCTで列を指定して重複を除去するときれいになりそう。
SELECT DISTINCT a.括りオーナーコード, a.代表オーナーコード as 代表オーナーコード1 , b.代表オーナーコード as 代表オーナーコード2
FROM `楽楽販売_括りオーナーマスタ_楽楽販売_代表オーナーマスタ` as a
left join `楽楽販売_括りオーナーマスタ_楽楽販売_代表オーナーマスタ`  as b on a.括りオーナーコード = b.括りオーナーコード
limit 50
;
SELECT a.括りオーナーコード, a.代表オーナーコード, b.代表オーナーコード
FROM `楽楽販売_括りオーナーマスタ_楽楽販売_代表オーナーマスタ` AS a
LEFT JOIN `楽楽販売_括りオーナーマスタ_楽楽販売_代表オーナーマスタ` AS b ON a.括りオーナーコード = b.括りオーナーコード
GROUP BY a.括りオーナーコード, a.代表オーナーコード, b.代表オーナーコード
limit 50
これでうまくいく。
{| class="wikitable"
|括りオーナーコード
|代表オーナーコード
|代表オーナーコード
|-
|1
|693
|693
|-
|2
|728
|728
|-
|3
|2676
|2676
|-
|5
|1357
|1357
|-
|6
|677
|677
|-
|7
|2675
|2675
|-
|8
|1187
|1187
|-
|9
|2231
|2231
|-
|10
|124
|124
|-
|11
|2486
|2486
|-
|12
|505
|505
|-
|13
|1677
|1677
|-
|14
|21
|21
|-
|15
|3567
|3567
|-
|16
|758
|758
|-
|17
|3167
|3167
|-
|18
|3404
|3404
|-
|18
|3404
|6412
|-
|18
|6412
|3404
|-
|18
|6412
|6412
|-
|19
|2295
|2295
|-
|20
|2968
|2968
|-
|21
|2771
|2771
|-
|22
|2414
|2414
|-
|23
|324
|324
|-
|24
|320
|320
|-
|25
|187
|187
|-
|26
|4043
|4043
|-
|27
|3613
|3613
|-
|28
|1853
|1853
|-
|29
|550
|550
|-
|30
|9
|9
|-
|31
|2639
|2639
|-
|31
|2639
|6413
|-
|31
|6413
|2639
|-
|31
|6413
|6413
|-
|32
|2946
|2946
|-
|33
|321
|321
|-
|34
|4262
|4262
|-
|35
|145
|145
|-
|36
|4270
|4270
|-
|37
|779
|779
|-
|38
|3383
|3383
|-
|38
|3383
|6414
|-
|38
|6414
|3383
|-
|38
|6414
|6414
|-
|39
|4076
|4076
|-
|40
|955
|955
|-
|41
|688
|688
|-
|42
|2011
|2011
|}
ただ、この方法だと重複除去したい他の列を表示できない。工夫が必要。サブクエリを使うか、DISTINCTの他にGROUP BY+HAVING集約関数。ややこしい。
https://chatgpt.com/c/672b21a0-83f4-800b-8b29-afa1d0a57817
うまくいっていない。こういうデータが残って重複が残る。
{| class="wikitable"
|31
|2639
|2639
|-
|31
|2639
|6413
|-
|31
|6413
|2639
|-
|31
|6413
|6413
|}
GROUP BY HAVINGで<>で違うものを2列目に用意して、同じ場合は結合みたいな。だめ。2個以上あった場合に複数ヒットしてしまう。
括りオーナーの同一番号のところにグループの連番列を追加して、その連番1/2を使ってピボットテーブルにすればいける。
SELECT
括りオーナーコード,
MAX(CASE WHEN rownum = 1 THEN `代表オーナーコード` END) AS 代表オーナーコード1,
MAX(CASE WHEN rownum = 2 THEN `代表オーナーコード` END) AS 代表オーナーコード2
FROM (
<nowiki> </nowiki>  SELECT
`括り代表オーナーコード`,
@rownum := IF(@prev_group_id = `括りオーナーコード`, @rownum + 1, 1) AS rownum,
@prev_group_id := `括りオーナーコード` AS 括りオーナーコード,
`代表オーナーコード`
FROM `楽楽販売_括りオーナーマスタ_楽楽販売_代表オーナーマスタ`, (SELECT @rownum:=0) hoge, (SELECT @prev_group_id:=<nowiki>''</nowiki>) fuga 
ORDER BY 括りオーナーコード, 括り代表オーナーコード
) numbered
GROUP BY 括りオーナーコード;
{| class="wikitable"
|括りオーナーコード
|代表オーナーコード1
|代表オーナーコード2
|-
|1
|693
|NULL
|-
|2
|728
|NULL
|-
|3
|2676
|NULL
|-
|5
|1357
|NULL
|-
|6
|677
|NULL
|-
|7
|2675
|NULL
|-
|8
|1187
|NULL
|-
|9
|2231
|NULL
|-
|10
|124
|NULL
|-
|11
|2486
|NULL
|-
|12
|505
|NULL
|-
|13
|1677
|NULL
|-
|14
|21
|NULL
|-
|15
|3567
|NULL
|-
|16
|758
|NULL
|-
|17
|3167
|NULL
|-
|18
|6412
|3404
|-
|19
|2295
|NULL
|-
|20
|2968
|NULL
|-
|21
|2771
|NULL
|-
|22
|2414
|NULL
|-
|23
|324
|NULL
|-
|24
|320
|NULL
|-
|25
|187
|NULL
|-
|26
|4043
|NULL
|}
成功。


== phpMyAdmin ==
== phpMyAdmin ==
630行目: 1,957行目:


これで出力・表示すると使いやすい。
これで出力・表示すると使いやすい。
=== Import ===
[https://docs.phpmyadmin.net/en/latest/import_export.html Import and export — phpMyAdmin 5.1.4 documentation]
ネット上のSQLのサンプルを試したいことがある。
例: [https://qiita.com/kyamadahoge/items/fdbc04790f620dddbf3c MySQLでSQL一発でグループごとに連番を振る #MySQL8.0 - Qiita]
以下のようなSQLの実行結果を取り込んで自分でもサンプルを試したい。
mysql> select * from history;
+----+----------+--------------------+------------+
| id | group_id | name              | created_at |
+----+----------+--------------------+------------+
|  1 |        1 | Cupcake            | 2020-01-01 |
|  2 |        1 | Donut              | 2020-01-02 |
|  3 |        2 | Eclair            | 2020-01-03 |
|  4 |        2 | Froyo              | 2020-01-04 |
|  5 |        2 | Gingerbread        | 2020-01-05 |
|  6 |        3 | Honeycomb          | 2020-01-06 |
|  7 |        4 | Ice Cream Sandwich | 2020-01-07 |
|  8 |        4 | Jelly Bean        | 2020-01-08 |
|  9 |        4 | KitKat            | 2020-01-09 |
+----+----------+--------------------+------------+
9 rows in set (0.00 sec)
これをこのまま取り込む方法はない。スプレッドシートでCSVに変換して取り込む。
* 部分インポート: 行ヘッダーがある場合ここに1を指定。
* フォーマット: CSV
* カラムの区切り記号: デフォルトの,の他\tでタブも認識する模様。


== MySQL ==
== MySQL ==
640行目: 1,996行目:


=== Type ===
=== Type ===
==== About ====
[https://dev.mysql.com/doc/refman/8.0/ja/data-types.html MySQL :: MySQL 8.0 リファレンスマニュアル :: 11 データ型]
[https://dev.mysql.com/doc/refman/8.0/ja/data-types.html MySQL :: MySQL 8.0 リファレンスマニュアル :: 11 データ型]


647行目: 2,005行目:
* CHAR(M)/VARCHAR(M): M=格納最大文字数。CHARのM=0-255までで末尾はスペース。VARCHARはM=0-65535までで、可変長文字列で256以上もOK。末尾のスペースは削除される。先頭にプレフィクスの1-2バイトのデータを付与している。255バイト以下の文字列なら1バイト、256バイト以上なら2バイト追加。
* CHAR(M)/VARCHAR(M): M=格納最大文字数。CHARのM=0-255までで末尾はスペース。VARCHARはM=0-65535までで、可変長文字列で256以上もOK。末尾のスペースは削除される。先頭にプレフィクスの1-2バイトのデータを付与している。255バイト以下の文字列なら1バイト、256バイト以上なら2バイト追加。


==== Backup ====
==== AUTO_INCREMENT ====
0やNULLがカラムに設定される場合、DBで持っている増分値を使ってくれる設定。
 
AUTO_INCREMENTのカラムを2以上定義しようとすると以下のエラーになる。
there can be only one auto column and it must be defined as a key
 
* [https://mimirswell.ggnet.co.jp/blog-185 【MySQL】AUTO_INCREMENT属性カラムの制約 | ミーミルの泥泉]
* [https://qiita.com/nakamoto_yuki/items/69cf9ba26f1b651808af マリアDBでテーブル作成時のエラー「there can be only one auto column and it must be defined as a key mariadb」 #MySQL - Qiita]
 
AUTO_INCREMENT属性は、PRIMARY KEYかUNIQUE KEYがないとつけられないらしい。
 
[https://zenn.dev/ikeo/articles/9b4d2513c0528f MySQLでAUTO_INCREMENTが指定されているカラムに「0」を入れるには]
 
[NO_AUTO_VALUE_ON_ZERO] を指定しておくと、0の場合。増分値を使わない。
 
==== Cast ====
 
* [https://dev.mysql.com/doc/refman/8.0/ja/insert.html MySQL :: MySQL 8.0 リファレンスマニュアル :: 13.2.6 INSERT ステートメント]
* [https://dev.mysql.com/doc/refman/8.0/ja/type-conversion.html MySQL :: MySQL 8.0 リファレンスマニュアル :: 12.3 式評価での型変換]
* [https://dev.mysql.com/doc/refman/8.0/ja/cast-functions.html MySQL :: MySQL 8.0 リファレンスマニュアル :: 12.11 キャスト関数と演算子]
 
暗黙の型変換がある。
 
INSERTでは、式のデータ型とカラムのデータ型が不一致の場合にも型変換が発生する。
 
文字列から数値への変換では、文字列の先頭から有効な数字部分までを数値とみなす。
 
ただ、'00120'のような0始まりの数字も先頭の0を除去して数値とみなしてくれる。この根拠は見つけられなかった。
 
==== 数値型の属性 ====
 
* [https://qiita.com/mamy1326/items/74b040f855d4023b0178 MySQL 8.0 から int の ZEROFILL 指定が deprecated #MySQL8.0 - Qiita]
* [https://stackoverflow.com/questions/69858233/why-default-int11 mysql - Why default INT(11) - Stack Overflow]
* [https://dev.mysql.com/doc/refman/8.0/en/integer-types.html MySQL :: MySQL 8.0 Reference Manual :: 13.1.2 Integer Types (Exact Value) - INTEGER, INT, SMALLINT, TINYINT, MEDIUMINT, BIGINT]
* [https://dev.mysql.com/doc/refman/8.0/ja/numeric-type-syntax.html MySQL :: MySQL 8.0 リファレンスマニュアル :: 11.1.1 数値データ型の構文]
* [https://stackoverflow.com/questions/14573451/mysql-size-limits-to-integer-columns phpmyadmin - MySQL - Size Limits to Integer Columns - Stack Overflow]
 
MySQLは独自拡張で数値系のデータ型にint(9)のように表示幅を指定できる。DB上の表示幅なだけで実データには影響ない。それに、MySQL 8.0.17からdeprecated/廃止予定扱い。使っちゃダメ。
 
データ型で幅を指定しない場合、デフォルトの幅が内部的に存在する模様。
 
この幅は、その数値型の最小・最大値の符号を除去した桁数。
 
phpmyadminなど一部のクライアントがこの幅を勝手に使うので注意する。
 
=== Backup ===
  mysql -u username -p database_name <file.sql
  mysql -u username -p database_name <file.sql


661行目: 2,064行目:


PHP 7.1.16, 7.2.4 以降から対応しているらしい。それ以前だと設定が必要。
PHP 7.1.16, 7.2.4 以降から対応しているらしい。それ以前だと設定が必要。
==== Numeric value out of range: 1264 Out of range value for column ====
[https://qiita.com/mindwood/items/9a47dfabd0c5cebf1a62 "Out of range value for column 'カラム名' at 行番号" に対処した #MySQL - Qiita]
エラーコード 1264 は、データ型の範囲を超えた値をINSERTしたときに発生するWarningである。直前に実行したSQLのWarningは SHOW WARNINGS で確認できる。


=== Config ===
=== Config ===
700行目: 2,108行目:
モードの値としては、ANSIが重要。基本はこれでよい。
モードの値としては、ANSIが重要。基本はこれでよい。


==== Collation ====
[https://qiita.com/kazu56/items/6af85ffcf8d3954455ad 【MySQL】照合順序とは？ #MySQL - Qiita]
[https://dev.mysql.com/doc/refman/8.4/en/charset-collation-names.html MySQL :: MySQL 8.4 Reference Manual :: 12.3.1 Collation Naming Conventions]
utf8_general_ciのようなcollate (照合順序) がある。これの意味。
書式 [文字集合]_[言語名]_[比較法]
* 文字集合: character set
* 言語名: general=マルチリンガル。0900=UCA 9.0.0。
* 比較法: ai=アクセント無視 (はとぱは同一)/as=アクセント区別/ci=大文字小文字無視 (case ignore)/cs=大文字小文字区別 (case sensitive)/ks=カナ区別/bin=バイナリー。
===== default =====
mysqlのデフォルトはutf8_general_ci。
* general:
* unicode: 全角半角を区別しない。
絵文字の扱いなどが変わってくる。
* MySQL 8.0=utf8mb4/utf8mb4_0900_ai_ci ([https://dev.mysql.com/doc/refman/8.0/en/charset-server.html MySQL :: MySQL 8.0 Reference Manual :: 12.3.2 Server Character Set and Collation]/[https://dev.mysql.com/doc/refman/8.0/en/charset-mysql.html MySQL :: MySQL 8.0 Reference Manual :: 12.2 Character Sets and Collations in MySQL])
* MySQL 5.7=latin1/latin1_swedish_ci ([https://dev.mysql.com/doc/refman/5.7/en/charset-server.html MySQL :: MySQL 5.7 Reference Manual :: 10.3.2 Server Character Set and Collation])
* MySQL 5.7=utf8mb4/utf8mb4_general_ci ([https://dev.mysql.com/doc/refman/5.7/en/charset-mysql.html MySQL :: MySQL 5.7 Reference Manual :: 10.2 Character Sets and Collations in MySQL])
===== utf8mb4_bin/utf8mb4_0900_bin =====
* [https://blog.siwa32.com/mysql_collation/ MySQL8のutf8mb4におけるCollation比較 - BLOG - siwa32.com]
* [https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-17.html MySQL :: MySQL 8.0 Release Notes :: Changes in MySQL 8.0.17 (2019-07-22, General Availability)]
utf8mb4_binは終端スペースを除去する。0900は除去しない。
utf8mb4_binは照合時に先頭に0を追加する場合があるが、0900はそれがなく、0900のほうが並べ替えがはるかに高速。らしい。


基本はutf8mb4_0900_binを使うとよさそう。
[[Category:IT]]
[[Category:IT]]

2024年11月12日 (火) 12:18時点における最新版

Other

Performance

SQL vs. app

第5回　DB側でやること、アプリ側でやることを見極めよう | gihyo.jp

DB側ですべき処理とアプリ側ですべき処理の見極め。

ネットワーク上でデータの受け渡しがあるので、ソート以外はSQLですべてしたほうがいい。

コンピューターが処理することに変わりはなく、

カラム順序

テーブルのカラム・列の順序。これに意味があるのか？カラムを追加する場合などに影響がある。

パフォーマンスに影響がある。特に下2個の情報が参考になる。

基本的に、頻繁に使用する列 (主キー、外部キー、頻出検索、頻出更新の順) を先頭に配置する。ただし、null許容は全体的に後ろ。null許容の中でも頻度順にする。

ディスクスペースの利用率、使用するデータの効率などに影響がある模様。

マスター

マスタデータって何？マスタデータについて詳しく解説！ - J-MDM／Snowflake

ユーザー、顧客や拠点の情報など、ベースとなるデータのこと。

マスターの他に、トランザクションというのもある。これは、売買などシステムや業務の稼働に伴って生じるデータ。

【データベース正規化完全ガイド】実務観点での正規化手順やポイントを詳しく解説   | PrAhaENGINEERLAB

トランザクションテーブルで、マスターテーブルを直接参照するのはまずい。

マスターテーブルを更新したら、トランザクションの情報も変わってしまうから。

登録時にマスターテーブルのデータをコピーしていれる。

Sharding

シャーディングとは - 意味をわかりやすく - IT用語辞典 e-Words

シャーディング。DBの負荷分散の手法。

データをレコード・行単位で水平分割して、複数のデータベースサーバーに分散して記録する。

分散したDBの単位をシャードと呼んでいる。データが増大しても台数を増やせば問題ない。

ただし、どのデータがどのサーバーで記録しているかを把握する必要がある。ハッシュ値などから計算する。

親子関係

親子関係、ツリー構造、階層構造を表現したいことがある。

１つのテーブルで親子関係を管理する方法

基本的に、親コードのカラムをどこかのテーブルに持たせて対応させる。

  • 隣接リスト (Adjacency List): 親子関係が再帰的になっている場合、途中データの削除はやっかいになる。親子の取得が少々複雑で何回もSQLが必要になり厄介。階層構造が2-3階程度なら問題ない。
  • 経路列挙:

Standard

ISO/IEC 9075 - Wikipedia

ISO/IEC 9075がSQLの標準。標準外のSQL/関数は独自実装なので扱いには注意が必要。標準のみの使用が望ましい。

ANSI SQLと呼んだりするらしい。

制約

3のキー制約がある。

  1. NOT NULL。
  2. UNIQUE制約: ユニークキー NULL許容
  3. PRIMARY KEY制約: プライマリーキー非NULL。UK AND NOT NULL相当。
  4. FOREIGN KEY制約: 外部キー非NULL。
  5. CHECK制約

Error

SQLSTATE[01000]: Warning: 1265 Data truncated for column

ALTER TABLEでデータ型を変更して、文字数を切り詰めるときに、元データがあって、切り詰められないときに出るエラー。

Transfer

import

主キーが文字列型のテーブルに、外部からインポートする場合、主キーの生成方法が問題になる。

データを削除して取り込む想定なら、自分で連番を作ればいい。SQLで生成してもいい。

COUNT/ROW/ROWS/ROWNUM/ROW_NUMBERなど。

ROW_NUMBERが使えないなら変数で行えるらしい (MySQLのSELECT結果に行番号を振る。 #mariadb - Qiita)。

Design

ID

DB ID

DBのIDの採番規則。非常に重要。全体のパフォーマンスにも影響する。

Instagramの方式が、後発なだけあって優れているように感じる。

Sharding & IDs at Instagram. With more than 25 photos and 90 likes… | by Instagram Engineering | Instagram Engineering

  1. 生成されたIDは時間順に並べ替え可能である必要がある(例えば、写真 ID のリストは、写真に関する詳細情報を取得せずに並べ替えることができます)
  2. ID は理想的には 64 ビットである必要があります (インデックスが小さくなり、Redis などのシステムでより優れたストレージを実現するため)
  3. システムには、新しい「可動部分」をできるだけ少なく導入する必要があります。私たちが非常に少数のエンジニアで Instagram を拡張できたのは、信頼できるシンプルでわかりやすいソリューションを選択したことが大きな理由です。

Instagramは独自の計算式でIDを算出している。

日時、ユーザーID、自動増分値。

この3要素で構成して算出している。これでバッティングせずに時間ソートで64ビットで収まる。よく考えられている。

議論

IDとコード

コードは広い概念。

IDはコードの内、一意なもの。

Naming

テーブルの主キーの命名規則にいろいろ議論がある。

ケース1=idとすべきか、ケース2=table_idとすべきか。それぞれ言い分がある。

  • ケース1=id
    • 主キー列、外部キー列が明確。
    • テーブル名の反復がなくDRY原則に従う。
  • ケース2=table_id
    • USINGで指定可能。
    • 複数テーブルで同じ列を同名にできる。

ケース1がいいと思う。テーブル名で十分コンテキストがある。ケース2を採用するならば、全部の列にテーブル名を前置しないと一貫性がない。

USINGはシンプルな結合しかできない。ONは条件を増やせる。

正規化

データの重複をなくし、整合的にデータを取り扱えるようにデータベースを設計すること。

第1-5、ボイスコッド正規形がある。一般的に用いられるのは1-3。

非正規形

正規化されていない状態のテーブル。具体的には、1セルに複数のデータが入っている状態。

学生テーブル
学生ID 学生名 コース名 教授名
1 山田太郎 サッカー、軽音 山田、鈴木
2 菊池太郎 野球 山田
3 川上太郎 バレーボール 山根

例えば、所属サークルに [サークル、軽音] の2個のデータが入っている。これがまずい。

第1正規形

「1つのフィールドには1つの値しか入らない」状態。

非正規形から第一正規形にするには、行でわけるか、列で分けるかの方法がある。

そのままやると、問題がある。行で分けると、主キーが2重になる。列だと大量のNULLセルが生まれる。

行で分けると以下のようになる。

学生テーブル
学生ID 学生名 コース名 教授名
1 山田太郎 サッカー 山田
1 山田太郎 軽音 鈴木
2 菊池太郎 野球 山田
3 川上太郎 バレーボール 山根

主キーが重複するが、ひとまず第一正規形にはなった。

重複がある場合、以下の2パターンがある。

  1. 似たカラムが複数 (TEL1/TEL2など)。
  2. 1セルに複数の値 (カンマ区切りなど)。

行に分けることがポイント。

第1正規形じゃないと、拡張できなくて困る。カラムを増やす場合、テーブル定義の変更が都度必要になる。

第2正規形

主キーの一部への依存関係をなくす。「部分関数従属性をなくす」。

片方にしか依存しない値を見つけ、それを専用のテーブルに移動する。

例えば、上記の学生テーブルだと、[学生ID/学生名] が一つの塊で、コース名は別。これらの塊は以下の依存関係がある。

  • 学生IDがキーで、学生名がIDに従属している (学生IDで学生名を特定できる)。
  • コース名だけでコース名と教授名を特定できる。コースID列を追加しても同じこと。

これを3のテーブルに分割する。

  1. ブロックごとのテーブルを作る。
  2. 片方のテーブルに、片方のIDを残す。
学生テーブル
学生ID 学生名
1 山田太郎
2 菊池太郎
3 川上太郎
コーステーブル
コースID コース名 教授名
1 サッカー 山田
2 軽音 鈴木
3 野球 山田
4 バレーボール 山根
関連テーブル、学生詳細テーブル
学生ID コースID
1 1
1 2
2 3
3 4

残ったIDだけのテーブル。関連テーブル、中間テーブルと呼ぶ。IDを片方に残して、複合主キーとすることもできる。

第２正規形の解決課題。

  1. 片方のデータだけ登録できない。
  2. データの不整合が簡単に発生する。

第2正規化を行ったことで、複合主キーが生まれる (「第二正規化」は、複合キーが存在するテーブルのみで行うのでしょうか)。

マスターテーブルじゃないなら、複合主キーも悪くないか。複合主キーを使わないなら、代理キーでIDを生成するだけだから。

この関連テーブルには注意が必要。実装の都合で複合主キーではなく、代理キーを主キーにするなら、複合ユニーク制約をつける「中間テーブルに複合ユニーク制約をつける理由 #SQL - Qiita」。そうしないと、同じデータが登録できてしまう。

第3正規形

「主キー」ではない項目への依存関係をなくす。推移関数従属性をなくす。

例えば、コーステーブルは、コース名に教授名が従属している。学生IDをコーステーブルに残したら、本来コースIDだけで決まるのに余計な学生IDが残る。

これを専用テーブルにする。

  1. 従属部分を別テーブルに移動する。
  2. それぞれにレコードIDを付与する。
  3. どちらかのテーブルに分離したテーブルのIDを付与する。

例だと、コースIDではないコース名に教授名が従属している。例えば、コースID=1/3はコース名が異なるが、同じ教授名になっており、教授名はコースIDには従属していない。

コーステーブル
コースID コース名 教授ID
1 サッカー 1
2 軽音 2
3 野球 1
4 バレーボール 3
教授テーブル
教授ID 教授名
1 山田
2 鈴木
3 山根

Naming

命名規則

DBスキーマの命名規則はあまりない。Yiiの規約がけっこうしっかりしている。

  • 複数形ではなく単数形でデータベースのテーブルに名前を付ける
  • フィールド名の前にDBテーブル名を追加しない
  • モデルクラス名にテーブルプレフィックスを含めない
  • テーブルのID列に「id」という名前を付けます
  • 意味のある主キー名を避ける
  • データベーススキーマで外部キーの関係を定義する
  • 「id」で終わる外部キーフィールドに名前を付ける
  • 単数形/複数形の性質を反映する名前関係を行う

Convention

List

いくつかSQLのコーディング規約がある。

大文字小文字

予約語以外、基本的に全部小文字で、複数単語はハイフン区切りにしたほうがいいらしい。

構文強調がないと、formなどと見分けがつきにくいので、大文字にしたほうがいい。可読性優先。

Data

enum/string

区分系データを数字で持つか、文字列で持つか。

文字列だとわかりやすい。数字で持つと、意味の把握にカラム定義など、別の資料を確認する必要がある。アプリ側で表示に毎回そのマップや変換が必要になる。

数値で必要ならマスターの主キーにする。基本は文字列でいいと思う。

画像データのパス/BLOBでの格納

画像のファイルサイズが1MB以下の場合は問題ない。が、それ以外だと問題があるので、ファイルパスで扱うのがいいらしい。

NULL

Ref: NOT NULL 制約をつけるべきカラムとは #PostgreSQL - Qiita.

絶対に必要な項目にNOT NULL制約をつける。NULLを許容する列は、最後のほうに集めると、ストレージなどの効率がいい。

例えば、取得に失敗した場合に、値をどうするのか？などを考えるとよい。

int/string

データベースのカラムでIDやコード類を定義する場合、型をintなどの数値系、charなどの文字列系にするかの議論がある。

  • WHEREの範囲、算術計算など数値計算が伴うなら数値。データを何に使うかの意味論が重要。
  • 数値のほうが高速。
  • 郵便番号のように先頭が0つく場合、文字列。
  • 数値だと、auto incrementで登録時の重複除去を保証できる。
  • 値を変更する可能性がないなら数値。
  • 文字列型だと文字数の検討が必要。

INT/BIGINT

型

  • TINYINT
    • 符号付き: -128から127まで
    • 符号なし: 0から255まで
    • 1バイト（8ビット）を使用
  • SMALLINT
    • 符号付き: -32,768から32,767まで
    • 符号なし: 0から65,535まで
    • 2バイト（16ビット）を使用
  • MEDIUMINT
    • 符号付き: -8,388,608から8,388,607まで
    • 符号なし: 0から16,777,215まで
    • 3バイト（24ビット）を使用
  • INT
    • 符号付き: -2,147,483,648から2,147,483,647まで
    • 符号なし: 0から4,294,967,295まで
    • 4バイト（32ビット）を使用
  • BIGINT
    • 符号付き: -9,223,372,036,854,775,808から9,223,372,036,854,775,807まで
    • 符号なし: 0から18,446,744,073,709,551,615まで
    • 8バイト（64ビット）を使用

int型は4バイトでsignedなら2,147,483,647 (21億) が最大値。1日1万件増加するとして588年。10万件で58年。増加追加量次第だが、中小規模なら通常はint型で問題ない。

BIGINTなら、1日100万件で25269512429年。BIGINTならほぼ問題ない。

ただ、BIGINTはINTの2倍のサイズになるのでストレージも消費する。よく考えておく。

BIGINTでも足りない場合、プライマリーキーを文字列にして、UUIDやhash値を使う。

NULLと空文字

住所などのようなテキスト系のデータで、NULLを許容するべきか？空文字とNULLで意味を区別すべきか？

NULLと空文字の意味論

  • NULL=値がない、何であるか不明な状態。わからないという状態。
  • 空文字=値がわかっていて、空なことが分かっているという状態。

例えば、人名のミドルネームの扱いがある。

  • ミドルネームなし=''
  • ミドルネームの有無不明=NULL

これを区別するにはNULLしかない。空の文字列は値。

NULLがないほうがSQLはシンプルになる。が、値が空なのか、不定なのか判別できない。判別したいなら、別のカラムが必要になる (分析用途のテーブルにnullはいらない)。

[カラム] なしと不明を区別する必要があるかどうか？不明=なしで問題ないなら空文字でいいと思われる。実務上違いがない。

文字列型なら空文字でいいが、数値型ならNULLを使うしかない。

データの要件を考えて判断する。基本は、NULLは使わない。文字列の選択肢なども。

ただ、他の日付などは空=NULLにするしかない。他のデータ型と合わせるなら、空=NULLでもいい気がする。

NULL

第8回　NULLって何？

そもそもNULLを理解する必要がある。

NULLは、「値が入っていない、値なし、値不明」を意味する。

空文字は「空という値が入っている」とみなす。値なしとは意味が違う。

VARCHARの文字数定義

varcharで最大文字数を定義できる。この最大文字数の考え方。

DB上は最大文字数は、定義するだけだとあまり意味がない。実際に使用された文字数でデータがカウントされる。varchar(2)でもvarchar(255)でも同じ。

ただし、varchar(255)までは1バイトだが、256文字目からは長さプレフィックスに2バイト使用する。なので、不必要に256文字のカラムにしない。

VARCHARの長さは、技術的・物理的なものというより、一種の制約、ビジネスルールとして考えたほうがいい。

無駄に長い文字数を許容すると、アプリ側でその幅を考慮必要になる。UI上の制約になるので注意したほうがよく感じる。

外部キー制約

整合性制約の有無によるINSERTの速度差 - kagamihogeの日記

外部キー制約をいれると、データの整合性が保証できる。代わりに速度が落ちる。ないほうがいいか。

Relation

多対多

原則として中間テーブルを使う。

中間テーブルなしでやる場合、1列に複数レコードを入れるしかない。PostgreSQLなどの配列、JSON、カンマ区切り文字列。やらないほうがいい。

2個のテーブルにそれぞれのID列をもたせるだけでは正しく管理できない。例えば以下の問題が生じる。

  1. データの重複: それぞれに相手のID列を渡す必要があり、同じデータを何度も追加必要になる。
  2. 管理の煩雑: あるIDを削除する場合、もう片方のテーブルにもIDが残る。
  3. 性能: 両方のテーブルの更新が必要で性能が低下する。

中間テーブルの命名規則。

2のテーブルを_でつなげる。2テーブルの関係が明確なら関係の名前をつけるといいが、これは考えるのが難しい。

多対多のデータの登録画面の実装方法は2種類。

追加ボタンで関連を追加できるようにするか、チェックボックス、ドロップダウンリストで複数選択できるようにする。

ポリモーフィック

1個のテーブルで複数のテーブルに関連させるパターン。

画像テーブルを、投稿テーブルとプロフィールテーブルに関連させる場合。

1テーブルだけなら、画像テーブルにプロフィールテーブルの外部キーを持たせれば済んでいた。これを1テーブルで投稿テーブルにも対応させるパターン。

外部キー列に追加してテーブル名の列も追加する。これで複数テーブルを特定する。一見柔軟だが、外部参照制約をつけられなかったり、SQLが複雑になるなどの欠点がある。SQLが複雑になるということは性能も悪くなる。

ポリモーフィック関連を使わなくても、中間テーブルを使って親子を関連付けることはできる。ただし、テーブル数は増える

  • 交差テーブル: 1レコードが共通で呼ばれる場合に対処できない。
  • 共通の親テーブル: 親idを全部で持つ。1レコードが共通で呼ばれる場合に対処できない。

親テーブルを持つパターンが理にかなっている気がする。オブジェクト指向の継承になっており、余計なテーブル数も少なくシンプル。

ポリモーフィック関連は使わないほうがよさそう。

Function/Operator

MySQL :: MySQL 8.0 リファレンスマニュアル :: 12 関数と演算子

Flow control function

表 12.7 「フロー制御演算子」

名前 説明
CASE CASE 演算子
IF() If/else 構文
IFNULL() Null if/else 構文
NULLIF() expr1 = expr2 の場合に NULL を返します

CASEとIFはSQL文としても同名が存在する。ただし、使うタイミングがSQL文だとストアドプロシージャであまり使うことはない。演算子と関数をよく使う。

SQL

DML

DML (Data Manipulation Language)。データ操作用言語。

INSERT/SELECT/UPDATE/DELETEが該当。

DELETE

DELETE文（SQLを基本から学ぶシリーズ） 

DELETE FROM [table] [条件]

構文がシンプル。

INSERT

basic
INSERT INTO テーブル名 (列名1, 列名2,...) VALUES (値1, 値2,...), (), ();

INSERT INTO テーブル名 VALUES (値1, 値2,...);

列名と値の順番があっていれば、順番はテーブルカラム定義の順番じゃなくてもOK。カラム名を省略した場合、全カラムが定義の順番で必要。

基本はカラム名を指定したほうがいい。

VALUESの後、,()で複数レコード一度に追加できる。

INSERT文に（SELECT結果＋入力結果）を入れる #MySQL - Qiita

SELECTの結果を値に使うこともできる。その場合、構文が若干異なる。 

INSERT INTO `テーブル名１` (col1, col2, ...)
  SELECT col1, col2, ... FROM `テーブル名２`

VALUESが不要。

例えば、データベースに登録されていない商品をIDが重複しないように、データを追加したい場合だとこんな感じに書きます。 

INSERT INTO `テーブル名` (id,item_name)
  SELECT MAX(id)+1,"商品名" FROM `テーブル名`
bulk

PHP/MySQL でレコードを N 件ずつバルクインサート | Born Too Late

一括INSERTする場合、一度の件数が大きすぎると問題がある模様。

1000件ずつくらいに分割するといいらしい。

UPDATE

Basic

UPDATE文（SQLを基本から学ぶシリーズ） 

UPDATE (表名) SET (カラム名1) = (値1) WHERE (条件);
UPDATE (表名) SET (カラム名1) = (値1), (カラム名2) = (値2) WHERE (条件);

一括で更新する。

別のテーブルで更新

UPDATE文（SQLを基本から学ぶシリーズ）

2種類の方法がある。 

UPDATE employees SET employees.manager_name =
( SELECT manager.manager_name FROM manager
  WHERE employees.emp_no = manager.emp_no );

服問い合わせ、サブクエリー。 

UPDATE employees JOIN manager
ON employees.emp_no = manager.emp_no
SET employees.manager_name = manager.manager_name

JOIN。JOINのほうがシンプルかもしれない。

UPSERT

Ref: 【総まとめ】UPSERTとは？DBごとの違いは？ | 初心者DIYプログラミング入門.

INSERTするかUPDATEするか、一度SELECTで値の有無を確認してから本来しないといけない。SQLを2回実行必要で、処理が無駄になるので、これを1回で行う。これがUPSERTと呼ばれるもの。ただし、UPSERTというSQLがあるわけではない。MERGE文が標準だが、実装していないものがあって、DBごとの独自拡張になる。

MySQL
INSERT ... ON DUPULICATE KEY UPDATE

MySQLにMERGE文はない (sql - Is merge statement available in MySQL - Stack Overflow)。代わりにINSERT ... ON DUPULICATE KEY UPDATEという文を使う。

「sql - What are practical differences between `REPLACE` and `INSERT ... ON DUPLICATE KEY UPDATE` in MySQL? - Stack Overflow」によると、REPLACE文も使えるが、内部的に一度削除して、副作用があるので、INSERT ... ON DUPULICATE KEY UPDATEがいい模様。

MySQL :: MySQL 8.4 Reference Manual :: 15.2.7.2 INSERT ... ON DUPLICATE KEY UPDATE Statement 

INSERT INTO t1 (a,b,c) VALUES (1,2,3)
  ON DUPLICATE KEY UPDATE c=c+1;

複数レコードを一括で更新したい場合。「MySQLでbulk insert + on duplicate key updateしたい - oinume journal」VALUESを使う。 

INSERT INTO users VALUES
  ('akuwano', 15), ('oinume', 14), ('oranie', 13)
  ON DUPLICATE KEY UPDATE age = VALUES(age);

MySQL insert row on duplicate key update multiple columns - Stack Overflow 

INSERT INTO tb (name, date, stat1, stat2, stat3)
VALUES (?, ?, ?, ?, ?)
ON DUPLICATE KEY UPDATE stat1 = stat1 + VALUES(stat1), stat2 = stat2 + VALUES(stat2), stat3 = stat3 + VALUES(stat3)

複数列指定する場合、列名 = values(列名), で複数列挙する模様。

AUTO_INCREMENT

MySQLのINSERT ... ON DUPULICATE KEY UPDATEは扱いに注意が必要。AUTO_INCREMENTがあると、更新でも増加する。

基本的に最初にINSERTを実行するから。

挙動として、一度のINSERT ... ON DUPULICATE KEY UPDATE中は連番が保証される。終わったらUPDATE回数分AIが増分される。

大量更新前提のテーブルの場合、増分値がすごい勢いで増加する。いくつか対策がある。

  • INSERT ... ON DUPULICATE KEY UPDATEを使わず、昔ながらのSELECTの結果を見てINSERT/UPDATEする。
  • AUTO_INCREMENTをそもそも使わない、idを使わない削除する。
  • idをbigintにする。
  • ストレージエンジンをInnoDBではなくMyIsamを使う。

最初2個が回答になっている。実行中は連番なので、ON DUPLICATE KEY UPDATEの使用後に、ALTER TABLEでAUTO_INCREMENTの初期値をリセットする。 

INSERT INTO `table_blah` (`the_col`) VALUES("the_value")
ON DUPLICATE KEY UPDATE `the_col` = "the_value";

SET @NEW_AI = (SELECT MAX(`the_id`)+1 FROM `table_blah`);
SET @ALTER_SQL = CONCAT('ALTER TABLE `table_blah` AUTO_INCREMENT =', @NEW_AI);
PREPARE NEWSQL FROM @ALTER_SQL;
EXECUTE NEWSQL;

これでOK。

なお、実行中の連番は注意が必要。1回のSQL内で同じ行に重複してINSERT/UPDATEしている場合、SQL実行中にAIが増加する。重複していなかったら連番。SQL実行前に事前に重複を除去したらいい。SQLの用意に工夫が必要。

UNIQUE

UPSERTで挿入する際のUNIQUE制約には注意が必要。複合UNIQUEでNULLを許容する場合、NULLは固有扱いになるので、実質重複キーが生まれる。回避したければ。

  1. 複合UNIQUEにはNOT NULL
  2. 文字列結合、ハッシュなど算出可能な参照用のカラムを追加する。
注意すべき挙動の整理

AUTO_INCREMENTとUNIQUE制約に由来する注意すべき挙動がある。(MySQL 5.7で確認。

  1. PKの指定がなく、UNIQUEで行特定の場合、PKがAIで更新→更新ならPKは常に必要。事前にSELECTでPKを取得しておく。
    1. 重複を削除するので、そもそもUNIQUE制約は不要かもと思ったが、重複は1SQL内で複数ある場合の話。重複が生じるのでUNIQUEは必要。
  2. UPDATE発生時、UPSERT後にAIがUPDATE回数分増加→UPSERT後にAIを最大値で初期化。
  3. UPDATE発生時、実行中にもAI増加→PKなしの複数UPDATE時はUPSERT実行前のSQLで一番最後を残して重複レコード除去。
  4. マスター系テーブルで固有でINSERTする場合、重複除去のためにNOT NULL+UNIQUE制約をつけておく。
    1. NULL != NULLが成立するので、UNIQUE制約をつける場合はNOT NULLもつけておく。

GROUP BY

Basic

【SQL入門】複数のデータをグループ化するGROUP BYの使い方 | 株式会社AMG Solution

テーブルの値でデータをグループ化する句。

基本構文 

SELECT 要素名 FROM テーブル名 GROUP BY グループ化したい要素名;

SELECT部分でGROUP BYで指定したカラム名の指定が必須？

GROUP BYで指定したカラム以外は、集約関数を使わないと選択できない。テキストのように集約不能な場合でも。その場合は集約の意味はない。

連番

MySQLでSQL一発でグループごとに連番を振る #MySQL8.0 - Qiita

MySQL 5.7だと変数で実現。 

mysql> select * from history;
+----+----------+--------------------+------------+
| id | group_id | name               | created_at |
+----+----------+--------------------+------------+
|  1 |        1 | Cupcake            | 2020-01-01 |
|  2 |        1 | Donut              | 2020-01-02 |
|  3 |        2 | Eclair             | 2020-01-03 |
|  4 |        2 | Froyo              | 2020-01-04 |
|  5 |        2 | Gingerbread        | 2020-01-05 |
|  6 |        3 | Honeycomb          | 2020-01-06 |
|  7 |        4 | Ice Cream Sandwich | 2020-01-07 |
|  8 |        4 | Jelly Bean         | 2020-01-08 |
|  9 |        4 | KitKat             | 2020-01-09 |
+----+----------+--------------------+------------+
9 rows in set (0.00 sec)

SELECT
  @rownum := IF(@prev_group_id = group_id, @rownum + 1, 1) AS rownum, -- 連番の列
  history.id,
  @prev_group_id := group_id AS group_id,
  history.name,
  history.created_at
FROM
  history,
  (select @rownum := 0) hoge,
  (select @prev_group_id := '') fuga
ORDER BY
  group_id,
  created_at DESC

+--------+----+----------+--------------------+------------+
| rownum | id | group_id | name               | created_at |
+--------+----+----------+--------------------+------------+
|      1 |  2 |        1 | Donut              | 2020-01-02 |
|      2 |  1 |        1 | Cupcake            | 2020-01-01 |
|      1 |  5 |        2 | Gingerbread        | 2020-01-05 |
|      2 |  4 |        2 | Froyo              | 2020-01-04 |
|      3 |  3 |        2 | Eclair             | 2020-01-03 |
|      1 |  6 |        3 | Honeycomb          | 2020-01-06 |
|      1 |  9 |        4 | KitKat             | 2020-01-09 |
|      2 |  8 |        4 | Jelly Bean         | 2020-01-08 |
|      3 |  7 |        4 | Ice Cream Sandwich | 2020-01-07 |
+--------+----+----------+--------------------+------------+
9 rows in set (0.00 sec)

MySQL8でROW_NUMBERが使えるならもっと簡単。 

SELECT
  ROW_NUMBER() OVER(PARTITION BY group_id ORDER BY group_id, created_at DESC) AS rownum,
  id,
  group_id,
  name,
  created_at
FROM
  history
ORDER BY
  group_id,
  created_at DESC

SELECT

MySQL :: MySQL 8.0 リファレンスマニュアル :: 13.2.10 SELECT ステートメント

JOIN

外部テーブルと外部キーで結合の方法がいくつかある。

  • INNER JOIN: AND。両方のテーブルにある項目のみ。
    • SELECT ... FROM ... INNER JOIN テーブル名 ON 項目A = 項目B
  • OUTER JOIN: OR。片方がなくても。
    • LEFT OUTER JOIN: 左を基準。右側はNULLありえる。
    • RIGHT OUTER JOIN: 右を基準。左側はNULLありえる。
  • CROSS JOIN: 全組み合わせ。
  • NATURAL: 同名項目を取得。
  • UNION/UNION ALL: 2以上のSELECT結果の結合。

他にWHEREで結合する方法がある。

結合すると、同名カラムは上書きされる。必要なら、select column as column_nameで別名をつける。

結合する場合、同じ値だから通常は問題ない。

列名が重複する場合の対応

addSelectで列名をASで地道に追加する。これが確実。プリペアードステートメントなどを使うと、プログラム側でできなくはない。

【Laravel】目的別クエリビルダの記述方法まとめ（記事を書く前の整理用）

結合したテーブルに同じ名前のカラムがあっても正しく取り出す方法【MySQL+PHP】 - りまねどっとねっと

PHP PDOだったら列番号で取得するオプションなどがあるのでそれで対応できる。

他にGroupConcatで文字列で結合する。

SQL select join: is it possible to prefix all columns as 'prefix.*'? - Stack Overflow

ダミーの空列を追加すれば、処理はしやすい。が、そもそも同じ列名を取得できるのか？

同じテーブルを複数回Joinしたい場合 #MySQL - Qiita 

SELECT * FROM table
INNER JOIN join_table AS join1
ON table.id = join1.id
INNER JOIN join_table AS join2
ON table.another_id = join2.another_id

ASで別名をつける。

JOINとWHERE

内部結合の場合、結果も速度も同じ。

ただ、外部結合はJOINしかできない。

JOINだと、テーブルと結合条件が一か所にまとまるためわかりやすい。 

SELECT * 
FROM table1, table2 #←結合させたいもの
LEFT OUTER JOIN (
    # 副問い合わせとか長いSQLが入ると想定
) AS sub
ON table1.id=sub.id AND table2.id=sub.id
WHERE table1.id=table2.id #←結合条件

SELECT * 
FROM table1
INNER JOIN table2 ON table1.id=table2.id #←結合させたいものと結合条件が一緒
LEFT OUTER JOIN (
    # 副問い合わせとか長いSQLが入ると想定
) AS sub
ON table1.id=sub.id

複数テーブルが登場するなら、基本はJOINがよいだろう。


information schema

Information schema - Wikipedia

DBのテーブル情報を格納する特殊なテーブルが [information schema]。

標準化されているが、SQLiteやOracle Databaseなどで未対応。

MySQL/PostgreSQLなどでは対応されている。が、SQLほど標準化されていないので扱いには注意。

「Why doesn't SQLite support INFORMATION_SCHEMA? - Quora」にあるように、SQLiteのような軽量な実装にすると、このメタデータのテーブル実装はコストが大きいのだと思われる。

VIEW

SELECT文の結果に別名をつけるようなこと。

何回も呼び出すSELECT文を省略でき、シンプルにできる。また、テーブルを直接使わないこととで、機密情報へのアクセスを制御できる。

  • SELECT文なので、ストレージを消費しない。
  • SELECT文呼び出しなので、複雑だと時間がかかる。



一括更新

Ref: MySQLで複数行を一括更新するクエリの実装方法.

ELT/FIELDを併用すれば一括更新可能とのこと。

NULLを含むLIKE

sql - like '%' does not accept NULL value - Stack Overflow

【SQL】NULL値を制御/SQLマスターへの道「COALESCE」 #初心者 - Qiita

LIKEで文字列検索する際、SELECT * FROM DB WHERE <column> LIKE '%%';であいまい検索してもNULLの場合ヒットしない。 

where COALESCE([table].[column],'') like '<parameter>'
IFNULL([table].[column], '') like '%'

COALESCEかISNULLで引数に空文字を指定するとうまくいく模様。

COALESCEが標準なのでこれを使う。

COALESCE (コアリース) は引数のリストから、最初の非NULLを取得する。NULLを含むデータの処理に便利。

ISNULL(<value>, <orValue>) はCOALESCEの引数2個版。理由がないならシンプルなISNULLを使うとよい。

同じ名前の関数で、動きが違う。

「MySQL の ISNULL()・COALESCE() - MySQL の基礎 - MySQL 入門」MySQLのISNULLは引数1個で、boolを返すだけ。

COALESCE() や IFNULL() を使うしかない。

NULLIF/COALESCEは標準。

MYSQLのNULLIFは引数1=引数2の場合、trueでNULLを、そうでなければ1個目の引数を返す。IFNULLと逆。 

NULLIF(引数1, 引数2);

以下の CASE 文と同等です。 

<CASE 
	WHEN 引数1 = 引数2 
		THEN NULL 
		ELSE 引数1
END;

日付範囲

注意点がある。

2024-01-01から2024-01-02としたい場合、いくつか記述方法がある。 

where PURCHASE_DATETIME >= '2024-01-01' and PURCHASE_DATETIME <= '2024-01-02'

SELECT * FROM users WHERE id BETWEEN 2 AND 4;
SELECT * FROM users WHERE id >= 2 AND id <= 4;

ただし、上記はまずい。2024-01-02T00:00までしか判定されない。方法がいくつかある。

  1. DATE関数で日付部分のみ抽出: SELECT * FROM users WHERE DATE(created_at) BETWEEN '2022-01-01' AND '2022-01-02'
  2. 1日進めて小なりで抽出: where PURCHASE_DATETIME >= '2024-01-01' and PURCHASE_DATETIME < '2024-01-03'
  3. ミリ秒まで指定: DATETIME <= '2010-10-31 23:59:59.999'

3はミリ秒の.999の部分がどこまでいるのか？という問題があるしスマートではない。

1-2で対応するのがよい。1がいいか。

Echo/SELECTだけでtable作成

SQLでechoのようにリテラル表示したいことがある。 

select '##  A ' as '';

asでカラム名を空にすると、必要なものだけになる。

UNION ALLで複数のselectを結合できる。 

SELECT 1 AS a, 2 AS b, 3 AS c, 4 AS d
UNION ALL
SELECT 2,4,6,8
UNION ALL
SELECT 3,6,9,12

これで4x3の表を作れる。

引用符

DBごとのSQLのクォーテーションを整理したった #MySQL - Qiita

'シングル' "ダブル" `バック` [大カッコ] 備考
MySQL 文字列定数 文字列定数 引用識別子 （なし） 引用識別子はUNIX系では大文字小文字を区別1
MySQL

（ANSI_QUOTES モード）

文字列定数 引用識別子 引用識別子 （なし）
PostgreSQL 文字列定数 引用識別子 （なし） （なし） 引用識別子では大文字小文字を区別
Oracle 文字列定数 引用識別子 （なし） （なし） 引用識別子では大文字小文字を区別
SQLite 文字列定数 引用識別子 引用識別子 引用識別子
Microsoft SQL Server

(Transact-SQL)

文字列定数 引用識別子 （なし） 引用識別子 QUOTED_IDENTIFIER が ON (既定値) の場合

基本的に、以下と思っておけばいい。

  • 一重引用符: 文字列定数
  • 二重引用符: 引用識別子

引用識別子は、テーブル名、カラム名など、DB中のオブジェクト。

SELECTのASなどで指定するリテラルは文字列定数扱い。あくまで定義済みのオブジェクトかどうかで変わる模様。

引用識別子

MySQL :: MySQL 8.0 リファレンスマニュアル :: 9.2 スキーマオブジェクト名

DBのテーブル名、カラム名など、DB中のオブジェクトを引用識別子と呼ぶ。引用識別子は、引用符で囲んだり、囲まなかったりする。特殊文字を含んだり、予約語の場合、囲みが必須になる。

識別子の条件は以下。

  • 引用符不要
    • ASCII: [0-9,a-z,A-Z$_] (基本的なラテン文字、0-9 の数字、ドル、下線)
    • 拡張: U+0080 .. U+FFFF
  • 識別子は数字で始められるが、数字のみは引用符必要。
  • 空白終わりはNG。
  • 引用符で囲んでいる場合に限り、識別子に引用符を含められる。その場合、識別子扱いの引用符は二重にする必要がある。
  • テーブル名などの修飾子のピリオド.の後に続く名前は、識別子が必須のため、予約語でも引用符不要。

スペースなどの、上記で許容されている$_以外の記号 (スペース、ハイフン、スラッシュなど) を含む場合に囲みが必要。

なお、文字列定数は常に一重引用符が必要。数字などは囲み不要。

COMMENT

SQL記述文内でコメントアウトする。単一行・複数行用の書き方 | アナリティクス沖縄│DataAnalytics

SQLのコメントアウト。癖がある。

  • [-- ]: ハイフン2個+スペースの後がコメント扱い。
  • /* */: ブロックコメントアウト。

行番号

MySQL 8.0からROW_NUMBERが使える。8.0未満だと変数を使って実現する。 

SET @rownum=0; SELECT @rownum:=@rownum+1 as ROW_NUM, TABLE_NAME FROM information_schema.TABLES LIMIT 10;
SET @row=0; SELECT @row:=@row+1 as ROW_NUM, t.* FROM [table] t;

Table

CREATE TABLE

複合unique制約のつけ方 - テックメモ跡地 

CREATE TABLE example(
    column1    SERIAL       PRIMARY KEY,
    column2    INTEGER      NOT NULL,
    column3    INTEGER      NOT NULL,
    UNIQUE ( column2, column3 )
);

SHOW tables

テーブル一覧やテーブルの確認方法。

完全に標準的な方法はない。

一番確実なのはテーブルに対してSELECTしてエラーが出るかを確認。 

SELECT count(*) FROM $table;

他に、information_schema.tablesを使う方法がある。MySQL/PostgreSQLなどSQLiteがないならこれでもOK。 

select count(*) from information_schema.tables where table_name = 'tablename';

ALTER TABLE

テーブル定義を変更する文。ほぼ標準化されていないので、DB毎の独自構文を理解する必要がある。

SQL│テーブルの定義を変更する。列のデータ型や名前、順番の変更 | アナリティクス沖縄│DataAnalytics 

-- テーブル名変更
ALTER TABLE [before table name] RENAME TO [after table name];
-- 列名変更
ALTER TABLE [table name] RENAME COLUMN [before column name] TO [after column name];

テーブル名と列名の変更だけシンプルで全体共通の模様。

COLUMN

DB製品ごとに微妙に構文が違う。

RENAMEだけ共通の模様。RENAMEとALTERはPostgreSQLと共通？

,で区切って、同じテーブルへの複数のサブ文？を1文で書ける。

PostgreSQL

ALTER TABLE 

ALTER TABLE [ IF EXISTS ] [ ONLY ] name [ * ]
    ALTER [ COLUMN ] column_name [ SET DATA ] TYPE data_type [ COLLATE collation ] [ USING expression ]
MySQL

MySQL :: MySQL 8.4 Reference Manual :: 15.1.9 ALTER TABLE Statement

  • CHANGE: 改名と定義。MySQLの独自拡張。カラム名を常に2回書かないといけないが、改名と定義変更を同時に行える。
  • MODIFY: Oracleとの互換用。定義のみ。カラム名1回のみ。単一カラムの定義変更用。
  • RENAME Oracleとの互換用。COLUMN: 改名のみ。
  • ALTER: デフォルト値の変更。
ALTER TABLE t1 MODIFY a TINYINT NOT NULL, CHANGE b c CHAR(20);

ALTER TABLE t1 RENAME COLUMN b TO a;

削除

DELETE文 TRUNCATE文 DROP文の違い(SQL構文) | 株式会社アースリンク

テーブルのデータ削除の方法がいくつかある。

  • DELETE: COMMITしてなければロールバック可能。WHEREで行を指定して削除。そのため多少時間かかる。AUTO_INCREMENT維持。
  • TRUNCATE: 全削除して再作成。DELETEより速い。ロールバック不能。AUTO_INCREMENTもリセット。
  • DROP: ロールバックも不能でテーブル構造も消える。AUTO_INCREMENTもリセット。
DELETE FROM table_name;
TRUNCATE TABLE table_name;

基本はTRUNCATEでOK。

AUTO_INCREMENT

MySQL :: MySQL 8.0 リファレンスマニュアル :: 3.6.9 AUTO_INCREMENT の使用

レコードの挿入時に番号を自動で増分してくれる仕組み。DB側でIDの一意性を保証してくれるので、同時実行などをアプリ側で考えなくていいので使うと楽。

増分のルールがある。

  • INSERT時にカラムを未指定または、値が0かNULLの場合に増分。
  • 値は1から始まる。
  • 値を指定してINSERTした場合、既存の行の最大値+1が次の値。

次の値を指定も可能。 

 ALTER TABLE tbl AUTO_INCREMENT = 100;

Function

String

LPAD/RPAD

SQL-2023標準 

LPAD(カラム, 桁数, 埋め文字)
RPAD(カラム, 桁数, 埋め文字)
LEFT/RIGHT

SQL-2023標準。

指定した文字数で切り取る。

CONCAT

カラム同士を結合したいことがある。いくつか方法がある。

  • ||: 標準SQL (2008 5.2)。ただし、MySQLはデフォルトだとOR扱いで使用不能。PIPES_AS_CONCAT モードだとOK。
  • CONCAT(): 非標準。MySQLなどデフォルトで||未対応なDBの場合こちら。
SELECT 'a' || 'b'
SELECT CONCAT(C1, C2) AS C1C2 FROM [table_name];

Other

Variable

SQL内で変数を使う構文が用意されている。

MySQLの場合。

ユーザー変数という呼び名？

  • 書式=@var_name。var_nameが変数名。
  • 変数名の使用可能文字=には英数字_$が使用可能。加えて、''で囲めば (@'var-name') 文字には何でも使える。
  • 制限=大文字小文字無視。最大64文字。
  • スコープ=ユーザー変数はセッション固有。

変数割当 

SET @var_name = expr [, @var_name = expr] ...

SET文で行う。=の他に:=も使える。なお、@var_name := exprでSETを省略することも可能だが、これは廃止予定。

ただ、短縮時に便利なことがある (sql - Getting values from previous rows using user-defined variable in MySQL - Stack Overflow)。 

SET @a = 1;
SELECT @a, @a:=@a+1;

SELECT内で割り当てと使用を同時にする場合、SETは構文エラーになる。それに、ユーザー変数を含む式の評価順序は未定義。やめたほうがいい。

ただし、直前の行を参照する場合などもこの構文を使うしかない。MySQL 8以上ならWIndow関数でこの構文は不要になる。MySQL 8未満の考慮が必要ならやむを得ない。

文を分けずに1文で変数定義と使用を同時に行う場合。FROMで定義するしかない (MySQLでSQL一発でグループごとに連番を振る #MySQL8.0 - Qiita)。 

SELECT
  @rownum := IF(@prev_group_id = group_id, @rownum + 1, 1) AS rownum, -- 連番の列
  history.id,
  @prev_group_id := group_id AS group_id,
  history.name,
  history.created_at
FROM
  history,
  (select @rownum := 0) hoge,
  (select @prev_group_id := '') fuga
ORDER BY
  group_id,
  created_at DESC

変数は定義しないと型が認識されなくて、値が更新されないらしい。

階層テーブルの取得

SQL Interview Question8: Parent Child Relationship-Use Case of Self Join | by Deeksha Singh | Geek Culture | Medium 

select a.son as grandson,
c.son as grandfather,c.dead_or_alive as dead_or_alive
from family_relation as a
left join family_relation as b on a.father=b.son
left join family_relation as c on b.father=c.son

こんな感じで、同じテーブルに対して、別名をつけてそれで連結して取得する。これで子と親を横に並べられる。

ピボットテーブル

行データの列データへの変換をピボットと呼ぶらしい。ピボットテーブルの作成という方法で実現できる模様。

いくつか方法がある。

  1. CASE文とGROUP BY句
  2. 独自拡張: PIVOT演算子、WITH (MySQL)

CASE文とGROUP BY句で実現するのが基本。

sales
product_id month sale_amount
1 Jan 100
1 Feb 150
1 Mar 120
2 Jan 200
2 Feb 250
2 Mar 210 
SELECT 
    product_id,
    MAX(CASE WHEN month = 'Jan' THEN sale_amount END) AS Jan,
    MAX(CASE WHEN month = 'Feb' THEN sale_amount END) AS Feb,
    MAX(CASE WHEN month = 'Mar' THEN sale_amount END) AS Mar
FROM 
    sales
GROUP BY 
    product_id;
product_id Jan Feb Mar
1 100 150 120
2 200 250 210

値が決まっているならこの方法でもいい。

複数行データの複数列

ピボットターブルに近いが、値が決まっていない場合の行の列への変換。 

peopleテーブル    telテーブル
| id | name |    | id | people_id | tel           |
|----|------|    |----|-----------|---------------|
| 1  | hoge |    | 1  | 1         | 090-0000-1111 |
| 2  | fuga |    | 2  | 1         | 090-0000-2222 |
| 3  | foo  |    | 3  | 2         | 090-0000-3333 |
| 4  | bar  |    | 4  | 3         | 090-0000-4444 |

| id | tel1          | tel2          |
|----|---------------|---------------|
| 1  | 090-0000-1111 | 090-0000-2222 |
| 2  | 090-0000-3333 | null          |
| 3  | 090-0000-4444 | null          |
| 4  | null          | null          |

上記のように複数行を複数列にしたい。

考え方としては、階層テーブルの取得に近いと思われる。 

SELECT *
FROM `cross_table` AS a
LEFT JOIN `cross_table` AS b ON a.括りオーナーコード = b.括りオーナーコード
limit 50

こんな感じで結合する。すると、重複するのが2行になってクロスに近い表示になる。ただし2個。ここからUNIQUEする？

括りオーナーコード 代表オーナーコード1 代表オーナーコード2
1 693 693
2 728 728
3 2676 2676
5 1357 1357
6 677 677
7 2675 2675
8 1187 1187
9 2231 2231
10 124 124
11 2486 2486
12 505 505
13 1677 1677
14 21 21
15 3567 3567
16 758 758
17 3167 3167
18 3404 3404
18 3404 6412
18 6412 3404
18 6412 6412
19 2295 2295
20 2968 2968
21 2771 2771
22 2414 2414
23 324 324
24 320 320
25 187 187
26 4043 4043
27 3613 3613
28 1853 1853
29 550 550
30 9 9
31 2639 2639
31 2639 6413
31 6413 2639
31 6413 6413
32 2946 2946
33 321 321
34 4262 4262
35 145 145
36 4270 4270
37 779 779
38 3383 3383
38 3383 6414
38 6414 3383
38 6414 6414
39 4076 4076
40 955 955
41 688 688
42 2011 2011

DISTINCTで列を指定して重複を除去するときれいになりそう。 

SELECT DISTINCT a.括りオーナーコード, a.代表オーナーコード as 代表オーナーコード1 , b.代表オーナーコード as 代表オーナーコード2
FROM `楽楽販売_括りオーナーマスタ_楽楽販売_代表オーナーマスタ` as a
left join `楽楽販売_括りオーナーマスタ_楽楽販売_代表オーナーマスタ`  as b on a.括りオーナーコード = b.括りオーナーコード
limit 50
;

SELECT a.括りオーナーコード, a.代表オーナーコード, b.代表オーナーコード
FROM `楽楽販売_括りオーナーマスタ_楽楽販売_代表オーナーマスタ` AS a
LEFT JOIN `楽楽販売_括りオーナーマスタ_楽楽販売_代表オーナーマスタ` AS b ON a.括りオーナーコード = b.括りオーナーコード
GROUP BY a.括りオーナーコード, a.代表オーナーコード, b.代表オーナーコード
limit 50

これでうまくいく。

括りオーナーコード 代表オーナーコード 代表オーナーコード
1 693 693
2 728 728
3 2676 2676
5 1357 1357
6 677 677
7 2675 2675
8 1187 1187
9 2231 2231
10 124 124
11 2486 2486
12 505 505
13 1677 1677
14 21 21
15 3567 3567
16 758 758
17 3167 3167
18 3404 3404
18 3404 6412
18 6412 3404
18 6412 6412
19 2295 2295
20 2968 2968
21 2771 2771
22 2414 2414
23 324 324
24 320 320
25 187 187
26 4043 4043
27 3613 3613
28 1853 1853
29 550 550
30 9 9
31 2639 2639
31 2639 6413
31 6413 2639
31 6413 6413
32 2946 2946
33 321 321
34 4262 4262
35 145 145
36 4270 4270
37 779 779
38 3383 3383
38 3383 6414
38 6414 3383
38 6414 6414
39 4076 4076
40 955 955
41 688 688
42 2011 2011

ただ、この方法だと重複除去したい他の列を表示できない。工夫が必要。サブクエリを使うか、DISTINCTの他にGROUP BY+HAVING集約関数。ややこしい。

https://chatgpt.com/c/672b21a0-83f4-800b-8b29-afa1d0a57817

うまくいっていない。こういうデータが残って重複が残る。

31 2639 2639
31 2639 6413
31 6413 2639
31 6413 6413

GROUP BY HAVINGで<>で違うものを2列目に用意して、同じ場合は結合みたいな。だめ。2個以上あった場合に複数ヒットしてしまう。

括りオーナーの同一番号のところにグループの連番列を追加して、その連番1/2を使ってピボットテーブルにすればいける。 

SELECT 
括りオーナーコード,
MAX(CASE WHEN rownum = 1 THEN `代表オーナーコード` END) AS 代表オーナーコード1,
MAX(CASE WHEN rownum = 2 THEN `代表オーナーコード` END) AS 代表オーナーコード2
FROM (
    SELECT 
`括り代表オーナーコード`, 
@rownum := IF(@prev_group_id = `括りオーナーコード`, @rownum + 1, 1) AS rownum, 
@prev_group_id := `括りオーナーコード` AS 括りオーナーコード,
`代表オーナーコード`
FROM `楽楽販売_括りオーナーマスタ_楽楽販売_代表オーナーマスタ`, (SELECT @rownum:=0) hoge, (SELECT @prev_group_id:='') fuga  
ORDER BY 括りオーナーコード, 括り代表オーナーコード
) numbered
GROUP BY 括りオーナーコード;
括りオーナーコード 代表オーナーコード1 代表オーナーコード2
1 693 NULL
2 728 NULL
3 2676 NULL
5 1357 NULL
6 677 NULL
7 2675 NULL
8 1187 NULL
9 2231 NULL
10 124 NULL
11 2486 NULL
12 505 NULL
13 1677 NULL
14 21 NULL
15 3567 NULL
16 758 NULL
17 3167 NULL
18 6412 3404
19 2295 NULL
20 2968 NULL
21 2771 NULL
22 2414 NULL
23 324 NULL
24 320 NULL
25 187 NULL
26 4043 NULL

成功。

phpMyAdmin

テーブル定義出力

database - How to export mysql schema from phpmyadmin? - Super User

[エクスポート]-[詳細]

  • フォーマット=Texy! text
  • 出力:◎出力をテキストで表示する
  • フォーマット特有のオプション:ダンプするテーブル=構造

これで出力・表示すると使いやすい。

Import

Import and export — phpMyAdmin 5.1.4 documentation

ネット上のSQLのサンプルを試したいことがある。

例: MySQLでSQL一発でグループごとに連番を振る #MySQL8.0 - Qiita

以下のようなSQLの実行結果を取り込んで自分でもサンプルを試したい。 

mysql> select * from history;
+----+----------+--------------------+------------+
| id | group_id | name               | created_at |
+----+----------+--------------------+------------+
|  1 |        1 | Cupcake            | 2020-01-01 |
|  2 |        1 | Donut              | 2020-01-02 |
|  3 |        2 | Eclair             | 2020-01-03 |
|  4 |        2 | Froyo              | 2020-01-04 |
|  5 |        2 | Gingerbread        | 2020-01-05 |
|  6 |        3 | Honeycomb          | 2020-01-06 |
|  7 |        4 | Ice Cream Sandwich | 2020-01-07 |
|  8 |        4 | Jelly Bean         | 2020-01-08 |
|  9 |        4 | KitKat             | 2020-01-09 |
+----+----------+--------------------+------------+
9 rows in set (0.00 sec)

これをこのまま取り込む方法はない。スプレッドシートでCSVに変換して取り込む。

  • 部分インポート: 行ヘッダーがある場合ここに1を指定。
  • フォーマット: CSV
  • カラムの区切り記号: デフォルトの,の他\tでタブも認識する模様。

MySQL

SQL

カラム名検索

MySQLで指定されたカラム名を持つテーブルを検索する #MySQL - Qiita 

select table_name, column_name from information_schema.columns where column_name = '検索したいカラム名' and table_schema = '検索対象のデータベース名';

Type

About

MySQL :: MySQL 8.0 リファレンスマニュアル :: 11 データ型

データ型の書式がある。

  • DECIMAL(M [, D]): M=全体の桁数。D=内小数部の桁数。
  • CHAR(M)/VARCHAR(M): M=格納最大文字数。CHARのM=0-255までで末尾はスペース。VARCHARはM=0-65535までで、可変長文字列で256以上もOK。末尾のスペースは削除される。先頭にプレフィクスの1-2バイトのデータを付与している。255バイト以下の文字列なら1バイト、256バイト以上なら2バイト追加。

AUTO_INCREMENT

0やNULLがカラムに設定される場合、DBで持っている増分値を使ってくれる設定。

AUTO_INCREMENTのカラムを2以上定義しようとすると以下のエラーになる。 

there can be only one auto column and it must be defined as a key

AUTO_INCREMENT属性は、PRIMARY KEYかUNIQUE KEYがないとつけられないらしい。

MySQLでAUTO_INCREMENTが指定されているカラムに「0」を入れるには

[NO_AUTO_VALUE_ON_ZERO] を指定しておくと、0の場合。増分値を使わない。

Cast

暗黙の型変換がある。

INSERTでは、式のデータ型とカラムのデータ型が不一致の場合にも型変換が発生する。

文字列から数値への変換では、文字列の先頭から有効な数字部分までを数値とみなす。

ただ、'00120'のような0始まりの数字も先頭の0を除去して数値とみなしてくれる。この根拠は見つけられなかった。

数値型の属性

MySQLは独自拡張で数値系のデータ型にint(9)のように表示幅を指定できる。DB上の表示幅なだけで実データには影響ない。それに、MySQL 8.0.17からdeprecated/廃止予定扱い。使っちゃダメ。

データ型で幅を指定しない場合、デフォルトの幅が内部的に存在する模様。

この幅は、その数値型の最小・最大値の符号を除去した桁数。

phpmyadminなど一部のクライアントがこの幅を勝手に使うので注意する。

Backup

mysql -u username -p database_name <file.sql

Error

Error: [PDOException] PDO::__construct(): The server requested authentication method unknown to the client [caching_sha2_password]

MySQL 8.0になってから、認証方式が変わったらしく、そのままだと接続に失敗する。

PHP 7.1.16, 7.2.4 以降から対応しているらしい。それ以前だと設定が必要。

Numeric value out of range: 1264 Out of range value for column

"Out of range value for column 'カラム名' at 行番号" に対処した #MySQL - Qiita

エラーコード 1264 は、データ型の範囲を超えた値をINSERTしたときに発生するWarningである。直前に実行したSQLのWarningは SHOW WARNINGS で確認できる。

Config

Character

MySQLの文字コードとCollation #MySQL - Qiita

DBで使用する文字セットと、その照合順序を選択できる。

文字コードはutf8mb4で問題ない。照合順序も基本はutf8mb4_binで問題ないと思う。

A ≠ a 区別する

≠  区別する

は≠ぱ≠ば 区別する

utf8mb4_general_ciは上2個を区別しない。

区別しないとあいまい検索で都合がいいことがある。

ただし、あいまい検索にすると、対象範囲が増えるので、少し遅くなる模様 (CharsetとCollationの設定がMySQLのパフォーマンスに与える影響 | Yakst)。

SQL mode

MySQL :: MySQL 8.0 リファレンスマニュアル :: 5.1.11 サーバー SQL モード

SQLの基本的な動きを制御する設定がある。以下で現在値を確認。 

SELECT @@GLOBAL.sql_mode;
SELECT @@SESSION.sql_mode;

以下で設定。 

SET GLOBAL sql_mode = 'modes';
SET SESSION sql_mode = 'modes';

GLOBALは権限が必要。SESSIONは誰でも変更可能。

デフォルト=ONLY_FULL_GROUP_BY, STRICT_TRANS_TABLES, NO_ZERO_IN_DATE, NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO および NO_ENGINE_SUBSTITUTION。

カンマで区切って複数指定できる。

モードの値としては、ANSIが重要。基本はこれでよい。

Collation

【MySQL】照合順序とは？ #MySQL - Qiita

MySQL :: MySQL 8.4 Reference Manual :: 12.3.1 Collation Naming Conventions

utf8_general_ciのようなcollate (照合順序) がある。これの意味。

書式 [文字集合]_[言語名]_[比較法]

  • 文字集合: character set
  • 言語名: general=マルチリンガル。0900=UCA 9.0.0。
  • 比較法: ai=アクセント無視 (はとぱは同一)/as=アクセント区別/ci=大文字小文字無視 (case ignore)/cs=大文字小文字区別 (case sensitive)/ks=カナ区別/bin=バイナリー。
default

mysqlのデフォルトはutf8_general_ci。

  • general:
  • unicode: 全角半角を区別しない。

絵文字の扱いなどが変わってくる。

utf8mb4_bin/utf8mb4_0900_bin

utf8mb4_binは終端スペースを除去する。0900は除去しない。

utf8mb4_binは照合時に先頭に0を追加する場合があるが、0900はそれがなく、0900のほうが並べ替えがはるかに高速。らしい。

基本はutf8mb4_0900_binを使うとよさそう。

「https://wiki.senooken.jp/index.php?title=DB&oldid=2241」から取得