MySQLのtable collationが意図した値になっているかテストするActions Workflowを作った

文字セットと照合順序

MySQLに限らず多くのRDBMSには文字をどのようにバイト列で表現するかを決めるルールである文字セットがあります。

また文字セットに対応して文字同士を比較するときのルールである照合順序*1という概念があります。MySQLの場合、この照合順序は1つの文字セットに対して複数存在する概念です。なのでそれぞれの文字セットに対してデフォルトの照合順序があります。

照合順序が何に設定されているのかを把握していないと思わぬ結果を招くことになり、有名なのが寿司ビール問題と呼ばれるものになります。これは寿司の絵文字とビールの絵文字がMySQLのutf8mb4_general_ciという照合順序の下で等価であると判定されることが原因です。

blog.kamipo.net

ここまで照合順序の話をしましたが間違ったことを話している可能性があるので公式ドキュメントを参照してください。

dev.mysql.com

デフォルト値の変更

さてMySQL5.7系とMySQL8.0系ではutf8mb4に対応するデフォルト照合順序が変更になります。 gihyo.jp

照合順序のあいまいさを排除するためにどこかで照合順序を明記すると良いでしょう。

Auroraなど照合順序の設定がテーブル定義以外で出来ないDBもあるのでCREATE TABLE文で文字セットを指定するのと同時に照合順序も指定すると良さそうです。

blog.smartbank.co.jp

何作ったの

テーブル定義ファイルを元にテーブルを作成し、作成したテーブルの照合順序が期待する値か確認するActions Workflowを作成しました。

github.com

使いかた

READMEにある通りですが簡単に使いかたを紹介します。

テーブル定義ファイルを渡して、その定義を元に作ったテーブルが意図する照合順序を持っているかをチェックします。

以下はそのまま動くコードです。

matrixを用いて複数バージョンで検査することで5.7から8.0へ移行したときもそのまま照合順序が変化しないかを確認できます。

name: Check Table Collations
on:
  push:

jobs:
  check-collations:
    runs-on: ubuntu-latest
    strategy:
      fail-fast: false
      matrix:
        mysql-version: ['5.7', '8.0']
    steps:
      - name: Checkout code
        uses: actions/checkout@v3
      - name: Check Collations
        uses: Azuki-bar/mysql-collations-checker-action@main
        with:
          table_definitions: |
            path/to/table.sql
            path/**/*.sql
          expect_collation: utf8mb4_general_ci
          mysql_version: ${{ matrix.mysql-version }}

例えばテーブル定義ファイルが以下のとき、5.7では照合順序がutf8mb4_general_ciとなるため成功しますが、8.0では照合順序がutf8mb4_0900_ai_ciとなるため失敗します。

CREATE TABLE t1 (
  id BIGINT
) CHARACTER SET=utf8mb4;

MySQLにおいてこのあいまいさを解消するには、以下のようにCREATE TABLE文で文字セットと一緒に照合順序を指定すると良いでしょう。

CREATE TABLE t1 (
  id BIGINT
) CHARACTER SET=utf8mb4 COLLATE=utf8mb4_general_ci;

照合順序を明示することで5.7と8.0のそれぞれでテストが成功します。

matrixでテストすることにより、テーブル定義に照合順序が指定されているかを間接的にテストすることができます。

まとめ

照合順序が意図した値で設定されるかを確認するテストを作りました。

公開リポジトリに置いたのでだれでも使用することができます。

ぜひ作成したテーブル定義が意図した照合順序を持っているのか確認してみてくださいね。

*1:Collation