MySQL で文字セットを 3 バイトから 4 バイトに変えたら TEXT 型のカラムが MEDIUMTEXT 型に変わってしまった話

はじめに

こんにちは、hamaguchi です
今日は、MySQL の文字系のカラムの文字セットを変更するとカラムの型が変わってしまったという話です
幸いローカルで試した段階で気づけたためどこにも影響はありませんでしたが、気づかないで進めていたら面倒なことになっていたかもしれません
ローカルで気づけてよかったです
コレーションの設定により寿司ビール問題（記事末尾で解説）、ハハパパ問題が発生するという話は割と有名かもと思いますが、文字セットの変更で型が変わってしまうのは知らなかったのでそんなバカな・・・となりました

発端

担当しているプロジェクトで絵文字を保存できるようにしておいた方がいいよなぁとなったこと、プロジェクト間のコレーションの違いを揃えておきたいという理由から、データベースの文字種を変更することになりました

状況を確認したところ、ざっくり以下のような状況でした

• 前から絵文字対応が必要だったプロジェクトでは文字コード utf8mb4、コレーション utf8mb4_bin
• 今回絵文字対応を進めたいプロジェクトでは文字コード utf8、コレーション utf8_bin

確認

utf8 について調べてみると、utf8mb3 のエイリアスで、将来的に削除されるようです
MySQL 8.0 の段階ではまだ使えますが、具体的にいつ廃止になるか決まっていなさそうでした

文字セットの変更をしていないまま気づかずに utf8mb3 廃止後のバージョンにアップグレードしようとした際に発覚するなんてことになると大変なため、早めに変更しておきたいですね

https://dev.mysql.com/doc/refman/8.0/ja/charset-unicode-utf8mb3.html

utf8 は utf8mb3 のエイリアスです。文字制限は、名前に明示的ではなく暗黙的です。

注記
utf8mb3 文字セットは非推奨であり、将来の MySQL リリースで削除される予定です。 かわりに utf8mb4 を使用してください。 utf8 は現在 utf8mb3 のエイリアスですが、ある時点では utf8 が utf8mb4 への参照になることが予想されます。 utf8 の意味があいまいにならないように、utf8 ではなく文字セット参照に utf8mb4 を明示的に指定することを検討してください。

次に文字セットの変更方法を確認します

https://dev.mysql.com/doc/refman/8.0/ja/alter-table.html#alter-table-character-set

文字セットの変更
テーブルのデフォルトの文字セットおよびすべての文字カラム (CHAR、VARCHAR、TEXT) を新しい文字セットに変更するには、次のようなステートメントを使用します。

ALTER TABLE tbl_name CONVERT TO CHARACTER SET charset_name;

簡単ですね
あとは実際のデータ量によってどれくらい時間がかかるかなという心配がある程度です

ですが、よく読むと

VARCHAR のデータ型または TEXT 型のいずれかを持つカラムの場合、CONVERT TO CHARACTER SET は必要に応じてデータ型を変更し、新しいカラムが元のカラムと同じ数の文字を格納できる長さになるようにします。 たとえば、TEXT カラムには、そのカラム内の値のバイト長 (最大 65,535) を格納するための 2 バイト長があります。 latin1 TEXT カラムの場合は、各文字に 1 バイトが必要なため、このカラムには最大 65,535 文字を格納できます。 このカラムが utf8 に変換された場合は、各文字に最大 3 バイトが必要になる可能性があるため、可能性のある最大の長さは 3 × 65,535 = 196,605 バイトになります。 この長さは TEXT カラムの長さバイトに収まらないため、MySQL はデータ型を MEDIUMTEXT に変換します。これは、長さバイトが 196,605 の値を記録できる最小の文字列型です。 同様に、VARCHAR カラムは MEDIUMTEXT に変換される可能性があります。

どうやら、文字のバイト数が違う場合には勝手に上位の型に変換されるようです

では Rails アプリを作ってやってみましょう

やってみる

環境構築

docker compose で Rails 5.2, 6.1, 7.0, 7.1 の環境を作りました
PC は M2 Macbook Pro です

環境構築の詳細

ディレクトリ構成

./
├─ 52/
├─ 61/
├─ 70/
├─ 71/
├─ Dockerfile.d/
│    ├─ 52
│    ├─ 61
│    ├─ 70
│    └─ 71
└ compose.yml

compose.yml

compose.yml は以下のように構成し、各コンテナを作成しました

services:
  app52:
    build:
      context: ./52
      dockerfile: ../Dockerfile.d/52
    volumes:
      - ./52:/app
  app61:
    build:
      context: ./61
      dockerfile: ../Dockerfile.d/61
    volumes:
      - ./61:/app
  app70:
    build:
      context: ./70
      dockerfile: ../Dockerfile.d/70
    volumes:
      - ./70:/app
  app71:
    build:
      context: ./71
      dockerfile: ../Dockerfile.d/71
    volumes:
      - ./71:/app
  db8:
      image: mysql:8.0
      environment:
        MYSQL_ALLOW_EMPTY_PASSWORD: 'yes'
      ports:
        - 3386:3306
      volumes:
        - mysql_data8:/var/lib/mysql
  db5:
      image: mysql:5.7
      platform: linux/x86_64
      environment:
        MYSQL_ALLOW_EMPTY_PASSWORD: 'yes'
      ports:
        - 3356:3306
      volumes:
        - mysql_data5:/var/lib/mysql

volumes:
  mysql_data8:
  mysql_data5:

M1 Mac は ARM なため、mysql:5.7 のイメージが見つからず、no matching manifest for linux/arm64/v8 in the manifest list entries と表示されてインストールできませんでした
platform の指定を x86_64 にすることでインストールできました

Dockerfile

Dockerfile は一旦 WORKDIR の設定までにとどめ、コンテナ内で Gemfile の生成まで行ってからコメントアウトを外します

FROM ruby:2.7

RUN mkdir /app
WORKDIR /app

# Gemfile はまだないためコンテナ内で作成してからコメントアウトを外す
# COPY ./Gemfile /Gemfile
# COPY ./Gemfile.lock /Gemfile.lock

# RUN bundle install

CMD ["rails", "server", "-b", "0.0.0.0"]

ruby のバージョンは Ruby & Rails Compatibility Table を参考に以下のようにしてみました

• Rails 5.2 => Ruby 2.7
• Rails 6.1 => Ruby 3.0
• Rails 7.0 => Ruby 3.1
• Rails 7.1 => Ruby 3.2

Rails 5.2 以外は上記の表にある推奨の Ruby バージョンでインストールできましたが、 Ruby 2.5 ではうまくインストールできなかったため Ruby 2.7 にしました

コンテナ内で作業

Rails のインストール

run コマンドでコンテナに入り、Rails のインストールを行います

ローカル

docker compoose run --rm app52 bash

コンテナ内

gem install rails -v 5.2.8.1

余談 Rails 5.2 のインストール

Rails 5.2 はすでに EOL なこともあり、新しくインストールするという人は稀かもしれませんが、上記コマンド実行時に gem install nokogiri -v 1.15.6 を実行してから rails のインストールを試すようにコメントが表示されたため、先に nokogiri をインストールしたところ rails のインストールまで辿り着けました

gem install nokogiri -v 1.15.6
gem install rails -v 5.2.8.1
> OK

Rails アプリの作成

ここではカレントディレクトリにインストールするため、rails new . とし、データベースは mysql を指定、フロントエンドは必要ないため --api オプションをつけました
ここで Gemfile, Gemfile.lock も作成されるため、Dockerfile のコメントアウトを外すと docker compsoe build 時に bundle install が実行され、すぐに実行可能な状態のコンテナが手に入りました

rails new . --force --database=mysql --api

database.ymlの確認

作成したそれぞれの Rails アプリの database.yml は以下のようになっており、encoding は 5.2 では utf8、6.1 以降では utf8mb4 が入っていました

5.2

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

6.1

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

以下略

7.0

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

7.1

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

database.yml 編集

Rails アプリからデータベースへアクセスできるように docker compose ファイルで指定した値に host を変更します
また、今回は複数のアプリを作成しているためデータベース名が被らないようにここで修正しています
これで Rails アプリからデータベースへアクセスできるようになりました

yaml

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

yaml

default: &default
  adapter: mysql2
  encoding: utf8mb4
  pool: <%= ENV.fetch("RAILS_MAX_THREADS") { 5 } %>
  username: root
  password:
- host: localhost
+ host: db5
  database: app61_development

以下略

yaml

default: &default
  adapter: mysql2
  encoding: utf8mb4
  pool: <%= ENV.fetch("RAILS_MAX_THREADS") { 5 } %>
  username: root
  password:
- host: localhost
+ host: db5
  database: app70_development

yaml

default: &default
  adapter: mysql2
  encoding: utf8mb4
  pool: <%= ENV.fetch("RAILS_MAX_THREADS") { 5 } %>
  username: root
  password:
- host: localhost
+ host: db5
  database: app71_development

データベース作成

環境構築はこれで最後になります
rails db:create でデータベースを作成します
これでdocker compose up で rails server が動くようになります

いくつかのバージョンの Rails アプリが使える環境が構築できました
文字に関するデータベース設定は mysql を指定しただけであとは作成されるがままです

Rails version	database.yml の encoding
5.2	utf8
6.1	utf8mb4
7.0	utf8mb4
7.1	utf8mb4

文字を使ったテーブル作成

文字関連のテーブルを持つモデルを作成してみます
BLOB の TEXT は除き、行のサイズは65534バイトに収まる必要があるため、その範囲内で大きめのものまで指定してみます

db/migrate/20240000000000_create_hoges.rb

class CreateHoges < ActiveRecord::Migration[5.2]
  def change
    create_table :hoges do |t|
      t.string :str
      t.string :str10, limit: 10
      t.string :str1000, limit: 1_000
      t.string :str15000, limit: 15_000
      t.text :txt
      t.text :txt10000, limit: 10_000
      t.text :txt100000, limit: 100_000

      t.timestamps
    end
  end
end

rails db:migrate

出力される schema.rb は

5.2

ActiveRecord::Schema.define(version: 2024_00_00_000000) do

  create_table "hoges", options: "ENGINE=InnoDB DEFAULT CHARSET=utf8", force: :cascade do |t|
    t.string "str"
    t.string "str10", limit: 10
    t.string "str1000", limit: 1000
    t.string "str15000", limit: 15000
    t.text "txt"
    t.text "txt10000"
    t.text "txt100000", limit: 16777215
    t.datetime "created_at", null: false
    t.datetime "updated_at", null: false
  end

end

6.1

ActiveRecord::Schema.define(version: 2024_00_00_000000) do

  create_table "hoges", charset: "utf8mb4", force: :cascade do |t|
    t.string "str"
    t.string "str10", limit: 10
    t.string "str1000", limit: 1000
    t.string "str15000", limit: 15000
    t.text "txt"
    t.text "txt10000"
    t.text "txt100000", size: :medium
    t.datetime "created_at", precision: 6, null: false
    t.datetime "updated_at", precision: 6, null: false
  end

end

以下略

7.0

ActiveRecord::Schema[7.0].define(version: 2024_00_00_000000) do
  create_table "hoges", charset: "utf8mb4", force: :cascade do |t|
    t.string "str"
    t.string "str10", limit: 10
    t.string "str1000", limit: 1000
    t.string "str15000", limit: 15000
    t.text "txt"
    t.text "txt10000"
    t.text "txt100000", size: :medium
    t.datetime "created_at", null: false
    t.datetime "updated_at", null: false
  end

end

7.1

ActiveRecord::Schema[7.1].define(version: 2024_00_00_000000) do
  create_table "hoges", charset: "utf8mb4", force: :cascade do |t|
    t.string "str"
    t.string "str10", limit: 10
    t.string "str1000", limit: 1000
    t.string "str15000", limit: 15000
    t.text "txt"
    t.text "txt10000"
    t.text "txt100000", size: :medium
    t.datetime "created_at", null: false
    t.datetime "updated_at", null: false
  end

end

データベース確認（データベース作成後）

起動中の DB コンテナに入る

ローカル

docker compose exec db5 bash

MySQL に入る

コンテナ内

mysql -uroot

作成されたデータベース一覧

mysql> SHOW databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| app52_development  |
| app52_test         |
| app61_development  |
| app61_test         |
| app70_development  |
| app70_test         |
| app71_development  |
| app71_test         |
| mysql              |
| performance_schema |
| sys                |
+--------------------+

各データベースの文字セットとコレーション確認

Rails 5.2 では 文字コードとコレーションは utf8, utf8_general_ci、 それ以外は utf8mb4, utf8mb4_general_ci になっていることがわかります
database.yml で指定した通りです

mysql> SELECT SCHEMA_NAME, DEFAULT_CHARACTER_SET_NAME, DEFAULT_COLLATION_NAME FROM INFORMATION_SCHEMA.SCHEMATA;
+--------------------+----------------------------+------------------------+
| SCHEMA_NAME        | DEFAULT_CHARACTER_SET_NAME | DEFAULT_COLLATION_NAME |
+--------------------+----------------------------+------------------------+
| information_schema | utf8                       | utf8_general_ci        |
| app52_development  | utf8                       | utf8_general_ci        |
| app52_test         | utf8                       | utf8_general_ci        |
| app61_development  | utf8mb4                    | utf8mb4_general_ci     |
| app61_test         | utf8mb4                    | utf8mb4_general_ci     |
| app70_development  | utf8mb4                    | utf8mb4_general_ci     |
| app70_test         | utf8mb4                    | utf8mb4_general_ci     |
| app71_development  | utf8mb4                    | utf8mb4_general_ci     |
| app71_test         | utf8mb4                    | utf8mb4_general_ci     |
| mysql              | latin1                     | latin1_swedish_ci      |
| performance_schema | utf8                       | utf8_general_ci        |
| sys                | utf8                       | utf8_general_ci        |
+--------------------+----------------------------+------------------------+

各データベースのテーブルの文字セットなど確認

Collation は Rails 5.2 では utf8_general_ci, それ以外は utf8mb4_general_ci になっていることがわかります
Collation 以外は一緒でした
こちらも database.yml で指定した通りです
後述するインデックスに関わってくる Row_format は Dynamic

mysql> SHOW TABLE STATUS FROM app52_development WHERE name = "hoges";
+-------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+
| Name  | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time | Check_time | Collation       | Checksum | Create_options | Comment |
+-------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+
| hoges | InnoDB |      10 | Dynamic    |    0 |              0 |       16384 |               0 |            0 |         0 |              1 | 2024-05-30 00:00:00 | NULL        | NULL       | utf8_general_ci |     NULL |                |         |
+-------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+
1 row in set (0.040 sec)

mysql> SHOW TABLE STATUS FROM app61_development WHERE name = "hoges";
+-------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+--------------------+----------+----------------+---------+
| Name  | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time | Check_time | Collation          | Checksum | Create_options | Comment |
+-------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+--------------------+----------+----------------+---------+
| hoges | InnoDB |      10 | Dynamic    |    0 |              0 |       16384 |               0 |            0 |         0 |              1 | 2024-05-30 00:00:00 | NULL        | NULL       | utf8mb4_general_ci |     NULL |                |         |
+-------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+--------------------+----------+----------------+---------+
1 row in set (0.020 sec)

mysql> SHOW TABLE STATUS FROM app70_development WHERE name = "hoges";
+-------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+--------------------+----------+----------------+---------+
| Name  | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time | Check_time | Collation          | Checksum | Create_options | Comment |
+-------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+--------------------+----------+----------------+---------+
| hoges | InnoDB |      10 | Dynamic    |    0 |              0 |       16384 |               0 |            0 |         0 |              1 | 2024-05-30 00:00:00 | NULL        | NULL       | utf8mb4_general_ci |     NULL |                |         |
+-------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+--------------------+----------+----------------+---------+
1 row in set (0.031 sec)

mysql> SHOW TABLE STATUS FROM app71_development WHERE name = "hoges";
+-------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+--------------------+----------+----------------+---------+
| Name  | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time | Check_time | Collation          | Checksum | Create_options | Comment |
+-------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+--------------------+----------+----------------+---------+
| hoges | InnoDB |      10 | Dynamic    |    0 |              0 |       16384 |               0 |            0 |         0 |              1 | 2024-05-30 00:00:00 | NULL        | NULL       | utf8mb4_general_ci |     NULL |                |         |
+-------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+--------------------+----------+----------------+---------+
1 row in set (0.032 sec)

各カラムの文字セット、コレーション、型、バイト数など確認

mysql> USE information_schema;
mysql> SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, CHARACTER_SET_NAME, COLLATION_NAME, DATA_TYPE, COLUMN_TYPE, CHARACTER_MAXIMUM_LENGTH, CHARACTER_OCTET_LENGTH FROM columns WHERE table_schema LIKE "app%" AND table_name = "hoges" AND collation_name IS NOT NULL;
+-------------------+------------+-------------+--------------------+--------------------+------------+----------------+--------------------------+------------------------+
| TABLE_SCHEMA      | TABLE_NAME | COLUMN_NAME | CHARACTER_SET_NAME | COLLATION_NAME     | DATA_TYPE  | COLUMN_TYPE    | CHARACTER_MAXIMUM_LENGTH | CHARACTER_OCTET_LENGTH |
+-------------------+------------+-------------+--------------------+--------------------+------------+----------------+--------------------------+------------------------+
| app52_development | hoges      | str         | utf8               | utf8_general_ci    | varchar    | varchar(255)   |                      255 |                    765 |
| app52_development | hoges      | str10       | utf8               | utf8_general_ci    | varchar    | varchar(10)    |                       10 |                     30 |
| app52_development | hoges      | str1000     | utf8               | utf8_general_ci    | varchar    | varchar(1000)  |                     1000 |                   3000 |
| app52_development | hoges      | str15000    | utf8               | utf8_general_ci    | varchar    | varchar(15000) |                    15000 |                  45000 |
| app52_development | hoges      | txt         | utf8               | utf8_general_ci    | text       | text           |                    65535 |                  65535 |
| app52_development | hoges      | txt10000    | utf8               | utf8_general_ci    | text       | text           |                    65535 |                  65535 |
| app52_development | hoges      | txt100000   | utf8               | utf8_general_ci    | mediumtext | mediumtext     |                 16777215 |               16777215 |
| app61_development | hoges      | str         | utf8mb4            | utf8mb4_general_ci | varchar    | varchar(255)   |                      255 |                   1020 |
| app61_development | hoges      | str10       | utf8mb4            | utf8mb4_general_ci | varchar    | varchar(10)    |                       10 |                     40 |
| app61_development | hoges      | str1000     | utf8mb4            | utf8mb4_general_ci | varchar    | varchar(1000)  |                     1000 |                   4000 |
| app61_development | hoges      | str15000    | utf8mb4            | utf8mb4_general_ci | varchar    | varchar(15000) |                    15000 |                  60000 |
| app61_development | hoges      | txt         | utf8mb4            | utf8mb4_general_ci | text       | text           |                    65535 |                  65535 |
| app61_development | hoges      | txt10000    | utf8mb4            | utf8mb4_general_ci | text       | text           |                    65535 |                  65535 |
| app61_development | hoges      | txt100000   | utf8mb4            | utf8mb4_general_ci | mediumtext | mediumtext     |                 16777215 |               16777215 |
| app70_development | hoges      | str         | utf8mb4            | utf8mb4_general_ci | varchar    | varchar(255)   |                      255 |                   1020 |
| app70_development | hoges      | str10       | utf8mb4            | utf8mb4_general_ci | varchar    | varchar(10)    |                       10 |                     40 |
| app70_development | hoges      | str1000     | utf8mb4            | utf8mb4_general_ci | varchar    | varchar(1000)  |                     1000 |                   4000 |
| app70_development | hoges      | str15000    | utf8mb4            | utf8mb4_general_ci | varchar    | varchar(15000) |                    15000 |                  60000 |
| app70_development | hoges      | txt         | utf8mb4            | utf8mb4_general_ci | text       | text           |                    65535 |                  65535 |
| app70_development | hoges      | txt10000    | utf8mb4            | utf8mb4_general_ci | text       | text           |                    65535 |                  65535 |
| app70_development | hoges      | txt100000   | utf8mb4            | utf8mb4_general_ci | mediumtext | mediumtext     |                 16777215 |               16777215 |
| app71_development | hoges      | str         | utf8mb4            | utf8mb4_general_ci | varchar    | varchar(255)   |                      255 |                   1020 |
| app71_development | hoges      | str10       | utf8mb4            | utf8mb4_general_ci | varchar    | varchar(10)    |                       10 |                     40 |
| app71_development | hoges      | str1000     | utf8mb4            | utf8mb4_general_ci | varchar    | varchar(1000)  |                     1000 |                   4000 |
| app71_development | hoges      | str15000    | utf8mb4            | utf8mb4_general_ci | varchar    | varchar(15000) |                    15000 |                  60000 |
| app71_development | hoges      | txt         | utf8mb4            | utf8mb4_general_ci | text       | text           |                    65535 |                  65535 |
| app71_development | hoges      | txt10000    | utf8mb4            | utf8mb4_general_ci | text       | text           |                    65535 |                  65535 |
| app71_development | hoges      | txt100000   | utf8mb4            | utf8mb4_general_ci | mediumtext | mediumtext     |                 16777215 |               16777215 |
+-------------------+------------+-------------+--------------------+--------------------+------------+----------------+--------------------------+------------------------+

database.yml に指定された通り、データベース、テーブル、カラムの文字セット、コレーションが設定されていることが確認できました

Rails version	database.yml の encoding	database の collation_name
5.2	utf8	collation_name: utf8_general_ci
6.1	utf8mb4	collation_name: utf8mb4_general_ci
7.0	utf8mb4	collation_name: utf8mb4_general_ci
7.1	utf8mb4	collation_name: utf8mb4_general_ci

同じカラムを抜粋して比較

mysql> SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, CHARACTER_SET_NAME, COLLATION_NAME, DATA_TYPE, COLUMN_TYPE, CHARACTER_MAXIMUM_LENGTH, CHARACTER_OCTET_LENGTH FROM columns
    -> WHERE table_schema LIKE "app%" AND table_name = "hoges" AND collation_name IS NOT NULL AND column_name = 'str10';
+-------------------+------------+-------------+--------------------+--------------------+-----------+-------------+--------------------------+------------------------+
| TABLE_SCHEMA      | TABLE_NAME | COLUMN_NAME | CHARACTER_SET_NAME | COLLATION_NAME     | DATA_TYPE | COLUMN_TYPE | CHARACTER_MAXIMUM_LENGTH | CHARACTER_OCTET_LENGTH |
+-------------------+------------+-------------+--------------------+--------------------+-----------+-------------+--------------------------+------------------------+
| app52_development | hoges      | str10       | utf8               | utf8_general_ci    | varchar   | varchar(10) |                       10 |                     30 |
| app61_development | hoges      | str10       | utf8mb4            | utf8mb4_general_ci | varchar   | varchar(10) |                       10 |                     40 |
| app70_development | hoges      | str10       | utf8mb4            | utf8mb4_general_ci | varchar   | varchar(10) |                       10 |                     40 |
| app71_development | hoges      | str10       | utf8mb4            | utf8mb4_general_ci | varchar   | varchar(10) |                       10 |                     40 |
+-------------------+------------+-------------+--------------------+--------------------+-----------+-------------+--------------------------+------------------------+

CHARACTER_OCTET_LENGTH をみると、utf8 と utf8mb4 で値が異なることがわかります
それぞれ 3 バイトと 4 バイトなため、サイズも 3:4 になっています

utf8 から utf8mb4 にする際に容量に収まらない場合が出ることが今回の問題です

絵文字の保存

Rails 5.2 のデフォルトのまま作ったアプリでは utf8 なため絵文字が保存できません
絵文字を保存できるようにするにはここを utf8mb4 に変更する必要があるということですね

rails c

Running via Spring preloader in process 172
Loading development environment (Rails 5.2.8.1)
> Hoge.all
=> #<ActiveRecord::Relation []>
> Hoge.create(str: 'a')
> Hoge.all
=> #<ActiveRecord::Relation [#<Hoge id: 1, str: "a", str10: nil, str1000: nil, str15000: nil, txt: nil, txt10000: nil, txt100000: nil, created_at: "2024-05-30 00:00:00", updated_at: "2024-05-30 00:00:00">]>
> Hoge.create(str: '🍣')
   (4.4ms)  BEGIN
  Hoge Create (20.8ms)  INSERT INTO `hoges` (`str`, `created_at`, `updated_at`) VALUES ('🍣', '2024-05-30 00:00:00', '2024-05-30 00:00:00')
   (10.9ms)  ROLLBACK
Traceback (most recent call last):
        1: from (irb):10
ActiveRecord::StatementInvalid (Mysql2::Error: Incorrect string value: '\xF0\x9F\x8D\xA3' for column 'str' at row 1: INSERT INTO `hoges` (`str`, `created_at`, `updated_at`) VALUES ('🍣', '2024-05-30 00:00:00', '2024-05-30 00:00:00'))

5.2 で作ったもの以外はデータベースの文字セットが utf8mb4 のため絵文字が保存できます

> Hoge.create(str: '🍣')
  TRANSACTION (2.8ms)  BEGIN
  Hoge Create (5.4ms)  INSERT INTO `hoges` (`str`, `created_at`, `updated_at`) VALUES ('🍣', '2024-05-30 00:00:00.000000', '2024-05-30 00:00:00.000000')
  TRANSACTION (5.3ms)  COMMIT
=>
#<Hoge:0x0000aaaae0dd0348
 id: 2,
 str: "🍣",
 str10: nil,
 str1000: nil,
 str15000: nil,
 txt: nil,
 txt10000: nil,
 txt100000: nil,
 created_at: Thu, 30 May 2024 00:00:00.000000000 UTC +00:00,
 updated_at: Thu, 30 May 2024 00:00:00.000000000 UTC +00:00>

ここまでのまとめ

• Rails 5.2 ではデフォルトの文字セットは utf8
• Rails 6 以降ではデフォルトの文字セットは utf8mb4
• Rails 5.2 で作成したアプリで絵文字を保存したい場合は utf8mb4 に変更する必要が可能性がある

本題の文字セット変更（MySQL 5.7 の場合）

それでは本題の文字セットの変更です
ドキュメントによると

文字セットの変更
テーブルのデフォルトの文字セットおよびすべての文字カラム (CHAR、VARCHAR、TEXT) を新しい文字セットに変更するには、次のようなステートメントを使用します。

ALTER TABLE tbl_name CONVERT TO CHARACTER SET charset_name;

https://dev.mysql.com/doc/refman/8.0/ja/alter-table.html

とのことなので、Rails 5.2 で作ったデータベースに対して以下を実行します

ALTER TABLE hoges CONVERT TO CHARACTER SET utf8mb4;

結果

Query OK, 1 row affected (0.240 sec)
Records: 1  Duplicates: 0  Warnings: 0

データベース確認（文字セット変更後）

先ほど確認したデータベースの各種文字設定がどうなったか確認します

まずはデータベース

変更はありません
ここを変えるには別途データベースの文字セットを変更する必要があるようです

mysql> SHOW TABLE STATUS FROM app52_development WHERE name = "hoges";
+-------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+--------------------+----------+----------------+---------+
| Name  | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time         | Check_time | Collation          | Checksum | Create_options | Comment |
+-------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+--------------------+----------+----------------+---------+
| hoges | InnoDB |      10 | Dynamic    |    0 |              0 |       16384 |               0 |            0 |         0 |              2 | 2024-05-30 00:00:00 | 2024-05-30 00:00:00 | NULL       | utf8mb4_general_ci |     NULL |                |         |
+-------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+--------------------+----------+----------------+---------+

次はテーブル

変更はありません
ここを変えるには別途テーブルの文字セットを変更する必要があるようです

mysql> SELECT SCHEMA_NAME, DEFAULT_CHARACTER_SET_NAME, DEFAULT_COLLATION_NAME FROM INFORMATION_SCHEMA.SCHEMATA;
+--------------------+----------------------------+------------------------+
| SCHEMA_NAME        | DEFAULT_CHARACTER_SET_NAME | DEFAULT_COLLATION_NAME |
+--------------------+----------------------------+------------------------+
| information_schema | utf8                       | utf8_general_ci        |
| app52_development  | utf8                       | utf8_general_ci        |
| app52_test         | utf8                       | utf8_general_ci        |
| app61_development  | utf8mb4                    | utf8mb4_general_ci     |
| app61_test         | utf8mb4                    | utf8mb4_general_ci     |
| app70_development  | utf8mb4                    | utf8mb4_general_ci     |
| app70_test         | utf8mb4                    | utf8mb4_general_ci     |
| app71_development  | utf8mb4                    | utf8mb4_general_ci     |
| app71_test         | utf8mb4                    | utf8mb4_general_ci     |
| mysql              | latin1                     | latin1_swedish_ci      |
| performance_schema | utf8                       | utf8_general_ci        |
| sys                | utf8                       | utf8_general_ci        |
+--------------------+----------------------------+------------------------+

最後にカラム

はい、変わりました

そんなバカな・・・
あなた、utf8mb3 の代わりに utf8mb4 を使えとか言っていたじゃない・・・
保存しているデータは a の 1 文字のテーブルなのに
そしてデータを入れたこともない txt100000 なんて 16777215 文字保存できるカラムから 4294967295 文字保存できるカラムに格上げされています
もはや何桁かわかりませんね

mysql> USE information_schema;
mysql> SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, CHARACTER_SET_NAME, COLLATION_NAME, DATA_TYPE, COLUMN_TYPE, CHARACTER_MAXIMUM_LENGTH, CHARACTER_OCTET_LENGTH FROM columns WHERE table_schema LIKE "app52%" AND table_name = "hoges" AND collation_name IS NOT NULL;
+-------------------+------------+-------------+--------------------+--------------------+------------+----------------+--------------------------+------------------------+
| TABLE_SCHEMA      | TABLE_NAME | COLUMN_NAME | CHARACTER_SET_NAME | COLLATION_NAME     | DATA_TYPE  | COLUMN_TYPE    | CHARACTER_MAXIMUM_LENGTH | CHARACTER_OCTET_LENGTH |
+-------------------+------------+-------------+--------------------+--------------------+------------+----------------+--------------------------+------------------------+
| app52_development | hoges      | str         | utf8mb4            | utf8mb4_general_ci | varchar    | varchar(255)   |                      255 |                   1020 |
| app52_development | hoges      | str10       | utf8mb4            | utf8mb4_general_ci | varchar    | varchar(10)    |                       10 |                     40 |
| app52_development | hoges      | str1000     | utf8mb4            | utf8mb4_general_ci | varchar    | varchar(1000)  |                     1000 |                   4000 |
| app52_development | hoges      | str15000    | utf8mb4            | utf8mb4_general_ci | varchar    | varchar(15000) |                    15000 |                  60000 |
| app52_development | hoges      | txt         | utf8mb4            | utf8mb4_general_ci | mediumtext | mediumtext     |                 16777215 |               16777215 |
| app52_development | hoges      | txt10000    | utf8mb4            | utf8mb4_general_ci | mediumtext | mediumtext     |                 16777215 |               16777215 |
| app52_development | hoges      | txt100000   | utf8mb4            | utf8mb4_general_ci | longtext   | longtext       |               4294967295 |             4294967295 |
+-------------------+------------+-------------+--------------------+--------------------+------------+----------------+--------------------------+------------------------+

比較します

+-------------------+------------+-------------+--------------------+--------------------+------------+----------------+--------------------------+------------------------+
| TABLE_SCHEMA      | TABLE_NAME | COLUMN_NAME | CHARACTER_SET_NAME | COLLATION_NAME     | DATA_TYPE  | COLUMN_TYPE    | CHARACTER_MAXIMUM_LENGTH | CHARACTER_OCTET_LENGTH |
+-------------------+------------+-------------+--------------------+--------------------+------------+----------------+--------------------------+------------------------+
-- 変更前
| app52_development | hoges      | str         | utf8               | utf8_general_ci    | varchar    | varchar(255)   |                      255 |                    765 |
| app52_development | hoges      | str10       | utf8               | utf8_general_ci    | varchar    | varchar(10)    |                       10 |                     30 |
| app52_development | hoges      | str1000     | utf8               | utf8_general_ci    | varchar    | varchar(1000)  |                     1000 |                   3000 |
| app52_development | hoges      | str15000    | utf8               | utf8_general_ci    | varchar    | varchar(15000) |                    15000 |                  45000 |
| app52_development | hoges      | txt         | utf8               | utf8_general_ci    | text       | text           |                    65535 |                  65535 |
| app52_development | hoges      | txt10000    | utf8               | utf8_general_ci    | text       | text           |                    65535 |                  65535 |
| app52_development | hoges      | txt100000   | utf8               | utf8_general_ci    | mediumtext | mediumtext     |                 16777215 |               16777215 |
-- 変更後
| app52_development | hoges      | str         | utf8mb4            | utf8mb4_general_ci | varchar    | varchar(255)   |                      255 |                   1020 |
| app52_development | hoges      | str10       | utf8mb4            | utf8mb4_general_ci | varchar    | varchar(10)    |                       10 |                     40 |
| app52_development | hoges      | str1000     | utf8mb4            | utf8mb4_general_ci | varchar    | varchar(1000)  |                     1000 |                   4000 |
| app52_development | hoges      | str15000    | utf8mb4            | utf8mb4_general_ci | varchar    | varchar(15000) |                    15000 |                  60000 |
| app52_development | hoges      | txt         | utf8mb4            | utf8mb4_general_ci | mediumtext | mediumtext     |                 16777215 |               16777215 |
| app52_development | hoges      | txt10000    | utf8mb4            | utf8mb4_general_ci | mediumtext | mediumtext     |                 16777215 |               16777215 |
| app52_development | hoges      | txt100000   | utf8mb4            | utf8mb4_general_ci | longtext   | longtext       |               4294967295 |             4294967295 |
+-------------------+------------+-------------+--------------------+--------------------+------------+----------------+--------------------------+------------------------+

変更前と変更後を比較すると、

• 文字セットが utf8 から utf8mb4 に変更
• string として作成した varchar のカラムの CHARACTER_OCTET_LENGTH が 4/3 に増加
• text として作成した text のカラムのタイプが mediumtext に変更
• text として作成した mediumtext のカラムのタイプが longtext に変更

という変化がありました
また、t.string :str20000, limit: 20_000 として作成したカラムがある場合には、varchar のまま保存できる上限を超えるためエラーになりました

mysql> ALTER TABLE hoges CONVERT TO CHARACTER SET utf8mb4;
ERROR 1074 (42000): Column length too big for column 'str20000' (max = 16383); use BLOB or TEXT instead

本題の文字セット変更（MySQL 8.0 の場合）

エイリアスの utf8 が utf8mb3 になっている以外は同様の変更が入りました

mysql> SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, CHARACTER_SET_NAME, COLLATION_NAME, DATA_TYPE, COLUMN_TYPE, CHARACTER_MAXIMUM_LENGTH, CHARACTER_OCTET_LENGTH FROM columns WHERE table_schema LIKE "app52%" AND table_name = "hoges" AND collation_name IS NOT NULL;
+-------------------+------------+-------------+--------------------+--------------------+------------+----------------+--------------------------+------------------------+
| TABLE_SCHEMA      | TABLE_NAME | COLUMN_NAME | CHARACTER_SET_NAME | COLLATION_NAME     | DATA_TYPE  | COLUMN_TYPE    | CHARACTER_MAXIMUM_LENGTH | CHARACTER_OCTET_LENGTH |
+-------------------+------------+-------------+--------------------+--------------------+------------+----------------+--------------------------+------------------------+
-- 変更前
| app52_development | hoges      | str         | utf8mb3            | utf8mb3_general_ci | varchar    | varchar(255)   |                      255 |                    765 |
| app52_development | hoges      | str10       | utf8mb3            | utf8mb3_general_ci | varchar    | varchar(10)    |                       10 |                     30 |
| app52_development | hoges      | str1000     | utf8mb3            | utf8mb3_general_ci | varchar    | varchar(1000)  |                     1000 |                   3000 |
| app52_development | hoges      | str15000    | utf8mb3            | utf8mb3_general_ci | varchar    | varchar(15000) |                    15000 |                  45000 |
| app52_development | hoges      | txt         | utf8mb3            | utf8mb3_general_ci | text       | text           |                    65535 |                  65535 |
| app52_development | hoges      | txt10000    | utf8mb3            | utf8mb3_general_ci | text       | text           |                    65535 |                  65535 |
| app52_development | hoges      | txt100000   | utf8mb3            | utf8mb3_general_ci | mediumtext | mediumtext     |                 16777215 |               16777215 |
-- 変更後
+-------------------+------------+-------------+--------------------+--------------------+------------+----------------+--------------------------+------------------------+
| app52_development | hoges      | str         | utf8mb4            | utf8mb4_0900_ai_ci | varchar    | varchar(255)   |                      255 |                   1020 |
| app52_development | hoges      | str10       | utf8mb4            | utf8mb4_0900_ai_ci | varchar    | varchar(10)    |                       10 |                     40 |
| app52_development | hoges      | str1000     | utf8mb4            | utf8mb4_0900_ai_ci | varchar    | varchar(1000)  |                     1000 |                   4000 |
| app52_development | hoges      | str15000    | utf8mb4            | utf8mb4_0900_ai_ci | varchar    | varchar(15000) |                    15000 |                  60000 |
| app52_development | hoges      | txt         | utf8mb4            | utf8mb4_0900_ai_ci | mediumtext | mediumtext     |                 16777215 |               16777215 |
| app52_development | hoges      | txt10000    | utf8mb4            | utf8mb4_0900_ai_ci | mediumtext | mediumtext     |                 16777215 |               16777215 |
| app52_development | hoges      | txt100000   | utf8mb4            | utf8mb4_0900_ai_ci | longtext   | longtext       |               4294967295 |             4294967295 |
+-------------------+------------+-------------+--------------------+--------------------+------------+----------------+--------------------------+------------------------+

カラムの型が変わると困る場合にどうしたら良いのか

MODIFY を使用することで型を変更せずに文字セットを変更することができるらしい

データ型の変更を回避するには、CONVERT TO CHARACTER SET を使用しないでください。 代わりに、MODIFY を使用して個々のカラムを変更します。 例:

ALTER TABLE t MODIFY latin1_text_col TEXT CHARACTER SET utf8;
ALTER TABLE t MODIFY latin1_varchar_col VARCHAR(M) CHARACTER SET utf8;

インデックス長について

今回作ったデータでは Row_format は Dynamic だったため問題はないですが、COMPACT または REDUNDANT の場合はインデックスの長さ注意が必要だそうです
文字セットの変換が必要になった際には Row_format も確認しましょう

3 バイト Unicode 文字セットと 4 バイト Unicode 文字セット間の変換

InnoDB では、COMPACT または REDUNDANT の行形式を使用するテーブルのインデックスの最大長は 767 バイトであるため、utf8mb3 または utf8mb4 のカラムでは、それぞれ 255 文字または 191 文字までインデックス付けできます。 191 文字を超えるインデックスを持つ utf8mb3 カラムが現在ある場合は、インデックス付けする文字数を減らす必要があります。

https://dev.mysql.com/doc/refman/8.0/ja/charset-unicode-conversion.html

おわり

文字セットを utf8 から utf8mb4 に変更する際、カラムの型やインデックスの長さに注意が必要であることがわかりました
また、今回は割愛しましたが、テーブルのデフォルトやデータベースのデフォルトについても変更が必要で、プロダクション環境・ローカル環境ともに揃えるようにしないとまた問題になりそうです

文字セット、コレーションは後から変えるのは大変なので、最初から設定したいですね
日本語を取り扱うアプリケーションでは utf8mb4 でも *_general_ci ではなく *_bin を使っておけばいいんじゃないかなと思います

（せっかくいくつかのバージョンの Rails アプリや Ruby バージョンで比較できるような環境を作ったので、何かに使えたらいいなと思います）

おまけ

ターミナルから複数のシェルを開いて同時に操作するのに tmux を使って作業しました
よく使う機能しか覚えていないので使いこなせてはいないかもしれませんが便利です
tmux を使うなら tmuximum も合わせておすすめです

寿司ビール問題

utf8mb4 になっている場合は絵文字は保存できますが、コレーション設定がそれぞれ *_general_ci になっているため、文字の区別されないものがあり、寿司ビール問題、ハハパパ問題などと呼ばれる現象が起きる可能性があります
これを回避するには区別したい文字が区別されるコレーションを選択する必要があります
utf8mb4_bin であれば全て区別されるため、今は迷ったらとりあえずこれでいいんじゃないかと思ってます

> Hoge.find_by(str: '🍺')
  Hoge Load (11.9ms)  SELECT `hoges`.* FROM `hoges` WHERE `hoges`.`str` = '🍺' LIMIT 1
=>
#<Hoge:0x0000aaaae11cd428
 id: 2,
 str: "🍣",
 str10: nil,
 str1000: nil,
 str15000: nil,
 txt: nil,
 txt10000: nil,
 txt100000: nil,
 created_at: Thu, 30 May 2024 00:00:00.000000000 UTC +00:00,
 updated_at: Thu, 30 May 2024 00:00:00.000000000 UTC +00:00>

🍣=🍺

コレーションについての詳細は以下のドキュメントを参照してください

https://dev.mysql.com/doc/refman/8.0/ja/charset-collation-names.html