DB
Other
画像データのパス/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以下の場合は問題ない。が、それ以外だと問題があるので、ファイルパスで扱うのがいいらしい。
カラム順序
テーブルのカラム・列の順序。これに意味があるのか?カラムを追加する場合などに影響がある。
- 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許容の中でも頻度順にする。
ディスクスペースの利用率、使用するデータの効率などに影響がある模様。
NULL
Ref: NOT NULL 制約をつけるべきカラムとは #PostgreSQL - Qiita.
絶対に必要な項目にNOT NULL制約をつける。NULLを許容する列は、最後のほうに集めると、ストレージなどの効率がいい。
例えば、取得に失敗した場合に、値をどうするのか?などを考えるとよい。
int/string
データベースのカラムでIDやコード類を定義する場合、型をintなどの数値系、charなどの文字列系にするかの議論がある。
- WHEREの範囲、算術計算など数値計算が伴うなら数値。データを何に使うかの意味論が重要。
- 数値のほうが高速。
- 郵便番号のように先頭が0つく場合、文字列。
- 数値だと、auto incrementで登録時の重複除去を保証できる。
- 値を変更する可能性がないなら数値。
- 文字列型だと文字数の検討が必要。
INT/BIGINT
- MySQLにおけるint型IDの限界とクラッシュ回避のための対策 #MySQL - Qiita
- 第178回 MySQLのAUTO_INCREMENTなINTEGERってどのくらいで使い切るの? | gihyo.jp
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値を使う。
マスター
マスタデータって何?マスタデータについて詳しく解説! - J-MDM/Snowflake
ユーザー、顧客や拠点の情報など、ベースとなるデータのこと。
マスターの他に、トランザクションというのもある。これは、売買などシステムや業務の稼働に伴って生じるデータ。
【データベース正規化完全ガイド】実務観点での正規化手順やポイントを詳しく解説 | PrAhaENGINEERLAB
トランザクションテーブルで、マスターテーブルを直接参照するのはまずい。
マスターテーブルを更新したら、トランザクションの情報も変わってしまうから。
登録時にマスターテーブルのデータをコピーしていれる。
DB ID
DBのIDの採番規則。非常に重要。全体のパフォーマンスにも影響する。
- 生成されたIDは時間順に並べ替え可能である必要がある(例えば、写真 ID のリストは、写真に関する詳細情報を取得せずに並べ替えることができます)
- ID は理想的には 64 ビットである必要があります (インデックスが小さくなり、Redis などのシステムでより優れたストレージを実現するため)
- システムには、新しい「可動部分」をできるだけ少なく導入する必要があります。私たちが非常に少数のエンジニアで Instagram を拡張できたのは、信頼できるシンプルでわかりやすいソリューションを選択したことが大きな理由です。
Instagramは独自の計算式でIDを算出している。
日時、ユーザーID、自動増分値。
この3要素で構成して算出している。これでバッティングせずに時間ソートで64ビットで収まる。よく考えられている。
Sharding
シャーディングとは - 意味をわかりやすく - IT用語辞典 e-Words
シャーディング。DBの負荷分散の手法。
データをレコード・行単位で水平分割して、複数のデータベースサーバーに分散して記録する。
分散したDBの単位をシャードと呼んでいる。データが増大しても台数を増やせば問題ない。
ただし、どのデータがどのサーバーで記録しているかを把握する必要がある。ハッシュ値などから計算する。
親子関係
親子関係、ツリー構造、階層構造を表現したいことがある。
基本的に、親コードのカラムをどこかのテーブルに持たせて対応させる。
- 隣接リスト (Adjacency List): 親子関係が再帰的になっている場合、途中データの削除はやっかいになる。親子の取得が少々複雑で何回もSQLが必要になり厄介。階層構造が2-3階程度なら問題ない。
- 経路列挙:
Standard
ISO/IEC 9075がSQLの標準。標準外のSQL/関数は独自実装なので扱いには注意が必要。標準のみの使用が望ましい。
ANSI SQLと呼んだりするらしい。
List of SQL reserved words - Wikipedia
ここに標準SQLの予約語一覧がある。
制約
3のキー制約がある。
- NOT NULL。
- UNIQUE制約: ユニークキー NULL許容
- PRIMARY KEY制約: プライマリーキー非NULL。UK AND NOT NULL相当。
- FOREIGN KEY制約: 外部キー非NULL。
- CHECK制約
Design
正規化
- 第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パターンがある。
- 似たカラムが複数 (TEL1/TEL2など)。
- 1セルに複数の値 (カンマ区切りなど)。
行に分けることがポイント。
第1正規形じゃないと、拡張できなくて困る。カラムを増やす場合、テーブル定義の変更が都度必要になる。
第2正規形
主キーの一部への依存関係をなくす。「部分関数従属性をなくす」。
片方にしか依存しない値を見つけ、それを専用のテーブルに移動する。
例えば、上記の学生テーブルだと、[学生ID/学生名] が一つの塊で、コース名は別。これらの塊は以下の依存関係がある。
- 学生IDがキーで、学生名がIDに従属している (学生IDで学生名を特定できる)。
- コース名だけでコース名と教授名を特定できる。コースID列を追加しても同じこと。
これを3のテーブルに分割する。
- ブロックごとのテーブルを作る。
- 片方のテーブルに、片方のIDを残す。
学生ID | 学生名 |
---|---|
1 | 山田太郎 |
2 | 菊池太郎 |
3 | 川上太郎 |
コースID | コース名 | 教授名 |
---|---|---|
1 | サッカー | 山田 |
2 | 軽音 | 鈴木 |
3 | 野球 | 山田 |
4 | バレーボール | 山根 |
学生ID | コースID |
---|---|
1 | 1 |
1 | 2 |
2 | 3 |
3 | 4 |
残ったIDだけのテーブル。IDを片方に残して、複合主キーとすることもできる。
第2正規形の解決課題。
- 片方のデータだけ登録できない。
- データの不整合が簡単に発生する。
第2正規化を行ったことで、複合主キーが生まれる (「第二正規化」は、複合キーが存在するテーブルのみで行うのでしょうか)。
マスターテーブルじゃないなら、複合主キーも悪くないか。複合主キーを使わないなら、代理キーでIDを生成するだけだから。
第3正規形
「主キー」ではない項目への依存関係をなくす。推移関数従属性をなくす。
例えば、コーステーブルは、コース名に教授名が従属している。学生IDをコーステーブルに残したら、本来コースIDだけで決まるのに余計な学生IDが残る。
これを専用テーブルにする。
- 従属部分を別テーブルに移動する。
- それぞれにレコードIDを付与する。
- どちらかのテーブルに分離したテーブルのIDを付与する。
例だと、コースIDではないコース名に教授名が従属している。例えば、コースID=1/3はコース名が異なるが、同じ教授名になっており、教授名はコースIDには従属していない。
コースID | コース名 | 教授ID |
---|---|---|
1 | サッカー | 1 |
2 | 軽音 | 2 |
3 | 野球 | 1 |
4 | バレーボール | 3 |
教授ID | 教授名 |
---|---|
1 | 山田 |
2 | 鈴木 |
3 | 山根 |
Naming
IDとコード
コードは広い概念。
IDはコードの内、一意なもの。
命名規則
- データベースのテーブル名とフィールド名の命名規約 - 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のコーディング規約がある。
大文字小文字
予約語以外、基本的に全部小文字で、複数単語はハイフン区切りにしたほうがいいらしい。
構文強調がないと、formなどと見分けがつきにくいので、大文字にしたほうがいい。可読性優先。
enum/string
区分系データを数字で持つか、文字列で持つか。
文字列だとわかりやすい。数字で持つと、意味の把握にカラム定義など、別の資料を確認する必要がある。アプリ側で表示に毎回そのマップや変換が必要になる。
数値で必要ならマスターの主キーにする。基本は文字列でいいと思う。
SQL
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と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を併用すれば一括更新可能とのこと。
UPSERT
Ref: 【総まとめ】UPSERTとは?DBごとの違いは? | 初心者DIYプログラミング入門.
INSERTするかUPDATEするか、一度SELECTで値の有無を確認してから本来しないといけない。SQLを2回実行必要で、処理が無駄になるので、これを1回で行う。これがUPSERTと呼ばれるもの。ただし、UPSERTというSQLがあるわけではなく、DBごとの独自拡張になる。
MERGE文が一応あるが、MySQLにはない (sql - Is merge statement available in MySQL - Stack Overflow)。代わりにINSERT ... ON DUPULICATE KEY UPDATEという文を使う。
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までしか判定されない。方法がいくつかある。
- DATE関数で日付部分のみ抽出:
SELECT * FROM users WHERE DATE(created_at) BETWEEN '2022-01-01' AND '2022-01-02'
- 1日進めて小なりで抽出:
where PURCHASE_DATETIME >= '2024-01-01' and PURCHASE_DATETIME < '2024-01-03'
- ミリ秒まで指定:
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 | 文字列定数 | 引用識別子 | 引用識別子 | (なし) | |
PostgreSQL | 文字列定数 | 引用識別子 | (なし) | (なし) | 引用識別子では大文字小文字を区別 |
Oracle | 文字列定数 | 引用識別子 | (なし) | (なし) | 引用識別子では大文字小文字を区別 |
SQLite | 文字列定数 | 引用識別子 | 引用識別子 | 引用識別子 | |
Microsoft SQL Server
(Transact-SQL) |
文字列定数 | 引用識別子 | (なし) | 引用識別子 | QUOTED_IDENTIFIER が ON (既定値) の場合 |
基本的に、以下と思っておけばいい。
- 一重引用符: 文字列定数
- 二重引用符: 引用識別子
引用識別子は、テーブル名、カラム名など、DB中のオブジェクト。
引用識別子
MySQL :: MySQL 8.0 リファレンスマニュアル :: 9.2 スキーマオブジェクト名
DBのテーブル名、カラム名など、DB中のオブジェクトを引用識別子と呼ぶ。引用識別子は、引用符で囲んだり、囲まなかったりする。特殊文字を含んだり、予約語の場合、囲みが必須になる。
識別子の条件は以下。
- 引用符不要
- ASCII: [0-9,a-z,A-Z$_] (基本的なラテン文字、0-9 の数字、ドル、下線)
- 拡張: U+0080 .. U+FFFF
- 識別子は数字で始められるが、数字のみは引用符必要。
- 空白終わりはNG。
- 引用符で囲んでいる場合に限り、識別子に引用符を含められる。その場合、識別子扱いの引用符は二重にする必要がある。
- テーブル名などの修飾子のピリオド.の後に続く名前は、識別子が必須のため、予約語でも引用符不要。
スペースなどの、上記で許容されている$_以外の記号 (スペース、ハイフン、スラッシュなど) を含む場合に囲みが必要。
なお、文字列定数は常に一重引用符が必要。数字などは囲み不要。
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
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バイト追加。
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 以降から対応しているらしい。それ以前だと設定が必要。
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が重要。基本はこれでよい。