🙆‍♀️

SQLAlchemy+MySQL8.0で照合順序をutf8mb4_general_ciにしたい

2022/06/11に公開約1,700字

SQLAlchemyを使ってsqliteを使っていたのをMySQLに変えるのは接続先変えるだけでそんなに苦労しないだろうと思ってたら意外とハマったのでここに対応方法を記します。

経緯

sqliteでいけるだろうと思っていたが、コンテナで動かすとファイル同期が遅く、コンテナ外のdbファイルに書き込むとパフォーマンスがありえないほど悪かったのと、レコード増加が1日4万件越えてきたので、MySQLに変更することにした。
ローカルでM1 Macを使っており、以前(2021/5頃)試したときはMySQLがネイティブで動かないのでintel版をエミュレートして動かすしか無いという状態だったが、今はいけるらしい。
docker-compose.ymlをintelマシンと共用にしたかったため、M1 macでも利用できるmysqlイメージを使ってMySQL環境を作成を参考にMySQL8.0を使うことに。
これだとarmとintelどっちでもネイティブで動くらしい(?)。

sqliteを使う時にこうなっていたのを

url = 'sqlite:///data.db'
engine = create_engine(url, echo=False)

こうするだけでいけると思っていたが

url = 'mysql+mysqlconnector://%s:%s@%s/%s?charset=utf8mb4' % (
    "root",
    "password",
    "db",
    "data",
)
engine = create_engine(url, echo=False)

文字列が入るカラムに絵文字が入っていると以下のようなエラーが出てしまう。

Incorrect string value: '\\xF0\\x9F\\x92\\xAB\\xE6\\xB8...' for column 'hoge' at row 1

過去の経験からデフォルトの照合順序をutf8mb4_general_ciにすれば解決することはわかっている。
しかし、SQLAlchemyが自動で作成するテーブルの文字列の照合順序がどうしてもutf8mb3_general_ciになってしまう。
色んなサイトを見てmy.cnfの設定をいじったりしたが効果がなく、mysql 8.0から設定項目が変わっているなどもあって恐ろしく時間を食われてしまった。
デフォルトの照合順序をutf8mb4_general_ciにしたいだけなのに!
手動で設定変更クエリなんて使いたくない!
そのものズバリの記事がなんでないの?
(SQLAlchemyを昔から使っている人には自明のことなんだろうけど。)

解決方法

以下のようなモデル定義の場合、

class User(Base):
  id = Column(Integer, autoincrement=True, primary_key=True)
  status = Column(Integer)
  name = Column(String(128))
  __tablename__ = 'user'

StringとかTextの所にcollation='utf8mb4_general_ci'を突っ込むだけ。

class User(Base):
  id = Column(Integer, autoincrement=True, primary_key=True)
  status = Column(Integer)
  name = Column(String(128,collation='utf8mb4_general_ci'))
  __tablename__ = 'user'

これだけ!
my.cnfの設定を漁りまくってたのに全くの無駄でした。
MySQL側の設定だけで解決しないのがなんとも気持ち悪い。

Discussion

ログインするとコメントできます