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

2019/4/28 PHP, プログラミング

MySQLからSQLite、あるいはその逆でも良いのですが、DBの移行作業というとDUMPデータを取って、そのSQL文を修正して…という例がよく出てきます。

しかし、テーブルや項目数が多いと手作業でSQL文を修正するのは面倒ですし、プログラムでSQL文を自動変換するにしても文字操作はちょっとしたバージョンアップで動かなくなることも多いので、PHPでDBへ接続して移行するプログラムを作ってみました。

PHPでMySQLのテーブルをSQLiteへ変換するサンプル

<?php
/******************************************************************************
 * MySQL->SQLite変換
 *
 ******************************************************************************/
//MySQL接続情報
$cfg['MYSQL_DSN'] = 'mysql:host=localhost;dbname=[MySQLデータベース名];';
$cfg['MYSQL_USER'] = '[MySQLユーザー名]';
$cfg['MYSQL_PASS'] = '[MySQLパスワード]';
//SQLiteデータベース
$cfg['SQLITE_DSN'] = 'sqlite:[SQLiteデータベース名]';
//変換するテーブル名
$cfg['TABLE_NAME'] = '[テーブル名]';

//MySQLへ接続
$pdo_mysql = new PDO($cfg['MYSQL_DSN'], $cfg['MYSQL_USER'], $cfg['MYSQL_PASS'], array(
	PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
	PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
	PDO::ATTR_EMULATE_PREPARES => true));

//SQLiteへ接続
$pdo_sqlite = new PDO($cfg['SQLITE_DSN'], '', '',  array(
	PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
	PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
	PDO::ATTR_EMULATE_PREPARES => true));

//CREATE TABLE文を生成
$query = genCreateQuery($pdo_mysql, $cfg['TABLE_NAME']);
echo "CREATE TABLE文<br><textarea>".$query."</textarea>";
//既にテーブルが存在した場合は削除する
$pdo_sqlite->query("DROP TABLE IF EXISTS ".$cfg['TABLE_NAME']);
//SQLiteにテーブル作成
$pdo_sqlite->query($query);

//INSERT文を生成
$query = genInsertQuery($pdo_mysql, $cfg['TABLE_NAME']);
echo "<br>INSERT文<br><textarea>".htmlentities($query)."</textarea>";
//SQLiteでINSERT文を実行
$pdo_sqlite->query($query);

/************************************************
 * MySQLのテーブル情報からSQLite用のCREATE TABLEを作成
 *
 ************************************************/
function genCreateQuery($pdo_mysql, $table_name)
{
	//SHOW COLUMNS文でテーブルのフィールド情報を取得
	$rows = $pdo_mysql->query('SHOW COLUMNS FROM '.$table_name);
	//SQLite用のCREATE TABLE文を生成
	$query_create = "CREATE TABLE ".$table_name." (\n";
	foreach ($rows as $i => $row) {
		if ($i > 0) {
			$query_create .= ",\n";
		}
		$query_create .= $row['Field'];
		//MySQLの型名をSQLiteに合わせる
		$query_create .= ' '.convert_type($row['Type']);
		//auto_increment対応
		if ($row['Extra'] == 'auto_increment') {
			$query_create .= ' primary key autoincrement';
		}
	}
	$query_create .= ');';
	return $query_create;
}
/************************************************
 * MySQLの型名をSQLiteに変換
 *
 ************************************************/
function convert_type($type_str)
{
	$types['/bigint(.*)/'] = 'integer';
	$types['/int(.*)/'] = 'integer';
	$types['/tinyint(.*)/'] = 'integer';
	$types['/smallint(.*)/'] = 'integer';
	$types['/mediumint(.*)/'] = 'integer';
	$types['/varchar(.*)/'] = 'text';
	$types['/character(.*)/'] = 'text';
	foreach ($types as $pattern => $replace) {
		$type_str = preg_replace($pattern, $replace, $type_str);
	}
	return $type_str;
}
/************************************************
 * MySQLのテーブルからINSERT文を生成
 *
 ************************************************/
function genInsertQuery($pdo_mysql, $table_name)
{
	$rows = $pdo_mysql->query('SELECT * FROM '.$table_name);
	$query_insert = "INSERT INTO ".$table_name." VALUES \n";
	foreach ($rows as $i => $row) {
		foreach ($row as $key => $value) {
			//シングルクォートのエスケープ処理
			$row[$key] = str_replace("'", "''", $value);
		}
		if ($i > 0) $query_insert .= ",\n";
		$query_insert .= "('".implode("','", $row)."')";
	}
	return $query_insert;
}
?>

MySQL→SQLite変換スクリプトの実行方法

PHPスクリプト上部の連想配列にMySQL接続情報、SQLite接続情報、変換するテーブル名、を指定して実行すればMySQLのテーブルをSQLiteに変換します。

SQLite上に既に同名のテーブルがあった場合は削除して作り直すようにしてあるので実行する際はご注意ください。

MySQL→SQLite変換スクリプトの解説

処理の流れとしては

  1. MySQLへ接続
  2. SQLiteへ接続
  3. MySQLでSHOW COLUMNS文を発行してテーブルのフィールド情報（フィールド名、データ型）を取得
  4. 取得したフィールド名をSQLite用に書き換え （convert_type関数）
  5. auto_incrementが設定されていた場合はSQLite用にprimary key autoincrementへ変換
  6. SQLiteへCREATE TABLE文を発行

という感じでテーブルを生成しています。

また、中身のデータに関しては特にSQLite用の変換もなく、極単純にMySQLへ発行したSELECT文で取得したデータをSQLiteへINSERT文で書き込んでいるだけです。

強いて言うなら、処理を早くするために毎行INSERT文を発行するのではなく、下記のようにINSERT文1つで複数行のデータを挿入できるようにしたくらいでしょうか。

INSERT INTO [テーブル名] VALUES ('...','...'), ('...','...'), ('...','...')......

これは別にSQLiteに限ったことではなく、MySQLも同様のSQL文で複数行のINSERTが発行できます。

今回はWordPress用に作ったわけではないのですが、wp_postsテーブルなどでテストした限りは特に問題なく移行できているようでした。

あ、インデックスまでは見ていないので、そこは貼り直しが必要ですね。

まとめ

  • MySQLのテーブル情報（フィールド名、データ型）はSHOW COLUMS文で取得可能
  • SHOW COLUMNSで取得したデータ型をSQLite用に変換（preg_replace）すればMySQLからSQLiteへのテーブル変換は簡単に出来る
  • 中身のデータに至ってはまんまSELECTで取ってINSERTで入れるだけの簡単なお仕事

といったところでしょうか。

1テーブルのみの変換だったら、わざわざスクリプトを組まずとも、DUMPしたSQL文をちょちょいと修正するだけでMySQLからSQLiteへの移行作業は終わると思いますが、今回はちょっと複数のテーブルを変換する必要があったため、軽くPHPで変換処理を組んでみた次第です。

自分用に作ったスクリプトをついでに記事にUPしてるだけなので、いろいろ抜けているところがある気もしますが（インデックスとかインデックスとかインデックスとか）何かの参考になれば幸いです。

尚、PDOについてよくわからない場合はこちらの記事もどうぞ。
PHPのPDOでMariaDB(MySQL)への接続テスト

adsbygoogle

フォロー

, @totchira