最近、MySQLのデータベースを新しいサーバに移行させるという仕事がありまして、SQL文にエクスポートをしたファイルを新しいサーバにインポートしようとしたのですが、その時に発生したことをメモしておきたいと思います。

なんとなくこの辺りに問題があるのかなと予測がつくようなものではありますが、念の為。
 

いきなり CREATE TABLE でエラー
TYPE＝MyISAMでNG

バージョンが新しいMySQLでは、

TYPE=MyISAM → ENGINE=MyISAM
TYPE=InnoDB → ENGINE=InnoDB

とのことでした。

エクスポートしたSQLを一括置換でOK。
 

Duplicate entry hogehoge for key 'hogehoge'
みたいなアラートが出て、インポートが途中で止まる

Duplicate entryということなので、重複エラーということがわかりますが、元々前のデータベースで稼働していたので、重複するはずがないですよね。新しいサーバのデータベースはまっさらの状態なので、データは存在していませんし。

で、とりあえずエラーの発生したテーブルを見てみました。それは、同じ言葉でも半角と全角などが混ざることで書き方の違うテキストを統一させるための名寄せに使っていたテーブルでした。

hair、Ｈａｉｒ、ｈａｉｒとか、いろんな書き方ができますが、全部同じ意味ですよね。そこで、あああ～って来ました。

そうなんです。新しく作成したデータベースの方は半角全角などの違いを区別しない状態、以前のデータベースは区別していた状態だったんです。

create database hogehoge
character set utf8mb4
collate utf8mb4_bin;

とデータベースを作成し直しました。

collate utf8mb4_general_ci
A、a→区別しない
絵文字→区別しない
は、ぱ、ば→区別する
よ、ょ→区別する

collate utf8mb4_unicode_ci
A、a→区別しない
絵文字→区別しない
は、ぱ、ば→区別しない
よ、ょ→区別しない

collate utf8mb4_bin
A、a→区別する
絵文字→区別する
は、ぱ、ば→区別する
よ、ょ→区別する

みたいな違いがあるんですよね。要メモです。
 

インポート時DATE型のカラムでなんやらエラーが発生

バージョンが新しいMySQLでは、サポートしている範囲は '1000-01-01' から '9999-12-31' です、とあります。
前のMySQLでは、DATE型のカラムにデフォルト値「0000-00-00 00:00:00」が設定されていて、その値が入っているデータがいくつかありまして、それでエラーが発生していたようでした。
削除されているデータだったり、日付を変更しても内容に問題ないデータだったので、1000-01-01 00:00:00に置換してインポートしました。

 

とりあえずデータの移行は大きなトラブルなく終わレそうです。ただ、プログラム側のPHP7→PHP8への移行がまだ残っているのだ・・・。それについては、また後日。

インスタグラムの投稿をMySQLに流し込み、それをPHPを通してWebサイトに掲載する。
よくある話ですが、絵文字がうまく反映されない不具合がありましたので、それの対応をメモ。

ちなみに、MySQL5.7.39とPHP5.6.40でしたが、最新のバージョンでも流れは変わらないと思います。

 

データベースのカラムの文字コードに注意

「UTF8」だと絵文字を登録するのには不充分なので、「UTF8mb4」に変更すること。

僕は、MySQLでテーブルの定義情報を確認するのに、入力する文字数が少なくて済む（笑）ので

DESC テーブル名;

をよく使うのですが、これだと文字コードがわからないので、

show full columns from テーブル名;

で確認しましょう。

余談ですが、

show create table テーブル名;

を使うと、テーブルのCREATE文が生成できます。

で、カラムのCollationを確認して、「utf8_general_ci」だと絵文字が化けちゃいますので、「utf8mb4_general_ci」に変更します。

データベースの文字コードをまとめて変更したりできるのですが、とりあえず問題なく動作している環境をドサッと丸ごと変更するのはためらわれるので、今回は関連するカラムのみ変更します。

カラムの文字コードを変更するには、

ALTER TABLE テーブル名
MODIFY カラム名 属性 (文字列長)
CHARACTER SET 文字コード
COLLATE 照合順序;

を使います。

ALTER TABLE blog_table MODIFY
blog_text longtext
CHARACTER SET utf8mb4
COLLATE utf8mb4_general_ci;

上記のような感じですね。
データベース側で絵文字を格納する準備ができました。

後は、インスタグラムのAPIから取得したテキストをそのカラムに向けてINSERTしてもらえればOKです。

 

PHP側の対応

さて、データベースにアクセスして読み取り出力するPHP側ですが、こちらもutf8mb4で処理してもらう必要があります。
ということで、データベースにアクセスしたら、第一に

SET NAMES 'utf8mb4'

を実行。
以上で問題なく表示されました。

ちなみに僕はMacのターミナルでSQLを実行したりするのですが、MySQLに接続するときに

./mysql -u ユーザー名 データベース名 --default-character-set=utf8mb4;

とデフォルトの文字コードを指定すると、文字がきちんと表示されます。
utf8mb4ではなくutf8で接続してる可能性がありますので、ご確認ください。