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

前回の記事で投稿した [いろんなSQL Vol.3 のつづき](https://qiita.com/h0iga3shi13/items/b68901b443f489679f2d)の記事となります。

----

環境
　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じゃないから読み込めない

**解決方法**

+ 読み込みたいファイルを選択子て、右クリックする
![スクリーンショット 2020-10-13 101641.jpg](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/619833/60eab01f-8697-71ff-a446-edd7d97e2eef.jpeg)

+ プログラムから開く(H)を選択し、メモ帳で開く
![スクリーンショット 2020-10-13 101739.jpg](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/619833/99809464-50c9-13ee-900a-1d47fc33ec8c.jpeg)

+ ファイル(F)をタップし、名前を付けて保存(A)を選択したら、文字コード(E)をUTF-8に変更して保存(S)する
![スクリーンショット 2020-10-13 101838.jpg](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/619833/192dec22-4961-cc96-94ca-d9d1ca3592a9.jpeg)

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

#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](https://qiita.com/h0iga3shi13/items/5bf241532d12bd5b2fff)
+ [いろんなSQL応用編 Vol.2]
(#)
+ [いろんなSQL応用編 Vol.3](https://qiita.com/h0iga3shi13/items/b68901b443f489679f2d)