DB

Other

Performance

SQL vs. app

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

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

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

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

カラム順序

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

• MySQLでカラムの順番は重要ですか？
• 定義するカラムの順番
• テーブルのカラムを増やす際に末尾に追加するのではなく、カラムの並び順を変更したい #考え方 - Qiita
• PostgreSQLのカラム順序がテーブルサイズに与える影響 #PostgreSQL - Qiita
• database - Does column order matter in your MySQL tables? - Stack Overflow
• mysql - Is there any reason to worry about the column order in a table? - Stack Overflow

パフォーマンスに影響がある。特に下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と呼んだりするらしい。

• List of SQL reserved words - Wikipedia: 標準SQLの予約語一覧
• SQL syntax - Wikipedia: 標準SQLの構文。

制約

• ユニークキーはNULL許容します（圧） #MySQL - Qiita
• データベースにおける制約の種類 #DB - Qiita

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の採番規則。非常に重要。全体のパフォーマンスにも影響する。

• ID生成方法についてあれこれ
• idをautoincrementして何が悪いの？

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とコード

• 「列名の付け方（～CD or ～ID ？）」（1） Database Expert － ＠IT
• コード、区分、フラグ、IDと名前。 - 日々常々

コードは広い概念。

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

Naming

• sql - Why is naming a table's Primary Key column "Id" considered bad practice? - Software Engineering Stack Exchange
• sql - Primary key/foreign Key naming convention - Stack Overflow
• sql - Naming of ID columns in database tables - Stack Overflow
• database design - Is prefixing each field name in a table with abbreviated table name a good practice? - Stack Overflow
• sql - Do you prefer verbose naming when it comes to database columns? - Stack Overflow
• SQLアンチパターン勉強会　第三回：IDリクワイアド #SQL - Qiita

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

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

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

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

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

正規化

• 第4回　データベースの正規化
• 正規化よりも実装のしやすさを優先させる｜東京のWeb制作会社・ホームページ制作会社｜Servithink
• 失敗しないデータベース正規化の3ステップをプロのエンジニアが解説 - みんなのシステム企画

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

第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

命名規則

• データベースのテーブル名とフィールド名の命名規約 - Perl Webアプリ開発入門
• Guidelines for good schema design | Wiki | Yii PHP Framework

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

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

Convention

List

• mysql - Are there any published coding style guidelines for SQL? - Stack Overflow
• SQL style guide by Simon Holywell
• SQL Style Guide - Mozilla Data Documentation
• SQL coding conventions | SQL | Drupal Wiki guide on Drupal.org
• SQL Coding Guidelines - IBM Documentation

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

大文字小文字

• SQLで大文字・小文字のルールはある？区別・変換方法も一緒に教えます！ | ポテパンスタイル
• MySQLの予約語は大文字に統一したほうがいいのは本当か？

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

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

Data

enum/string

• DBで区分値などのCODEは数値ではなく文字列を利用するべき
• 区分値は数値にすべきか意味のある文字列にすべきか

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

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

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

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

• database - Saving images: files or blobs? - Stack Overflow
• database - Storing Images in DB - Yea or Nay? - Stack Overflow
• To BLOB or Not To BLOB: Large Object Storage in a Database or a Filesystem - Microsoft Research
• データベースに画像を保存するメリット・デメリットと必要性について
• データベースに画像を保存するのはありでしょうか？

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

NULL

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

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

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

int/string

• language agnostic - Integer vs String in database - Stack Overflow
• データベースの値の持ち方は文字列にするか数字にするか迷っています

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

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

INT/BIGINT

• MySQLにおけるint型IDの限界とクラッシュ回避のための対策 #MySQL - Qiita
• 第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と空文字

• SQL Server: Null VS Empty String - Stack Overflow
• design - SQL: empty string vs NULL value - Software Engineering Stack Exchange

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

NULLと空文字の意味論

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

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

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

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

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

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

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

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

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

NULL

第8回　NULLって何？

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

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

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

VARCHARの文字数定義

• mysql - Best practices for SQL varchar column length - Stack Overflow
• MySQL :: MySQL 8.4 リファレンスマニュアル :: 13.3.2 CHAR 型と 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.4. 多対多の関連を持つオブジェクトを編集可能なフォーム — Railsアプリケーション構築ガイド
• 1.5. 多対多の関連を持つオブジェクトをチェックボックスで編集可能なフォーム — Railsアプリケーション構築ガイド

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

ポリモーフィック

• ORM のポリモーフィック関連は使うべきか？
• ポリモーフィック関連を活用し、森羅万象の「いいね」を実現する手法 - クックパッド開発者ブログ
• SQLアンチパターン6章　ポリモーフィック関連 #SQLアンチパターン - Qiita
• SQLアンチパターン勉強会　第6回：ポリモーフィック関連 #SQLアンチパターン - Qiita

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

• SQL文についての質問です。INSERT文でINTOの後のカラム名指定のところ... - Yahoo!知恵袋
• INSERT文（SQLを基本から学ぶシリーズ）

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

• ON DUPLICATE KEY + AUTO INCREMENT issue mysql - Stack Overflow
• 【MySQL】DUPLICATE で自動採番が飛ぶケースについて（２） | 株式会社スタイラジーの「居心地の良い」ブログ
• MySQL で ON DUPLICATE KEY UPDATE を使ってたら、カンスト(カウンターストップ)した話 - R-Hack（楽天グループ株式会社）
• MySQLでのINSERT ON DUPLICATE構文との付き合い方 - Mobile Factory Tech Blog
• 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

• 【MySQL】Nullable カラムを含めて複合ユニーク制約を設ける方法 | ramble - ランブル -
• 「NULLがUNIQUE制約に縛られないことを利用する」のは、正当なNULLの使い方 - 極北データモデリング

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

• SQLのテーブル結合（JOIN, UNION, NATURAL）を覚えよう！基本構文と使用例を解説
• SQL の内部結合(INNER JOIN)と内部結合 (OUTER 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

• INNER JOIN とWHERE結合の違いについて理解する
• テーブル結合は JOIN と FROM どちらで行うべきか。両者の違いと注意点 | Ritolabo
• WHERE句とON句の適切な使い方

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

ただ、外部結合は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

• ビュー(DB)について #SQL - Qiita
• SQLのビュー(VIEW)って何？基本的な使い方理解しよう【サンプル有り】 | ポテパンスタイル
• SQLのビューとは？考え方から実際にできることまで解説！ | アンドエンジニア

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;

日付範囲

• 日付範囲の条件指定を定型化(安全に) | DBFlute
• SQLで日付の範囲指定をしたいときにありがちなミス

注意点がある。

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/print するための代替方法 - それマグで！

• SQL SELECT基礎(3) その場で表を作る #SQL - Qiita

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のSELECT結果に行番号を振る。 #mariadb - Qiita
• 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

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

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

SHOW tables

• php - Check if table exist using PDO - Stack Overflow
• 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' [sql - How can I list the tables in a SQLite database file that was opened with ATTACH? - Stack Overflow];
• 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毎の独自構文を理解する必要がある。

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

• Concatenation Operator - SQL in a Nutshell, 3rd Edition [Book]
• MySQL :: MySQL 8.0 リファレンスマニュアル :: 12.4.3 論理演算子
• MySQLで使える文字列結合方法 - プロエンジニア
• 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の場合。

• MySQL :: MySQL 8.0 リファレンスマニュアル :: 9.4 ユーザー定義変数
• MySQL :: MySQL 8.0 リファレンスマニュアル :: 13.7.6.1 変数代入の SET 構文

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

• 書式=@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

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

複数行データの複数列

• 同じキー項目毎の縦複数行データを横複数列データに変換するSQL | オラクルマスター11g ブロンズ＆シルバー ～取得のためにすべきこと～
• 複数レコードを単一レコード・複数カラムにする #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する？

括りオーナーコード	代表オーナーコード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

• 【MySQL】AUTO_INCREMENT属性カラムの制約 | ミーミルの泥泉
• マリア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がないとつけられないらしい。

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

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

Cast

• MySQL :: MySQL 8.0 リファレンスマニュアル :: 13.2.6 INSERT ステートメント
• MySQL :: MySQL 8.0 リファレンスマニュアル :: 12.3 式評価での型変換
• MySQL :: MySQL 8.0 リファレンスマニュアル :: 12.11 キャスト関数と演算子

暗黙の型変換がある。

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

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

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

数値型の属性

• MySQL 8.0 から int の ZEROFILL 指定が deprecated #MySQL8.0 - Qiita
• mysql - Why default INT(11) - Stack Overflow
• MySQL :: MySQL 8.0 Reference Manual :: 13.1.2 Integer Types (Exact Value) - INTEGER, INT, SMALLINT, TINYINT, MEDIUMINT, BIGINT
• MySQL :: MySQL 8.0 リファレンスマニュアル :: 11.1.1 数値データ型の構文
• 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

Error

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

• MySQL8.0以降で接続できない場合は「認証プラグイン」を変更する
• phpからMySQL 8.0へPDOで接続時「SQLSTATE[HY000 [2054] The server requested authentication method unknown to the client」 - Symfoware]
• 日々の覚書: MySQL 8.0.4におけるデフォルト認証形式の変更
• docker-compose MySQL8.0 のDBコンテナを作成する #Docker - Qiita

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: 全角半角を区別しない。

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

• MySQL 8.0=utf8mb4/utf8mb4_0900_ai_ci (MySQL :: MySQL 8.0 Reference Manual :: 12.3.2 Server Character Set and Collation/MySQL :: MySQL 8.0 Reference Manual :: 12.2 Character Sets and Collations in MySQL)
• MySQL 5.7=latin1/latin1_swedish_ci (MySQL :: MySQL 5.7 Reference Manual :: 10.3.2 Server Character Set and Collation)
• MySQL 5.7=utf8mb4/utf8mb4_general_ci (MySQL :: MySQL 5.7 Reference Manual :: 10.2 Character Sets and Collations in MySQL)

utf8mb4_bin/utf8mb4_0900_bin

• MySQL8のutf8mb4におけるCollation比較 - BLOG - siwa32.com
• MySQL :: MySQL 8.0 Release Notes :: Changes in MySQL 8.0.17 (2019-07-22, General Availability)
• https://chatgpt.com/c/674ce499-dcd0-800b-bb9c-42a8fbe21805

utf8mb4_0900_binなどの0900はMySQL 8.0から使用可能。0900はUnicode 9.0の意味。MySQL専用でMariaDBにはない。

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

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

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

設定・変更

MariaDBのDBの文字コードと照合順の変更 | GNU social JP Web

指定したDBの文字コード・照合順の情報は以下のコマンド・SQLで確認可能です。

DB_USER=root
DB_NAME=mysql
mysql -u $DB_USER -p -e \
'SELECT SCHEMA_NAME,DEFAULT_CHARACTER_SET_NAME,DEFAULT_COLLATION_NAME
    FROM INFORMATION_SCHEMA.SCHEMATA;'

+--------------------+----------------------------+------------------------+
| SCHEMA_NAME        | DEFAULT_CHARACTER_SET_NAME | DEFAULT_COLLATION_NAME |
+--------------------+----------------------------+------------------------+
| information_schema | utf8                       | utf8_general_ci        |
| performance_schema | utf8                       | utf8_general_ci        |
| mysql              | utf8mb4                    | utf8mb4_general_ci     |
+--------------------+----------------------------+------------------------+

設定は以下で行えます。

DB_USER=root
DB_NAME=mysql
## MariaDB
mysql -u $DB_USER -p -e \
 "ALTER DATABASE \`$DB_NAME\` DEFAULT CHARACTER SET 'utf8mb4' DEFAULT COLLATE 'utf8mb4_bin';"
## MySQL
mysql -u $DB_USER -p -e \
  "ALTER DATABASE $DB_NAME DEFAULT CHARACTER SET 'utf8mb4' DEFAULT COLLATE 'utf8mb4_0900_bin';"