phpでPODを使ってmysqlに接続すると、
Uncaught PDOException: SQLSTATE[HY000] [1045] Access denied for user
このエラーが出てきて、ググった方法を試しても全く解決されなかった。
ユーザー名もパスワードも合ってるし、環境設定もできてるし、php.iniの設定もできてるし、ユーザー権限もALLになってるし、、、、
そこでもう一回上記の確認をしてみると、、、、、

あ！！！！！！！

ユーザー権限のdb名の部分が間違っていた、、、、
db名を今使ってる名前にして、楽々接続完了！！！

db周りの環境設定とかエラーとかすごい苦手だったけど、今回のことで少し詳しなったのでmysqlインストール後の設定方法に関して一応整理しておく。

①mysqlインストール
②php.iniでpdo部分のコメントアウト
③Apacheの再起動
④.bashrcにPATHを通す
⑤mysqlを起動後にユーザー権限を付与

とりあえず、dbのエラー発生したら、①PATHが通っているのか（.bashrc)②ユーザー権限が合っているのかを真っ先に確認すればいいんだね。

環境

MySQL
※多分他のやつでも同じように動くはず

やりたいこと

タイトルの通り、SQLの適当なテーブルから、特定のカラムの値が重複しているレコードだけを抽出。

コード

SELECT * FROM <テーブル> GROUP BY <カラム...> HAVING COUNT(*) >= 2;

ちなみに、カラムを複数対象にしたい場合には、GROUP BYに複数のカラム(「,」区切り)を書くことができます。

例) users テーブル(id,name_sei,name_mei,age,sex)

同姓同名がテーブルにいるアカウントのみ抽出

SELECT * FROM users GROUP BY name_sei,name_mei HAVING COUNT(*) >= 2;

最後に

SQLで全件検索した後にawkを使って無理やりやろうとしてましたが、冷静に考えてみたらSQLの方が素直に書けて良いですね。

SQLでかけるようなときは、AWK自重します。

はじめに

最近SharePointやらRPAツールばっかりでSQLなんて1年以上まともに触っていなかったけど
久しぶりでもやりゃあ出来ると思っていたんです。

できませんでした。35過ぎると何も思い出せずにとんちんかんなクエリを書いて
客先のSEさんに白い目（でもやさしいよ）で見られる羽目になった。
カッコつけてクロス集計しようとしてこかして焦って訳の分からぬクエリを書いた。
Case書くときに勉強中のJavaScript ES6と脳内で混じるなど。
恥を忍んでここにぼちぼち書いておく。
ババアのリハビリである。
このクエリ使えるなどの優しさはコメントしてくれたらうれしい。
なお、データ要素のわんこ率の高さは、ちょっと他の人にもやってみてほしくて
progateのSQL学習コースのデータを使ったので。
（コード書く欄に書いてみるとちゃんと反映されるはずなのでよかったらやってみてほしい）

　クロス集計

カラムは category,character_name,price
bookはpurchases　とする。
データ要素は本来"category"に"雑費","食費","交通費"…と複数あるものを雑費とそれ以外に二分。
"character_name"は"にんじゃわんこ"、"ベイビーわんこ","ひつじ仙人"、"とりずきん"…と複数あるのを"わんこ"含むもので前後ワイルドカード指定。それ以外も出す。
合計入れたかったけどなんでかROLLUPが通らなかった…
（わかる方情報求む）

クロス集計

SELECT
case category
when "雑費" then "雑費"
else "その他"
end "カテゴリ",
sum(case when character_name = "にんじゃわんこ" then price else 0 end) as "わんわん",
sum(case when not character_name = "にんじゃわんこ" then price else 0 end) as "わんわん以外",
sum(price) as "みんな"
FROM purchases
group by "カテゴリ";

※ほんとはここのgroup byをrollupしたかった。下のように書いたが駄目。なんでダメなんじゃっけ？
group by rollup("カテゴリ");
* 結果

はじめに

最近SharePointやらRPAツールばっかりでSQLなんて1年以上まともに触っていなかったけど
久しぶりでもやりゃあ出来ると思っていたんです。

できませんでした。35過ぎると何も思い出せずにとんちんかんなクエリを書いて
客先のSEさんに白い目（でもやさしいよ）で見られる羽目になった。
カッコつけてクロス集計しようとしてこかして焦って訳の分からぬクエリを書いた。
Case書くときに勉強中のJavaScript ES6と脳内で混じるなど。
恥を忍んでここにぼちぼち書いておく。
ババアのリハビリである。
このクエリ使えるなどの優しさはコメントしてくれたらうれしい。
なお、データ要素のわんこ率の高さは、ちょっと他の人にもやってみてほしくて
progateのSQL学習コースのデータを使ったので。
（コード書く欄に書いてみるとちゃんと反映されるはずなのでよかったらやってみてほしい）

　クロス集計

カラムは category,character_name,price
bookはpurchases　とする。
データ要素は本来"category"に"雑費","食費","交通費"…と複数あるものを雑費とそれ以外に二分。
"character_name"は"にんじゃわんこ"、"ベイビーわんこ","ひつじ仙人"、"とりずきん"…と複数あるのを"わんこ"含むもので前後ワイルドカード指定。それ以外も出す。
合計入れたかったけどなんでかROLLUPが通らなかった…
（わかる方コメントくれたらうれしいです）

クロス集計

SELECT
case category
when "雑費" then "雑費"
else "その他"
end "カテゴリ",
sum(case when character_name = "にんじゃわんこ" then price else 0 end) as "わんわん",
sum(case when not character_name = "にんじゃわんこ" then price else 0 end) as "わんわん以外",
sum(price) as "みんな"
FROM purchases
group by "カテゴリ";

※ほんとはここのgroup byをrollupしたかった。下のように書いたが駄目。なんでダメなんじゃっけ？
group by rollup("カテゴリ");
* 結果

仕方がないからUNION ALL（暫定）

仕方がないから下にUNION ALL　+ 同じコードコピペ　でとりあえず。
何かに負けた感…

クロス集計・合計あり

SELECT
case category
when "雑費" then "雑費"
else "その他"
end "カテゴリ",
sum(case when character_name = "にんじゃわんこ" then price else 0 end) as "わんわん",
sum(case when not character_name = "にんじゃわんこ" then price else 0 end) as "わんわん以外",
sum(price) as "みんな"
FROM purchases
group by "カテゴリ"
union all
SELECT "カテゴリ",
sum(case when character_name = "にんじゃわんこ" then price else 0 end) as "わんわん",
sum(case when not character_name = "にんじゃわんこ" then price else 0 end) as "わんわん以外",
sum(price) as "みんな"
FROM purchases

はじめに

最近SharePointやらRPAツールばっかりでSQLなんて1年以上まともに触っていなかったけど
久しぶりでもやりゃあぶっつけで出来ると思っていたんです。

…できませんでした。35過ぎると脳が衰えるのだ。
客先のSEさんに白い目（でもやさしいよ）で見られる羽目になった。
カッコつけてクロス集計しようとしてこかして焦って訳の分からぬクエリを書いた。
恥を忍んでここにぼちぼち書いておく。おばさんのリハビリである。
このクエリ使えるなどの優しさはコメントしてくれたらうれしい。
なお、データ要素のわんこ率の高さは、ちょっと他の人にもやってみてほしくて
progateのSQL学習コースのデータを使ったので。
（もしprogateやってる人がいたら、SQLのコースⅡあたりでコード書く欄に書いてみるとちゃんと反映されるはず）

　クロス集計

カラムは category,character_name,price
bookはpurchases　とする。
データ要素は本来"category"に"雑費","食費","交通費"…と複数あるものを雑費とそれ以外に二分。
"character_name"は"にんじゃわんこ"、"ベイビーわんこ","ひつじ仙人"、"とりずきん"…と複数あるのを"わんこ"含むもので前後ワイルドカード指定。それ以外も出す。
合計入れたかったけどなんでかROLLUPが通らなかった…サブクエリ作って外に出してもだめだった…
（わかる方コメントくれたらうれしいです）

クロス集計

SELECT
CASE category
 WHEN "雑費" THEN "雑費"
 ELSE "その他"
 END "カテゴリ",
SUM(CASE WHEN character_name = "にんじゃわんこ" then price else 0 END) AS "わんわん",
SUM(CASE WHEN NOT character_name = "にんじゃわんこ" then price else 0 END) AS "わんわん以外",
SUM(price) AS "みんな"
FROM purchases
GROUP BY "カテゴリ";

※ほんとはここのgroup byをrollupしたかった。下のように書いたが駄目。評価順？と思ってSQの外に出してもだめ。なんでダメなんじゃっけ？
ちなみに下記どっちもだめ。
group by rollup("カテゴリ");
GROUP BY "カテゴリ" WITH ROLLUP

• 結果

仕方がないからUNION ALL（暫定）

仕方がないから下にUNION ALL　+ 同じコードコピペ　でとりあえず。
何かに負けた感…

クロス集計・合計あり

SELECT
CASE category
 WHEN "雑費" THEN "雑費"
 ELSE "その他"
 END "カテゴリ",
SUM(CASE WHEN character_name = "にんじゃわんこ" then price else 0 end) AS "わんわん",
SUM(CASE WHEN NOT character_name = "にんじゃわんこ" then price else 0 end) AS "わんわん以外",
SUM(price) AS "みんな"
FROM purchases
GROUP BY "カテゴリ"
UNION ALL
SELECT "カテゴリ",
SUM(CASE WHEN character_name = "にんじゃわんこ" then price else 0 end) AS "わんわん",
SUM(CASE WHEN NOT character_name = "にんじゃわんこ" then price else 0 end) AS "わんわん以外",
SUM(price) AS "みんな"
FROM purchases;

• 結果 なんかヤダけどまあまあありえるやつ。（知能が低い）

サブクエリ化して絞り込みしやすくする

とりあえず、クロス集計のクエリをそのまんま入れ子にしたクエリを置いておく。
ここの*（全部選択）を"わんわん"に変えればわんわんのデータが出てくる。便利。
なお、SQ = サブクエリの略とお思い下さい。

サブクエリ

SELECT * FROM(
 SELECT
 CASE category
  WHEN "雑費" THEN "雑費"
  ELSE "その他"
 END "カテゴリ",
 SUM(CASE when character_name = "にんじゃわんこ" then price else 0 end) AS "わんわん",
 SUM(case when not character_name = "にんじゃわんこ" then price else 0 end) AS "わんわん以外",
 SUM(price) AS "みんな"
 FROM purchases
 GROUP BY "カテゴリ"
) SQ
ORDER BY "カテゴリ" DESC;

※ORDER BYはこのケースでは別に必要ないですがなんとなく。

• 結果1（絞り込み１つ）
  最初の* を"カテゴリ","わんわん以外"に置き換えるとこうなる。
  

• 結果2（複数条件で絞り込み）
  さらに、)SQとORDER BYの間にwhere "カテゴリ" = "雑費"と入れると雑費のみにデータが絞れてこうなる。
  

• 結果3（全データ使わずに特定の日付だけ）
  下記のように、SQの中を変えると、WHEREでSQの中を絞込できる。
  これはSQの外で書いても意味がないです。（カラム構造変わっちゃってるから）
  なお、日付だけでなく他のカラムの条件でももちろんOK。
  FROM purchases
where purchased_at = "2019-12-18"←これ追加。日付は任意の値に変更。
  GROUP BY "カテゴリ"

はじめに

モチベーションクラウドシリーズAdvent Calendar 2019の21日目を担当する松本です。

現在、モチベーションクラウドは10数名規模の会社様から数万人規模の大企業様まで幅広くご利用いただいております。私たち開発チームはどの規模のユーザー様にも快適にご利用いただけるよう、日々性能改善に取り組んでいます。

今回は、MySQL InnoDBストレージエンジンにおける、INSERTステートメントの並列性向上および挿入速度向上が期待できるTipsをまとめていきます。

前提条件

まず、行の挿入に必要な時間は以下の要因で決まります。(MySQLドキュメントより)
(数字は割合)

VALUESリストを複数指定

個別の単一行のINSERTステートメントより、複数のVALUESリストを使用して、同時に複数行挿入する。

単一行

INSERT INTO t1 (c1, c2) VALUES ('a', 1);
INSERT INTO t1 (c1, c2) VALUES ('b', 1);
INSERT INTO t1 (c1, c2) VALUES ('c', 1);

複数のVALUESリスト

INSERT INTO t1 (c1, c2) VALUES ('a', 1), ('b', 1), ('c', 1);

多くの行データを一度に送ることで、すべてのインデックスの更新と、一貫性のチェックを最後まで延期できるため、単一行INSERTに比べて、大幅なパフォーマンス向上が見込めます。

デフォルト値の省略

カラムにデフォルト値がある場合、デフォルト値と異なる場合のみ、値を指定する。

デフォルト値あり

INSERT INTO t1 (c1, c2) VALUES ('a', 1), ('b', 1), ('c', 1);

デフォルト値なし

INSERT INTO t1 (c1) VALUES ('a'), ('b'), ('c');

デフォルト値の登録をDB側に任せることにより、DBサーバーへのクエリー送信サイズやMySQLが実行するクエリー解析が減るため、パフォーマンス向上が見込めます。

AUTO_INCREMENTロック(innodb_autoinc_lock_mode)

InnoDBではAUTO_INCREMENTカラムを含むテーブルへINSERTを行う際、AUTO-INCロック呼ばれるテーブルレベルでロックする仕組みがあります。
これは、一度に複数行挿入されるINSERTステートメントに、該当ステートメント(トランザクションではない)が終了するまでAUTO-INCロック取得することで、仮に並列にINSERTステートメントが実行された場合も、1つのINSERTステートメントによって生成される自動インクリメント値は連続した値となることを保証するためです。
以下のように実行中のトランザクションが2つあり、トランザクション1の方が早く処理が実行されていた場合、

トランザクション１

INSERT INTO t1 (c1) SELECT another_c1 FROM another ...;

トランザクション2

INSERT INTO t1 (c1) VALUES ('a-2'), ('b-2'), ('c-2')...;

トランザクション1のステートメントが終了するまでAUTO-INCロックが保持されるため、トランザクション2の実行は待たされることになります。そのため、トランザクション2で発行されるAUTO_INCREMENT値はトランザクション1で挿入された行のすべてのAUTO_INCREMENT値より大きい値が割り当てられることになります。

なぜAUTO-INCロックが必要か

ステートメントベースのレプリケーションを使用していた場合、複数のINSERTステートメントが同じ順番に実行される限り、AUTO-INCロックの使用により、マスタ-スレーブ間でAUTO_INCREMENT値が同じになることが保証されます。

innodb_autoinc_lock_modeの設定値

このロックの挙動はinnodb_autoinc_lock_modeで変更することができます。
ロックの挙動を左右する、INSERTの種類は以下に分類されます。

• 単純挿入
  • 挿入行数が事前に(ステートメントの初期化時に)わかるステートメント。単一行および複数のVALUESリストのINSERT。
• 一括挿入
  • 挿入行数が事前にわからないステートメント。INSERT...SELECTなど。
• 混在モード挿入
  • INSERT ... ON DUPLICATE KEY UPDATEなど。

innodb_autoinc_lock_modeに設定できるパラメータは以下の3つです。

• innodb_autoinc_lock_mode=0 従来ロックモード

  • innodb_autoinc_lock_modeが存在する前と同じ挙動
  • ステートメントベースのレプリケーションで使用しても安全
  • 全てのINSERTステートメントでAUTO-INCロックが取得され、AUTO_INCREMENT値が連続した値になる
  • 並列性=×

• innodb_autoinc_lock_mode=1 連続ロックモード

  • 「単純挿入」の場合、AUTO-INCロックを取得しない、別のトランザクションが「一括挿入 or 混在モード挿入」でAUTO-INCロックを取得している場合はステートメントの実行が待たされる
  • ステートメントベースのレプリケーションで使用しても安全
  • 「混在モード挿入」の場合、実際に挿入される行以上にAUTO_INCREMENT値が増え、ステートメント間でAUTO_INCREMENT値のギャップが発生する
  • 並列性=△
  • MySQL8.0以前のデフォルト

• innodb_autoinc_lock_mode=2 インターリーブロックモード

  • 全てのINSERTステートメントでAUTO-INCロックを使用しない
  • ステートメントベースのレプリケーションでの使用は安全でない
  • 並列実行されている複数のステートメント間で同時にAUTO_INCREMENT値を割り当てているため、1ステートメントで挿入される行のAUTO_INCREMENT値の連続性は保証されない
  • 並列性=○
  • MySQL8.0のデフォルト

MySQL8.0以前で、ステートメントベースのレプリケーションでなくて良いケースでは、インターリーブロックモード(2)に変更することで、INSERTステートメントの並列性向上が見込めます。

変更バッファリング(innodb_change_buffering)

INSERT、UPDATE, DELETEステートメントの実行時に、セカンダリインデックスへの更新によるディスクIOを抑制する仕組みです。デフォルトはall。

innodb_change_bufferingの設定値

値	説明
all	挿入、削除マーキング、物理的な削除操作をバッファリング
none	バッファリングしない
inserts	挿入操作をバッファリング
deletes	削除マーキング操作をバッファリング
changes	挿入と削除マーキング操作をバッファリング
purges	物理的な削除操作をバッファリング

影響を受ける行と更新するセカンダリインデックスが大量にある場合は、変更バッファのマージに大量の時間がかかる場合もあるとのことで、注意が必要です。

なお、Amazon RDS for MySQLでは以下の理由からnoneが推奨されています。

Amazon RDS for MySQL のパラメータ設定 パート 1: パフォーマンス関連のパラメータ

変更バッファリングでは値を none にして無効にすることを推奨します。これは、all ではアップグレード時のシャットダウン時間がきわめて長くなるためです。この機能はディスク速度の遅い時代には有用でしたが、現在では意味がありません。

また、Aurora MySQLではそもそもこの設定値が無効化されています。

最後に

今回のまとめは以上となります。
間違った解釈などあれば、ご指摘いただければと思います。

参考

8.2.5.1 Optimizing INSERT Statements
15.5.2 Change Buffer
15.6.1.6 AUTO_INCREMENT Handling in InnoDB
AuroraかRDSどちらを選ぶべきか比較する話をDevelopers.IO 2019 in OSAKAでしました #cmdevio
適切ではない MySQL パラメータおよびステータス変数

１はじめに

Wordpressを勉強する人が最初にやることは、環境構築と管理画面の使い方だと思いますが！私はなんとデータベースから入りまして、管理画面⇒フォルダ構成と、なぜか逆走しながらWordpressを勉強中。今回は自作SQLを使ってアイキャッチ画像を取得したときのアハ体験をまとめました。

Wordpressのデータの全てはWP_POSTSが握っている！？

Wordpressの便利なところは、ネットで検索すればなんでも出てくるところ。それだけユーザが多く、みんな困ったことを共有しているからなのでしょう。
Wordpressのデータベース（以下、DB）は、全部で12テーブルしかないとてもシンプルなもの。こんなにいろいろ出来るのに、12テーブルしか持ってないことに驚きを隠せないのですが、本当すごいですね…。（※でもこの後、データベースの構造的には否定的な見解になってきたのでそれはまた後日。）
Wordpressの主となるデータは全てWP_POSTSにあると言ってもいいくらい、このテーブルはとっても重要なテーブルです。固定ページ・記事ページ・画像など、画面で見えるページのほとんどをこのテーブルで管理しています。

※参考：Wordpress Codex 日本語版（データベース構造）
http://wpdocs.osdn.jp/%E3%83%87%E3%83%BC%E3%82%BF%E3%83%99%E3%83%BC%E3%82%B9%E6%A7%8B%E9%80%A0

記事とアイキャッチ画像の紐づけはWP_POSTMETAで管理！

今回、記事に使われているアイキャッチ画像のURLを取得するためネットで調べていたのですが、どのサイトもPHPの処理ありきで書いてあるではありませんか。いやいや、私はSQL1本でとってきたいのです。だって、PHP分かんないんだもん(´;ω;｀)
でも、どの方法を見てもWP_POSTMETAのmeta_key="_thumnail"を条件に何かをみているではありませんか！（そういうのは分かる。笑）
どうやら、記事に使われているアイキャッチ画像の居場所を教えてくれているみたい…。ってか！数字しか書いてない！！どゆこと？と思ったら、どうやらその数字は、WP_POSTSのID情報だということが分かりました。（さすが！すべてを握っているテーブル！！）

アイキャッチ画像自体の情報はやっぱりWP_POSTSにある！

さっそくアイキャッチ画像のIDを指定して表示したところ、見事！WP_POSTSのguidにアイキャッチ画像のURLが格納されていました。神！これで、記事に使われているアイキャッチ画像の保存先を特定することが出来ました。

アイキャッチ画像取得のために作成した鬼SQLがこちら↓↓↓

下記に記載するSQLは、アイキャッチ画像を取得するためだけのSQLではなく、「現在公開中の記事＆ある特定のタグが付いている記事」という条件も追加しているSQLになっています。なので、途中ごちゃごちゃやっててすみません。
※[xxxxx]のところは、限定したいタグのタグIDを記載するところなので、このまま実行しても動かないのでご注意ください。

select
    post_list.id,
    post_list.term_id,
    post_list.name,
    post_list.post_title,
    post_list.post_date,
    img_list.guid
from
    (select
         post.meta_id,
         post.id,
         tag.term_id,
         tag.name,
         post.post_title,
         post.post_date
     from
         (select
              wp_postmeta.meta_id,
              wp_posts.ID,
              wp_posts.post_title,
              wp_posts.post_date
          from
              wp_postmeta left join wp_posts on wp_postmeta.post_id = wp_posts.id
          where
              wp_postmeta.meta_key = '_thumbnail_id'
         ) as post left join (select
                                  wp_posts.id,
                                  wp_terms.term_id,
                                  wp_terms.name
                              from
                                  wp_posts left join wp_term_relationships on wp_posts.ID = wp_term_relationships.object_id
                                           left join wp_term_taxonomy on wp_term_relationships.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id
                                           left join wp_terms on wp_term_taxonomy.term_id = wp_terms.term_id 
                              where
                                  wp_posts.post_status = 'publish' and
                                  wp_term_taxonomy.taxonomy = 'post_tag'
                             ) as tag on post.id = tag.id
    ) as post_list left join (select
                                  wp_postmeta.meta_id,
                                  wp_postmeta.meta_value,
                                  wp_posts.guid
                              from
                                  wp_postmeta left join wp_posts on wp_postmeta.meta_value = wp_posts.id 
                              where wp_postmeta.meta_key = '_thumbnail_id'
                             ) as img_list on post_list.meta_id = img_list.meta_id
where
post_list.term_id = '[xxxxx]' order by post_list.post_date DESC;

「長くて読む気にならん…汗」と近くに座っている人に言われたのですが（笑）、WP_POSTSが記事ページやら画像情報やらを全部レコード単位で保持しているのでややこしいだけで、実は結構考え方は簡単です。
上記SQLでは大きく分けて、post_listという私が勝手に名前を付けたSQL内のテーブルと、img_listというこれまた私が勝手に名前を付けたSQL内のテーブルが結合されています。それぞれのテーブルは、どちらもWP_POSTSから必要なデータを取得しています。つまり！WP_POSTSテーブル同士を結合させ、記事とアイキャッチ画像を紐づけているWP_POSTMETAで繋いでいる、そんなイメージで作成しました。（何言ってるか分からなかったらすみません…。）

WP_POSTSにあるpost_parentに騙されてはいけない！

はい、ここが今回のアハ体験によって声を大にして言いたいところです！！！
アイキャッチ画像のレコードを眺めていると、WP_POSTSのpost_parentに設定されている記事のIDが記載されているではありませんか！おお！WP_POSTMETAで確認しなくても、ここで見ればええやん！って早とちりしてまんまとハマりました。
このpost_paretには、確かにアイキャッチ画像を設定した記事IDが入ることもあるのですが、100%アイキャッチ画像として設定されている記事IDがセットされているわけではないのです。

アイキャッチ画像の場合、WP_POSTSのpost_parentの格納IDは管理画面の操作によって異なる

今となってはちょっとずつ理解できているのでなんとなく分かることも多いですが、これを調べていた時の私はWordpress Codexの各テーブルのフィールド解説を読んでもチンプンカン。親ってなんや！添付ファイルが所属する記事IDってなんや！何がどう違うんやっ！！！ってほぼほぼ半泣き。笑
アイキャッチ画像の登録をいろいろな方法で試してみた結果、post_parentに格納されるIDが3パターン存在することが分かりました。

パターン1：記事編集画面にあるところからアイキャッチ画像を新規登録
⇒この場合は記事IDが格納されます。

パターン2：メディアに新規登録したあと、記事編集画面からアイキャッチ画像を登録
⇒この場合は記事に依存しない画像という扱いになるので、一律ゼロが格納されます。

パターン3：他の記事で使用・登録された画像を作成中記事のアイキャッチ画像として使う
⇒この場合、流用元の記事IDが格納されます。

まとめ

WordpressはDBやプログラムが分からなくても簡単にWebサイトが作成出来るのが売りなのに、完全無視してDBを穴が開くほど見た結果、ちょっと楽しくなってきちゃいました。（今となっては前職の経験に感謝！笑）おそらく需要はないと思いますが、まだまだ同じようなアハ体験がけっこうあるので、ちょこちょこまとめて行きたいと思います。

おまけ

現在、絶賛中途採用募集中です！
もしご興味ありましたら、中途採用ページよりご応募ください。
※小声）一生懸命、中途採用ページをリニューアルしたので見に来てください！笑

■株式会社サンシーア@上野（最寄り駅：銀座線 稲荷町駅）
https://www.sunseer.co.jp/recruit/careers/position.html

よくある話

必要に迫られMySQLにAccessデータをインポートする事になり、四苦八苦した話です。ほぼ備忘録ですが、同じところで躓いている方のお役に立てれば。（類似記事は多かったけれど、中々自分のパターンにあてはまるものが無かった）

環境

・Windows10 Enterprise
・Docker Desktop for Windows：2.1.0.1
・MySQL：5.7.28
・MySQL Workbench 8.0

概要

・Docker ComposeでMySQLコンテナをビルド
・MySQL WorkBenchでコンテナのDBに接続し編集
・Accessからcsvエクスポート(UTF-8)したものをMySQLにインポート

インポート失敗

MySQL Workbenchのメニュー「Database」→「Migration Wizard」はちょっと面倒そうだったので、左メニューのインポートしたいデータベース名を右クリック→「Table Data Import Wizard」でインポート作業。
サクッといけると思ったけれど日本語が入っているとエラーでインポートできない。
テスト用にCSVで簡単なテーブルを作ってインポートしたが、１文字でも日本語が入っているレコードはインポートされなかった。

デフォルト文字コード変更

色々調べた中で、こちら↓の記事の内容で解消。助かりました。
MySQLでcharacter_set_databaseがlatin1になってしまう問題の対応方法

MySQLにログインした状態でコマンド「show variables like 'char%';」実行した際に表示される各文字コードの中で、character_set_databaseが「latin1」から変更出来ないのが原因だった。下記手順にて設定変更に成功した。

手順

１：mysqlログイン
２：コマンド「use {インポート先のDB名}」でDB指定
３：コマンド「show variables like 'char%';」実行　→　character_set_databaseが「latin1」になっている事を確認
４：コマンド「show create database `{インポート先のDB名}`;」　→　/*!40100 DEFAULT CHARACTER SET latin1 */になっている
５：「ALTER DATABASE `{インポート先のDB名}` default character set utf8;」実行し、デフォルトをUTF8に変更
６：コマンド「show variables like 'char%';」実行　→　character_set_databaseが「utf8」に変更された事を確認
７：MySQL WorkBenchで日本語含むcsvのインポート成功

おわりに

業務逼迫中のメモなので図も無いざっくりで申し訳ないですが、また余裕出来たらちゃんと書き直したいと思います。読んで頂いてありがとうございました。

よくある話

必要に迫られMySQLにAccessデータをインポートする事になり、四苦八苦した話です。ほぼ備忘録ですが、同じところで躓いている方のお役に立てれば。（類似記事は多かったけれど、中々自分のパターンにあてはまるものが無かった）

環境

・Windows10 Enterprise
・Docker Desktop for Windows：2.1.0.1
・MySQL：5.7.28
・MySQL Workbench 8.0

概要

・Docker ComposeでMySQLコンテナをビルド
・MySQL WorkBenchでコンテナのDBに接続し編集
・Accessからcsvエクスポート(UTF-8)したものをMySQLにインポート

インポート失敗

MySQL Workbenchのメニュー「Database」→「Migration Wizard」はちょっと面倒そうだったので、左メニューのインポートしたいデータベース名を右クリック→「Table Data Import Wizard」でインポート作業。
サクッといけると思ったけれど日本語が入っているとエラーでインポートできない。
テスト用にCSVで簡単なテーブルを作ってインポートしたが、１文字でも日本語が入っているレコードはインポートされなかった。

デフォルト文字コード変更

色々調べた中で、こちら↓の記事の内容で解消。助かりました。
MySQLでcharacter_set_databaseがlatin1になってしまう問題の対応方法

MySQLにログインした状態でコマンド「show variables like 'char%';」実行した際に表示される各文字コードの中で、character_set_databaseが「latin1」から変更出来ないのが原因だった。下記手順にて設定変更に成功した。

手順

１：mysqlログイン
２：コマンド「use {インポート先のDB名}」でDB指定
３：コマンド「show variables like 'char%';」実行　→　character_set_databaseが「latin1」になっている事を確認
４：コマンド「show create database `{インポート先のDB名}`;」　→　/*!40100 DEFAULT CHARACTER SET latin1 */になっている
５：「ALTER DATABASE `{インポート先のDB名}` default character set utf8;」実行し、デフォルトをUTF8に変更
６：コマンド「show variables like 'char%';」実行　→　character_set_databaseが「utf8」に変更された事を確認
７：MySQL WorkBenchで日本語含むcsvのインポート成功

おわりに

業務逼迫中のメモなので図も無いざっくりで申し訳ないですが、また余裕出来たらちゃんと書き直したいと思います。読んで頂いてありがとうございました。

例えば

favorite_table

name	food
tanaka	野菜ラーメン
satou	ちゃんぽん
ikeda	みそラーメン
numata	やさい
inoue

ラーメン以外のnameを取得したい時に

SELECT *
FROM favorite
WHERE food NOT LIKE 'ラーメン';

みたいにしてしまうと
inoueさんが出てこなくなってしまう。

今の所

WHERE food IS NULL OR food NOT LIKE 

ってやるとうまくいくみたいだけどもっといい書き方ないかな。

SQL Server で・・・

むかし、「Visual Basic」＋「SQL Server」で、
とある工場の生産管理のシステムを作ってまして、
１システムの中に、複数のデータベースが作られていました。

・共通ＤＢ（作業員のマスタとか）
・製品ＤＢ（製品の状態管理とか）
・資材ＤＢ
・購買ＤＢ
・ ：

最初に設計した人は、何となく業務単位で分けたのでしょう。
でも これが仇となってしまいました。
上記の複数のＤＢに接続するので、ＤＢコネクション数が膨れ上がるのです。

たとえば、工場内の端末が１００台あり、
１台につき「共通ＤＢ」と「製品ＤＢ」に接続すると、
単純計算で２００のコネクションが張られます。

工場立ち上げ時に、全端末の電源を入れたところ、
あっさり SQL Server のコネクション上限に達してしまい、
やむなく上限を引き上げたりしました。

いま考えれば、すべてをひとつのＤＢにすればよかったです。

MySQL で・・・

最近も、私が（途中から）担当するシステムに、Ａ業務とＢ業務の２業務があり、
MySQL(MariaDB) 上、やはり２つのＤＢに分かれていました。
これも最初の設計者が 何となくで分けたのでしょう。

そのせいで（場合によっては）わざわざ２つのＤＢに接続しなければなりません。
（あぁ めんどくさい。）

これも ひとつのＤＢにすればよかったです。

複数ＤＢどう使う？

そもそも どんなときに、１サーバ内に複数のＤＢを張るのでしょう？

思いつくのは、共用のレンタルサーバで、
お互いに無関係のユーザのために、独立したＤＢを準備するという使い方でしょう。

また あるとすれば、テストサーバで、
開発者ごとにデータベースを準備する、なども考えられます。

あと詳しくないのですが、ＤＭＰ（データマネジメントプラットフォーム）だと
利用するユーザやツールも変わってくるので、ＤＢを分けたりするのでしょうか？
（それ以前に、物理サーバが別かもしれませんが。）

最後に

create database は基本、１システムにつき１データベース！

何かしら関連があれば、ひとつのＤＢにまとめる。
何となくでＤＢを分けたら、後でめんどうになる（場合があります）

参考

RDBMS毎の概念の違い

はじめに

フロントエンジニアを目指してプログラミング学習をしている小林と申します。
本記事ではオリジナルWebサービス「Lunches」の概要や制作過程について説明します。

URL

リンク:Lunches

目的

• フルスクラッチ開発でPHP、SQLの言語理解を深める
• Webサービスの基本的な構成、動作を把握する

スペック

プログラミング言語:HTML５/ CSS3 / Javascript / PHP

データベース言語:MySQL

開発環境:macOS Catalina 10.15.1

バージョン管理:SourceTree

本番環境:さくらサーバー

機能

ユーザー管理機能
　・ユーザー登録
　・ログイン
　・プロフィール編集
　・ユーザー削除機能
　・退会

イベント機能(メインサービス)
　・イベント投稿
　・イベント詳細
　・イベント一覧(ページネーション)
　・カテゴリー検索、日付検索

サービス概要

「Lunches」はランチタイムに恋愛やビジネス、友達作りなどもイベントを気軽に開催することができる　
Webサービスです。
インターネットを通じて人との交流を活性化させることを目的として制作しました。

開発手順

1.ワイヤーフレーム作成

7つブラウザ画面をノートに手書きでワイヤーフレームを作成しました。

2.テーブル設計

実装させたい機能から必要な情報を洗い出し、それに応じてテーブルを作成しました。
作成したテーブルは以下の3つです。

users

event

category

3.画面モック作成

ワイヤーフレームを元にHTML・CSSでコーディングを行い画面モックを作成しました。

セキュリティ

バリデーションチェック

　・未入力チェック
　・Email型式チェック(正規表現)
　・Email重複チェック
　・最大、最小文字数チェック
　・半角英数字チェック
　・同値チェック

＊コード一部抜粋

例外処理

DBへ接続する際にはエラーで接続できない可能性を考慮して「try」「catch」で例外処理を行っています。

セッションIDの再生成

セッションハイジャックによって第三者による乗っ取りを防ぐためにsession_regenerate_idを使用
この関数をコールすることで現在のセッションデータを保持したまま、セッションIDを新しくすることができる。

パスワードハッシュ

DB側でユーザーのパスワードが漏れないようにパスワードをpassword_hashでセキュリティを高めています。

ログイン時にはpassword_verifyを使用してハッシュ化されたパスワードと照合しています。
このとき第一引数である$passにはフォームからpostされたパスワード
そして、第二引数にはDBから配列形式で取り出した情報を$resultに詰め
array_shiftを使って先頭のパスワードを取り出しています。

SQLインジェクション対策

DB接続時にはプレースホルダーを使用し、SQL文を作成。
値をバインドすることでSQLインジェクション対策を行っています。

Lunchesの使い方

①イベントの登録・　プロフィール編集

イベント投稿、プロフィール編集の画像登録ではjQueryを使用し、ドラック&ドロップでファイルをinputすることができます。

②イベント一覧・検索機能

イベント一覧ページではGETパラメータを使用して、ページネーションと検索機能を実装しています。
検索機能はカテゴリー検索、投稿日時のソート順検索ができます。

今後の課題

・スマートフォンにも対応したレスポンシブデザイン
・オブジェクト指向に基づく、保守性の高いコード設計
・FLOCSSをベースとしたCSS設計

主に以上の３点です。
特に様々なデバイスからアクセスされるユーザーを想定したレスポンシブデザインでの設計は
現在のWebサービスでは必要不可欠な物だと思いました。

この記事は、Docker Advent Calendar 2019の19日目の記事です。

TL;DR

• Dockerを開発環境で使う際に、replica databaseを使うようなアプリケーションにて、書き込みバグを検知する
• MySQLのデータベースにreadOnlyユーザーを作成してアプリケーションから利用する
• レプリカラグの考慮などには対応できないが、最低限書き込みバグを防ぐことができる

ReadOnlyユーザーを作る

Webアプリケーション開発において、PostgreSQLやMySQLといったRDBMSをシステムのデータベースとして使うケースは多いです。そのような構成において、負荷対策で参照クエリを逃がすと行った用途でReplicaインスタンス（読み込み専用）を用意することは多いでしょう。

このシステム構成で、Docker開発環境を作る場合、データベースサービスをローカルのDockerコンテナとして立てる際、間違えてReplicaデータベースに書き込むバグ作っちゃったとはならないようにしたいですよね。この目的感のもと採用できる打ち手の一つが、ReadOnlyユーザーを作成する方法です。

この方法は、すぐに実践できる手軽さもあり、それなりの数の実践事例を観測しています。たとえば、CakeFest 2019というCakePHPの国際カンファレンスでの「Working with Database Replications in CakePHP」という発表の中でも、レプリカデータベースに対して書き込んでしまうようなアプリケーションバグを検知するために、ReadOnlyユーザーを作成するという話をしていました。

実際に、筆者の現場でも同様の方法を採用しています。この方法はレプリケーションラグの考慮などを踏まえると完全なシミュレーション方法ではありませんが、初手としては良い方法と考えています。

実現例

今回実現する例は、次のような構成です。

• MySQLを利用、MySQLのimageは公式のものを利用します
• 複数コンテナサービスが必要なため docker-compose を用います

例題として取り上げるものは下記のGitHub repositoryに公開しています。

https://github.com/hgsgtk/health-endpoint

├── docker-compose.yml
├── docker
│   └── db
│       ├── conf.d
│       │   └── custom_my.cnf
│       └── docker-entrypoint-initdb.d
│           ├── 1_initialize_tables.sql
│           └── 2_readuser.sql
└── src
    └── Dockerfile

docker-compose.yml

早速、docker-compose.ymlを見てみましょう。Compose file formatはversion 3を用いています。

refs: https://docs.docker.com/compose/compose-file/

docker-compose.yml

version: '3'

services:
  health-api:
    build:
      context: "./src/"
    ports:
      - "8080:8080"
    depends_on:
      - db
    restart: on-failure
    env_file:
      - ".env"

  db:
    image: mysql:5.6
    ports:
      - "3306:3306"
    expose:
      - 3306
    environment:
      MYSQL_ROOT_PASSWORD: password
      MYSQL_DATABASE: healthy
      MYSQL_USER: wruser
      MYSQL_PASSWORD: password
    volumes:
      - ./docker/db/data:/var/lib/mysql:cached
      - ./docker/db/conf.d:/etc/mysql/conf.d:cached
      - ./docker/db/docker-entrypoint-initdb.d:/docker-entrypoint-initdb.d:cached

話を単純にするために２つのサービスのみを取り上げます。Web APIであるheath-apiとMySQLデータベースであるdbを定義しています。

mysql公式イメージの使い方のおさらい

環境変数

mysqlの公式イメージの使い方をざっとおさらいすると、MYSQL_ROOT_PASSWORDなど特定の環境変数を指定することで作成されるrootユーザーのパスワードなどを指定することが出来ます。

https://hub.docker.com/_/mysql のEnvironment Variablesを見ることで環境変数で設定できる項目を確認することが出来ます。

データの永続化

コンテナが停止したときにデータが消えてしまうと開発環境としては少し物足りませんね。データをコンテナ停止しても永続化された状態にするために、次の記述でボリュームマウントします。

docker-compose.yml

    volumes:
      - ./docker/db/data:/var/lib/mysql:cached

これは、 https://hub.docker.com/_/mysql のCaveats > Where to Store Dataに説明されています。Git管理下におくとMySQLのデータがそのままコミットされてしまうので、対象ディレクトリを.gitignoreに追加しておくのを忘れないようにしましょう。

.gitignore

# docker database local mount files
docker/db/data

Custom MySQL Configuration

デフォルトの設定ではなく、ユーザー自身でMySQLの設定をカスタムしたいという場合は、コンテナ内の/etc/mysql/conf.dに設定をマウントします。これは、 https://hub.docker.com/_/mysql の Using a custom MySQL configuration file に説明されています。

docker-compose.yml

    volumes:
      - ./docker/db/conf.d:/etc/mysql/conf.d:cached

例えば、このケースではつぎのようなmy.cnfを設定します。

docker/db/cnf.d/custom_my.cnf

[mysqld]
explicit_defaults_for_timestamp=1
symbolic-links=0
sql_mode=TRADITIONAL,NO_AUTO_VALUE_ON_ZERO,ONLY_FULL_GROUP_BY
character-set-server=utf8

[client]
default-character-set=utf8

ReadOnlyユーザーを設定する

ReadOnlyユーザーを設定します。docker-compose.ymlでは次の設定項目が該当します。

docker-compose.yml

    volumes:
      - ./docker/db/docker-entrypoint-initdb.d:/docker-entrypoint-initdb.d:cached

まずは、docker-entrypoint-initdb.dについてですが、コンテナ内の/docker-entrypoint-initdb.dに.sh・.sql・.sql.gz拡張子のファイルをおくと、初期化の実行してくれます。

そのため、この初期化時にReadOnlyユーザーを作るSQLを実行すれば、ReadOnlyユーザーを作成できます。

docker/db/docker-entrypoint-initdb.d/2_readuser.sql

GRANT SELECT, PROCESS ON *.* TO 'reader'@'%' IDENTIFIED BY 'password';

ファイル名ですが、

Files will be executed in alphabetical order.

という記述が、https://hub.docker.com/_/mysql の Initializing a fresh instanceにあることが理由です。アルファベット順にソートされ実行されるので、明示的に順番になるように数字を先頭にしています。

実際にReadOnlyユーザーが作れているか

実際に、rootアカウントでデータベースにアクセスし、ユーザーが作られているか確認します。mysql.userテーブルに対して検索すると次のような結果が得られます。

SELECT 
    User
FROM
    mysql.user;

reader
root
wruser
root

作成したReadOnlyユーザーであるreaderを確認することが出来ます。このユーザーには、GRANTで権限を付けているので、SELECTとPROCESSが可能なユーザーになっています。

SELECT 
    User,
    Select_priv,
    Insert_priv,
    Update_priv,
    Delete_priv,
    Process_priv
FROM
    mysql.user
WHERE
    User = 'reader';

reader  Y   N   N   N   Y

これで、このデータベースを使うコンテナサービスが、レプリカ接続にReadOnlyユーザーを使うことで、誤って書き込みがおこなれても無事失敗するようになります。

ReadOnlyユーザーで書き込みをした場合

INSERT INTO customers VALUES (1, now(), now())

INSERT command denied to user 'reader'@'172.27.0.1' for table 'customers'

余談: test_データベース

MySQL、とくに5.6の挙動に詳しい方であれば、ReadOnlyユーザーと聞いたときに、「テスト用データベースの場合の考慮はいらないだろうか」と疑問に思うかもしれません。MySQL 5.6の挙動として、次のような仕様があります。

さらに、mysql.db テーブルにはすべてのアカウントが test データベースおよび test_ で始まる名前を持つその他のデータベースにアクセスすることを許可する行が含まれます。これは、デフォルトの匿名アカウントのように、そうでなければ特別な権限を持たないアカウントにも当てはまります。

https://dev.mysql.com/doc/refman/5.6/ja/default-privileges.html

この仕様のままであれば、testあるいはtest_で始まるデータベースの場合、ReadOnlyユーザーの権限を作っても書き込めてしまうことになります。これに対して、MySQL管理者への推奨は、

データベースへのアクセスを、その目的のために明示的に許可を付与されたアカウントのみに制限する場合は、管理者は mysql.db テーブルのこれらの行を削除するとよいでしょう。

とある通り、mysql.dbテーブルから該当行を削除することでこの挙動を無効にする対応が必要と説明しています。

これは、MySQLの公式イメージでは実際意識する必要はありません。なぜなら、イメージのdocker-entrypoint.shにて、該当行の削除をしてくれているからです。

DELETE FROM mysql.db WHERE Db='test' OR Db='test\_%' ;

https://github.com/docker-library/mysql/blob/6659750146b7a6b91a96c786729b4d482cf49fe6/5.6/docker-entrypoint.sh#L241

公式イメージを使用すると、このようなコミュニティのナレッジを対応してくれているものを利用できるのが利点ですね。

最後に

ReadOnlyユーザーを作る方法は、比較的手軽に実践できるので、まだやっていない方は、一つの選択肢として検討してみてはいかがでしょうか。

TL;DR

MySQL覚えられなくて同じことを何度もググってしまうので自分用にチートシートを作成しておく。プレーンのmysqlコマンドではなくmycliを使うと補完が利くので便利。

チートシート

CREATE

AUTO_INCREMENT, NOT NULLの付与

CREATE TABLE table_name (col_name1 type NOT NULL AUTO_INCREMENT)

あとからAUTO_INCREMENTを付与する

ALTER TABLE table_name CHANGE col_name type AUTO_INCREMENT

UPDATE

データの更新

UPDATE table_name SET col_name1=value1 [, col_name2=value2, ...] [WHERE condition]

DELETE

データの削除

DELETE FROM table_name [WHERE condition]

参考

• https://www.dbonline.jp/mysql/
• http://blog.doli.jp/blog/2012/post509/

データベースとは

　　「複数で共有」「検索、加工」される、一定の形式で整理されたデータの集まりのこと。

データベースの活用

　　事例1)
　　　①facebookの自分のページにアクセス
　　　②facebookのデータベース内にある自分の情報を探す
　　　　(氏名、メールアドレス、投稿内容、投稿した写真等)
　　　③インターネットを経由して自分の情報を呼び出し、ブラウザ上で表示

　　事例2)
　　　①ショッピングサイトにアクセスし、欲しい商品を検索
　　　②検索条件を受け、データベース内でマッチした商品情報を探す
　　　③インターネットを経由してマッチした商品情報を呼び出し、ブラウザ上で表示

リレーショナルデータベース(RDB)

　　　一般的に使用されているデータベースは下記の表のようなリレーショナル・データベースである。

氏名	電話番号	年齢	住所
山田　太郎	090-0000-0000	20歳	東京都千代田区
佐藤　次郎	090-0000-0000	25歳	千葉県千葉市
鈴木　花子	090-0000-0000	30歳	神奈川県横浜市

　　表全体をtable
　　表の列をカラム=フィールドという
　　表の行をロー=レコードという

SQLとは

　　プログラミング言語とは違い、RDBMS(データベース管理システム/Relational DataBase Management System)専用に作られた問い合わせ言語。
　　SQL(Structured Query Language)
　　

MySQLとは

　　MySQLは、世界で最も利用されているデータベース管理システム。
　　大容量のデータに対しても高速で動作し、機能も豊富で実用性が高い。

クエリとトランザクション

　　MySQLに限らず、データベースは、使用者が発行する命令によって動作しており、
　　この命令を一般にクエリという。
　　そのクエリの集合をトランザクションという。
　　それらのクエリがすべて適用できた場合のみデータベースに反映される。
　　一つでも適用できないクエリがあった場合、そのまとまりすべてのクエリの結果は反映されない。

コミットとロールバック

　　データベースのユーザーは、トランザクションにおいて、コミット(COMMIT)とロールバック(ROLLBACK)を行うことができる。
　　処理を確定する場合にはコミット、データベースに対する変更処理をすべて取り消す場合、ロールバックを行う。
　　※コミットを行うと、ロールバックによる処理の取り消しはできなくなる

データ型

　　MySQLにおけるデータ型
■整数型

型	バイト	最小値	最大値
TYNYINT	1	-128	127
SMALLINT	2	-32768	32767
MEDIUMINT	3	-8388608	83388607
INTEGER	4	-2147483648	2147483647
BIGINT	8	-9223372036854775808	9223372036854775807

■浮動小数点型

型	バイト	最小値	最大値
FLOAT	4	-3.4-2823466E+38〜-1.175494351E-38	1.175494351E-38〜3.402823466E+38
DOUBLE	8	-1.7976931348623157E+308〜-2.2250738585072014E-308	2.2250738585072014E-308〜1.7976931348623157E+308

■文字列型

型	バイト	特徴
CHAR	255	固定長文字列
VARCHAR	255	可変長文字列
TEXT	６５５５３５	文章に利用

■日付・時刻型

型	内容	範囲
DATETIME	日付と時刻	'1000-01-01　00:00:00'〜'9999-12-31　23:59:59'
TIMESTAMP	日付と時刻	'1970-01-01　00:00:01.000000'UTC〜'2038-01-19　03:14:07.999999'
DATE	日付	'1000-01-01'〜'9999-12-31
TIME	時刻	'-838:59:59〜:838:59:59'
YEAR	時刻	'1901〜2155'

MySQLの使用方法

　　①レンタルサーバーを借りて、そのサーバー上でMySQLを使用する
　　②Linuxサーバを自分のPCにインストールして、その上でMySQLを使用する
　　③XAMPPを自分のPCにインストールして、その上でMySQLを使用する

XamppとMamp

　　Xamppは、Windows、Linux、maxOS、Solarisなどの様々なOSと、Apache(webサーバ)
　　MariaDB(SQLデータベースサーバ;旧バージョンはMySQL)、PHPがパッケージになったもの。

　　Mampは、Xampp同様の開発環境がWindowsとmacOSのデスクトップ上で使用できる。
　　※macユーザーは一般的にMampを使用することが多い

テーブルの作成と削除

対応するSQL文	操作内容/意味/活用事例
Create文	データベースにテーブルを作成する
Drop文	作成したテーブルを削除する

テーブルの操作

　データベース操作は大きく４つに分かれる。
　※CRUDと呼ばれる

名称	対応するSQL文	操作内容/意味/活用事例
Create	insert文	データをテーブルに書き込む。 Facebookに新規で自分の名前を登録する際には、システムの裏側で、insert文が走っている
Read	select文	テーブルに入っているデータを呼び出す。 アカウントを持っているFacebookにログインした際に、自分のプロフィールや投稿内容が表示されるとき、その裏側ではselect文が走っている。
Update	update文	テーブルにすでに入っているデータを呼び出す。 facebookにすでに登録してあるメールアドレスを変更する際、その裏側ではupdate文が走っている。
Delete	delete文	テーブルにすでに入っているデータを削除する。 facebookにすでに登録してあるアカウントを削除する際に、その裏側でdelete文が走っている。

SQL文

　データベースを操作する文のことを総称して、SQL文という。
　※SQL分は、大文字小文字の区別がなく、改行してもしなくても良い

create文

　　テーブルを作成しデータを書き込むSQL文。
　　create　table　任意のテーブル名(
　　カラム名　データ型、　//int(数字)、varchar(文字列)、decimal(金額)等がある
　　カラム名　データ型
　　);

　例)テーブル「addresslist」を作成し、user_id、name、mail、tellとういう名前のカラムを作成
　　create table addresslist(
　　 user_id int(11),
　　 name varchar(255),
　　 mail varchar(255),
　　 tell varchar(255),
　　 prefecture varchar(255)
　　);

　●デーブル/データベースが存在していない場合、それを作成する場合
　　create table/database if note exists テーブル名/データベース名;

insert文

　　空のテーブルにデータを挿入するSQL文。
　　insert　into　テーブル名　values
　　（"挿入するデータ","挿入するデータ","挿入するデータ"）;

　例1)データを1行追加するinsert文
　　insert into addresslist VALUES
　　　("1","山田太郎","abcd123@yahoo.co.jp","03-0000-0000","東京");

　例2)データを複数行追加するinsert文
　　insert into addresslist VALUES
　　　("2","佐藤花子","xyz777@yahoo.co.jp","073-0000-0000","神奈川"),
　　　("3","田中浩史","hello888@yahoo.co.jp","045-0000-0000","静岡"),
　　　("4","鈴木次郎","efg123@yahoo.co.jp","080-0000-0000","沖縄"),
　　　("5","藤田三郎","ccc999@yahoo.co.jp","090-0000-0000","千葉");

select文

　　テーブルに入っているデータを抽出するために使用するSQL文
　　select　抽出対象のカラム名　from　抽出対象のテーブル名;
　　※すべてのカラムを対象とする場合はカラム名の箇所に「*」と記述する

例1）addresslistのすべてのデータを抽出するselect文
　　select * from addresslist;

例2）addresslistの指定するカラム(name)を抽出するselect文
　　select name from addresslist;

例3）addresslistのprefectureが千葉のフィールドのうち、指定するカラム(mail)を抽出するselect文
　　select mail from addresslist where prefecture = "千葉";
　　※where以下には、演算子を使用することもできる

update文

　　テーブルに入っているデータを上書きして更新するSQL文
　　update　対象のテーブル名　set　上書き対象のカラム名　=　"上書きするデータ"　where　上書きするフィールドの指定

　例1)addresslistのuser_idが1のレコードのprefectureを埼玉に上書きするupdate文
　　update addresslist set prefecture = "埼玉" where user_id = 1;

delete文

　　テーブルに入っているデータを削除するSQL文
　　delete　from　対象のテーブル名;

　例2)addresslistのuser_idが3の行を削除するdelete文
　　delete from addresslist where user_id = "3"'

ソート

　　select文でデータを抽出する際に、データを並び替えて表示できる。
　　これをソートという。
　　select　カラム名　from　テーブル名　order　by　カラム名　asc;
　　※ascは昇順、descは降順となる、何も書かなければ昇順となる

　例1)fruit_stockテーブルのすべてのデータをnumberカラムの昇順に並べ替えるソート文
　　select * from fruit_stock order by number asc;

　例2)fruit_stockテーブルのすべてのデータのうち、numberが25以上のものを、priceカラムの降順に並べ替えるソート文
　　select * from fruit_stock where number >= 25 order by price desc;

データの集計

　　select文を使って、データの合計値、最大値、平均値などのデータの集会ができる。

データの件数を取得

　　select　count(カラム名)from　テーブル名;

　例1)fruit_stockテーブルのfruitカラムのデータ件数を取得するselect文
　　select count(fruit) from fruit_stock;

　例2)fruit_stockテーブルの中から、priceが200以上で、madeinが日本のfruitカラムのデータ件数を取得する条件付きselect文
　　select count(fruit) from fruit_stock where price >= 200 and madein = "日本";

データの合計値を取得

　　select　sum(カラム名)　from　テーブル名;

　例)fruit_stockテーブルのnumberカラムの合計値を取得するselect文
　　select sum(number) from fruit_stock;

データの最大値を取得

　　select　max(カラム名)　from　テーブル名;

データの最大値を取得

　　select　min(カラム名)　from　テーブル名;

データの平均値を取得

　　select　avg(カラム名)　from　テーブル名;

コマンドプロンプト/ターミナルでのMySQL操作

　　ターミナルでのMySQL操作の前に、以下の手順が必要。
　　①Xampp/Mampを起動し、ApacheとMySQLを起動
　　②MySQLにログインコマンドでログイン
　　③useコマンドで使用したいデータベースへ移動
　　④select、insert、descコマンドなどでデータベースを操作

ログインコマンド

コマンド	意味
mysql	mysqlにログインするためのコマンド
-u	user(ユーザー)オプション：-uの次にユーザー名を指定し、そのアカウントでログイン
root	root(ルート)は最高権限をもつユーザー ※会社等の組織では、通常rootではなく、固定された権限を持つユーザーアカウントを使う
-p	password(パスワード)オプション：このコマンドの後にパスワードが求められる

基本操作コマンド

コマンド	意味
show	データベースやテーブルを見るためのコマンド
use	使用したいデータベースへ移動するためのコマンド
desc	テーブルの構造やデータを見るためのコマンド

show

　　データベース一覧を表示するコマンド
　　show databases;

use

　　使用したいデータベースにするコマンド
　　use データベース名;

show

　　テーブル一覧を表示するコマンド
　　show tables;

desc

　　テーブルの構造を表示するコマンド
　　desc テーブル名;

select

　　テーブルのデータを表示するコマンド
　　select * from テーブル名;

drop

　　テーブル/データベース自体を削除するコマンド
　　drop table/database テーブル名/データベース名;

　　デーブル/データベースが存在している場合、それを削除する
　　drop table/database if exists テーブル名/データベース名;

ALTER TABLE

　　　一度作成されたテーブルはALTER TABLEというコマンドを使用してテーブル構造の変更・削除・追加ができる。
　　alter　table　テーブル名;

　　◆テーブル名を変更
　　　alter table テーブル名 rename 新しいテーブル名;

　　◆カラム名を変更
　　　alter table テーブル名 change 変更するカラム名 データ型;

　　◆カラムのデータ型のみを変更
　　　alter table テーブル名 modify 変更するカラム名 新しいデータ型;

　　◆カラムを追加
　　　alter table テーブル名 add カラム名 データ型;

　　◆カラムを削除
　　　alter table テーブル名 drop カラム名;

--テーブルの作成
mysql> create table sumple01(
    -> id int,
    -> name varchar(255),
    -> email varchar(255),
    -> class enum("no1","no2","no3")
    -> ); 
Query OK, 0 rows affected (0.02 sec)

--テーブルにデータを入力
mysql> insert into sumple01 values
    -> (1,"田中さん","tanaka@yahoo.co.jp","no1"),
    -> (2,"山田さん","yamada@gmail.com","no3"),
    -> (3,"木村さん","kimura@yahoo.co.jp","no2");
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

--テーブルをすべて表示
mysql> select * from sumple01;
+------+--------------+--------------------+-------+
| id   | name         | email              | class |
+------+--------------+--------------------+-------+
|    1 | 田中さん     | tanaka@yahoo.co.jp | no1   |
|    2 | 山田さん     | yamada@gmail.com   | no3   |
|    3 | 木村さん     | kimura@yahoo.co.jp | no2   |
+------+--------------+--------------------+-------+
3 rows in set (0.00 sec)

--テーブルの構造を表示
mysql> desc sumple01;
+-------+-------------------------+------+-----+---------+-------+
| Field | Type                    | Null | Key | Default | Extra |
+-------+-------------------------+------+-----+---------+-------+
| id    | int(11)                 | YES  |     | NULL    |       |
| name  | varchar(255)            | YES  |     | NULL    |       |
| email | varchar(255)            | YES  |     | NULL    |       |
| class | enum('no1','no2','no3') | YES  |     | NULL    |       |
+-------+-------------------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

--テーブルの名前を変更
mysql> alter table sumple01 rename sample02;
Query OK, 0 rows affected (0.00 sec)

--テーブルを表示
mysql> show tables;
+--------------------+
| Tables_in_lesson02 |
+--------------------+
| sample02           |
+--------------------+
1 row in set (0.00 sec)

--テーブルの中身を表示
mysql> select * from sample02;
+------+--------------+--------------------+-------+
| id   | name         | email              | class |
+------+--------------+--------------------+-------+
|    1 | 田中さん     | tanaka@yahoo.co.jp | no1   |
|    2 | 山田さん     | yamada@gmail.com   | no3   |
|    3 | 木村さん     | kimura@yahoo.co.jp | no2   |
+------+--------------+--------------------+-------+
3 rows in set (0.00 sec)

--idカラムをNumberカラムに名称変更
mysql> alter table sample02 change id Number int;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

--結果の確認
mysql> select * from sample02;
+--------+--------------+--------------------+-------+
| Number | name         | email              | class |
+--------+--------------+--------------------+-------+
|      1 | 田中さん     | tanaka@yahoo.co.jp | no1   |
|      2 | 山田さん     | yamada@gmail.com   | no3   |
|      3 | 木村さん     | kimura@yahoo.co.jp | no2   |
+--------+--------------+--------------------+-------+
3 rows in set (0.00 sec)

--emailカラムをmailaddlessカラムに名称変更
mysql> alter table sample02 change email mailaddless varchar(255);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

--結果の確認
mysql> select * from sample02;
+--------+--------------+--------------------+-------+
| Number | name         | mailaddless        | class |
+--------+--------------+--------------------+-------+
|      1 | 田中さん     | tanaka@yahoo.co.jp | no1   |
|      2 | 山田さん     | yamada@gmail.com   | no3   |
|      3 | 木村さん     | kimura@yahoo.co.jp | no2   |
+--------+--------------+--------------------+-------+
3 rows in set (0.00 sec)

--テーブルの構造を表示
mysql> desc sample02;
+-------------+-------------------------+------+-----+---------+-------+
| Field       | Type                    | Null | Key | Default | Extra |
+-------------+-------------------------+------+-----+---------+-------+
| Number      | int(11)                 | YES  |     | NULL    |       |
| name        | varchar(255)            | YES  |     | NULL    |       |
| mailaddless | varchar(255)            | YES  |     | NULL    |       |
| class       | enum('no1','no2','no3') | YES  |     | NULL    |       |
+-------------+-------------------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

--classカラムの型のみをintからvarcharに変更
mysql> alter table sample02 modify class varchar(255);
Query OK, 3 rows affected (0.03 sec)
Records: 3  Duplicates: 0  Warnings: 0

--結果の確認
mysql> desc sample02;
+-------------+--------------+------+-----+---------+-------+
| Field       | Type         | Null | Key | Default | Extra |
+-------------+--------------+------+-----+---------+-------+
| Number      | int(11)      | YES  |     | NULL    |       |
| name        | varchar(255) | YES  |     | NULL    |       |
| mailaddless | varchar(255) | YES  |     | NULL    |       |
| class       | varchar(255) | YES  |     | NULL    |       |
+-------------+--------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

--テーブルの中身を表示
mysql> select * from sample02;
+--------+--------------+--------------------+-------+
| Number | name         | mailaddless        | class |
+--------+--------------+--------------------+-------+
|      1 | 田中さん     | tanaka@yahoo.co.jp | no1   |
|      2 | 山田さん     | yamada@gmail.com   | no3   |
|      3 | 木村さん     | kimura@yahoo.co.jp | no2   |
+--------+--------------+--------------------+-------+
3 rows in set (0.00 sec)

--ageカラムを追加
mysql> alter table sample02 add age int;
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

--結果の確認
mysql> select * from sample02;
+--------+--------------+--------------------+-------+------+
| Number | name         | mailaddless        | class | age  |
+--------+--------------+--------------------+-------+------+
|      1 | 田中さん     | tanaka@yahoo.co.jp | no1   | NULL |
|      2 | 山田さん     | yamada@gmail.com   | no3   | NULL |
|      3 | 木村さん     | kimura@yahoo.co.jp | no2   | NULL |
+--------+--------------+--------------------+-------+------+
3 rows in set (0.00 sec)

--classカラムの削除
mysql> alter table sample02 drop class;
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

--結果の確認
mysql> select * from sample02;
+--------+--------------+--------------------+------+
| Number | name         | mailaddless        | age  |
+--------+--------------+--------------------+------+
|      1 | 田中さん     | tanaka@yahoo.co.jp | NULL |
|      2 | 山田さん     | yamada@gmail.com   | NULL |
|      3 | 木村さん     | kimura@yahoo.co.jp | NULL |
+--------+--------------+--------------------+------+
3 rows in set (0.00 sec)

テーブル結合

　　テーブル結合とは、作成してある既存のテーブル同士をつなぎ合わせて表示すること。

　 　 　 　

結合の種類		コマンド/書き方	意味/使い方
内部結合		INNNER JOIN / JOIN	それぞれのテーブルの指定した列の値が一致するデータだけを取得する。 nullとしてデータは取得しない。
外部結合	左外部結合	LEFT OUTER JOIN / LEFT JOIN	それぞれのテーブルの指定した列の値が一致しない場合も、 nullとしてデータを取得。左のテーブル(selectの右に記述するテーブル)を基準にして結合。
	右外部結合	RIGHT OUTER JOIN / RIGHT JOIN	それぞれのテーブルの指定した列の値が一致しない場合も、 nullとしてデータを取得。右のテーブル(joinの右に記述するテーブル)を基準にして結合。

内部結合(INNER JOIN)

　　テーブル①とテーブル②を比較し、結合条件に合ったテーブル内の値を表示する
　　SELECT　カラム名　FROM　テーブル名①
　　　　INNER JOIN　テーブル名②　ON　結合の条件
　　※テーブル①の中で、結合の条件と合致したデータをテーブル②から取得する

左外部結合(LEFT OUTER JOIN)

　　テーブル①を基準として、テーブル②から結合条件に合ったデータを取得し、テーブル①に結合して表示する
　　SELECT　カラム名　FROM　テーブル名①
　　　　LEFT OUTER JOIN　テーブル名②　ON　結合の条件
　　※テーブル①が基準となり、結合の条件と合致したデータをテーブル②から取得する

右外部結合(RIGHT OUTER JOIN)

　　テーブル①を基準として、テーブル②から結合条件に合ったデータを取得し、テーブル①に結合して表示する
　　SELECT　カラム名　FROM　テーブル名②
　　　　LEFT OUTER JOIN　テーブル名①　ON　結合の条件
　　※テーブル①が基準となり、結合の条件と合致したデータをテーブル②から取得する

--fruitsデータベースの作成
mysql> create database fruits;
Query OK, 1 row affected (0.00 sec)

--fruitsデータベースの使用の宣言
mysql> use fruits;
Database changed

--table_aテーブルの作成
mysql> create table table_a(
    -> fruit_a int, name varchar(255), price int)
    -> ;
Query OK, 0 rows affected (0.03 sec)

--table_aテーブルにデータを入力
mysql> insert into table_a values
    -> (1,"りんご",100),
    -> (2,"みかん",150),
    -> (3,"バナナ",140);
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

--table_aのデータの確認
mysql> select * from table_a;
+---------+-----------+-------+
| fruit_a | name      | price |
+---------+-----------+-------+
|       1 | りんご    |   100 |
|       2 | みかん    |   150 |
|       3 | バナナ    |   140 |
+---------+-----------+-------+
3 rows in set (0.00 sec)

--table_bテーブルの作成
mysql> create table table_b(
    -> fruit_id int, place varchar(255), stock int);
Query OK, 0 rows affected (0.03 sec)

----table_bテーブルにデータを入力
mysql> insert into table_b values
    -> (1,"青森",5),
    -> (2,"愛媛",30),
    -> (3,"沖縄",20),
    -> (4,"東京",50),
    -> (5,"長野",10),
    -> (6,"和歌山",25);
Query OK, 6 rows affected (0.00 sec)
Records: 6  Duplicates: 0  Warnings: 0

--table_bのデータの確認
mysql> select * from table_b;
+----------+-----------+-------+
| fruit_id | place     | stock |
+----------+-----------+-------+
|        1 | 青森      |     5 |
|        2 | 愛媛      |    30 |
|        3 | 沖縄      |    20 |
|        4 | 東京      |    50 |
|        5 | 長野      |    10 |
|        6 | 和歌山    |    25 |
+----------+-----------+-------+
6 rows in set (0.00 sec)

--table_cテーブルの作成
mysql> create table table_c(
    -> place varchar(225), shopping_fee int
    -> );
Query OK, 0 rows affected (0.02 sec)

--table_cにデータを入力
mysql> insert into table_c values
    -> ("青森",400),
    -> ("愛媛",400),
    -> ("沖縄",650),
    -> ("東京",250),
    -> ("長野",350),
    -> ("和歌山",350);
Query OK, 6 rows affected (0.01 sec)
Records: 6  Duplicates: 0  Warnings: 0

--table_cの構造の確認
mysql> select * from table_c;
+-----------+--------------+
| place     | shopping_fee |
+-----------+--------------+
| 青森      |          400 |
| 愛媛      |          400 |
| 沖縄      |          650 |
| 東京      |          250 |
| 長野      |          350 |
| 和歌山    |          350 |
+-----------+--------------+
6 rows in set (0.00 sec)

--table_aのfruit_aカラムをfruit_idに名称変更
mysql> alter table table_a change fruit_a fruit_id int;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

--内部結合
mysql> select * from table_a INNER JOIN table_b on table_a.fruit_id = table_b.fruit_id;
+----------+-----------+-------+----------+--------+-------+
| fruit_id | name      | price | fruit_id | place  | stock |
+----------+-----------+-------+----------+--------+-------+
|        1 | りんご    |   100 |        1 | 青森   |     5 |
|        2 | みかん    |   150 |        2 | 愛媛   |    30 |
|        3 | バナナ    |   140 |        3 | 沖縄   |    20 |
+----------+-----------+-------+----------+--------+-------+
3 rows in set (0.00 sec)

--table_bのfruit_idが５のものを1に変更
mysql> update table_b set fruit_id = 1 where fruit_id = 5;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

--table_bの構造の確認(上記変更の確認)
mysql> select * from table_b;
+----------+-----------+-------+
| fruit_id | place     | stock |
+----------+-----------+-------+
|        1 | 青森      |     5 |
|        2 | 愛媛      |    30 |
|        3 | 沖縄      |    20 |
|        4 | 東京      |    50 |
|        1 | 長野      |    10 |
|        6 | 和歌山    |    25 |
+----------+-----------+-------+
6 rows in set (0.00 sec)

--table_bのfruit_idが６のものを２に変更
mysql> update table_b set fruit_id = 2 where fruit_id = 6;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

--table_bの構造の確認(上記変更の確認)
mysql> select * from table_b;
+----------+-----------+-------+
| fruit_id | place     | stock |
+----------+-----------+-------+
|        1 | 青森      |     5 |
|        2 | 愛媛      |    30 |
|        3 | 沖縄      |    20 |
|        4 | 東京      |    50 |
|        1 | 長野      |    10 |
|        2 | 和歌山    |    25 |
+----------+-----------+-------+
6 rows in set (0.00 sec)

--内部結合
mysql> select * from table_a INNER JOIN table_b on table_a.fruit_id = table_b.fruit_id;
+----------+-----------+-------+----------+-----------+-------+
| fruit_id | name      | price | fruit_id | place     | stock |
+----------+-----------+-------+----------+-----------+-------+
|        1 | りんご    |   100 |        1 | 青森      |     5 |
|        2 | みかん    |   150 |        2 | 愛媛      |    30 |
|        3 | バナナ    |   140 |        3 | 沖縄      |    20 |
|        1 | りんご    |   100 |        1 | 長野      |    10 |
|        2 | みかん    |   150 |        2 | 和歌山    |    25 |
+----------+-----------+-------+----------+-----------+-------+
5 rows in set (0.00 sec)

--table_aの構造の確認
mysql> select * from table_a;
+----------+-----------+-------+
| fruit_id | name      | price |
+----------+-----------+-------+
|        1 | りんご    |   100 |
|        2 | みかん    |   150 |
|        3 | バナナ    |   140 |
+----------+-----------+-------+
3 rows in set (0.01 sec)

--table_bの構造の確認
mysql> select * from table_b;
+----------+-----------+-------+
| fruit_id | place     | stock |
+----------+-----------+-------+
|        1 | 青森      |     5 |
|        2 | 愛媛      |    30 |
|        3 | 沖縄      |    20 |
|        4 | 東京      |    50 |
|        1 | 長野      |    10 |
|        2 | 和歌山    |    25 |
+----------+-----------+-------+
6 rows in set (0.00 sec)

--table_cの構造の確認
mysql> select * from table_c;
+-----------+--------------+
| place     | shopping_fee |
+-----------+--------------+
| 青森      |          400 |
| 愛媛      |          400 |
| 沖縄      |          650 |
| 東京      |          250 |
| 長野      |          350 |
| 和歌山    |          350 |
+-----------+--------------+
6 rows in set (0.00 sec)

--table_bのplaceカラムをplace_idに名称変更
mysql> alter table table_b change place place_id varchar(255);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

--table_bの構造の確認(上記変更の確認)
mysql> select * from table_b;
+----------+-----------+-------+
| fruit_id | place_id  | stock |
+----------+-----------+-------+
|        1 | 青森      |     5 |
|        2 | 愛媛      |    30 |
|        3 | 沖縄      |    20 |
|        4 | 東京      |    50 |
|        1 | 長野      |    10 |
|        2 | 和歌山    |    25 |
+----------+-----------+-------+
6 rows in set (0.00 sec)

--内部結合
mysql> select * from table_b INNER JOIN table_c on table_b.place_id = table_c.place;
+----------+-----------+-------+-----------+--------------+
| fruit_id | place_id  | stock | place     | shopping_fee |
+----------+-----------+-------+-----------+--------------+
|        1 | 青森      |     5 | 青森      |          400 |
|        2 | 愛媛      |    30 | 愛媛      |          400 |
|        3 | 沖縄      |    20 | 沖縄      |          650 |
|        4 | 東京      |    50 | 東京      |          250 |
|        1 | 長野      |    10 | 長野      |          350 |
|        2 | 和歌山    |    25 | 和歌山    |          350 |
+----------+-----------+-------+-----------+--------------+
6 rows in set (0.00 sec)

--左外部結合
mysql> SELECT
    -> * from table_b left outer join table_a on table_a.fruit_id = table_b.fruit_id;
+----------+-----------+-------+----------+-----------+-------+
| fruit_id | place_id  | stock | fruit_id | name      | price |
+----------+-----------+-------+----------+-----------+-------+
|        1 | 青森      |     5 |        1 | りんご    |   100 |
|        1 | 長野      |    10 |        1 | りんご    |   100 |
|        2 | 愛媛      |    30 |        2 | みかん    |   150 |
|        2 | 和歌山    |    25 |        2 | みかん    |   150 |
|        3 | 沖縄      |    20 |        3 | バナナ    |   140 |
|        4 | 東京      |    50 |     NULL | NULL      |  NULL |
+----------+-----------+-------+----------+-----------+-------+
6 rows in set (0.00 sec)

--右外部結合
mysql> select * from table_a RIGHT OUTER JOIN table_b on table_a.fruit_id = table_b.fruit_id;
+----------+-----------+-------+----------+-----------+-------+
| fruit_id | name      | price | fruit_id | place_id  | stock |
+----------+-----------+-------+----------+-----------+-------+
|        1 | りんご    |   100 |        1 | 青森      |     5 |
|        1 | りんご    |   100 |        1 | 長野      |    10 |
|        2 | みかん    |   150 |        2 | 愛媛      |    30 |
|        2 | みかん    |   150 |        2 | 和歌山    |    25 |
|        3 | バナナ    |   140 |        3 | 沖縄      |    20 |
|     NULL | NULL      |  NULL |        4 | 東京      |    50 |
+----------+-----------+-------+----------+-----------+-------+
6 rows in set (0.00 sec)

フィールドオプション

　テーブル作成のオプション
　create tableを使う際に使用する主なオプション

●NOT NULL

　入力を必須とする

●AUTO_INCREMENT

　連続した数値を自動でカラムに格納する
　create　table　テーブル名　(カラム名　カラムのデータ型　AUTO_INCREMENT);

●PRIMARY KEY(主キー)

　PRIMARY KEYが指定されたカラムは、重複禁止のインデックスとして定義され、NOT NULL成約が自動的に付与される
　データベースのデータを一意に識別するための項目で、項目を主キーとして使うには
　・中身が空でない
　・中身がキー内で重複していない（一意である）
　必要がある

●UNIQUE　重複した値を登録できなくする

　UNIQUEが指定されたカラムは、重複禁止のインデックスとして定義され、NULLを許容する

●FOREGN KEY

　外部テーブルとリンクするためのキー
　他のテーブルのカラムを参照するキー
　参照先である他テーブルのカラムに登録されている値以外を登録できなくする

例

  create table table1(
    id INT(11) PRYMARY KEY AUTO_INCREMENT, /*primary keyとauto_incrementの指定*/
    column1 VARCAHR(255) NOT NULL, /*not nullの指定*/
    column2 VARCHAR(255) UNIQIE, /*uniqueの指定*/
    /*foregn keyの使い方*/
    FOREGN KEY(column1) 参照先テーブル名 REFERENCES 参照テーブル名(カラム名)
  );

はじめに

CAMエンジニア Advent Calendar 2019 19日目の記事となります。
前回は @mori_keisuke さんの shellの基礎構文 でした。
記事一覧はこちらから↓
https://qiita.com/advent-calendar/2019/cam-inc

最近「理論から学ぶ実践データベース入門」や「プログラマのためのSQL」などの書籍を読んで、
SQLにおける NULL という概念について改めて考える機会があったので、
その内容についてまとめさせていただきました。

結論から言うと、どうしても必要な場合を除いて、可能な限りNULLは使用すべきではないという内容になっています。

NULL の何が問題なのか

SQLでのNULLは、強力な意味を持ちます。
プログラミング言語の世界では、単に「空である」という認識で扱っても問題にならないことが多いですが、
その認識で捉えていると不自然なふるまいをすることがあるため、扱いには注意が必要です。

具体的な例をいくつか挙げていきます。

NULL の比較

SQL で NULL であるかどうかの条件を書く場合、
= NULL や != NULL と書くことはできません。
IS NULL や IS NOT NULL と書く必要があります。

SELECT
    `Country`.`Name`,
    `Country`.`Capital`
  FROM `Country`
  WHERE
    `Country`.`Capital` != NULL -- `Country`.`Capital` IS NOT NULL と書く必要があるため、エラーになる
;

SQL に日常的に触れている方であれば、当然の挙動として認識されているかと思いますが、
最初は不思議に感じた方も多いのではないでしょうか。

そもそもなぜこのように書き分けをする必要があるかというと、
SQL において NULL は、不明または適応不能な状態を表すものであり、
そもそも値ですらないという扱いを徹底しているからです。
不明だから一致しているかしていないかも不明なので、等号や不等号を使って比較をするということができません。

この、不明または適応不能であるという状態を論理的に厳密に表現しているということが、
SQL における NULL を考える上でとても重要な手がかりになってきます。

三値論理

boolean型の列要素に対してNULL許可がされている以下のテーブルを例に取ります。

-- テーブルを用意
CREATE TABLE `flags` (
  `flag_id` int(10) unsigned NOT NULL,
  `raised` boolean NULL DEFAULT NULL,
  PRIMARY KEY (`flag_id`)
);

-- レコードを３件挿入
INSERT INTO `flags` (`flag_id`, `raised`) VALUES
(1, true),
(2, false),
(3, NULL);

raised という boolean型のカラムを持つ flags テーブルを作成し、
raised カラムにそれぞれ true, false, NULL という値の入ったレコードを3件作成しました。

このとき、 flags のテーブルに対して、「 raised が true ではない」レコードを検索します。

直感的には raised が false, NULL で登録されている2件のレコードがヒットしてほしいです。
ところが結果は以下のようになります。

SELECT * FROM `flags` WHERE `raised` != true;
+---------+--------+
| flag_id | raised |
+---------+--------+
|       2 |      0 |
+---------+--------+
1 row in set (0.00 sec)

raised が false で定義されたレコードしかヒットしません。
NULL は値が不明なので、 true でないかどうかも不明なのです。

同様に「 raised が false ではない」レコードを検索した場合も、 raised が true のレコードの1件のみしかヒットしない結果となります。

また、 NULL は数値と比較しても結果は不明なので、
NULL <= 10 や NULL > 32 のような演算は、どんな数と比較したとしても真にも偽にもなりません。
その結果は NULL になります。

このことから、 SQL は true, false の二値論理ではなく、
そのどちらにも当てはまらない NULL という状態を加えた
true, false, NULL の三値論理での演算を前提にしていることがわかるかと思います。

三値論理での演算は、二値論理と比べて途端に複雑さを増します。
考える必要のある条件が複雑なほど、条件漏れなどの人為的なミスの可能性が増え、
書き上がるSQLの可読性の低下にもつながるということを考えると、
NULL許可のカラムを使う上でのデメリットの一つと言えます。

↓二値論理での真理値表例

A	B	A AND B	A OR B
T	T	T	T
T	F	F	T
F	T	F	T
F	F	F	F

↓三値論理での真理値表例

A	B	A AND B	A OR B
T	T	T	T
T	F	F	T
T	N	N	T
F	T	F	T
F	F	F	F
F	N	F	N
N	T	N	T
N	F	F	N
N	N	N	N

NULL の伝播

NULLは、四則演算やSQL関数演算の結果のほとんどを NULL に変えてしまいます。
以下の演算の結果は全て NULL です。

• 100 + NULL
• NULL / 0
• CONCAT('ABC', NULL)

上記例の通り、 0 で割った場合も結果は NULL になります。
文字列と NULL を結合させようとした場合も、結果は NULL です。

このように、対象のデータの一部に NULL が含まれている場合、
演算を重ねるごとに NULL がどんどん増殖してしまう可能性を含んでいます。

「雑草が繁殖する」というようなニュアンスを含んでよばれており、
演算結果を台無しにすることにもつながる性質を持っているため、
ときに厄介な性質として現れることがあります。

インデックスが参照されない

IS NULL や IS NOT NULL をWHERE条件に指定した場合、インデックスが効きません。
単純にパフォーマンスの低下にもつながるというデメリットを持っています。

NULL を回避するには？

正規化する

NULL を回避するのに最も有効な方法は、テーブルを正規化することです。
正規化についての詳しい説明は割愛しますが、
ざっくり説明すると、データの重複や矛盾を設計レベルで防ぎ、
論理的に正しいデータ構造を保つための設計理論・手法のことです。

正規化には第1から第6までの段階があり、それぞれの段階が、前の段階の条件を満たすことを条件に持っているのですが、
第1正規化からすでに、NULLを含んではいけないという条件を満たす必要があります。
つまり NULL 許可の設定が含まれている時点で、そのテーブルは正規化されているとは言えません。

全てのテーブルを正規化する必要はありませんが、
不可能なものを除き、適切に正規化することができれば、
よりよい状態でデータを保持することにも繋がります。

COALESCE 関数を使う

NULL の影響を最小限に抑える方法として COALESCE 関数 を使う方法があります。

この関数は、与えられた引数のうち、最初に見つかった NULL ではない値を返すという動作をするので、
例えば、SUM 関数で数値の集計をしたいときなど、
NULL を 0 に置換して集計を可能にする、などの動作をさせることが可能です。

-- `price` に NULL が含まれていても、その価格を0とすることができるので集計が可能
SELECT SUM(COALESCE(`price`, 0)) FROM `products`;

この例の他にも、 OUTER JOIN をすることによって NULL になってしまう項目に対しても、
COALESCE 関数を使って初期値のように置換するという使い方もできます。

最後に

SQL における NULL の扱いについて自分なりにまとめてみました。
自分自身、普段何気なく扱っていた NULL という概念ですが、
理論的な部分も含めて改めて学びなおしてみると、また違った見え方ができるようになるなと感じました。

この記事が DB 設計をされる方への、考え方の一つとして一助となれば幸いです。

NULL は薬だと思ってほしい。正しく使っている限りは有用だが、乱用すれば全てをぶち壊す。最良の選択は、可能な限り使用を避けてどうしても使わざるを得ないときだけ、適切に使用することだ。

「プログラマのためのSQL 第4版」 著 ジョー・セルコ 監訳 ミック

明日は @tmyy さんの記事です。

参考文献

• プログラマのためのSQL 第4版
• 理論から学ぶ実践データベース入門