MySQL応用編！いろんなSQL Vol.4

前回の記事で投稿した いろんなSQL Vol.3 のつづきの記事となります。

---

環境
　Windows 10
　MySQL : version(5.7.28)
Windows PowerShell : version(5.1.18362.1110)

---

21 TRIGGER

とあるテーブルで何らかの処理が起きた時に、それをトリガー（きっかけ）として何らかの処理をすることができる、という仕組みがTRIGGER

例 投稿一覧テーブルが更新されたら、ログ一覧テーブルに id, メッセージ, 作成日時 のログが残るようにする。

mysql> DROP TABLE IF EXISTS コメント一覧;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> DROP TABLE IF EXISTS 投稿一覧;
Query OK, 0 rows affected (0.02 sec)

mysql> DROP TABLE IF EXISTS ログ一覧;
Query OK, 0 rows affected (0.01 sec)

mysql> DROP TRIGGER IF EXISTS 投稿の更新;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CREATE TABLE 投稿一覧 (
    ->   id INT NOT NULL AUTO_INCREMENT,
    ->   メッセージ VARCHAR(140),
    ->   PRIMARY KEY (id)
    -> );
Query OK, 0 rows affected (0.02 sec)

mysql> CREATE TABLE ログ一覧 (
    ->   id INT NOT NULL AUTO_INCREMENT,
    ->   メッセージ VARCHAR(140),
    ->   作成日時 DATETIME DEFAULT NOW(),
    ->   PRIMARY KEY (id)
    -> );
Query OK, 0 rows affected (0.02 sec)

mysql> CREATE TRIGGER
    ->   投稿の更新 -- triggerの名前を付ける
    -> AFTER UPDATE ON
    ->   投稿一覧 -- どのタイミングでどのような処理をするかを記述(投稿一覧が更新されたあとに)
    -> FOR EACH ROW
    ->   INSERT INTO
    ->     ログ一覧 (メッセージ)
    ->   VALUES
    ->     ('更新されたよ！');
-- 更新されたひとつひとつの行に対して、ログ一覧テーブルにデータを挿入できるようにする
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO 投稿一覧 (メッセージ) VALUES
    ->   ('はじめまして'),
    ->   ('春になったら行きたいところ'),
    ->   ('ドライブスポット教えて');
Query OK, 3 rows affected (0.01 sec)

mysql> UPDATE 投稿一覧 SET メッセージ = 'はじめまして！よろしくおねがいします！' WHERE id = 1;
 Query OK, 1 row affected (0.01 sec)


mysql> SELECT * FROM 投稿一覧;
+----+---------------------------------------------+
| id | メッセージ                                   |
+----+---------------------------------------------+
|  1 | はじめまして！よろしくおねがいします！         |
|  2 | 春になったら行きたいところ                    |
|  3 | ドライブスポット教えて                        |
+----+---------------------------------------------+
3 rows in set (0.00 sec)

mysql> SELECT * FROM ログ一覧;
+----+-----------------------+---------------------+
| id | メッセージ             | 作成日時            |
+----+-----------------------+---------------------+
|  1 | 更新されたよ！         | 2020-10-12 10:45:47 |
+----+-----------------------+---------------------+
1 row in set (0.00 sec)

補足

• トリガーはUPDATE以外に、INSERTやDELETEのタイミングでも使える.
• AFTERではなくBEFOREとすれば処理前にトリガーを実行することができる。

例えば、
CREATE TRIGGER トリガー名 BEFORE UPDATE ON
のように書くと、「更新前にトリガーを実行する」という命令にすることができる。

#22 設定されているTRIGGERの一覧を確認
SHOW TRIGGERS;

mysql> SHOW TRIGGERS;
+-----------------+--------+--------------+---------------------------------------------------------------------------------------+--------+------------------------+----------------------------------------------------------------+----------------+----------------------+----------------------+--------------------+
| Trigger         | Event  | Table        | Statement                                                                             | Timing | Created                | sql_mode                                                       | Definer        | character_set_client | collation_connection | Database Collation |
+-----------------+--------+--------------+---------------------------------------------------------------------------------------+--------+------------------------+----------------------------------------------------------------+----------------+----------------------+----------------------+--------------------+
| 投稿の更新      | UPDATE | 投稿一覧     | INSERT INTO
ログ一覧 (メッセージ)
  VALUES
('更新されたよ！')                 | AFTER  | 2020-10-12 10:45:47.83 | STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION | root@localhost | utf8                 | utf8_general_ci      | utf8_general_ci    |
+-----------------+--------+--------------+---------------------------------------------------------------------------------------+--------+------------------------+----------------------------------------------------------------+----------------+----------------------+----------------------+--------------------+
1 row in set (0.01 sec)

;を\Gに変えて実行すると、縦表示になって見やすくなる

mysql> SHOW TRIGGERS\G
*************************** 1. row ***************************
         Trigger: 投稿の更新
           Event: UPDATE
           Table: 投稿一覧
       Statement: INSERT INTO
ログ一覧 (メッセージ)
  VALUES
('更新されたよ！')
          Timing: AFTER
         Created: 2020-10-12 10:45:47.83
        sql_mode: STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
         Definer: root@localhost
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: utf8_general_ci

#23 外部ファイルの読み込み
data.csvというデータが入ったファイル（今回はcsvファイル）を用意して、それをMySQLで読み込みします。

LOAD DATA LOCAL INFILE 'ファイル名もしくはファイルのパス' INTO TABLE データを読み込む先のテーブル名 -- 以下、オプションを記述 FIELDS TERMINATED BY ',' -- 項目の区切りを指定（今回は','） LINES TERMINATED BY '\n' -- 行の区切りを指定（今回は改行で'\n'を使用している） IGNORE 1 LINES -- データの1行目を挿入したくない場合 (メッセージ,いいね,地域); -- データをフィールドに挿入する順番を指定

mysql> LOAD DATA LOCAL INFILE 'C:/Users/xxxx/data.csv' INTO TABLE 投稿一覧
    ->   FIELDS TERMINATED BY ','
    ->   LINES TERMINATED BY '\n'
    ->   LINES TERMINATED BY '\n'
    ->   IGNORE 1 LINES
    ->   (メッセージ,いいね,地域);
Query OK, 48 rows affected, 1 warning (0.01 sec)

mysql> SELECT * FROM 投稿一覧;
+----+----------------------------------------+-----------+----------+
| id | メッセージ                             | いいね    | 地域       |
+----+----------------------------------------+-----------+----------+
       |よろしくおねがいします                 |       212 | 徳島県
       |はじめまして                          |        46 | 兵庫県
       |フォローさせてもらいます               |         9 | 鹿児島県
       |はじめまして                          |       234 | 新潟県
       |はじめまして                          |       777 | 富山県
       |よろしくおねがいします                 |        98 | 鳥取県
       |はじめまして                          |       187 | 京都府
       |フォローさせてもらいます               |         4 | 佐賀県
       |フォローさせてもらいます               |       314 | 高知県
       |リプください                          |         2 | 秋田県
       |いいねください                        |        78 | 山形県
       |フォローありがとう                     |        99 | 埼玉県
       |フォローさせてもらいます               |       534 | 福岡県
       |よろしくおねがいします                 |        12 | 奈良県
       |フォローして                          |         9 | 群馬県   
       |今日の天気は                          |       312 | 東京都
       |はじめまして                          |        65 | 岐阜県
       |フォローさせてもらいます               |        34 | 愛媛県
       |よろしくおねがいします                 |       712 | 山口県
       |はじめまして                          |        37 | 大阪府
       |相互フォローよろ                      |       123 | 千葉県
       |リツイート求む                        |         5 | 茨城県
       |いいね                               |        98 | 福島県
       |ヒマ人集合                           |        23 | 宮城県
       |フォローさせてもらいます              |        84 | 沖縄県
       |はじめまして                         |        39 | 静岡県
       |フォローさせてもらいます              |       454 | 宮崎県
       |よろしくおねがいします                |       412 | 広島県
       |フォローさせてもらいます              |        24 | 長崎県
       |はじめまして                         |       231 | 石川県
       |よろしくおねがいします                |       124 | 島根県
       |はじめまして                         |       141 | 愛知県
       |はじめまして                         |        44 | 山梨県
       |はじめまして                         |        12 | 北海道
       |よろしくおねがいします                |       132 | 岡山県
       |はじめまして                         |       104 | 滋賀県
       |仲良くしてね                         |         1 | 岩手県
       |はじめまして                         |        34 | 福井県
       |今なにしてる                         |        13 | 神奈川県
       |フォローさせてもらいます              |        82 | 熊本県
       |よろしく                             |       123 | 青森県
       |リツイートありがとう                  |        10 | 栃木県
       |フォローさせてもらいます              |        34 | 香川県
       |はじめまして                         |        87 | 三重県
       |フォローさせてもらいます              |        12 | 大分県
       |はじめまして                         |        54 | 長野県
       |よろしくおねがいします                |        12 | 和歌山県
+------+------------------------------------+-----------+---------+

48 rows in set (0.00 sec)

###　注意点
外部ファイルの読み込みを試していた時に出たエラーと、その解決方法をご紹介
1つめのエラー

mysql> LOAD DATA LOCAL INFILE 'data.csv' INTO TABLE 投稿一覧
    ->   FIELDS TERMINATED BY ','
    ->   LINES TERMINATED BY '\n'
    ->   (メッセージ,いいね,地域);
ERROR 2 (HY000): File 'data.csv' not found (Errcode: 2 - No such file or directory)

エラー内容

• 使用しているデータベースのデータが保存されているディレクトリC:\ProgramData\MySQL\MySQL Server 5.7\Data\shop03を探して、同じディレクトリ内にcsvファイルを保存したのですが、ファイルの読み込みができなかった。

解決方法

• 別のディレクトリにcsvファイルを移し、そのディレクトリの絶対パスを指定することで正常に読み込みができた。

2つめのエラー

mysql> LOAD DATA LOCAL INFILE 'C:/Users/xxxx/data.csv' INTO TABLE 投稿一覧
    ->   FIELDS TERMINATED BY ','
    ->   LINES TERMINATED BY '\n'
    ->   (メッセージ,いいね,地域);
ERROR 1300 (HY000): Invalid utf8 character string: '"'

エラー内容
読み込もうとしてファイルの文字コードがutf8じゃないから読み込めない

解決方法

• 読み込みたいファイルを選択子て、右クリックする
  

• プログラムから開く(H)を選択し、メモ帳で開く
  

• ファイル(F)をタップし、名前を付けて保存(A)を選択したら、文字コード(E)をUTF-8に変更して保存(S)する
  

これで文字コードの変更は完了です。
これが文字コードを変更する際の一番簡単な方法だと思います。

#24 INDEX
よく検索されるカラムにインデックスをつける（＝索引みたいなもの）

メリット
あらかじめデータを整列させておくことで、コンピューターが大量のレコードの中から目的のデータにたどり着くまでの時間を大幅に短縮することができるので、データの抽出が早くになる

デメリット
データの挿入、更新、削除をすると整列していたデータが崩れるので、インデックスのデータをいちいち再構築する手間が発生する。
また、インデックスを作る分データベースに必要な容量が増えてしまう。

補足
主キーに関してはPRIMARYというインデックスが自動的に作られるので、主キーを付けて作成したテーブルではidを使った検索はすでに高速に動作することができる。

インデックスの設定を確認
SHOW INDEX FROM テーブル名;

mysql> SHOW INDEX FROM 投稿一覧\G
*************************** 1. row ***************************
        Table: 投稿一覧
   Non_unique: 0
     Key_name: PRIMARY
 Seq_in_index: 1
  Column_name: id
    Collation: A
  Cardinality: 47
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:

Column_name: idでidのフィールドにKey_name: でPRIMARYというINDEXが設定されていることがわかります。

idを使った検索で、Key_name: で設定されているINDEXが実際に使われているかどうかを調べてみましょう。

下記のように、SELECT文でidを使った検索のクエリを書き、先頭にEXPLAINを付けてあげます。

EXPLAIN SELECT * FROM テーブル名 WHERE id = 値\G

mysql> EXPLAIN SELECT * FROM 投稿一覧 WHERE id = 2\G
*************************** 1. row ***************************
       id: 1
  select_type: SIMPLE
    table: 投稿一覧
   partitions: NULL
     type: const
possible_keys: PRIMARY
      key: PRIMARY
  key_len: 4
      ref: const
     rows: 1
 filtered: 100.00
    Extra: NULL

key: で実際に使われたINDEXの名前が、
rows: で検索対象となるレコード数の見積りがわかります。

#25 INDEXが設定されていない場合

mysql> EXPLAIN SELECT * FROM 投稿一覧 WHERE 地域  = '山口県'\G
*************************** 1. row ***************************
       id: 1
  select_type: SIMPLE
    table: 投稿一覧
   partitions: NULL
     type: ALL
possible_keys: NULL
      key: NULL
  key_len: NULL
      ref: NULL
     rows: 47
 filtered: 10.00
    Extra: Using where

key: を見るとNULLとなっているので、INDEXが設定されていないことがわかります。
また、rows: 47となっていることから47件、最初から最後まですべてのデータを確認した返してきた結果ということがわかります。

INDEXの設定方法

CREATE TABLE内に記述して設定することもできるが、あとから付け外しすることが多いのでALTER TABLE文を書いて設定していく方がより使いやすくなる。
ALTER TABLE テーブル名 ADD INDEX インデックス名(インデックスを指定したいカラム名);

mysql> ALTER TABLE 投稿一覧 ADD INDEX area_index(地域);
Query OK, 0 rows affected (0.08 sec)

mysql> SHOW INDEX FROM 投稿一覧\G
*************************** 1. row ***************************
    Table: 投稿一覧
   Non_unique: 0
 Key_name: PRIMARY
 Seq_in_index: 1
  Column_name: id
Collation: A
  Cardinality: 47
 Sub_part: NULL
   Packed: NULL
     Null:
   Index_type: BTREE
  Comment:
Index_comment:
*************************** 2. row ***************************
    Table: 投稿一覧
   Non_unique: 1
 Key_name: area_index
 Seq_in_index: 1
  Column_name: 地域
Collation: A
  Cardinality: 47
 Sub_part: NULL
   Packed: NULL
     Null: YES
   Index_type: BTREE
  Comment:
Index_comment:

INDEX設定後、EXPLAINで確認

mysql> EXPLAIN SELECT * FROM 投稿一覧 WHERE 地域 = '山口県'\G
*************************** 1. row ***************************
       id: 1
  select_type: SIMPLE
    table: 投稿一覧
   partitions: NULL
     type: ref
possible_keys: area_index
      key: area_index
  key_len: 63
      ref: const
     rows: 1
 filtered: 100.00
    Extra: NULL

key: area_indexで、設定したINDEXがちゃんと使われており,
rows: 1で、検索が高速になっていることがわかります。

#26 INDEXを外す方法
ALTER TABLE テーブル名 DROP INDEX 外したいインデックス名;

mysql> ALTER TABLE 投稿一覧 DROP INDEX area_index;
Query OK, 0 rows affected (0.02 sec)

mysql> SHOW INDEX FROM 投稿一覧\G
*************************** 1. row ***************************
    Table: 投稿一覧
   Non_unique: 0
 Key_name: PRIMARY
 Seq_in_index: 1
  Column_name: id
Collation: A
  Cardinality: 47
 Sub_part: NULL
   Packed: NULL
     Null:
   Index_type: BTREE
  Comment:
Index_comment:
1 row in set (0.00 sec)

SHOW INDEXで確認してみてもarea_indexは表示されないので、ちゃんと外れていることがわかります。

#27 WARNING
SQLを実行していると、
1 row in set, 1 warning (0.00 sec)
という一文にちょいちょい出くわして、「warningってなんだか恐ろしい
！」ってなったので少し調べてみました。

mysql> EXPLAIN SELECT * FROM 投稿一覧 WHERE 地域  = '山口県'\G
*************************** 1. row ***************************
       id: 1
  select_type: SIMPLE
    table: 投稿一覧
   partitions: NULL
     type: ALL
possible_keys: NULL
      key: NULL
  key_len: NULL
      ref: NULL
     rows: 47
 filtered: 10.00
    Extra: Using where
1 row in set, 1 warning (0.00 sec)

mysql> SHOW WARNINGS\G
*************************** 1. row ***************************
  Level: Note
   Code: 1003
Message: /* select#1 */ select `shop03`.`投稿一覧`.`id` AS `id`,`shop03`.`投稿一覧`.`メッセージ` AS `メッセージ`,`shop03`.`投稿一覧`.`いいね` AS `いいね`,`shop03`.`投稿一覧`.`地域` AS `地域` from `shop03`.`投稿一覧` where (`shop03`.` 投稿一覧`.`地域` = '山口県')
1 row in set (0.00 sec)

SHOW WARNINGSコマンドで、直前に実行したSQLのwarningの内容を確認することができます。（別コマンドを実行するとwarningの内容が上書きされるので注意が必要です。）

#おわりに
4回に分けてやってきましたが、知れば知るほどにSQLの奥深さを知る結果となった気がします。
ただ、MySQLでできることが増えた嬉しさと楽しさを知りました！SQLが使えるようになると、色んなことができるし、SQLができると一生飯が食えるスキルになるという話も聞いたりしているので、引き続き勉強していきたいと思います。

• いろんなSQL応用編 Vol.1
• [いろんなSQL応用編 Vol.2]
  (#)
• いろんなSQL応用編 Vol.3