MySQLからSQLiteに変換 SQLiteのまとめ
2013.11.17
この記事は最終更新日から1年以上が経過しています。
便利だ。
SQLiteは便利だ。
SQLiteとは、MySQLやMariaDBやPostgreSQLと同じ関係データベース管理システム(RDBMS)であり、
データの保存に単一のファイルのみを使用することが特徴であります。
DataBaseを扱うそんなに大規模でないweb Appを制作する際にSQLiteの便利さが発揮するかと思います。
また、レンタルサーバー等で1つしかDataBaseを使う事が出来ない。等の状況でも活躍するかと思います。
前回の 【JS】ドラゴンクエスト バトルシミュレーター （ドラクエ戦闘シーン改） の制作では、MySQLでデータを構築し、SQLiteに変換したのですが、その手順等を紹介出来ればと。
SQLite
データベース作成
SQLiteのデータベースの作成はコマンドラインで
sqlite3 データベース名
で作成することができます。
また、SQLiteにアクセスする際も同様のコマンドとなります。
test.sqlite3といったデータベースを作成してみます。
sqlite3 test.sqlite3
すると、SQLiteのデータベースが作成され、コマンドもSQLiteに変わったかと思います。
（変わらない場合は再度入力）
.helpを入力すると、ダーっとコマンドが表示するかと思いますので、軽く確認しときましょう。
確認すると分かりますが、SQLiteのコマンドは .〇〇と入力する形となります。
テーブル作成
では、早速テーブルを作成していきます。
テーブル作成のSQL文は
CREATE TABLE テーブル名 (カラム名1, カラム名2, …);
となります。
では、user テーブルを作ってみます。
CREATE TABLE user(id, name);
とこれで作成が可能です。
SQLite3では個々のカラムにデータ型を指定するかどうかは自由となっていまして省略することができます。
データ型を指定する場合、
CREATE TABLE テーブル名 (カラム名1 データ型, カラム名2 データ型, …);
と入力していきます。
では、データ型を指定して作成します。
CREATE TABLE user(id integer, name text);
作成したら確認してみます。
テーブルの確認は
.tables
となります。
上記のデータ型指定でも分かる様にSQLiteではデータ型の指定が異なります。
SQLiteで使用可能のデータ型指定は以下になります。
SQLite使用可能データ型
- NULL NULL値
- INTEGER 符号付整数。1, 2, 3, 4, 6, or 8 バイトで格納
- REAL 浮動小数点数。8バイトで格納
- TEXT テキスト。UTF-8, UTF-16BE or UTF-16-LEのいずれかで格納
- BLOB Binary Large OBject。入力データをそのまま格納
容易に扱えることが分かったところで、では実際にMySQLからSQLiteに変換していきたいと思います。
MySQL から SQLite変換 〜 dump 〜
まずはdumpを行います。
通常、ダンプはこのように入力すればダンプすることが出来ます。
mysqldump -u root -p データベース名 > ダンプファイル名.sql
ですが、SQLiteへの変換の為、最適化してダンプを行います。
データベースとテーブルをSQL出力
1：一行ずつのインサート文にして出力
mysqldump -u root -p データベース名 --no-create-info -c --order-by-primary --skip-extended-insert > data_dump.sql
–order-by-primary
主キーでソートするように、
–no-create-info
で、テーブル作成情報（CREATE TABLE ステートメント）を書き込まない様に、
–skip-extended-insert
で、複数行INSERTせず、一行ずつにしておくとどこでエラーが出たかわかりやすくなります。
上記のコマンドを入力すると、パスワードを求められますので入力してダンプします。
ダンプファイルファイルをテキストエディタ等で確認すると、
このように INSERT文が1行ずつになってダンプされているのが確認できます。
2：テーブル定義の取り出し
続いて、テーブル作成の為にテーブル定義をダンプします。
mysqldump -u root -p データベース名 -d > create_table.sql
エディタで確認すると、
このような感じになっているかと思います。
MySQL から SQLite変換 〜 ダンプファイル 手作業変換 〜
では、ダンプファイルを手作業で修正していきます。
先ほど、テーブル作成の為にテーブル定義をダンプしたファイル「create_table.sql」から修正。
データ型編集
`id` int(10) NOT NULL DEFAULT '0', `name` varchar(24) DEFAULT NULL,
↓
`id` integer, `name` text,
SQLite用にデータ型を修正します。
ENGINE=InnoDB DEFAULT CHARSET=utf8;
ENGINE=InnoDB DEFAULT CHARSET=utf8; を削除します。
全て修正しましたら、ダンプしたファイルをSQLiteに読み込みます。
.read
SQLiteのコマンドである .readコマンドを使って読み込みます。
sqlite> .read create_table.sql
何処か間違っていたりすると、
とエラーを出力するので該当する行を直して再度実行しましょう。
.tables
でテーブルが作成できているか確認しましょう。
テーブルが作成できていたら、INSERT文のダンプファイルである、 data_dump.sql を読み込んでテーブルにデータを流しこんでいきます。
ここで修正する内容は LOCK TABLES `テーブル名` WRITE; と UNLOCK TABLES; を削除していきます。
LOCK TABLES `テーブル名` WRITE;
UNLOCK TABLES;
全て削除したら、また、.read data_dump.sql で読み込み完了です！
ファイルを読み込みsql文を実行
.read ファイル名
気をつける点は、.read ファイル名 の後に「;」は不要です。
付けてしまうとファイル名として認識し、エラーになるかと思います。
SQLite設定変更
.show
で現在の設定を確認できます。
と確認すると、 separatorが “｜”となっているため、こちらを「,」（カンマ）に変更してみます。
separatorの変更は .separatorで変更することが可能です。
.separator ,
で、「,」に変更します。
↓
このように変更が可能です。
と、ざっと 変換の方法でした。
PHPの方の読み込みも書こうと思いましたが、意外と長くなってしまったので次回書かせて頂きます。
