📝
MySQL で文字セットを 3 バイトから 4 バイトに変えたら TEXT 型のカラムが MEDIUMTEXT 型に変わってしまった話
はじめに
こんにちは、hamaguchi です
今日は、MySQL の文字系のカラムの文字セットを変更するとカラムの型が変わってしまったという話です
幸いローカルで試した段階で気づけたためどこにも影響はありませんでしたが、気づかないで進めていたら面倒なことになっていたかもしれません
ローカルで気づけてよかったです
コレーションの設定により寿司ビール問題（記事末尾で解説）、ハハパパ問題が発生するという話は割と有名かもと思いますが、文字セットの変更で型が変わってしまうのは知らなかったのでそんなバカな・・・となりました
発端
担当しているプロジェクトで絵文字を保存できるようにしておいた方がいいよなぁとなったこと、プロジェクト間のコレーションの違いを揃えておきたいという理由から、データベースの文字種を変更することになりました
状況を確認したところ、ざっくり以下のような状況でした
- 前から絵文字対応が必要だったプロジェクトでは文字コード utf8mb4、コレーション utf8mb4_bin
- 今回絵文字対応を進めたいプロジェクトでは文字コード utf8、コレーション utf8_bin
確認
utf8 について調べてみると、utf8mb3 のエイリアスで、将来的に削除されるようです
MySQL 8.0 の段階ではまだ使えますが、具体的にいつ廃止になるか決まっていなさそうでした
文字セットの変更をしていないまま気づかずに utf8mb3 廃止後のバージョンにアップグレードしようとした際に発覚するなんてことになると大変なため、早めに変更しておきたいですね
utf8 は utf8mb3 のエイリアスです。文字制限は、名前に明示的ではなく暗黙的です。
注記
utf8mb3 文字セットは非推奨であり、将来の MySQL リリースで削除される予定です。 かわりに utf8mb4 を使用してください。 utf8 は現在 utf8mb3 のエイリアスですが、ある時点では utf8 が utf8mb4 への参照になることが予想されます。 utf8 の意味があいまいにならないように、utf8 ではなく文字セット参照に utf8mb4 を明示的に指定することを検討してください。
次に文字セットの変更方法を確認します
文字セットの変更
テーブルのデフォルトの文字セットおよびすべての文字カラム (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;
とのことなので、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 カラムが現在ある場合は、インデックス付けする文字数を減らす必要があります。
おわり
文字セットを 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>
🍣=🍺
コレーションについての詳細は以下のドキュメントを参照してください
GitHubで編集を提案
Discussion