MySQL＠RDB¶

はじめに¶

本サイトにつきまして、以下をご認識のほど宜しくお願いいたします。

https://hiroki-it.github.io/tech-notebook/

01. DBエンジン¶

インメモリ方式¶

▼ MEMORY¶

メモリ上にデータを保管する。

揮発的なため、MySQLを再起動するとデータが削除されてしまう。

https://dev.mysql.com/doc/refman/8.0/en/show-engines.html

オンディスク方式¶

▼ InnoDB¶

ディスク上にデータを永続化する。

https://dev.mysql.com/doc/refman/8.0/en/show-engines.html

02. エンドポイント¶

プロトコル¶

MySQLは、TCPスリーウェイハンドシェイクでTCPコネクションを確立し、MySQLプロトコルのクエリを受信する。

つまり、アプリケーションはDBへの接続時にTCPスリーウェイハンドシェイクを実行し、MySQLにクエリを実行する時はMySQLプロコトルを使用することになる。

https://gihyo.jp/dev/serial/01/mysql-road-construction-news/0078

http://www.cybergarage.org/memo/rdbms_query_protocols/

03. テーブル¶

エクスポート、インポート¶

▼ テーブルのエクスポート¶

DBからテーブルをエクスポートする。

エクスポートしたいテーブルの数だけ、テーブル名を連ねる

$ mysqldump --force -u "{ アカウント }" -p -h "{ DBのホスト }" "{ DB名 }" "{ テーブル名1 }" "{ テーブル名2 }" > table.sql

▼ テーブルのインポート¶

DBにテーブルをインポートする。

forceオプションで、エラーが出ても強制的にインポート。

$ mysql --force -u "{ アカウント }" -p -h "{ DBのホスト }" "{ DB名 }" < table.sql

データ型¶

▼ integer型¶

integer値がどのくらい増えるかによって、3個を使い分ける。

符号なし (Unsigned) を有効化した場合、マイナス値を使用しなくなった分、使用できるプラス値が増える。

データ型	値	符号なし (Unsigned) を有効化した場合
TINYINT	`-128` ~ `+127`	`0` ~ `+255`
INT	`2147483648` ~ `+2147483647`	`0` ~ `+4294967295`
BIGINT	`-9223372036854775808` ~ `+9223372036854775807`	`0` ~ `+18446744073709551615`

▼ string型¶

文字数がどのくらい増えるかによって、3個を使い分ければ良い。

データ型	最大バイト数
VARCHAR(M)	`255`
TEXT	`65535`
MEDIUMTEXT	`16777215`

Collation (照合順序)¶

▼ string型の照合順序とは¶

string型のカラムに関して、WHERE句の比較における値の特定、ORDER BY句における並び替えの昇順降順、JOIN句における結合、GROUP BYにおけるグループ化のルールを定義する。

カラム/テーブル/DB単位で設定でき、比較するカラム同士では同じ照合順序が設定されている必要がある。

https://johobase.com/sqlserver-where-collate/

▼ 照合順序の種類¶

寿司とビールの絵文字が区別されないことを『寿司ビール問題』という。

大文字Aと小文字aを区別しないことは、CI：Case Insensitiveと表現され、照合順序名にも特徴としてCIの文字が含まれている。

照合順序名	A/a	:sushi:/:beer:	は/ぱ/ば	や/ゃ
utf8mb4_unicode_ci	`=`	`=`	`=`	`=`
utf8mb4_unicode_520_ci	`=`	`≠`	`=`	`=`
utf8mb4_general_ci	`=`	`=`	`≠`	`≠`
utf8mb4_bin	`≠`	`≠`	`≠`	`≠`

特殊文字¶

▼ 改行¶

MySQLでは、\r\nを使用して、レコード内の値を改行する。

https://www.sukerou.com/2020/09/mysql.html

INSERT INTO foo_table (id,description) VALUES (1,'前の行\r\n後の行');

制約¶

▼ 制約とは¶

DBで、アプリケーションのCRUD処理に対するバリデーションのルールを定義する。

しかし、必ずしも制約を使用する必要はなく、代わりのロジックをアプリケーション側で実装しても良い。

その制約を、DBとアプリケーションのいずれの責務とするかを考え、使用するか否かを判断する。

▼ プライマリーキー制約¶

プライマリーキーとするカラムにはプライマリーキー制約を課すようにする。

プライマリーキー制約によって、Unique制約とNot Null制約の両方が課される。

▼ Not Null制約¶

レコードに挿入される値のデータ型を指定しておくことによって、データ型不一致やNullのための例外処理を実装しなくてもよくなる。

▼ 外部キー制約¶

親テーブルのカラムを参照する子テーブルのカラムを『外部キー』といい、この時に子テーブルに課す制約を『外部キー制約』という。

子テーブルにおける外部キー制約によって、親子テーブル間に以下の整合性ルールが課される。

親テーブルの参照元カラムに存在しない値は、子テーブルに登録できない。
子テーブルの外部キーが参照する値が、親テーブルの参照元カラムに存在する場合、参照元カラムは削除できない。

＊例＊

会社情報テーブル (親テーブル) と個人情報テーブル (子テーブル) があるとする。

子テーブルの会社IDカラムを外部キーとして、親テーブルの会社IDカラムを参照する。

親テーブルの参照元カラムに存在しないIDは、子テーブルの外部キーに登録できない。

また、親テーブルの参照元カラムは外部キーに参照されているため、参照元カラムは削除できない。

外部キー

プライマリーキー¶

▼ プライマリーキーとは¶

テーブルの中で、レコードを一意に識別できる値を『プライマリーキー』の値と呼ぶ。

主キー

▼ プライマリーキーとして使用できるもの¶

一意に識別できるものあれば、何をプライマリーキーとして使用しても問題なく、基本的に以下が使用される。

プライマリーキーになるもの	説明	補足
MySQLのAuto Increment機能によって増加する番号カラム	プライマリーキー制約を課したカラムのAuto Increment機能を有効化しておく。`CREATE`処理でドメインモデルを作成する時に、『`0`』または『`null`』をモデルのID値として割り当てる。これにより、処理によって新しいレコードが追加される時に、現在の最新番号に+１した番号が割り当てられるようになる。これはプライマリーキー制約を満たす。・https://dev.mysql.com/doc/refman/8.0/en/example-auto-increment.html	・ドメインモデルとDBがより密結合になり、Active Recordパターンと相性が良い。 MySQLの環境変数として『`NO_AUTO_VALUE_ON_ZERO`』を設定すると、『`0`』の割り当てによる自動連番が拒否されるようになる。
UUID (例：`3cc807ab-8e31-3071-aee4-f8f03781cb91`)	`CREATE`処理でモデルを作成する時に、アプケーションで作成したUUID値をドメインモデルのID値として割り当てる。UUID値が重複することは基本的に発生し得ないため、プライマリーキー制約を満たす。UUID値の作成関数は言語の標準パッケージとして用意されている。	・ドメインモデルとDBがより疎結合にでき、Repositoryパターンと相性が良い。 UUID値は文字列として管理されるため、DBアクセス処理の負荷が高まってしまう。プライマリーキーを使用してソートできない。

▼ 複合プライマリーキー¶

プライマリーキーは複数設定でき、複合プライマリーキーの場合、片方のフィールドの値が異なれば、異なるプライマリーキーとして見なされる。

＊例＊

ユーザーIDと期間開始日付を複合プライマリーキーとすると、一人のユーザーが複数の期間を持つことを表現できる。

user_id	period_start_date	period_end_date	fee_yen
1	2019-04-03	2019-05-03	200
1	2019-10-07	2019-11-07	400
2	2019-10-11	2019-11-11	200

▼ 採番テーブル¶

各テーブルのプライマリーキーを統合的に管理するテーブルを採番テーブルという。

各テーブルのプライマリーキーは採番テーブルを元に割り当てられるため、連番ではなく飛び飛びになる。

http://blog.livedoor.jp/sasata299/archives/51280681.html

あらかじめ、最初のレコードのみ手動で挿入しておく。

-- 採番テーブルの作成する。
CREATE TABLE id_sequence (id BIGINT NOT NULL);

-- 最初のレコードを手動で挿入する。
INSERT INTO id_sequence VALUES (0);

CREATE処理時には、事前に、採番テーブルに新しくプライマリーキーを作成する。

INSERT文のプライマリーキーに『0』や『null』を割り当てるのではなく、採番テーブルから取得したIDを割り当てるようにする。

-- 新しくプライマリーキーを作成する。
UPDATE id_sequence SET id = LAST_INSERT_ID(id + 1);

-- プライマリーキーを取得する。
SELECT LAST_INSERT_ID();