PHPでSQLiteからMySQLへテーブル構造を変換しデータも移行する例

PHP

2022/02/22 (更新日：2022/02/22)

先日投稿した「SQLiteとMySQLにおける一括更新と逐次更新でパフォーマンス比較をしてみる」に関連して、一部のサイトで実験的に並列更新部分をMySQLへ移行しています。

数日試験運用した限りでは特に問題なさそうなので、今度はほとんどのデータベースをSQLiteからMySQLへ移行しようと画策中なのですが、「SQLite MySQL データ移行」と検索すると手動でDUMPしてアレコレする解説ばかりがヒットします。

でも稼働中のサイトやシステムでDBを移行する場合って、そんな手動でたらたらやってる時間ってありますかね？

そんな長時間Webサイトを止めてられませんし、たぶん手動でやるとしてもせいぜいスキーマだけ移行しておいて、データについては即座にコピーできるようにするとか、なんだったら完全移行前に1時間に1回ごとにデータの同期処理を実施しておいて、本番移行時に差分だけコピーして即メンテ完了！とするのが一般的ではないかなーと思います。

というわけで、何か参考になるPHPスクリプトでもないかなと「SQLite MySQL データ移行 PHP」などとググっていたら、3年ほど前に書いた自分のブログ記事がヒットしてしまいました…。

https://blog.ver001.com/php-mysql-sqlite-convert/

そいやWordPressが遅くてたまらないのでMySQLからSQLiteへデータ変換して、自前でブログシステム作ろうとしてたことありましたね。結局少しお高いレンタルサーバーに切り替えたら案外快適になったのでそのままなのですけども。

いやそんな話はどうでも良くて、今欲しいのは MySQL→SQLite じゃなくて SQLite→MySQL なんですわ…。

PHPでSQLiteからMySQLへテーブル構造／データ／インデックスを一括変換する

というわけで、探している時間で作れそうな気がしたので、サクっと変換スクリプトを書いてみました。

残念ながらあまり汎用性はありません。なぜならSQLiteのデータ型はTEXT/INTEGER/REAL/BLOBしかないから！

一応DATETIMEとかも使えますけど、内部的にはテキストとして扱われてますしね。ともかく、その関係でデータ型そのまんまでMySQLへ持ってくるとキー長の問題などで、うまくいきません。そのあたりはプライマリキーかどうか、とか項目名で判断するという強引な手法を取っています。なので、コードを参照してご自身のシステムに合わせて書き換える必要があるでしょう。

やってることは単純なワリに少々長いですが、PHPスクリプトの全文をどうぞ。

<?php
$cfg['PDO_DSN_SQLITE'] = 'sqlite:test.db';
$cfg['PDO_DSN_MYSQL'] = 'mysql:host=localhost;charset=utf8mb4;dbname=test;';
$cfg['PDO_DSN_USER'] = '[MySQLユーザー]';
$cfg['PDO_DSN_PASS'] = '[MySQLパスワード]';
$cfg['PDO_OPTION'] = array(PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC, PDO::ATTR_TIMEOUT => 20);
set_time_limit(600);

$db_sqlite = new PDO($cfg['PDO_DSN_SQLITE'], '', '', $cfg['PDO_OPTION']);
$db_mysql = new PDO($cfg['PDO_DSN_MYSQL'], $cfg['PDO_USER'], $cfg['PDO_PASS'], $cfg['PDO_OPTION']);

//MySQLのトランザクション開始
$db_mysql->beginTransaction();

//テーブル一覧を取得
$st = $db_sqlite->query("SELECT name FROM sqlite_master WHERE type='table'");
while ($row = $st->fetch()) {
	$db_mysql->query("DROP TABLE IF EXISTS ".$row['name']);
	//CREATE TABLE文を生成
	$query = convertTable($db_sqlite, $db_mysql, $row['name']);
	//データのINSERT処理
	convertData($db_sqlite, $db_mysql, $row['name']);
}
//インデックスのコンバート処理
convertIndex($db_sqlite, $db_mysql);
//
$db_mysql->commit();

/************************************************
 * テーブルのコンバート処理 SQLite→MySQL
 *
 ************************************************/
function convertTable($db_sqlite, $db_mysql, $table_name)
{
	$query_create = "CREATE TABLE ".$table_name." (\n";

	$st = $db_sqlite->query('PRAGMA TABLE_INFO('.$table_name.')');
	$rows = $st->fetchAll();

	//PRIMARY KEYの数をチェックするために1回ループしておく
	$query_pk = '';
	$pk = 0;
	foreach ($rows as $i => $row) {
		if ($row['pk'] == 1) {
			$query_pk = ",\nPRIMARY KEY (".$row['name'];
			$pk++;
		} else if ($row['pk'] >= 2) {
			$query_pk .= ",".$row['name'];
			$pk++;
		}
	}
	//あらためて各項目でCREATE TABLE文を作っていく
	foreach ($rows as $i => $row) {
		if ($i > 0) {
			$query_create .= ",\n";
		}
		$query_create .= $row['name']; //項目名
		$row['type'] = strtoupper($row['type']); //データ型は大文字にする

		if ($row['type'] == 'INTEGER') {
			if ($row['pk'] == 1 && $pk == 1) {
				//PRIMARY KEYが1つしか存在せず、かつINTEGERだった場合はSERIAL型（連番）と判断する
				$row['type'] = 'SERIAL';
			} else if ($row['name'] == 'ip') {
				//IPアドレスの格納にはINTEGERだと足りないためUNSIGNEDに変更する
				$row['type'] = 'INT UNSIGNED';
			}
		} else if ($row['type'] == 'TEXT') {
			switch (strtoupper($row['name'])) {
				case 'COMMENT':
				case 'REMARK':
					//項目名がCOMMENTやREMARKの場合は長文を格納する可能性があるためTEXT型のまま
					break;
				default:
					//それ以外のTEXTはキーとして使われる可能性を考え、VARCHARとする
					$row['type'] = 'VARCHAR(255)';
			}
		}
		$query_create .= ' '.$row['type'];
	}
	if ($query_pk != '') {
		$query_create .= $query_pk.")";
	}
	$query_create .= ');';

	echo '<p><textarea>'.$query_create.'</textarea></p>';
	try {
		$db_mysql->query($query_create);
	} catch (PDOException $e) {
		echo "<textarea>".$query_create."</textarea>";
		echo $e;
		exit();
	}
}
/************************************************
 * データのコンバート処理 SQLite→MySQL
 *
 ************************************************/
function convertData($db_sqlite, $db_mysql, $table_name)
{
	//INSERTクエリーの準備
	$query = 'INSERT INTO '.$table_name.' (';
	$st = $db_sqlite->query('PRAGMA TABLE_INFO('.$table_name.')');
	$columns = $st->fetchAll();
	$types = array();
	foreach ($columns as $i => $column) {
		if ($i > 0) $query .= ',';
		$query .= $column['name'];
		$types[$column['name']] = strtoupper($column['type']);
	}
	$query .= ') VALUES (';
	$query .= substr(str_repeat(',?', $i + 1), 1);
	$query .= ')';
	//
	echo '<textarea>'.$query.'</textarea>';
	$st = $db_mysql->prepare($query);
	$rows = $db_sqlite->query('SELECT * FROM '.$table_name);
	foreach ($rows as $i => $row) {
		try {
			$rowInsert = array();
			foreach ($row as $key=>$value) {
				switch ($types[$key]) {
					case 'REAL':
					case 'INTEGER':
						if ($value == '') {
							$value = null;
						}
				}
				$rowInsert[] = $value;
			}
			$st->execute($rowInsert);
		} catch (PDOException $e) {
			echo "<textarea>".serialize($row)."</textarea>";
			echo $e;
			exit();
		}
	}
}
/************************************************
 * INDEXのコンバート処理
 *
 ************************************************/
function convertIndex($db_sqlite, $db_mysql)
{
	$st = $db_sqlite->query("SELECT * FROM sqlite_master WHERE type='index' AND sql IS NOT NULL");
	while ($row = $st->fetch()) {
		//シングルクォートとダブルクォートを削除する
		$query = str_replace('"', '', $row['sql']);
		$query = str_replace("'", '', $query);
		echo '<textarea>'.$query.'</textarea>';
		$db_mysql->query($query);
	}
}
?>

解説

PHPスクリプトの目的は単純で、指定したSQLiteからMySQLへテーブル構造、データ、インデックスをコピーすることです。

わざわざスクリプト化するのは、既に稼働中のシステムをなるべく止めずにDBを移行したいから。SQLiteをDUMPして～書き換えて～実行して～などと手動でやっていては時間がかかりますし、人間がやる以上ミスも発生します。それならスクリプト化しておいて事前にテストを済ませ、本番移行時には1発実行で完了したいものですよね。

SQLiteの構造について

SQLiteのテーブル構造やインデックス構造については sqlite_master テーブルに格納されています。そのため、PHPスクリプトでは SELECT name FROM sqlite_master WHERE type='table' でテーブル一覧を、SELECT * FROM sqlite_master WHERE type='index' でインデックスの一覧を取得しています。

また、テーブル内の列名やプライマリーキーについては PRAGMA TABLE_INFO([テーブル名]) で取得できます。

SQLiteとMySQLの連番対応

SQLiteは様々なことが簡略化されており、自動採番についても「テーブル内のPRIMARY KEYが1つで、かつそれがINTEGERだったら自動採番の項目である」と判断されます。

MySQLでいうところのAUTO_INCREMENTが指定された状態になるわけですね。

となると、SQLiteからMySQLへデータ移行する場合もコレを反映させてあげなければなりません。

そのため、少々泥臭いですが、PHPスクリプト中で「PRIMARY KEYの数をチェックするために1回ループしておく」とあるようにテーブル内のPRIMARY KEYの数を数えています。

このチェックにより、PRIMARY KEYが1個であり、かつINTEGER型だった場合には、MySQLへ持ってくるときには SERIAL 型に変更しています。[列名] NOT NULL AUTO_INCREMENTといった昔ながらの書き方をしても良かったのですが、SERIAL型ならMySQLでもPostgreSQLでも自動採番扱いになるため、こちらを採用しました。

TEXT型の扱い

SQLiteでVARCHARの指定はできますが、それに意味はなく、内部的にはTEXT型になります。そのため、文字列が入る項目はすべてTEXT型としていましたが、これをMySQLへ持ってくるとなるとちょっと考えないといけません。

先日投稿した「MySQLのTEXT型とVARCHAR型の速度比較」にあるとおり、パフォーマンス面を気にする必要はほとんどありません。全部TEXT型でも良いくらいです。

ただ、MySQLのTEXT型の場合、インデックス長を指定しないとキーが張れないという制約があります。そのため、TEXT型をVARCHARにするか、それともPRIMARY KEY設定のところをTEXT(255)みたいに書き換えるかで悩んだのですが、後者だとPRIMARY KEYとは別のインデックスでもキー長の指定をしなければならないので面倒くせーな…と。

そんなわけで、こんな強引な手法を取っています。

switch (strtoupper($row['name'])) {
	case 'COMMENT':
	case 'REMARK':
		//項目名がCOMMENTやREMARKの場合は長文を格納する可能性があるためTEXT型のまま
		break;
	default:
		//それ以外のTEXTはキーとして使われる可能性を考え、VARCHARとする
		$row['type'] = 'VARCHAR(255)';
}

TEXT型の項目名がCOMMENTやREMARKならそのままMySQLへ持ってきて、それ以外はVARCHARとするわけですね…。

うちのシステムでは備考欄(=REMARK)のような項目は数千文字入っているケースもあったのでTEXT型にするのは必須要件でした。

ちなみに困ったのがURLの入力欄です。Web上で入力できるURL欄ですが、VARCHAR(255)で良いだろうと思っていたら、最大で1800文字くらいのURLが存在しました。…正直びっくり。ほら、AmazonやAliExpressなどのECサイトではSEO対策を狙ってか、URLに商品名そのまんま持ってきたりするじゃないですか。売主も必死だからその商品名を1文字でも長くして、ユーザーの検索結果にひっかかるようにしたいし。そういった複数の思惑が重なってアホみたいに長いURLがあるのでした。

上述のスクリプトはあくまでサンプルなので COMMENT と REMARK だけTEXT型にしていますが、実運用ではこのURLのような例も含めてけっこう設定する必要があるかと思います。

ちなみに「じゃあVARCHAR(8000)とかにすればええやん」と思うかも知れませんが、それはそれで今度はキー長の最大が3072バイト（＝utf8mb4だと768文字）という制限にひっかかるため、うまくありません。SQLiteよりも色々制約あって面倒くせぇなぁ、と思いつつも色々歴史的経緯もあるので仕方ない…仕方ない…。

INTEGER型の扱い

SQLiteのINTEGERの最大値は9,223,372,036,854,775,807です。…数えられない？　約922京と覚えれば良いと思います。64ビットの最大値なのでプログラマーなら覚えておいて損なし。最小値は-922京。

一方でMySQLのINTEGERの最大値は2,147,483,647。約21億。32ビットの最大値なのでおっさんプログラマー的にはこちらのほうが馴染み深いかな？

ほいで、WebサイトのシステムではIPアドレスを記録することがよくあると思うんですけど、うちのサイトも例にもれずIPを記録しています。ただ、文字列で格納していると何十万、何百万レコードとなるとデータ量も膨大になるためLONG値で記録していたんですね。IPアドレスって文字で書くと（人間にわかりやすいように） 192.168.0.1 という表記をしますが、これ実際（コンピューター的）には 3232235521 という数値です。

IPアドレスを文字列で表現すると15バイト。utf8mb4で格納しようもんなら4倍の60バイト必要です。一方、数値で格納すれば32ビットだから4バイト。データ量が15分の1ですよ、奥さん！

まぁそんなわけでINTEGER型で格納していたわけなんですけど、MySQLだとINTEGERは符号付きなので-21億～+21億までしか格納できず、符号のないIPアドレスでは最大約42億までの数値が必要なので、桁あふれを起こします。

} else if ($row['name'] == 'ip') {
	//IPアドレスの格納にはINTEGERだと足りないためUNSIGNEDに変更する
	$row['type'] = 'INT UNSIGNED';
}

そのため、このようにINT UNSIGNEDへ変更しています。システムによっては問答無用で全てINT UNSIGNEDにしても良いでしょうし、21億以上の数値を扱うシステムではBIGINTにしても良いでしょう。

データコンバート時の微調整

SQLiteはとても自由度が高く、データ型なんて無視してほとんどどんなデータでも格納してしまえるのですが、その分、プログラム側のチェックが甘いと数値型の項目に文字が入っていたりします。うちのサイトでも同様の現象があり、数値項目に "" （長さ0の文字）が入っているレコードがありました。

そのため、これまた泥臭いですが、データ型がINTEGERまたはREALに限り、長さ0の文字列が入っていた場合にnullへ変換する処理を入れてあります。

$rowInsert = array();
foreach ($row as $key=>$value) {
	switch ($types[$key]) {
		case 'REAL':
		case 'INTEGER':
			if ($value == '') {
				$value = null;
			}
	}
	$rowInsert[] = $value;
}
$st->execute($rowInsert);

うちでは数値項目はINTEGERかREALだけでしたが、場合によっては別の型名を使っているところもあるでしょうから、その場合は随時変更が必要です。

インデックスのコンバートはほとんどしない

SQLiteの構造データを調べる際、sqlite_masterテーブルを見ていると書きましたが、この中身がけっこう雑なんですよね。

テーブルや項目のデータはまだマシなんですが、インデックスに至ってはインデックス名とCREATE INDEX文が入っているだけなんですよ。

仕方ないので、そのCREATE INDEX文をそのままMySQLへ投げています。

あ、いや、厳密にはシングルクォートとダブルクォートだけは消していますけども。…それだけ。複雑なインデックスを作っていたらエラーになるかも。

まぁ、ほら、データ移行においてインデックスはオマケというか…。そこは移行が終わった後でもチューニングできるからだいたいで良いかな、って…。

まとめ

SQLiteからMySQLへ移行するにあたって、何か良さげなツールはないものかなーと軽く探してみたものの、見つからなかったので自分で作ってみました。

パッと見でわかるとおり、自分が使いやすいようにしか作っていませんのであまり参考になるかはわかりませんが、一応ブログ記事にしてみた感じです。

あとで自分で検索したときにブログ記事がヒットするかも知れないしね！

PHPでSQLiteからMySQLへテーブル構造を変換しデータも移行する例

PHPでSQLiteからMySQLへテーブル構造／データ／インデックスを一括変換する

解説

SQLiteの構造について

SQLiteとMySQLの連番対応

TEXT型の扱い

INTEGER型の扱い

データコンバート時の微調整

インデックスのコンバートはほとんどしない

まとめ

関連記事

PHPでCookieを保持しつつWebスクレイピングする例

INSERT文を一括処理して5万件のデータ追加を12倍高速化する

PHPのjson_encodeで空白が返ってきてしまう場合のエラー確認方法

MySQLのTEXT型とVARCHAR型の速度比較

SQLiteとMySQLにおける一括更新と逐次更新でパフォーマンス比較をしてみる

PHPでの自動ログイン処理とセッションロックの備忘録

コメント

新しいコメントを投稿する