DB

提供：senooken JP Wiki
2024年11月7日 (木) 09:02時点におけるSenooken (トーク | 投稿記録)による版 (→‎複数行データの複数列)

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レコードが共通で呼ばれる場合に対処できない。

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

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

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

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

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

基本構文 

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

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

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文呼び出しなので、複雑だと時間がかかる。

JOIN

SQLのテーブル結合（JOIN, UNION, NATURAL）を覚えよう！基本構文と使用例を解説

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

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

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

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

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

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

addSelectで列名を地道に追加する。これが確実。

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

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がよいだろう。

一括更新

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

階層テーブルの取得

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

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

PIVOT 複数行データの複数列

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する？

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

SELECT DISTINCT a.括りオーナーコード, a.代表オーナーコード AS 代表オーナーコード1 , b.代表オーナーコード AS 代表オーナーコード2
FROM `cross_table` AS a
LEFT JOIN `cross_table` AS b ON a.括りオーナーコード = b.括りオーナーコード
limit 50

これでうまくいく。

ただ、この方法だと重複除去したい他の列を表示できない。工夫が必要。サブクエリを使うか、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列目に用意して、同じ場合は結合みたいな。

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

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

いくつか方法がある。

  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

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

phpMyAdmin

テーブル定義出力

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

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

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

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

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=2184」から取得