POSTED BY
2024-04-30

１、テーブル定義文は何個あっても大した量じゃないはずなので手動で書き直す。主な違いは以下。

★MySQLデフォルトはデータ値の大文字小文字の区別をしない。
これを忘れてユニークカラムを作ると運用時エラーになってしまう。
実際、ドコモケータイユーザのUIDを保存する際、昔のUIDは短いので、大文字小文字を区別しなかったら結構な頻度でカブる。UIDには当然ユニーク制約をかけるのでエラー。

大文字小文字を区別させたいカラムには BINARY 句を付与する。

id VARCHAR(32) BINARY PRIMARY KEY,
name VARCHAR(64) BINARY UNIQUE KEY,
data TEXT BINARY, -- UNIQUE系のキーは付与できない。

★CREATE TABLE定義最後にパラメータ付与
何も指定しなければデフォルト設定が入るので問題ないが、一応

CREATE TABLE (...) ENGINE=InnoDB  DEFAULT CHARSET=utf8mb4;

などとしておく。

★TEXT型にUNIQUEキーは指定できない。VARCHAR型に書き直すこと。

普通のインデックスは張ることはできるが、PostgreSQLのようにそのまま張ろうとすると（data TEXT,があったとして）

CREATE INDEX i_data on datarec (data);
ERROR 1071 (42000): Specified key was too long; max key length is 3072 bytes

エラーになる。これはデフォルトだとそのカラムの取りうるＭＡＸ長まで張ろうとするためで（TEXT型は数万バイトまで入る）、
エラー文にあるように、インデックス長は3072バイトまでと怒られエラー。なのでMySQLはインデックス長を指定できるようになっている。

CREATE INDEX i_data on datarec (data(512));
Query OK, 0 rows affected (14.75 sec)
Records: 0  Duplicates: 0  Warnings: 0

などと、先頭512バイトまでのインデックス、とすればTEXT型はVARCHAR(1024)など長いカラムにもインデックスが張れる。が、MySQLはこのようなノーマルINDEX＋LIKE検索は遅くて使い物にならない。レコードが30万件ほどあったとして、上記にこんな検索かけると、

SELECT data FROM datarec WHERE data LIKE '%ローソン%';

平気で数分結果が返ってこない。
よってMySQLではフルテキストインデックスをBOOLEAN MODEで使う。詳しくはこちら。

★TEXT型はMEDIUMTEXTとLONGTEXT型がある。INSERTでtoo longエラーが出た場合どちらかに書き直す。

★フィールド名に「key」は指定できない。別の名前に変更する。

key TEXT NOT NULL #エラー
ekey TEXT NOT NULL #OK

★TIMESTAMP,DATEの扱いに大きな差あり。

TIMESTAMP NOT NULL DEFAULT 'now()'

はエラー。

TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP

と書き直す。更新時に自動で更新させたいなら

TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

また、NULLを許容するなら明示的にそれを書かなくてはいけない。

TIMESTAMP NULL DEFAULT NULL

★SEQUENCE型は無い。

number PRIMARY KEY DEFAULT NEXTVAL('my_sequence')

はエラー。

number SERIAL PRIMARY KEY

と、シリアル型を直接指定する。

★OFFSET N LIMIT N はエラー。LIMIT N OFFSET N の順番でなければいけない。

２、PostgreSQLの実データをINSERT形式でダンプする。

pg_dump --data-only --no-owner --no-privileges --disable-dollar-quoting --column-inserts pgtestdb > pgtestdb.dmp

３、文字コードが違うなら変換する（同じな場合不要）
例）UTF-8へ変換

apt install nkf
nkf -wx pgtestdb.dmp > pgtestdb_utf.dmp

４、INSERT文のみをMySQL用に変換抜き出し（ここがキモ）
・ダンプファイル先頭数行にPostgreSQL専用文あり、INSERT文のみgrep
・public.という接頭辞はいらない、削除する。
・VALUESの前のフィールド名列挙を削除（フィールド名変更した場合必須）。

grep "INSERT INTO public." pgtestdb_utf.dmp | sed 's/ public.\(.*\) (.*) VALUES / \1 VALUES /g' > mytestdb_utf.dmp

５、これでテーブル定義、変換後データの出来上がり。

create database mytestdb;
mysql -D mytestdb