MySQLからSQLiteへデータ移行

PHP SQLite

さくらVPSサーバーからレンタルサーバー（ライトプラン）へ引っ越す必要があったのでMySQL（innodb）からSQLiteにデータを移行した時のメモ

MySQLの主なデータ型

数値型

	データ型	値の範囲
TYNYINT	整数型（1バイト）	-128～127 0～255
SMALLINT	整数型（2バイト）	-32768～32767 0～65536
MEDIUMINT	整数型（3バイト）	-8388608～8388607 0～16777215
INT	整数型（4バイト）	-2147483648～2147483647 0～4294967295
BIGINT	整数型（8バイト）	-9223372036854775808～9223372036854775807 0～18446744073709551615
FLOAT	単精度浮動小数点型（4バイト）	-3.402823466E+38～-1.175494351E-38、0 1.175494351E-38～3.402823466E+38
DOUBLE	倍精度浮動小数点型（8バイト）	-1.7976931348623157E+308～-2.2250738585072014E-308、0 2.2250738585072014E-308～1.7976931348623157E+308
DECIMAL(M,D)	固定小数点型	M：1～65 D：0～30

文字列（バイナリ）型

	データ型	値の範囲
CHAR(M)	固定長文字列型	M：0～255文字
VARCHAR(M)	可変長文字列型	M：0～65535文字
TYNYTEXT	テキスト型	0～255文字
TEXT	テキスト型	0～65535文字
MEDIUMTEXT	テキスト型	0～16777215文字
LONGTEXT	テキスト型	0～4294967295文字
TYNYBLOB	バイナリ型	0～255バイト
BLOB	バイナリ型	0～65535バイト
MEDIUMBLOB	バイナリ型	0～16777215バイト
LONGBLOB	バイナリ型	0～4294967295バイト

日付（時刻）型

	データ型	基本フォーマット「ゼロ」値
DATE	日付型（3バイト）	'YYYY-MM-DD' '0000-00-00'
DATETIME	日付時刻型（8バイト）	'YYYY-MM-DD HH:MM:SS' '0000-00-00 00:00:00'
TIMESTAMP	タイムスタンプ型（4バイト）	'YYYY-MM-DD HH:MM:SS' '0000-00-00 00:00:00'
TIME	時刻型（3バイト）	'HH:MM:SS' '00:00:00'
YEAR	年型（1バイト）	'YYYY' 0000

SQLite3のデータ型

データ型	詳細
TEXT	テキスト。エンコーディング（UTF-8、UTF-16BE、またはUTF-16LE）を使用して格納
INTEGER	符号付整数。値の大きさに応じて1、2、3、4、6、または8バイトに格納
REAL	浮動小数点数。8バイトで格納
BLOB	入力されたとおりに格納

MySQLのデータをmysqldumpでバックアップ

mysqldumpの基本の構文は、次のようになります。

mysqldump -u ユーザ名 -p データベース名 > ダンプファイル名

# mysqldump --opt --default-character-set=utf8 --skip-lock-tables --single-transaction --hex-blob -u dbuser -p webadmin > dump.sql
# mysqldump --defaults-file=${CONF_FILE} --opt --default-character-set=utf8 --skip-lock-tables --single-transaction --hex-blob -u ${DB_USER} ${DB_NAME} > ${BACKUP_DIR}/${BACKUP_FILE_NAME}.sql
# mysqldump --opt --default-character-set=utf8 --skip-lock-tables --single-transaction --hex-blob -u ユーザ -p データベース名 > dump.sql

--default-character-set

デフォルト文字セットを指定

--skip-lock-tables

lock-tablesオプションを無効にする

--single-transaction

BEGINステートメントを発行しDBをロックせずにダンプする

--hex-blob

16進表記を使用してバイナリカラムをダンプする

ダンプファイルをSQLite用に加工

必要なものだけ読み込む

//コメント
if (preg_match('#^--#', $value, $arrMatch) === 1) {
	continue;
}
//コメント
if (preg_match('#^/\*[\s\S]*\*/;$#', $value, $arrMatch) === 1) {
	continue;
}
//LOCK TABLES
if (preg_match('#^LOCK TABLES [\s\S]*;$#i', $value, $arrMatch) === 1) {
	continue;
}
//UNLOCK TABLES
if (preg_match('#^UNLOCK TABLES;$#i', $value, $arrMatch) === 1) {
	continue;
}
//SET
if (preg_match('#^SET [\s\S]*;$#i', $value, $arrMatch) === 1) {
	continue;
}
//START TRANSACTION;
if (preg_match('#^START TRANSACTION;$#i', $value, $arrMatch) === 1) {
	continue;
}
//COMMIT;
if (preg_match('#^COMMIT;$#i', $value, $arrMatch) === 1) {
	continue;
}

SQLiteで使用できないものは削除

$row = preg_replace('#\) ENGINE=[\s\S]*;$#', ');', $row);
$row = preg_replace('# COMMENT \'[\s\S]*\'#', ' ', $row);
$row = preg_replace('#\([0-9,\s]+\)#', '', $row);
$row = preg_replace('#COLLATE [a-zA-z0-9_]+#', ' ', $row);
$row = preg_replace('#CHARACTER SET [a-zA-z0-9]+#', ' ', $row);
$row = str_ireplace(' UNSIGNED', ' ', $row);
$row = str_ireplace(' ZEROFILL', ' ', $row);

データ型変換

$row = str_ireplace(array(' TINYINT',' SMALLINT',' MEDIUMINT',' BIGINT'), ' INTEGER', $row);
$row = str_ireplace(array(' BOOLEAN'), ' INTEGER', $row);
$row = str_ireplace(' INT ', ' INTEGER', $row);
$row = str_ireplace(array(' FLOAT',' DOUBLE'), ' REAL', $row);
$row = str_ireplace(array(' NUMERIC', ' DECIMAL'), ' REAL', $row);
$row = str_ireplace(array(' VARCHAR',' CHAR'), ' TEXT', $row);
$row = str_ireplace(array(' TINYTEXT',' MEDIUMTEXT',' LONGTEXT'), ' TEXT', $row);
$row = str_ireplace(array(' DATETIME',' TIMESTAMP',' DATE',' TIME',' YEAR'), ' TEXT', $row);
$row = str_ireplace(array(' TINYBLOB',' MEDIUMBLOB',' LONGBLOB', ' BINARY',' VARBINARY'), ' BLOB', $row);
$row = str_ireplace(array(' GEOMETRY'), ' BLOB', $row);

文字列のエスケープ処理

//シングルクォーテーション
$value = str_replace("\'",    "''",    $value);
//ダブルクォーテーション
$value = str_replace('\"',    '"',    $value);
//改行
$value = str_replace('\n',    "\n",    $value);

PDOを使ってSQLiteにインポート

//オプションの設定（エラーレポート => 例外を投げる）
$options = [
	PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION
];
//インスタンスを作成
$clsDb = new PDO('sqlite:sample.db', '', '', $options);

//トランザクションを開始する
$clsDb->beginTransaction();

//SQL ステートメントを実行し、結果セットを PDOStatement オブジェクトとして返す
$clsDb->query($sql)

//SQL ステートメントを実行し、作用した行数を返す;
$clsDb->exec($sql);

//トランザクションをコミットする
$clsDb->commit();

//トランザクションをロールバックする
$clsDb->rollBack();

//接続を閉じる
$clsDb = null;

使い方

サンプルソースのファイル名設定・データベース名設定を環境に合わせて修正してください。

$filename = 'dump.sql';

$filename = 'sample.db';

サンプルソース

import.php

//ファイル名を設定
$filename = 'dump.sql';
//データベース名を設定
$dbname = 'sample.db';
try {
	//ダンプファイル読込み
	$data    = readDumpFile($filename);
	//SQL加工
	$arrSQL  = array();
	$arrData = explode(";\n", $data);
	foreach ($arrData as $key=>$row) {
		if (strlen($row) === 0) {
			continue;
		}
		$row .= ';';
		if (preg_match('#^CREATE TABLE (.*?) [\s\S]*;$#i', $row, $arrMatch) === 1) {
			//CREATE TABLE
			$table = trim($arrMatch[1]);
			createTable($table, $row, $arrSQL);
		} else if (preg_match('#^DROP TABLE IF EXISTS (.*?);$#i', $row, $arrMatch) === 1) {
			//DROP TABLE
			$table = trim($arrMatch[1]);
			$arrSQL['drop'][$table][] = $row;
		} else if (preg_match('#^INSERT INTO (.*?) [\s\S]*\);$#i', $row, $arrMatch) === 1) {
			//INSERT INTO
			$table = trim($arrMatch[1]);
			//16進表記(--hex-blob)
			$row = preg_replace('#(\(|, ?)0x([a-f0-9]+)(\)|, ?)#i', "$1x'$2'$3", $row);
			//\（エスケープ）連続
			$pos = mb_strpos($row, '\\', 0, 'UTF-8');
			if ($pos !== false) {
				$rowBk   = '';
				$value   = '';
				$count   = 0;
				$arrChar = preg_split('##u', $row, -1, PREG_SPLIT_NO_EMPTY);
				foreach ($arrChar as $key2=>$row2) {
					$char = $row2;
					if ($char === '\\') {
						$value .= $char;
						$count++;
					} else {
						if (strlen($value) === 0) {
							$rowBk .= $char;
						} else {
							$value = $value.$char;
							$value = str_replace('\\\\',  '\\',    $value);
							//奇数回
							if (($count % 2) !== 0) {
								if ($char === '\'') {
									//シングルクォーテーション
									$value = str_replace("\'",    "''",    $value);
								} else if ($char === '"') {
									//ダブルクォーテーション
									$value = str_replace('\"',    '"',    $value);
								} else if ($char === 'n') {
									//改行
									$value = str_replace('\n',    "\n",    $value);
								}
							}
							$rowBk .= $value;
							$count  = 0;
							$value  = '';
						}
					}
				}
				$row = $rowBk;
			}
			$arrSQL['insert'][$table][] = $row;
		}
	}
	//DBインポート
	if ((is_array($arrSQL) === true) && (count($arrSQL) > 0)) {
		importDb($dbname, $arrSQL);
	}
} catch (Exception $e) {
	echo $e->getMessage()."\n";
}

/**
 * ダンプファイル読込み
 * 引数：ダンプファイル名
 * 戻値：読込みデータ
 */
function readDumpFile($filename) {
	if (is_file($filename) === false) {
		throw new Exception('ERROR FILE');
	}
	$fp = @fopen($filename, 'r');
	if ($fp === false) {
		throw new Exception('ERROR FOPEN');
	}
	$data = '';
	while (!feof($fp)) {
		$value = fgets($fp);
		if ($value === false) {
			break;
		}
		//コメント
		if (preg_match('#^--#', $value, $arrMatch) === 1) {
			continue;
		}
		//コメント
		if (preg_match('#^/\*[\s\S]*\*/;$#', $value, $arrMatch) === 1) {
			continue;
		}
		//LOCK TABLES
		if (preg_match('#^LOCK TABLES [\s\S]*;$#i', $value, $arrMatch) === 1) {
			continue;
		}
		//UNLOCK TABLES
		if (preg_match('#^UNLOCK TABLES;$#i', $value, $arrMatch) === 1) {
			continue;
		}
		//SET
		if (preg_match('#^SET [\s\S]*;$#i', $value, $arrMatch) === 1) {
			continue;
		}
		//START TRANSACTION;
		if (preg_match('#^START TRANSACTION;$#i', $value, $arrMatch) === 1) {
			continue;
		}
		//COMMIT;
		if (preg_match('#^COMMIT;$#i', $value, $arrMatch) === 1) {
			continue;
		}
		//改行コード変換
		$value = preg_replace("#\r\n|\r#", "\n", $value);
		if ($value === "\n") {
			continue;
		}
		//必要なものだけを取得
		$data .= $value;
	}
	fclose($fp);
	return $data;
}

/**
 * CREATE TABLE
 * 引数：テーブル名
 * 引数：ダンプデータ
 * 引数：SQL(参照渡し)
 * 戻値：なし
 */
function createTable($table, $row, &$arrSQL) {
	$primary = '';
	$auto    = '';
	//SQLiteにないものは削除
	$row = preg_replace('#\) ENGINE=[\s\S]*;$#', ');', $row);
	$row = preg_replace('# COMMENT \'[\s\S]*\'#', ' ', $row);
	$row = preg_replace('#\([0-9,\s]+\)#', '', $row);
	$row = preg_replace('#COLLATE [a-zA-z0-9_]+#', ' ', $row);
	$row = preg_replace('#CHARACTER SET [a-zA-z0-9]+#', ' ', $row);
	$row = str_ireplace(' UNSIGNED', ' ', $row);
	$row = str_ireplace(' ZEROFILL', ' ', $row);
	//データ型変換
	$row = str_ireplace(array(' TINYINT',' SMALLINT',' MEDIUMINT',' BIGINT'), ' INTEGER', $row);
	$row = str_ireplace(array(' BOOLEAN'), ' INTEGER', $row);
	$row = str_ireplace(' INT ', ' INTEGER', $row);
	$row = str_ireplace(array(' FLOAT',' DOUBLE'), ' REAL', $row);
	$row = str_ireplace(array(' NUMERIC', ' DECIMAL'), ' REAL', $row);
	$row = str_ireplace(array(' VARCHAR',' CHAR'), ' TEXT', $row);
	$row = str_ireplace(array(' TINYTEXT',' MEDIUMTEXT',' LONGTEXT'), ' TEXT', $row);
	$row = str_ireplace(array(' DATETIME',' TIMESTAMP',' DATE',' TIME',' YEAR'), ' TEXT', $row);
	$row = str_ireplace(array(' TINYBLOB',' MEDIUMBLOB',' LONGBLOB', ' BINARY',' VARBINARY'), ' BLOB', $row);
	$row = str_ireplace(array(' GEOMETRY'), ' BLOB', $row);
	//PRIMARY KEY
	if (preg_match('#PRIMARY KEY \((.*?)\)#i', $row, $arrMatch) === 1) {
		$primary = trim($arrMatch[1]);
	}
	$arrWork = explode("\n", $row);
	foreach ($arrWork as $key2=>$row2) {
		//AUTO_INCREMENT
		if (preg_match('#^\s*(.*?) [\s\S]* AUTO_INCREMENT#i', $row2, $arrMatch) === 1) {
			$auto = trim($arrMatch[1]);
			if ($primary === $auto) {
				$row2 = str_ireplace('AUTO_INCREMENT', 'PRIMARY KEY AUTOINCREMENT', $row2);
			} else {
				throw new Exception('ERROR AUTOINCREMENT *1');
			}
		}
		//PRIMARY KEY
		if (preg_match('#^\s*PRIMARY KEY \((.*?)\)#i', $row2, $arrMatch) === 1) {
			if ($primary === $auto) {
				continue;
			}
		}
		//UNIQUE KEY
		if (preg_match('#^\s*UNIQUE KEY (.*?) \((.*?)\)#i', $row2, $arrMatch) === 1) {
			if ((isset($arrMatch[1]) === true) && (isset($arrMatch[2]) === true)) {
				$arrSQL['index'][$table][trim($arrMatch[1])] = sprintf('CREATE UNIQUE INDEX %s ON %s(%s);', $arrMatch[1], $table, $arrMatch[2]);
			}
			continue;
		}
		//KEY
		if (preg_match('#^\s*KEY (.*?) \((.*?)\)#i', $row2, $arrMatch) === 1) {
			if ((isset($arrMatch[1]) === true) && (isset($arrMatch[2]) === true)) {
				$arrSQL['index'][$table][trim($arrMatch[1])] = sprintf('CREATE INDEX %s ON %s(%s);', $arrMatch[1], $table, $arrMatch[2]);
			}
			continue;
		}
		$arrSQL['create'][$table][] = $row2;
	}//foreach ($arrWork as $key2=>$row2)
}

/**
 * DBインポート
 * 引数：DB名
 * 引数：SQL
 * 戻値：なし
 */
function importDb($dbname, $arrSQL) {
	try {
		$chk = false;
		$msg = '';
		$sql = '';
		$options = [
			PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION
		];
		$dsn   = sprintf('sqlite:%s', $dbname);
		$clsDb = new PDO($dsn, '', '', $options);
		$clsDb->beginTransaction();
		$arrType = array('drop', 'create', 'insert', 'index');
		foreach ($arrType as $key=>$row) {
			if (isset($arrSQL[$row]) === true) {
				if ($row === 'create') {
					foreach ($arrSQL[$row] as $key2=>$row2) {
						$sql = implode('', $row2);
						$sql = preg_replace('#,\);$#', ');', $sql);
						$clsDb->exec($sql);
						usleep(1);
					}
				} else {
					foreach ($arrSQL[$row] as $key2=>$row2) {
						foreach ($row2 as $key3=>$row3) {
							$sql = $row3;
							$clsDb->exec($sql);
							usleep(1);
						}
					}
				}
			}
		}
		$clsDb->commit();
		$chk = true;
	} catch (Exception $e) {
		$msg =  $e->getMessage()."\n";
		$clsDb->rollBack();
	}
	$clsDb = null;
	if ($chk === false) {
		throw new Exception(sprintf("ERROR IMPORT DB\n %s %s", $msg, $sql));
	}
}