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

 

尚、よくみかける[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全体とは違う文字コードのテーブルを作成することが可能となる。

 

 

 

 

 

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

 

 

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

 

 

 

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

 

 

 

 

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

 

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

 

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

 

 

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

 

 

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

 

 

 

 

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

 

 

 

 

 

以下補足情報↓

 

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

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

 

 

 

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

 

 

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

 

 

 

 

 

 

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

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

 

 

 

上記を記載の上、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