将MySQL数据库导出到SQLite数据库

Please help me with exporting a MySQL database into a SQLite database.

请帮助我将MySQL数据库导出到SQLite数据库。

7 个解决方案

#1

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

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

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

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

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