MySQL Advent Calendar 2019の23日目エントリです。

初めてAdvent Calendarに参加してみました。

OracleメインでSQL使ってきたひとが、
ひょんなことからMySQLを使った際の気付きをまとめました。

MySQLを使うことになったきっかけ

知人にSQLを教えてと言われたのがきっかけです。
SQLのなかでもMySQLなのは、特別な理由はなかったみたいです。

ちなみに、そのときのわたしは、
「Oracle SQLできるなら、MySQLもなんとか大丈夫だろう」
とか思って、軽く了承してしまいました。

ただ、そんなのは間違いでした。
バージョンまわりで3つのつまづきポイントがありましたので紹介してみます。

前提

• MySQL5がインストールだけしてた。
• OSはWindows10
• MySQL8だと、WITH句、WINDOW関数が使える

MySQL5では、WITH句、WINDOW関数が使えない

OracleではおなじみのWTIH句、WINDOW関数が使えないことに驚きました。

データの構成比をだすのに、累積和の出し方をWINDOW関数を使って教えられたらいいなと思ったのです。
しかし、WINDOW関数は使えないので別のロジックを探してみました。
以下のリンクの方法でできました。

MySQL で累積和を求める方法

ただ、リンク先のページの最後のほうに以下のようなことが書いてあり笑ってしまうとともに、
WITH句、WINDOW関数が使えるMySQL8を使う方針に切り替えました。

同じことをPostgreSQLでする場合
もっと簡単に取得できる
https://www.postgresql.jp/document/9.6/html/tutorial-window.html
PostgreSQLを使いましょう

オンライン実行環境は、MySQL5の環境しかみつからない

そこで、MySQLの環境を自分のPC上に作る前に、オンラインで公開されている
MySQLの環境から本当にWITH句やWINDOW関数が使えるか確認したくなりました。

結論から言うと、WITH句もWINDOW関数は使えませんでした。
私が探した限り、MySQL５の環境しかなかったからです。
よくばりすぎました。

ちなみに、確認してみたサイトは以下の通りです。
WITH句やWINDOW関数を使うとか欲張らなければとても便利です。

• dokoQL
• paiza.io
• SQL Fiddle

個人的には、SQL Fiddleが使いやすいように感じました。
サンプルコードが充実しており、実行結果の表示が見やすいなと思います。

MySQL8インストールしたら、MySQL5の残骸があったのかDB接続できない

MySQLに限らずな気がしますが、これも困りました。
今までインストールしていたMySQL5を「アプリケーションの追加と削除」で
アンインストール後に、MySQL8をインストールしました。
しかし、その後にテスト接続をしてみるもののエラーになってしまいました。

安易に「アプリケーションの追加と削除」でアンインストールしただけなのが原因だったようです。
以下の手順でやり直したらうまくいきました。

MySQL 5.7 for Windowsのアンインストール方法

おわりに

以上、MySQLバージョンまわりのつまづきポイント3つでした。
安請け合いはしてはいけませんね。

MySQLを始めたばかりなので考慮漏れ等あるかもしれません。
変更したほうがよさそうな場所があれば教えてくれると嬉しいです。

つぎの24日目は、@knktknkさんです。

使用頻度の高いコマンドまとめ

データベース一覧を表示

show databases;

データベース切り替え

use データベース名;

DB検索

select カラム名 from テーブル名;

DBのテーブルを表示

SHOW TABLES FROM データベース名;

テーブルのカラムを表示

DESCRIBE テーブル名;

docker-composeからMySQLにアクセス

docker-compose exec db bash -c 'mysql -uroot -p${MYSQL_PASSWORD} ${MYSQL_DATABASE}'

マイグレーションコマンド系

# php artisan ~
migrate:status # マイグレーション履歴を確認
migrate              マイグレーションを実行
migrate:refresh      全てのマイグレーションをリセットし、再度実行
migrate:reset        全てのマイグレーションをロールバック
migrate:rollback     過去1件のマイグレーションをリセット

マイグレーションファイルを作成

php artisan make:migration make_users_table
php artisan make:migration change_users_table --table=users

随時更新予定

使用頻度の高いコマンドまとめ

データベース一覧を表示

show databases;

データベース切り替え

use データベース名;

DB検索

select カラム名 from テーブル名;

DBのテーブルを表示

SHOW TABLES FROM データベース名;

テーブルのカラムを表示

DESCRIBE テーブル名;

docker-composeからMySQLにアクセス

docker-compose exec db bash -c 'mysql -uroot -p${MYSQL_PASSWORD} ${MYSQL_DATABASE}'

随時更新予定

ruby on railsのCircleCiで結構はまったのでメモ

データベースの接続設定

appli/config/database.yml

default: &default
  adapter: mysql2
  encoding: utf8
  pool: <%= ENV.fetch("RAILS_MAX_THREADS") { 5 } %>
  username: root
  password:
  host: localhost

test:
  <<: *default
  username: 'root'
  password: 'rootpass'
  port: 3306
  host: '127.0.0.1'
  database: ci_test

circleci/config.yml

# Ruby CircleCI 2.0 configuration file
#
# Check https://circleci.com/docs/2.0/language-ruby/ for more details
#
version: 2
jobs:
  build:
    docker:
      # specify the version you desire here 
      - image: circleci/ruby:2.6.5-node-browsers
        environment:
          BUNDLER_VERSION: 2.0.1
          RAILS_ENV: test
          DB_HOST: 127.0.0.1
          DB_USERNAME: 'root'
          DB_PASSWORD: 'rootpass'

      - image: circleci/mysql:5.7.22
        environment:
          MYSQL_DATABASE: 'ci_test'
          MYSQL_USER: 'root'
          MYSQL_ROOT_PASSWORD: 'rootpass'
      # Specify service dependencies here if necessary
      # CircleCI maintains a library of pre-built images
      # documented at https://circleci.com/docs/2.0/circleci-images/
      # - image: circleci/postgres:9.4

    working_directory: ~/repo

    steps:
      - checkout
      - run:
          name: yarn install
          command: yarn install
      - run:
          name: setup bundler
          command: |
            sudo gem update --system
            sudo gem uninstall bundler
            sudo rm /usr/local/bin/bundle
            sudo rm /usr/local/bin/bundler
            sudo gem install bundler
        # Download and cache dependencies
      - restore_cache:
          keys:
            - v1-dependencies-{{ checksum "Gemfile.lock" }}
            # fallback to using the latest cache if no exact match is found
            - v1-dependencies-

      - run:
          name: install dependencies
          command: |
            yarn install --check-files
            bundle install --jobs=4 --retry=3 --path vendor/bundle

      - save_cache:
          paths:
            - ./vendor/bundle
          key: v1-dependencies-{{ checksum "Gemfile.lock" }}

      # Database setup
      - run:
          name: bundle exec
          command: |
            dockerize -wait tcp://127.0.0.1:3306 -timeout 120s
            bundle exec rake db:create
            bundle exec rake db:schema:load

      # run tests!
      - run:
          name: run tests
          command: |
            mkdir /tmp/test-results
            TEST_FILES="$(circleci tests glob "spec/**/*_spec.rb" | \
              circleci tests split --split-by=timings)"

            sudo gem install bundler
            sudo gem install rspec
            sudo gem install rspec-core
            bundle exec rspec \
              --format progress \
              --format RspecJunitFormatter \
              --out /tmp/test-results/rspec.xml \
              --format progress \
              $TEST_FILES

      # collect reports
      - store_test_results:
          path: /tmp/test-results
      - store_artifacts:
          path: /tmp/test-results
          destination: test-results

よく忘れるのでメモ用。

特定のデータベースの中身を全てエクスポート

以下のコマンドをクリック。

mysqldump -u root -p testdb > dump.sql

以上の例では、
rootユーザー、testdbというデータベース名になります。
tesdbの中身を「dump.sql」というファイル名で出力します。

以上のコマンドを打った後、パスワードが求められます。

特定にデータベースsqlファイルをインポート

以下のコマンドをクリック。

mysql -u root -p testdb < dump.sql

以上の例では、
rootユーザー、testdbというデータベース名になります。
dump.sqlのファイルを、testdbにインポートします。

以上のコマンドを打った後、パスワードが求められます。

以上

メリークリスマス!

この記事はナビタイムジャパンアドベントカレンダー 25日目の記事です。
2019年も残すところあと1週間切りましたね。年末に向けて大掃除をしないといけませんね。
この記事では、MySQLのいらないインデックスを断捨離して、心地よく2020年を迎える準備をする話です。

はじめに

こんにちは。ナビタイムジャパンで地点情報などを管理するデータベースについての運用/バッチ処理の開発をしているRNです。
当社では、経路検索サービスをユーザに提供しております。その経路検索には、経路検索の目的地とするための地点(POI)が必要不可欠です。
地点情報のデータストアには主にMySQL(とAurora MySQL)を利用しているのですが、日々、数万、数十万の地点データの更新を安定して、なるべく高速に行うためには工夫が必要です。
今回は「MySQLの更新高速化」のために行った、sysスキーマに基づくインデックス削除について、話していきます。

MySQLのインデックスについて

インデックスの基礎

MySQLを含む多くのリレーショナルデータベースは、参照の処理の高速化のために「インデックス」という機能を持ちます。
これは、よく「本の索引と似たようなものだ」と言われています。索引で目的の項目を探して、そのページを開く。そうすることにより、より早く目的の情報を取得することができますよね。
MySQLでも一つまたは複数のカラムに対してインデックスを作成しておくことで、参照パフォーマンスを向上させることが可能です。

インデックスを作成する

参照時のパフォーマンスを出すためにどのようにインデックスを作成すればよいでしょうか。
新規にテーブル設計を検討する場合には、よくwhere句やorder by句で指定されることの多いカラムや、外部テーブルとの結合キーとなるカラムにインデックスを作成すると良いです。
(今回は主題ではないので、詳細についてはお話いたしませんが、興味がありましたら、「MySQL インデックス設計」などで調べてみてください。)
既存のテーブルにインデックス追加を検討する場合、自分の場合は、以下のように実施しています。

1. スロークエリログを分析し、実行時間のかかるSQLを確認する
2. そのクエリの実行計画を確認する
3. 効果のありそうなインデックスを検討する
4. インデックス作成し、効果を検証する
5. インデックスの採用可否を判断する

MySQLにはクエリの実行の際に使用するインデックスを最適に選択してくれる機能(オプティマイザ)が備わっています。
そのため、無駄なインデックス(検索キーにならないカラムへのインデックスや、他のインデックスで賄えてしまうインデックスなど)を追加したとしても、それによって、参照時のパフォーマンス劣化が起こることは稀です。

インデックスを断捨離しましょう

インデックスについて気をつけることが2点あります。

1. テーブルのレコードが更新(DELETE/INSERT/UPDATE)される場合、インデックスも更新される
   • 本の例でも、本編が更新されたら索引も変える必要があるのと同じです。
   • インデックスの数が多ければ、その分更新するデータ量が増えてしまいます。
2. インデックスが役にたたないこともある
   • テーブルのレコード数や値の種類(例えば性別)が少ないと、インデックスの効果が薄くなる。
   • 検索のキーとして指定されない場合、作成したインデックスが使われないこともある。

今回主に論じたいことは、1.についてです。
不必要なインデックスを「使うかもしれないから」といって、無闇矢鱈に作成していると、更新データサイズが肥大化し、更新時のパフォーマンスが劣化します。(劣化の程度についての実験は後述します。)
しかし、個人的には、インデックスを積極的に減らすことについて書かれている話は多くないかと思います。

インデックスの断捨離は難しい(?)

個人的にインデックスの断捨離は難しく感じます。その理由として、

1. 参照パフォーマンス劣化を招きうる
   • 使用されているインデックスを消してしまった場合、参照のパフォーマンスが大きく低下する可能性があります。
2. インデックスが使用されるかはMySQLのオプティマイザ次第
   • 例えば、テーブルのレコード件数が大きく減った場合、インデックスが使用されなくなることもありえます。
3. 不要になっても削除されにくい
   • 更新のパフォーマンスやインデックスのデータサイズが問題にならない限り、自発的に断捨離はしない。

それでは、不要なインデックスを特定するために、MySQLの機能の1つである、sysスキーマを利用してみましょう。

sysスキーマについて

performance_schemaについて

MySQL5.5.3以降では、performance_schemaという機能があります。
これはMySQLのパフォーマンスチューニングのための機能であり、名前の通り、DBのパフォーマンスに関する統計情報を収集する機能です。
ちょっとややこしいのですが、performance_schemaというスキーマ名で予めDBに存在しています。
DBのperformance_schemaが有効化されているかを調べるには、以下のコマンドを打ってみてください。(MySQL5.6以降ではデフォルトで有効化されています。)

mysql> show variables like "performance_schema";
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| performance_schema | ON    |
+--------------------+-------+
1 row in set, 1 warning (0.00 sec)

上記の例では有効化されています。

sysについて

performance_schema自体は便利な機能ではあるのですが、情報が取り出しにくいという課題がありました。
そこで、MySQL5.7以降では、sysという機能が登場しました。スキーマ名であるためsysスキーマとも呼ばれるそうです。
これは、performance_schemaとinformation_schema(テーブルやカラム型などのDB内部メタ情報を持つスキーマ)を見やすくしたもので、実体はビューやプロシージャ、関数の集まりです。
そのためperformance_schemが有効化されていれば、sysの機能も有効となります。
MySQL5.6以前ではsysスキーマがないので、sysのリポジトリでクローンして、sqlを実行すれば作成可能です。
そのsysの中にschema_unused_indexesが存在しています。これは名前の通り使用されていないインデックスの一覧を表示できるビューです。

sys.schema_unused_indexesを使って断捨離をする

ちょっと実験してみます。

実験環境

MySQL Version : MySQL5.7.28
OS : Windows10
CPU : Intel Corei7-4790
メモリ: 16G

時間測定は各施行5回ずつ行って平均値としています。

準備

ダミーデータとして,以下SQLでテーブルを作成します.

テーブル作成とサンプルデータ投入

create database test;
create table sample(
    txt_1 varchar(5),
    txt_2 varchar(5)
);
insert into sample () values ();
-- 倍々に524288レコードになるまで増やす
insert into sample () select * from sample;
insert into sample () select * from sample;
insert into sample () select * from sample;
insert into sample () select * from sample; 
insert into sample () select * from sample; 
insert into sample () select * from sample; 
insert into sample () select * from sample; 
insert into sample () select * from sample; 
insert into sample () select * from sample; 
insert into sample () select * from sample; 
insert into sample () select * from sample; 
insert into sample () select * from sample; 
insert into sample () select * from sample; 
insert into sample () select * from sample; 
insert into sample () select * from sample; 
insert into sample () select * from sample; 
insert into sample () select * from sample; 
insert into sample () select * from sample; 
insert into sample () select * from sample;
-- ランダム英数5文字の値を入れる
update sample 
set 
    txt_1 = SUBSTRING(MD5(RAND()), 1, 5),
    txt_2 = SUBSTRING(MD5(RAND()), 1, 5);

-- データの確認
select count(*) from sample;
select * from sample limit 3;

実験1 インデックスのない状態での参照パフォーマンスの確認

この状態での参照パフォーマンスの確認をしてみます。

実験1

select sql_no_cache count(*) from sample where txt_1 like "a%";

平均0.74secでした。

実験2 インデックスを作成している状態での参照パフォーマンスの確認

続いてインデックスをtxt_1カラムに作成して参照パフォーマンスの確認をしてみます。

実験2

alter table sample add index idx_txt1(txt_1);
select sql_no_cache count(*) from sample where txt_1 like "a%";

selectの実行時間を測定したところ、平均0.01secでした。速いですね。
このように文字列型では完全一致、前方一致ではインデックスによる効果が出ます。

実験3 インデックスを1つ作成している状態での更新パフォーマンスの確認

さて、この状態での更新パフォーマンスはどうなるでしょうか。

実験3

begin;
delete from sample where txt_1 like "a%"; 
rollback;

delete の処理では平均15.90secかかっていました。

実験4 インデックスを2つ作成している状態での更新パフォーマンスの確認

ここでtxt_2に対してインデックスを追加して同様に計測してみます。

実験4

alter table sample add index idx_txt2(txt_2);
begin;
delete from sample where txt_1 like "a%"; 
rollback;

今度は平均21.3secとなりました。インデックスを追加することで更新パフォーマンスが劣化するとお分かりいただけたかと思います。
(ちなみに、追加でidx_txt21(txt_2,txt_2) のように複合インデックスを作成すると更に更新処理が遅くなります。)

実験5 sys.schma_unused_indexedの確認

本題の使われていないインデックス情報について確認してみます。

実験5

select * from sys.schema_unused_indexes;

以下のような結果が得られると思います.

+---------------+-------------+------------+
| object_schema | object_name | index_name |
+---------------+-------------+------------+
| test          | sample      | idx_txt2   |
+---------------+-------------+------------+

期待どおり、未使用のインデックスの情報が出てきています。
では断捨離をしましょう。

alter table sample drop index idx_txt2;

これにより、更新時のパフォーマンスが15.90secほどに戻ります.

(補足)sys.schema_redundant_indexes について

今回の実験では、sys.schma_unused_indexesを用いましたが、sysにはまた別のschema_redundant_indexというビューがあります。
こちらは他のインデックスと重複している冗長なインデックスを一覧で出せるビューです。
冗長なインデックスであるため、削除による影響が少ないので、こちらから断捨離するほうがよさそうです。
ただ、hint句などでこの冗長なインデックスが指定されていると影響が出てしまうので要注意です。そこはschema_unused_indexesと突き合わせて見ましょう.

schema_redundant_indexesはinformation_schemaからのみ作られるビューのため、performance_schemaやsysが有効になっていない場合でもgithubのSQLを少し組み替えると出力が可能です。

sys.schema_redundant_indexes

create table sample(
    txt_1 varchar(5),
    txt_2 varchar(5)
);
alter table sample add index idx_txt1(txt_1);  -- idx_txt12(複合インデックス)と重複しているため、idx_txt12で賄える
alter table sample add index idx_txt1_copy(txt_1); -- idx_txt1と同一
alter table sample add index idx_txt12(txt_1,txt_2);


select * from sys.schema_redundant_indexes;
+----------------+------------+----------------------+-------------------------+----------------------------+---------------------+------------------------+---------------------------+----------------+--------------------------------------------------------+
| table_schema   | table_name | redundant_index_name | redundant_index_columns | redundant_index_non_unique | dominant_index_name | dominant_index_columns | dominant_index_non_unique | subpart_exists | sql_drop_index                                         |
+----------------+------------+----------------------+-------------------------+----------------------------+---------------------+------------------------+---------------------------+----------------+--------------------------------------------------------+
| spoit_analysis | sample     | idx_txt1_copy        | txt_1                   |                          1 | idx_txt1            | txt_1                  |                         1 |              0 | ALTER TABLE `test`.`sample` DROP INDEX `idx_txt1_copy` |
| spoit_analysis | sample     | idx_txt1             | txt_1                   |                          1 | idx_txt12           | txt_1,txt_2            |                         1 |              0 | ALTER TABLE `test`.`sample` DROP INDEX `idx_txt1`      |
| spoit_analysis | sample     | idx_txt1_copy        | txt_1                   |                          1 | idx_txt12           | txt_1,txt_2            |                         1 |              0 | ALTER TABLE `test`.`sample` DROP INDEX `idx_txt1_copy` |
+----------------+------------+----------------------+-------------------------+----------------------------+---------------------+------------------------+---------------------------+----------------+--------------------------------------------------------+
3 rows in set (0.05 sec)

sql_drop_indexまで出てくるので非常に使い勝手が良さそうですね。

実施するうえでの注意

自分もschema_unused_indexesやschema_redundant_indexesを元にインデックスの断捨離を実施しています。
ただ、本番稼働しているもので実施するにはいくつか注意事項があります。

• あくまでも断捨離の目安にする
  • schema_unused_indexesで出てくるものはたまたま直近で使われていなかったインデックスという場合もあります。あくまでも目安として見て、クエリログと突き合わせてから断捨離しましょう。
• performance_schemaの統計情報は、DB起動時にリセットされる
  • 定期再起動などをを行っている場合は要注意です。
  • 有効化してすぐに断捨離するのではなく、ある程度統計情報をためてから断捨離しましょう。
• マスタ/スレーブ構成をとっている場合は双方のschema_unused_indexesを確認した上で断捨離する
  • それぞれで実行されるSQLも異なるためです。
• performance_schemaを有効化することで、メモリ/CPU/ストレージを専有する
  • パオーマンスの懸念からかMySQLの派生であるMariaDBやAWSのAurora MySQLではデフォルトで無効化されています。
  • 最近のMySQL(5.7以降)では改善されていると聞きますが(参考)、本番環境で有効化する場合は事前の負荷試験を実施することを推奨します。
  • 使用メモリ上限サイズをチューニングすることも可能です。
• 断捨離は年始以降に
  • 少なからずリスクはあるので、心穏やかに年末年始を過ごされたい場合は、年明け以降に実施しましょう。

まとめ

• MySQLの機能のひとつであるperformance_schema及びsysスキーマの機能を使って不要なインデックスを断捨離してみました。
• sysには他にもパフォーマンス改善に使えるものが多くあるそうなので、もっと使ってみようかと思います。公式ドキュメント

はじめに

Rails 6 に追加された新機能を試す第113段。 今回は、MySQL enum set 編です。
Rails 6 では、MySQL の enum や set のカラムの schema dump が正しく出力されるようになりました。

Ruby 2.6.5, Rails 6.0.2.1, MySQL 8.0.16 で確認しました。 (Rails 6.0.0 でこの修正が入っています。)

$ rails --version
Rails 6.0.2.1

今回は、users テーブル (User モデル）に enum と set のカラムを追加して試してみます。

Rails プロジェクトを作成する

$ rails new rails_sandbox
$ cd rails_sandbox

User モデルを作成する

name カラムだけもつ User モデルを作成します。

enum と set のカラムは、後で、migration ファイルを直接編集して、追加します。

bin/rails g model User name

migration ファイルを編集する

migration ファイルを編集して、 enum カラムの generation と set カラムの learning を追加します。

db/migrate/20191221002717_create_users.rb

class CreateUsers < ActiveRecord::Migration[6.0]
  def change
    create_table :users do |t|
      t.string :name
      t.column :generation, "enum('baby', 'toddler', 'preschool', 'gradeschool', 'teen', 'young_adult')"
      t.column :learning, "set('piano', 'english', 'swimming', 'ballet', 'calligraphy')"

      t.timestamps
    end
  end
end

seed データを作成する

機能を試すためには、作る必要は、無いのですが、一応、 seed データを1件作成します。

db/seeds.rb

User.create(name: 'Dave', generation: 'gradeschool', learning: 'english,swimming')

マイグレーションを実行する

マイグレーションを実行します。
shell
$ bin/rails db:create db:migrate


スキーマファイルを確認する

できたスキーマファイルを確認します。

db/schema.rb

ActiveRecord::Schema.define(version: 2019_12_21_002717) do

  create_table "users", options: "ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci", force: :cascade do |t|
    t.string "name"
    t.column "generation", "enum('baby','toddler','preschool','gradeschool','teen','young_adult')"
    t.column "learning", "set('piano','english','swimming','ballet','calligraphy')"
    t.datetime "created_at", precision: 6, null: false
    t.datetime "updated_at", precision: 6, null: false
  end

end

Rails 5 では

Rails 5.2.4 では、スキーマファイルは以下のようになってしまいます。

generation と learning のカラムが string になってしまい、ENUM と SET であることがわからなくなってしまいます。

db/schema.rb

ActiveRecord::Schema.define(version: 2019_12_21_005503) do

  create_table "users", options: "ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci", force: :cascade do |t|
    t.string "name"
    t.string "generation", limit: 11
    t.string "learning", limit: 41
    t.datetime "created_at", null: false
    t.datetime "updated_at", null: false
  end

end

MySQL で直接確認する

なお、MySQL で直接確認した場合は、Rails 6.0.2.1 も、Rails 5.2.4 も同じです。

mysql> show columns from users;
+------------+-----------------------------------------------------------------------+------+-----+---------+----------------+
| Field      | Type                                                                  | Null | Key | Default | Extra          |
+------------+-----------------------------------------------------------------------+------+-----+---------+----------------+
| id         | bigint(20)                                                            | NO   | PRI | NULL    | auto_increment |
| name       | varchar(255)                                                          | YES  |     | NULL    |                |
| generation | enum('baby','toddler','preschool','gradeschool','teen','young_adult') | YES  |     | NULL    |                |
| learning   | set('piano','english','swimming','ballet','calligraphy')              | YES  |     | NULL    |                |
| created_at | datetime(6)                                                           | NO   |     | NULL    |                |
| updated_at | datetime(6)                                                           | NO   |     | NULL    |                |
+------------+-----------------------------------------------------------------------+------+-----+---------+----------------+
6 rows in set (0.00 sec)

bin/rails db:reset を試す

bin/rails db:reset を実行してから、MySQL で直接確認すると

Rails 6.0.2.1 は、

mysql> show columns from users;
+------------+-----------------------------------------------------------------------+------+-----+---------+----------------+
| Field      | Type                                                                  | Null | Key | Default | Extra          |
+------------+-----------------------------------------------------------------------+------+-----+---------+----------------+
| id         | bigint(20)                                                            | NO   | PRI | NULL    | auto_increment |
| name       | varchar(255)                                                          | YES  |     | NULL    |                |
| generation | enum('baby','toddler','preschool','gradeschool','teen','young_adult') | YES  |     | NULL    |                |
| learning   | set('piano','english','swimming','ballet','calligraphy')              | YES  |     | NULL    |                |
| created_at | datetime(6)                                                           | NO   |     | NULL    |                |
| updated_at | datetime(6)                                                           | NO   |     | NULL    |                |
+------------+-----------------------------------------------------------------------+------+-----+---------+----------------+
6 rows in set (0.01 sec)

と変わりないですが、Rails 5.2.4 では

mysql> show columns from users;
+------------+--------------+------+-----+---------+----------------+
| Field      | Type         | Null | Key | Default | Extra          |
+------------+--------------+------+-----+---------+----------------+
| id         | bigint(20)   | NO   | PRI | NULL    | auto_increment |
| name       | varchar(255) | YES  |     | NULL    |                |
| generation | varchar(11)  | YES  |     | NULL    |                |
| learning   | varchar(41)  | YES  |     | NULL    |                |
| created_at | datetime     | NO   |     | NULL    |                |
| updated_at | datetime     | NO   |     | NULL    |                |
+------------+--------------+------+-----+---------+----------------+
6 rows in set (0.00 sec)

と変わってしまいました。

rails console からデータを検索する

(ここからはおまけです。)

bin/rails db:reset で seed データが登録されたので、検索してみます。

irb(main):006:0> User.where(generation: 'gradeschool')
  User Load (1.2ms)  SELECT `users`.* FROM `users` WHERE `users`.`generation` = 'gradeschool' LIMIT 11
=> #<ActiveRecord::Relation [#<User id: 1, name: "Dave", generation: "gradeschool", learning: "english,swimming", created_at: "2019-12-21 02:04:41", updated_at: "2019-12-21 02:04:41">]>
irb(main):007:0> User.where("learning like '%swimming%'")
  User Load (0.9ms)  SELECT `users`.* FROM `users` WHERE (learning like '%swimming%') LIMIT 11
=> #<ActiveRecord::Relation [#<User id: 1, name: "Dave", generation: "gradeschool", learning: "english,swimming", created_at: "2019-12-21 02:04:41", updated_at: "2019-12-21 02:04:41">]>

正しくないデータを投入する

generation や、learning に不正なデータを指定して登録すると ActiveRecord::StatementInvalid が発生します。

generation も learning も不正なデータの場合を試してみます。

最後のエラーメッセージが Data truncated for column 'generation' at row 1 であることから generation に問題があることがわかります。
(が、どうして truncate されたか、ちょっとわかりにくいです...。)

irb(main):008:0> User.create(name: 'NG', generation: 'NG', learning: 'NG')
   (0.5ms)  BEGIN
  User Create (1.6ms)  INSERT INTO `users` (`name`, `generation`, `learning`, `created_at`, `updated_at`) VALUES ('NG', 'NG', 'NG', '2019-12-21 02:15:29.767037', '2019-12-21 02:15:29.767037')
   (0.4ms)  ROLLBACK
Traceback (most recent call last):
        1: from (irb):8
ActiveRecord::StatementInvalid (Mysql2::Error: Data truncated for column 'generation' at row 1)

generation は正しいが、 learning が不正なデータの場合です。

最後のエラーメッセージが Data truncated for column 'learning' at row 1 であることから learning に問題があることがわかります。

irb(main):009:0> User.create(name: 'NG', generation: 'gradeschool', learning: 'NG')
   (0.4ms)  BEGIN
  User Create (1.2ms)  INSERT INTO `users` (`name`, `generation`, `learning`, `created_at`, `updated_at`) VALUES ('NG', 'gradeschool', 'NG', '2019-12-21 02:15:50.398770', '2019-12-21 02:15:50.398770')
   (0.2ms)  ROLLBACK
Traceback (most recent call last):
        2: from (irb):9
        1: from (irb):9:in `rescue in irb_binding'
ActiveRecord::StatementInvalid (Mysql2::Error: Data truncated for column 'learning' at row 1)

Rails 5 では

bin/rails db:migrate で作成した場合は、Rails 6 と同じ動作ですが、 bin/rails db:reset すると Type がただの string になってしまうため、不正なデータでも登録できてしまいます。

ActiveRecord の enum との関係

ActiveRecord の enum と MySQL の enum は全く関係がなく、以下のように、 User モデルに enum を定義しても、MySQL の generation カラムの enum とは連動せず、期待通りの動作はしません。

app/models/user.rb

class User < ApplicationRecord
  enum generation: %i[baby toddler preschool gradeschool teen young_adult]
end

試したソース

https://github.com/suketa/rails_sandbox/tree/try113_mysql_enum

参考情報

• MySQL: Fix schema dumping enum and set columns correctly
• MySQL: The ENUM Type
• MySQL: The SET Type