Please help me with exporting a MySQL database into a SQLite database.
请帮助我将MySQL数据库导出到SQLite数据库。
7 个解决方案
#1
54
There's a fantastic Linux shell script on Github that 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。伟大的工作。
#2
3
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/b79ace36113bcfbd9b7b7c7da8e9ae8d上找到有详细注释的要点
#3
2
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不能很好地处理主键行,它不写尾)来完成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删除主键和键:
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.
就是这样。我还没有找到一个适合我的程序。我希望这能帮助某人。
#4
2
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 sed to fix a different apex encoding in the two databases
我不得不使用sed来修复两个数据库中不同的apex编码。
#5
0
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脚本。
#6
-1
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/
#7
-12
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(密码)选项
#1
54
There's a fantastic Linux shell script on Github that 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。伟大的工作。
#2
3
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/b79ace36113bcfbd9b7b7c7da8e9ae8d上找到有详细注释的要点
#3
2
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不能很好地处理主键行,它不写尾)来完成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删除主键和键:
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.
就是这样。我还没有找到一个适合我的程序。我希望这能帮助某人。
#4
2
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 sed to fix a different apex encoding in the two databases
我不得不使用sed来修复两个数据库中不同的apex编码。
#5
0
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脚本。
#6
-1
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/
#7
-12
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(密码)选项