mysql: insert時にIncorrect string valueエラー

事象

• pythonを使って入力値をテーブルにインサートする際に、次のエラーが発生します。
  今回のケースでは、入力値に絵文字が含まれている場合に発生するようです。
  ...MySQLInterfaceError: Incorrect string value: '\xF0\x9F\x91\x8922...' for column 'remarks' at row 1The above exception was the direct cause of the following exception:Traceback (most recent call last): File "C:\Program Files\Python311\Lib\runpy.py", line 198, in _run_module_as_main return _run_code(code, main_globals, None, ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ File "C:\Program Files\Python311\Lib\runpy.py", line 88, in _run_code exec(code, run_globals)

  1 2 3 4 5 6 7 8 9 10 11

  ...MySQLInterfaceError: Incorrect string value: '\xF0\x9F\x91\x8922...' for column 'remarks' at row 1   The above exception was the direct cause of the following exception:   Traceback (most recent call last):     File "C:\Program Files\Python311\Lib\runpy.py", line 198, in _run_module_as_main     return _run_code(code, main_globals, None,            ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^   File "C:\Program Files\Python311\Lib\runpy.py", line 88, in _run_code     exec(code, run_globals)

• 使用環境は次の通りです。
  

  OS	Windows 10(64ビット)
  言語	Python 3.11.7
  DB	mysql 5.7.27 (winx64, linux)

対応

• mysqlでは、クライアントからのデータベース接続、データベース、テーブル、カラム等の各レベルで文字セット(CHARACTER SET)を指定できます。絵文字等のUTF-8で4バイトになる文字を扱うためには、文字セットとしてutf8mb4を指定する必要があります。
  • unicodeには、当初から存在する基本的な文字群（基本多言語面）、後から追加された文字群（追加多言語面）が含まれています。基本多言語面の文字群は3バイトで表現されますが、絵文字等の追加多言語面の文字群は4バイト（サロゲートペア）で表現されます。
  • 文字セットutf8は、基本多言語面の3バイトまでの文字に対応します。
  • 文字セットutf8mb4は、基本多言語面＋追加多言語面の4バイトまでの文字に対応します。
  • 各文字セットの詳細は、リファレンス 「10.9 Unicode のサポート」をご覧ください。
• 各種の文字セットを確認する例です。
  show variables, show create table等で文字セットを確認できます。
  
  mysql> 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/mysql/charsets/ |+--------------------------+----------------------------+mysql> show create table t_result;...| t_result| CREATE TABLE `t_result` ( `target_date` date NOT NULL, ... `ship_id` char(2) DEFAULT NULL, `ship_flight` varchar(128) CHARACTER SET utf8 DEFAULT NULL, `ope_status` char(1) DEFAULT NULL, `remarks` varchar(1024) CHARACTER SET utf8 DEFAULT NULL, `created_on` datetime NOT NULL, `updated_on` datetime NOT NULL, PRIMARY KEY (`target_date`,`seq`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 |...

  1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28

  mysql> 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/mysql/charsets/ | +--------------------------+----------------------------+   mysql> show create table t_result; ... | t_result| CREATE TABLE `t_result` (   `target_date` date NOT NULL,   ...   `ship_id` char(2) DEFAULT NULL,   `ship_flight` varchar(128) CHARACTER SET utf8 DEFAULT NULL,   `ope_status` char(1) DEFAULT NULL,   `remarks` varchar(1024) CHARACTER SET utf8 DEFAULT NULL,   `created_on` datetime NOT NULL,   `updated_on` datetime NOT NULL,   PRIMARY KEY (`target_date`,`seq`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 | ...

• 私の場合、エラーメッセージで明記されたremarks列の文字セットが”CHARACTER SET utf8″になっていることが原因のようです。次のように列の文字コードをutf8mb4に変更することで問題が解決しました。各レベルでの文字セットの指定方法はリファレンスをご覧ください。
  
  -- remarks, ship_flight 列の文字セットをutf8mb4に変更alter table t_result modify remarks varchar(1024) CHARACTER SET utf8mb4;alter table t_result modify ship_flight varchar(1024) CHARACTER SET utf8mb4;

  1 2 3

  -- remarks, ship_flight 列の文字セットをutf8mb4に変更 alter table t_result modify remarks varchar(1024) CHARACTER SET utf8mb4; alter table t_result modify ship_flight varchar(1024) CHARACTER SET utf8mb4;