MySQL（MariaDB）で#22007 Incorrect string valueエラー

テーブルへの行追加や行更新しようとしたところ「#22007 Incorrect string value」エラーが発生しました。対象テーブルの文字コードを確認すると「DEFAULT CHARSET=latin1」というラテン系になっていました。

MySQL（MariaDB）の文字コード問題を解決する

MySQL（MariaDB）の文字コード問題を解決する

サーバーにSSHで接続し、MariaDBに接続する

[youruser@157-7-193-0 ~]$ su
Password: 
[root@157-7-193-0 youruser]# mysql -u root -p
Enter password:

文字コードを確認する

「utf8」ではなく「utf8mb4」に変更していく。
なおutf8になっていればマルチバイトが問題無さそうな気もするが、MySQL（MariaDB）では3バイトまでしか扱えない。

MariaDB [(none)]> SHOW VARIABLES LIKE 'chara%';
+--------------------------+------------------------------+
| Variable_name            | Value                        |
+--------------------------+------------------------------+
| character_set_client     | utf8                         |
| character_set_connection | utf8                         |
| character_set_database   | latin1                       |
| character_set_filesystem | binary                       |
| character_set_results    | utf8                         |
| character_set_server     | latin1                       |
| character_set_system     | utf8                         |
| character_sets_dir       | /usr/share/mariadb/charsets/ |
+--------------------------+------------------------------+
8 rows in set (0.001 sec)

文字コードを変更するための設定ファイルの場所を探す

MySQL（MariaDB）の設定ファイルの場所は「mysql –help | grep my.cnf」で探せる。一度場所を暗記すれば必要のない操作。

[root@157-7-193-0 /]# mysql --help | grep my.cnf
/etc/my.cnf ~/.my.cnf 
                      order of preference, my.cnf, $MYSQL_TCP_PORT,

my.cnfの中身を確認する

「!includedir /etc/my.cnf.d」となっており、このフォルダ配下の「*.cnf」を読み込むようになっている。

#
# This group is read both both by the client and the server
# use it for options that affect everything
#
[client-server]

#
# include all files from the config directory
#
!includedir /etc/my.cnf.d

「/etc/my.cnf.d」の中で手を加えて良さそうなファイルを探す

私の環境では「mariadb-server.cnf」が適切っぽい

[root@157-7-193-0 my.cnf.d]# ll
total 24
-rw-r--r-- 1 root root   42 Jun 13  2022 auth_gssapi.cnf
-rw-r--r-- 1 root root  295 Mar 26  2022 client.cnf
-rw-r--r-- 1 root root  763 May 18  2022 enable_encryption.preset
-rw-r--r-- 1 root root 1458 Jun 13  2022 mariadb-server.cnf
-rw-r--r-- 1 root root  232 May 18  2022 mysql-clients.cnf
-rw-r--r-- 1 root root  120 May 18  2022 spider.cnf

文字コードの指定を追加、または変更する

[mysqld]
character-set-server=utf8mb4

[client]
default-character-set=utf8mb4

MySQL（MariaDB）の再起動

設定変更したので再起動する（ほぼ一瞬）

sudo systemctl restart mariadb

文字コードを確認する

「utf8mb4」に切り替わったのでこちらの設定はこれでOK。
ただし、作成済みのテーブルがある場合、そちらの対応も必要。

MariaDB [(none)]> SHOW VARIABLES LIKE 'chara%';
+--------------------------+------------------------------+
| Variable_name            | Value                        |
+--------------------------+------------------------------+
| character_set_client     | utf8mb4                      |
| character_set_connection | utf8mb4                      |
| character_set_database   | utf8mb4                      |
| character_set_filesystem | binary                       |
| character_set_results    | utf8mb4                      |
| character_set_server     | utf8mb4                      |
| character_set_system     | utf8                         |
| character_sets_dir       | /usr/share/mariadb/charsets/ |
+--------------------------+------------------------------+
8 rows in set (0.002 sec)

各テーブルの文字コードを変更する

テーブル数が多いと大変だが間違ってもテーブル名を手打ちするなどしないこと。
「show tables;」でテーブル名の一覧を取得すると楽ちん。

ALTER TABLE table_name 
CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;