将 MySQL 转换为 SQLite
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3890518/
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
Convert MySQL to SQlite
提问by mullek
Is it possible to convert from MySQL to SQLite with a free tool on windows?
是否可以使用 Windows 上的免费工具从 MySQL 转换为 SQLite？
回答by David LeBauer
There is a mysql2sqlite.sh script on GitHub
GitHub 上有一个 mysql2sqlite.sh 脚本
As described in the header, the script can be used like this:
如标题中所述，该脚本可以这样使用：
./mysql2sqlite.sh myDbase | sqlite3 database.sqlite
alternatives
备择方案
- an updated version https://github.com/dumblob/mysql2sqlite
- A simpler script was posted at the the MySQL Forums
- 更新版本https://github.com/dumblob/mysql2sqlite
- MySQL 论坛上发布了一个更简单的脚本
回答by David d C e Freitas
Hereis a list of converters. (snapshot at archive.today)
An alternative method that would work even on windows but is rarely mentioned is: use an ORM class that abstracts specific database differences away for you. e.g. you get these in PHP (RedBean), Python (Django's ORM layer, Storm, SqlAlchemy), Ruby on Rails (ActiveRecord), Cocoa (CoreData) etc.
一种甚至在 Windows 上也能工作但很少提及的替代方法是：使用 ORM 类为您抽象出特定的数据库差异。例如，您可以在 PHP（RedBean）、Python（Django 的 ORM 层、Storm、SqlAlchemy）、Ruby on Rails（ActiveRecord）、Cocoa（CoreData）等中获得这些。
i.e. you could do this:
即你可以这样做：
- Load data from source database using the ORM class.
- Store data in memory or serialize to disk.
- Store data into destination database using the ORM class.
- 使用 ORM 类从源数据库加载数据。
- 将数据存储在内存中或序列化到磁盘。
- 使用 ORM 类将数据存储到目标数据库中。
回答by Macario
Sequel (Ruby ORM) has a command line tool for dealing with databases, you must have ruby installed, then:
Sequel（Ruby ORM）有一个处理数据库的命令行工具，你必须安装ruby，然后：
$ gem install sequel mysql2 sqlite3
$ sequel mysql2://user:password@host/database -C sqlite://db.sqlite
回答by Assaf Lavie
Not every DB schema can be converted. MySQL is more complex and feature-rich than SQLite. However, if your schema is simple enough, you could dump it into an SQL file and try to import it / load it into an SQLite DB.
并非每个数据库架构都可以转换。MySQL 比 SQLite 更复杂、功能更丰富。但是，如果您的架构足够简单，您可以将其转储到 SQL 文件中，然后尝试将其导入/加载到 SQLite 数据库中。
回答by Klemen Tu?ar
I faced the same problem about 2 days ago when I had to convert a 20GB+ MySQL database to SQLite. It was by no means an easy task and I ended up writing this Python packagethat does the job.
大约 2 天前，当我不得不将 20GB+ MySQL 数据库转换为 SQLite 时，我遇到了同样的问题。这绝不是一件容易的事，我最终编写了这个 Python 包来完成这项工作。
The upside of it being written in Python is that it's cross platform (unlike a shell/bash script) and can all be easily installed using pip install(even on Windows). It uses generators and chunking of the data being processed and is therefore very memory efficient.
它用 Python 编写的好处是它是跨平台的（与 shell/bash 脚本不同）并且可以很容易地安装使用pip install（甚至在 Windows 上）。它使用生成器和正在处理的数据分块，因此内存效率非常高。
I also put in some effort to correctly translate most of the datatypes from MySQL to SQLite.
我还努力将大部分数据类型从 MySQL 正确转换为 SQLite。
The tool is also thoroughly testedand works on Python 2.7 and 3.5+.
该工具还经过全面测试，可在Python 2.7 和 3.5+ 上运行。
It is invokable via command line but can also be used as a standard Python class which you can include in some larger Python orchestration.
它可以通过命令行调用，但也可以用作标准 Python 类，您可以将其包含在一些更大的 Python 编排中。
Here's how you use it:
以下是您如何使用它：
Usage: mysql2sqlite [OPTIONS]
Options:
-f, --sqlite-file PATH SQLite3 database file [required]
-d, --mysql-database TEXT MySQL database name [required]
-u, --mysql-user TEXT MySQL user [required]
-p, --mysql-password TEXT MySQL password
-h, --mysql-host TEXT MySQL host. Defaults to localhost.
-P, --mysql-port INTEGER MySQL port. Defaults to 3306.
-c, --chunk INTEGER Chunk reading/writing SQL records
-l, --log-file PATH Log file
-V, --vacuum Use the VACUUM command to rebuild the SQLite
database file, repacking it into a minimal amount
of disk space
--use-buffered-cursors Use MySQLCursorBuffered for reading the MySQL
database. This can be useful in situations where
multiple queries, with small result sets, need to
be combined or computed with each other.
--help Show this message and exit.
回答by maytham-???????
Sqlite has officially list of converting tools.
Sqlite 有官方的转换工具列表。
回答by Paulo Luan
I found the perfect solution
我找到了完美的解决方案
First, you need this script (put it into a file called 'mysql-to-sqlite.sh'):
首先，您需要此脚本（将其放入名为“mysql-to-sqlite.sh”的文件中）：
#!/bin/bash
if [ "x" == "x" ]; then
echo "Usage: you@prompt:~$ mysqldump -u root -p --compatible=ansi --skip-opt generator > dumpfile
<dumpname>"
exit
fi
cat |
grep -v ' KEY "' |
grep -v ' UNIQUE KEY "' |
grep -v ' PRIMARY KEY ' |
sed '/^SET/d' |
sed 's/ unsigned / /g' |
sed 's/ auto_increment/ primary key autoincrement/g' |
sed 's/ smallint([0-9]*) / integer /g' |
sed 's/ tinyint([0-9]*) / integer /g' |
sed 's/ int([0-9]*) / integer /g' |
sed 's/ character set [^ ]* / /g' |
sed 's/ enum([^)]*) / varchar(255) /g' |
sed 's/ on update [^,]*//g' |
sed 's/\r\n/\n/g' |
sed 's/\"/"/g' |
perl -e 'local $/;$_=<>;s/,\n\)/\n\)/gs;print "begin;\n";print;print "commit;\n"' |
perl -pe '
if (/^(INSERT.+?)\(/) {
$a=;
s/\'\''/'\'\''/g;
s/\n/\n/g;
s/\),\(/\);\n$a\(/g;
}
' > .sql
cat .sql | sqlite3 .db > .err
ERRORS=`cat .err | wc -l`
if [ $ERRORS == 0 ]; then
echo "Conversion completed without error. Output file: .db"
rm .sql
rm .err
else
echo "There were errors during conversion. Please review .err and .sql for details."
fi
Then, dump a copy of your database:
然后，转储数据库的副本：
you@prompt:~$ mysql-to-sqlite.sh dumpfile
And now, run the conversion:
现在，运行转换：
you@prompt:~$ sqlite3 dumpfile.db
SQLite version 3.6.10
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> .tables
dg_cities dg_forms dg_surnames
dg_counties dg_provinces dg_user_accounts
dg_countries dg_provinces_netherlands
dg_first_names dg_states
And if all goes well, you should now have a dumpfile.db which can be used via sqlite3.
如果一切顺利，您现在应该有一个可以通过 sqlite3 使用的 dumpfile.db。
##代码##回答by Sandeep Yohans
Simplest way to Convert MySql DB to Sqlite:
将 MySql DB 转换为 Sqlite 的最简单方法：
1) Generate sql dump file for you MySql database.
1) 为您的 MySql 数据库生成 sql 转储文件。
2) Upload the file to RebaseData online converter here
2) 在此处上传文件到 RebaseData 在线转换器
3) A download button will appear on page to download database in Sqlite format
3) 页面上会出现一个下载按钮，可以下载Sqlite格式的数据库
回答by Uzair Qaiser
There are different ways to do this. I also had this problem and I searched a lot and then I got a simple way to convert MySQL to SQLite.
有不同的方法可以做到这一点。我也有这个问题，我搜索了很多，然后我找到了一种将 MySQL 转换为 SQLite 的简单方法。
Follow these steps:
按着这些次序：
First You Need to Install SQLite DB Browser (very small and fast to view Tables and Data)
Open your MySQL File in Notepad or it would be great if you open in Notepad++
Remove First extra Lines Contains Information or Queries and Save it.
Open SQLite DB Browser, Create Database, then Tables, and Same Types as it is in MySQL Database.
In Menu Bar of SQLite DB Browser Select File-> then Import data MySQL File which you saved.
首先你需要安装 SQLite DB Browser（非常小而且快速查看表和数据）
在记事本中打开您的 MySQL 文件，或者如果您在 Notepad++ 中打开它会很棒
删除第一个额外的行包含信息或查询并保存它。
打开 SQLite 数据库浏览器，创建数据库，然后是表，和 MySQL 数据库中的相同类型。
在 SQLite DB Browser 的菜单栏中选择 File-> 然后导入您保存的数据 MySQL 文件。
It will easily Convert into SQLite After Warning Dialog.
它会在警告对话框后轻松转换为 SQLite。
If error then remove more extra lines if your MySQL file have.
如果出现错误，则删除更多额外的行（如果您的 MySQL 文件有）。
You can also Install MySQL to SQLite Converter Software on trial Basis, but the information I am giving for conversion is life time.
您也可以在试用基础上安装 MySQL 到 SQLite 转换器软件，但我提供的转换信息是生命周期。
回答by Vali Munteanu
I tried a number of methods on this thread, but nothing worked for me. So here is a new solution, which I also found to be very simple:
我在这个线程上尝试了多种方法，但没有任何方法对我有用。所以这是一个新的解决方案，我也发现它非常简单：
- Install RazorSQL. Works for Mac, Windows and Linux.
- In RazorSQL connect to your database, on localhost for example. Conversion doesn't work with sql dump files.
- Right click on your database -> Database Conversion -> select SQLite.
This will save a txt file with all the
sqlitequeries necessary to create this database. - Install a SQLite database manager, like DB Browser for SQLite. It works on any OS.
- Create an empty database, go to Execute SQL tab and paste the content from step 3.
- 安装RazorSQL。适用于 Mac、Windows 和 Linux。
- 在 RazorSQL 中连接到您的数据库，例如在 localhost 上。转换不适用于 sql 转储文件。
- 右键单击您的数据库 -> 数据库转换 -> 选择 SQLite。这将保存一个 txt 文件，其中包含
sqlite创建此数据库所需的所有查询。 - 安装 SQLite 数据库管理器，例如DB Browser for SQLite。它适用于任何操作系统。
- 创建一个空数据库，转到执行 SQL 选项卡并粘贴步骤 3 中的内容。
That's it, now you have your SQLite database.
就是这样，现在你有了你的 SQLite 数据库。
