文字コードエラーについての処理情報は多いが、根本的解決に至る情報が少なかったため、簡単にまとめて備忘録としておく

 

尚、よくみかける[show variables like ‘%chat%’]ではテーブルやデータベース毎の文字コードの使い分けに対応しきれない為、今回は文字コードの使い分けまで自由にコントロールできる情報内容とする

 

 

 

■発生問題

mysqlのテーブルへデータをインポートしようとしたときに

Invalid utf8mb4 character string というエラーが発生。インポートができない問題。

 

文字化けするにしてもインポート出来て良いはずだが、何が問題であったのか不明な状態。

 

 

 

■DB文字コード状況

mysql :utf8mb4

database:utf8mb4

table:utf8mb4

データ:sjis

 

 

 

■原因

データのSJISがutf8mb4環境で文字化けして、utf8mb4が認識しない謎の文字列が発生していたためのエラー

 

全ての文字コードを揃える必要が一時的に必要である

 

 

 

 

■解決方法

データベース内のテーブル毎に文字コードが違う場合はテーブル作成時とインポート時に文字設定SQLを少し記述することで問題解決を図ることができる

 

 

 

まず、文字コード設定の強い順番のイメージは

テーブル単位>データベース単位>DB全体

 

データベースやテーブルの文字コード整合性が取れることを少し意識すれば自由に文字コード設定をコントロール可能となる。以下にその手順を記す(1−1,1−2まででokです。2以降は補足情報)

 

 

 

1−1,テーブル単位の文字コード設定について

 

テーブル作成時の最終()の後に「DEFAULT CHARSET=文字コード;」を加えることで、データベースやDB全体とは違う文字コードのテーブルを作成することが可能となる。

 

 

CREATE TABLE IF NOT EXISTS `test_table` (
xxx text,
yyyy integer
) 
DEFAULT CHARSET=utf8;

 

 

 

DBはutf8でテーブルはsjisの場合、テーブルをsjisで作成し、sjisでインポートすることで文字コードの反映された結果テーブルが作成できる。

 

 

尚、テーブルの文字コード確認コードは以下のコードにて確認可能

show table status from test;

 

 

 

しかし、データインポート時(load data local infile等)にデータベースの文字設定とテーブルの文字コード設定が違うとエラーが出てしまうため、少しインポートにSQL記述が必要となる。その詳細について、下記1−2で説明する

 

 

 

 

1−2、データインポート時のデータベースとの整合性について

 

データベースがutf8、テーブルがsjis、インポートファイルもsjisの場合、テーブルがsjisだからといってそのままでは上手くインポートできない。

 

そこで、インポートSQLに1行だけ一時的にデータベースの文字コードを変更するコードを記述する必要がある。それが以下である

 

 

#以下記載でShift_JISのファイルが取り込み可能※一時的なデータベース文字コード変更

set character_set_database=sjis;

 

 

#以下通常のデータインポート方法

load data infile "File_name"

into table test fields terminated by,,,,,,

 

 

 

 

つまるところ、データベースを一時的に文字コード変更して、テーブルとファイルの文字コードさえ合っていれば、大方問題なく文字コードを自由にコントロール可能となる

 

 

 

 

 

以下補足情報↓

 

2,データベース単位の文字設定について

データベース毎の作成時に文字コードを指定できるので合わせて紹介しておく

 

CREATE DATABASE  `db_name` DEFAULT CHARACTER SET 文字コード ;

 

 

特定の文字コードしか使用しない場合は設定しておけばテーブルへのインポート時に1−1の記述が必要なくなる

 

 

また、データベースの文字コードは下記コードで確認可能である。データインポート時に設定した文字コードは一時的変更であるため、インポート終了後には元の文字コードに戻っている。確認したくなった場合によく使う

 

SHOW CREATE DATABASE データベース名

 

 

 

 

 

3,Mysql全体の文字設定について

一番良く見かける文字コード解決方法であるvariables or my.cnfの修正による対応

 

mysql> show variables like 'character\_set\_%';

+--------------------------+--------+

| 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   |







これらの文字コードをmy.cnfの編集にて

[client]

character_set_client = sjis

/

/

/




[mysqld]

character_set_database = sjis

/

/

/

 

 

上記を記載の上、systemctl restart mysql等にてデータベースを再起動すればDB全体の文字kジョー度が変更されるというもの。全体の変更となってしまう点に注意。

 

my.cnfの文字コード設定について詳細はコチラを参照ください↓

https://proengineer.internous.co.jp/content/columnfeature/6653

 

 

 

上記解決方法はよく見かける情報であり、文字コードが画一的であれば十分な永続的解決方法ではある。

しかし、文字コードがテーブルごと、データベースごとに違う場合があるようなDB構築ではエラーが発生し続ける問題がある

 

 

 

 

 

 

参考URL:

http://pentan.info/sql/mysql/character_set.html

https://www.softel.co.jp/blogs/tech/archives/1470

https://www.softel.co.jp/blogs/tech/archives/2293

http://mysql.javarou.com/dat/000573.html

https://qiita.com/yukiyoshimura/items/d44a98021608c8f8a52a

https://qiita.com/kazu56/items/6af85ffcf8d3954455ad