[MySQL] QBCoreで起きた "Illegal mix of collations ~" エラーの解決方法

## まえがき
最近はGTA5 RPサーバのエンジニアとして携わらせていただいてます
そんな自分用メモ書き
あるいは同じ状況で困ってる人向け

## エラー文の発生状況
```diff_lua
[script:qb-core] SCRIPT ERROR: citizen:/scripting/lua/scheduler.lua:739: SCRIPT ERROR: @ps-mdt/server/main.lua:256: ps-mdt was unable to execute a query!
[script:qb-core] Query: SELECT p.citizenid, p.charinfo, md.pfp, md.fingerprint FROM players p LEFT JOIN mdt_data md on p.citizenid = md.cid WHERE LOWER(CONCAT(JSON_VALUE(p.charinfo, '$.firstname'), ' ', JSON_VALUE(p.charinfo, '$.lastname'))) LIKE ? OR LOWER(charinfo) LIKE ? OR LOWER(citizenid) LIKE ? OR LOWER(md.fingerprint) LIKE ? AND jobtype = ? LIMIT 20
[script:qb-core] ["%admin%","%admin%","%admin%","%admin%","police"]
[script:qb-core] Illegal mix of collations (utf8mb4_unicode_ci,IMPLICIT) and (utf8mb4_general_ci,IMPLICIT) for operation '='
```
MySQl(MariaDB)環境下
ps-mdtという外部プラグインを導入した時の話
どうやらそのスクリプトが持つ、**Profiles**検索を行うと出る模様

## 結論から言うと
MariaDB(MySQL)のテーブル同士の**照合順序**が異なることが原因

エラー文で指定されている **ps-mdt/server/main.lua:256** を確認すると…

```diff_lua
local people = MySQL.query.await("SELECT p.citizenid, p.charinfo, md.pfp, md.fingerprint FROM players p LEFT JOIN mdt_data md on p.citizenid = md.cid WHERE LOWER(CONCAT(JSON_VALUE(p.charinfo, '$.firstname'), ' ', JSON_VALUE(p.charinfo, '$.lastname'))) LIKE :query OR LOWER(`charinfo`) LIKE :query OR LOWER(`citizenid`) LIKE :query OR LOWER(md.fingerprint) LIKE :query AND jobtype = :jobtype LIMIT 20", { query = string.lower('%'..sentData..'%'), jobtype = JobType })
```
となっている
が、しかしデータベースから照合順序を確認してみると、

何故か大量に存在するが、**players**テーブルの照合順序
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2697232/7d2ff39b-2f82-fd2c-2bbb-6200ff2c568c.png)

**mdt_data**の照合順序
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2697232/d56f89bd-983c-77fc-8e30-8dcc65c4ce10.png)

と、画面右側の **TABLE_COLLATION**に指定されている照合順序が異なることがわかる

これが原因であるが、既に存在するテーブルの照合順序を書き換えるとバグの原因となりかねないため、SELECT文の方を修正していく

:::note alert
個人的には不必要にSQLを操作してDBをイジるのはオススメしない
ソースコードは戻せても、DBのデータを簡単に戻すことは出来ない
:::

## コード修正
単純に照合順序(COLLATION)を合わせればよい

:::note info
どちらに合わせるかはその時次第
今回はより厳密に検索させたかったので utfmb4_general_ci に統一
:::

修正後 > **ps-mdt/server/main.lua:256**
```diff_lua
local people = MySQL.query.await("SELECT p.citizenid, p.charinfo, md.pfp, md.fingerprint FROM players p LEFT JOIN mdt_data md on p.citizenid COLLATE utf8mb4_general_ci = md.cid WHERE LOWER(CONCAT(JSON_VALUE(p.charinfo, '$.firstname'), ' ', JSON_VALUE(p.charinfo, '$.lastname'))) LIKE :query OR LOWER(`charinfo`) LIKE :query OR LOWER(`citizenid`) LIKE :query OR LOWER(md.fingerprint) LIKE :query AND jobtype = :jobtype LIMIT 20", { query = string.lower('%'..sentData..'%'), jobtype = JobType })
```

分かりづらいが、**LEFT JOIN mdt_data ON players ～** の後ろに
**COLLATE utf8mb4_general_ci** が挿入されている

これにより、JOINされる2つのテーブルが同じ照合順序になったため、エラーを修正できた

## 解説
ここから先はスルーOK
あくまで、**何故このエラーが発生したか？** を解説する

:::note info
少しSQLの知識が必要。詳しくは解説しない
:::


### 照合順序とは

MySQLは **文字コード** と **ソート順序(照合順序)** を持っていて、
文字コードが**Charactor Set**
ソート順序(照合順序)が**Collation**
と呼ばれている

そして、文字コードが一致しているが**照合順序が異なる**という場合に
今回のエラーが発生する

:::note info
今回はともに" **utf8mb4** "という文字コードだが、
Collationは" **general_ci** " と " **unicode_ci** " となっていた
:::

### general_ci と unicode_ci の違い
WHERE句やJOIN句での検索順に違いが出る

:::note info
なんのこっちゃって人は、とりあえず検索ヒット数が違うと覚えておくと良いかも
:::


同じSQL文なら、unicode_ciの方が処理が遅い(らしい)

MySQLでの標準は **utf8mb4_0900_ai_ci** であるが、
大体の日本語対応DBでは **utf8mb4_general_ci** か **utf8mb4_unicode_ci**が使われてるはず

([utf8mb4_0900_ai_ciは "="と"≠"を同一視してしまってる](https://yoku0825.blogspot.com/2018/10/utf8mb40900aici.html)とかなんとか)

#### utf8mb4_general_ci の特徴

・半角大文字と半角小文字　**同じと認識**
・カタカナとひらがな　　　**別物として認識**

:::note info
恐らく大文字小文字しか区別しない模様
:::

#### utfmb4_unicode_ci の特徴
・半角大文字と半角小文字　**同じと認識**
・カタカナとひらがな　　　**同じと認識**
・全角大文字と全角小文字　**同じと認識**
・全角と半角　　　　　　　**同じと認識**

:::note info
unicode_ciの方があいまい検索が可能
:::

以上

## 引用
- [utf8mb4_0900_ai_ciは "=" と "≠" を同じ文字だと思っている](https://yoku0825.blogspot.com/2018/10/utf8mb40900aici.html)
- [【MySQL】照合順序とは？](https://qiita.com/kazu56/items/6af85ffcf8d3954455ad)