将 MySQL 数据库导出到 SQLite 数据库
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5164033/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Export a MySQL Database to SQLite Database
提问by Devi
Please help me with exporting a MySQL database into a SQLite database.
请帮助我将 MySQL 数据库导出到 SQLite 数据库中。
回答by georgiecasey
There's a fantastic Linux shell script on Githubthat converts Mysql to an Sqlite3 file. You need both mysqldump and sqlite3 installed on your server. Works great.
Github 上有一个很棒的 Linux shell 脚本，可以将 Mysql 转换为 Sqlite3 文件。您需要在服务器上安装 mysqldump 和 sqlite3。效果很好。
回答by George Fisher
The answer by @user2111698 edited by @quassy works as promised. Since I do this frequently I put their instructions into a bash script:
由@quassy 编辑的@user2111698 的答案按承诺工作。由于我经常这样做，因此我将他们的指令放入 bash 脚本中：
#!/bin/bash
mysql_host=localhost
mysql_user=george
mysql_dbname=database
sqlite3_dbname=database.sqlite3
# dump the mysql database to a txt file
mysqldump \
--skip-create-options \
--compatible=ansi \
--skip-extended-insert \
--compact \
--single-transaction \
-h$mysql_host \
-u$mysql_user \
-p $mysql_dbname \
> /tmp/localdb.txt
# remove lines mentioning "PRIMARY KEY" or "KEY"
cat /tmp/localdb.txt \
| grep -v "PRIMARY KEY" \
| grep -v KEY \
> /tmp/localdb.txt.1
# mysqldump leaves trailing commas before closing parentheses
perl -0pe 's/,\n\)/\)/g' /tmp/localdb.txt.1 > /tmp/localdb.txt.2
# change all \' to ''
sed -e 's/\'\''/'\'''\''/g' /tmp/localdb.txt.2 > /tmp/localdb.txt.3
if [ -e $sqlite3_dbname ]; then
mv $sqlite3_dbname $sqlite3_dbname.bak
fi
sqlite3 $sqlite3_dbname < /tmp/localdb.txt.3
A gist with detailed comments can be found at https://gist.github.com/grfiv/b79ace3656113bcfbd9b7c7da8e9ae8d
可以在https://gist.github.com/grfiv/b79ace3656113bcfbd9b7c7da8e9ae8d找到带有详细评论的要点
回答by George Fisher
mysql2sqlite.sh mentioned in the top post doesn't cope well with PRIMARY KEY lines, it doesn't write the trailing )to complete the CREATE statement.
顶帖中提到的 mysql2sqlite.sh 不能很好地处理 PRIMARY KEY 行，它不会编写尾随)来完成 CREATE 语句。
This is what I did. I ran the mysql dump as:
这就是我所做的。我将 mysql 转储运行为：
mysqldump --skip-create-options --compatible=ansi --skip-extended-insert --compact --single-transaction -h<host> -u<user> -p<passwd> <database name> > localdb.txt
I then used grep to remove PRIMARY KEY and KEY:
然后我使用 grep 删除 PRIMARY KEY 和 KEY：
cat localdb.txt | grep -v "PRIMARY KEY' | grep -v KEY > localdb.txt.1
I then used an editor to fix the file. When the keys are removed you end up with a CREATE statement that looks like:
然后我使用编辑器来修复文件。删除键后，您最终会得到一个 CREATE 语句，如下所示：
CREATE ...
...,
)
That trailing ,has to be removed. In vi this expression matches them, ,$\n)
,必须删除该尾随。在 vi 这个表达式匹配它们， ,$\n)
Then you need to change all \'to ''
然后你需要将所有更改\'为''
Then you can do the import:
然后你可以进行导入：
sqlite3 local.sqlite3 < localdb.txt.1
And that's it. I haven't found a single program that worked for me. I hope this helps someone.
就是这样。我还没有找到一个适合我的程序。我希望这可以帮助别人。
回答by caiofior
I manualy created the table structure in sqlite database.
我手动在 sqlite 数据库中创建了表结构。
Than I uploaded the data with teh following command:
比我使用以下命令上传数据：
mysqldump -u root {database} {table} --no-create-info --skip-extended-insert --complete-insert --skip-add-locks --compatible=ansi | sed "s/\\'/''/g" |sqlite3 flora.db
I had to use sedto fix a different apex encoding in the two databases
我不得不使用sed来修复两个数据库中不同的顶点编码
回答by Nuno
Personally I like the simple usage of mysqldump, yet some adjustments are need (depending on your art with Unix and what you want to do).
我个人喜欢 mysqldump 的简单用法，但需要进行一些调整（取决于您对 Unix 的艺术以及您想要做什么）。
Ex. for just one table (prods) with PK:
前任。对于只有一张桌子（产品）与 PK：
$ mysqldump mysql prods -u ME -pPASS --compatible ansi --compact |grep -v "^\/\*" |sqlite3 testme2.db
$ mysqldump mysql prods -u ME -pPASS --compatible ansi --compact |grep -v "^\/\*" |sqlite3 testme2.db
Error: near line 1: table "prods" already exists
Error: near line 7: UNIQUE constraint failed: prods.id, prods.ts
$ sqlite3 testme2.db '.schema'
CREATE TABLE "prods" (
"id" varchar(30) NOT NULL DEFAULT '',
"ts" int(11) NOT NULL DEFAULT '0',
"val" double DEFAULT NULL,
PRIMARY KEY ("id","ts")
);
For more complex things, probably better to write a wrapper, or then, use the already mentioned fantastic awk Linux shell script on Gist.
对于更复杂的事情，最好编写一个包装器，或者在 Gist 上使用已经提到的 出色的 awk Linux shell 脚本。
回答by Aaron Ratner
There is a fantastic, lightweight tool called SQLite Database Browser that allows you to create and edit sqlite databases. I used it to craete databases for Android apps. You can run SQL statements against it to load up the data so if you export the data from a mySQL database you can just import it using this tool. Here's a link: http://sqlitebrowser.sourceforge.net/
有一个很棒的轻量级工具，称为 SQLite 数据库浏览器，它允许您创建和编辑 sqlite 数据库。我用它来为 Android 应用程序创建数据库。您可以针对它运行 SQL 语句以加载数据，因此如果您从 mySQL 数据库导出数据，您只需使用此工具导入它。这是一个链接：http: //sqlitebrowser.sourceforge.net/
回答by Orbit
export the data with
导出数据
mysqldump database > database.sql
and import the data with
并导入数据
sqlite3 database < database.sql
you may need -u (user) and -p (password) options
您可能需要 -u（用户）和 -p（密码）选项
