MySQL(MariaDB)にUTF-8の4バイト文字を登録する方法

MySQL(MariaDB)にUTF-8の4バイト文字を登録する方法

TwitterなどのSNSで取得したHTML埋め込みコードをMySQL(MariaDB)に保存した時に、中身がはてな(文字化け)で登録されてしまう現象が発生してしまって、調査をしました。

現象

TwitterなどのSNSで取得したHTML埋め込みコードに絵文字が入っているとMySQL(MariaDB)に保存できない。

はてな(文字化け)で登録されてしまう。

-- テーブル
show create table tbl_test2;
+-----------+----------------------------+
| Table     | Create Table               |
+-----------+----------------------------+
| tbl_test2 | CREATE TABLE `tbl_test2` (
  `test` text CHARACTER SET utf8mb4
) ENGINE=InnoDB DEFAULT CHARSET=utf8     |
+-----------+----------------------------+

-- INSERT
INSERT INTO tbl_test2 (test)VALUES('😀🍀🚀🍺');

-- SELECT
SELECT test FROM tbl_test2;
????????????????

-- 文字コード
SHOW VARIABLES LIKE '%char%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | utf8                       |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | utf8                       |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+

原因

MYSQL(MariaDB)のUTF-8コードでは、4バイト文字列を使えないため。

UTF-8の4バイト文字列は、下記のような絵文字です。
😀🍀🚀🍺

対応方法

(1)4バイト文字を削除する。

$SNS_TEXT = preg_replace('/[\xF0-\xF7][\x80-\xBF][\x80-\xBF][\x80-\xBF]/', '', $SNS_TEXT);

(2)MySQLテーブルの文字コードをUTF8MB4に変更する。

●MySQLの設定ファイルを変更

vi /etc/my.cnf

[mysqld]
character-set-server=utf8

[client]
default-character-set = utf8

[mysqldump]
default-character-set = utf8

[mysql]
default-character-set = utf8
#再起動
systemctl restart mariadb.service

●既存データベースの文字コードを変更

-- データベースを変更
ALTER DATABASE データベース名 CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

-- テーブルを新規作成する場合
CREATE TABLE テーブル名 (
  カラム名 TEXT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 既存テーブルの文字コードを変更する場合
ALTER TABLE テーブル名 DEFAULT CHARACTER SET utf8mb4;

-- 既存テーブルのカラムの文字コードを変更
ALTER TABLE テーブル名 MODIFY カラム名 型 CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

●修正後

-- 文字コード
SHOW VARIABLES LIKE '%char%';
+--------------------------+----------------------------+
| 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/ |
+--------------------------+----------------------------+

-- テーブル
+-----------+----------------------------+
| Table     | Create Table               |
+-----------+----------------------------+
| tbl_test2 | CREATE TABLE `tbl_test2` (
  `test` text CHARACTER SET utf8mb4
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4  |
+-----------+----------------------------+

-- INSERT
INSERT INTO tbl_test2 (test)VALUES('😀🍀🚀🍺');

-- SELECT
SELECT test FROM tbl_test2;
😀🍀🚀🍺

絵文字の登録が出来ました。

絵文字などのUTF-8の4バイト文字をMySQL(MariaDB)に登録する場合は、文字コードをUTF8MB4にすることをおすすめします。

参考サイト

https://www.softel.co.jp/blogs/tech/archives/596
https://nekonenene.hatenablog.com/entry/2016/08/02/125424