DB

Other

カラム順序

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

• 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

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

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

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

DB ID

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

idをautoincrementして何が悪いの？

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ビットで収まる。よく考えられている。

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の予約語一覧がある。

制約

• ユニークキーは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でデータ型を変更して、文字数を切り詰めるときに、元データがあって、切り詰められないときに出るエラー。

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パターンがある。

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を生成するだけだから。

第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

IDとコード

• 「列名の付け方（～CD or ～ID ？）」（1） Database Expert － ＠IT
• コード、区分、フラグ、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のコーディング規約がある。

大文字小文字

• 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

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

第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上の制約になるので注意したほうがよく感じる。

SQL

VIEW

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

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

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

一括更新

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;

日付範囲

• 日付範囲の条件指定を定型化(安全に) | 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個+スペースの後がコメント扱い。
• /* */: ブロックコメントアウト。

ALTER TABLE

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];
-- 列データ型変更
ALTER TABLE [table name] RENAME COLUMN [before column name] TO [after column name];

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

MySQL

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

• CHANGE: 改名と定義。MySQLの独自拡張。
• MODIFY: 定義のみ。
• RENAME COLUMN: 改名のみ。
• ALTER: デフォルト値の変更。

RENAMEとALTERはPostgreSQLと共通？

RENAMEだけ共通の模様。

PostgreSQL

ALTER TABLE

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

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が重要。基本はこれでよい。