将 SQLite3 迁移到 MySQL 的快速简便方法？

Quick easy way to migrate SQLite3 to MySQL?

提问by Stephen Cox

Anyone know a quick easy way to migrate a SQLite3 database to MySQL?

有人知道将 SQLite3 数据库迁移到 MySQL 的快速简便方法吗？

采纳答案by David d C e Freitas

Here is a list of converters (not updated since 2011):

以下是转换器列表（自 2011 年以来未更新）：

• https://www2.sqlite.org/cvstrac/wiki?p=ConverterTools(or snapshot at archive.org)

• https://www2.sqlite.org/cvstrac/wiki?p=ConverterTools（或archive.org 上的快照）

---

---

一种可以很好地工作但很少提及的替代方法是：使用 ORM 类为您抽象出特定的数据库差异。例如你在 PHP 中得到这些（RedBeanRedBean), Python (Django 的 ORM 层,Storm风暴，SqlAlchemySqlAlchemy)、Ruby on Rails (ActiveRecordActiveRecord), 可可 (CoreData核心数据)

i.e. you could do this:

即你可以这样做：

1. Load data from source database using the ORM class.
2. Store data in memory or serialize to disk.
3. Store data into destination database using the ORM class.

1. 使用 ORM 类从源数据库加载数据。
2. 将数据存储在内存中或序列化到磁盘。
3. 使用 ORM 类将数据存储到目标数据库中。

回答by Shalmanese

Everyone seems to starts off with a few greps and perl expressions and you sorta kinda get something that works for your particular dataset but you have no idea if it's imported the data correctly or not. I'm seriously surprised nobody's built a solid library that can convert between the two.

每个人似乎都从一些 grep 和 perl 表达式开始，你有点得到一些适用于你的特定数据集的东西，但你不知道它是否正确导入了数据。我很惊讶没有人建立一个可以在两者之间转换的可靠库。

Here a list of ALL the differences in SQL syntax that I know about between the two file formats: The lines starting with:

这里列出了我所知道的两种文件格式之间 SQL 语法的所有差异： 以以下内容开头的行：

• BEGIN TRANSACTION
• COMMIT
• sqlite_sequence
• CREATE UNIQUE INDEX

• 开始交易
• 犯罪
• sqlite_sequence
• 创建唯一索引

are not used in MySQL

未在 MySQL 中使用

• SQLlite uses CREATE TABLE/INSERT INTO "table_name"and MySQL uses CREATE TABLE/INSERT INTO table_name
• MySQL doesn't use quotes inside the schema definition
• MySQL uses single quotes for strings inside the INSERT INTOclauses
• SQLlite and MySQL have different ways of escaping strings inside INSERT INTOclauses
• SQLlite uses 't'and 'f'for booleans, MySQL uses 1and 0(a simple regex for this can fail when you have a string like: 'I do, you don\'t' inside your INSERT INTO)
• SQLLite uses AUTOINCREMENT, MySQL uses AUTO_INCREMENT

• SQLlite 使用CREATE TABLE/INSERT INTO "table_name"和 MySQL 使用CREATE TABLE/INSERT INTO table_name
• MySQL 不在架构定义中使用引号
• MySQL 对INSERT INTO子句内的字符串使用单引号
• SQLlite 和 MySQL 有不同的方法来转义INSERT INTO子句中的字符串
• SQLlite用途't'和'f'为布尔值，MySQL使用1和0（当你有一个字符串像这样一个简单的正则表达式可能会失败：“我这样做，你不\”吨您的内部” INSERT INTO）
• SQLLite 使用AUTOINCREMENT，MySQL 使用AUTO_INCREMENT

Here is a very basic hacked up perl script which works for mydataset and checks for many more of these conditions that other perl scripts I found on the web. Nu guarentees that it will work for your data but feel free to modify and post back here.

这是一个非常基本的修改过的 perl 脚本，它适用于我的数据集，并检查我在网上找到的其他 perl 脚本中的更多这些条件。Nu 保证它适用于您的数据，但随时可以在此处修改和发回。

#! /usr/bin/perl

while ($line = <>){
    if (($line !~  /BEGIN TRANSACTION/) && ($line !~ /COMMIT/) && ($line !~ /sqlite_sequence/) && ($line !~ /CREATE UNIQUE INDEX/)){

        if ($line =~ /CREATE TABLE \"([a-z_]*)\"(.*)/){
            $name = ;
            $sub = ;
            $sub =~ s/\"//g;
            $line = "DROP TABLE IF EXISTS $name;\nCREATE TABLE IF NOT EXISTS $name$sub\n";
        }
        elsif ($line =~ /INSERT INTO \"([a-z_]*)\"(.*)/){
            $line = "INSERT INTO \n";
            $line =~ s/\"/\\"/g;
            $line =~ s/\"/\'/g;
        }else{
            $line =~ s/\'\'/\\'/g;
        }
        $line =~ s/([^\'])\'t\'(.)/THIS_IS_TRUE/g;
        $line =~ s/THIS_IS_TRUE/1/g;
        $line =~ s/([^\'])\'f\'(.)/THIS_IS_FALSE/g;
        $line =~ s/THIS_IS_FALSE/0/g;
        $line =~ s/AUTOINCREMENT/AUTO_INCREMENT/g;
        print $line;
    }
}

回答by Jiaaro

Here is a python script, built off of Shalmanese's answer and some help from Alex martelli over at Translating Perl to Python

这是一个 python 脚本，建立在 Shalmanese 的回答和 Alex martelli 在Translating Perl to Python 上的一些帮助的基础上

I'm making it community wiki, so please feel free to edit, and refactor as long as it doesn't break the functionality (thankfully we can just roll back) - It's pretty ugly but works

我正在把它变成社区维基，所以请随意编辑和重构，只要它不破坏功能（谢天谢地我们可以回滚） - 它很丑但有效

use like so (assuming the script is called dump_for_mysql.py:

像这样使用（假设脚本被称为dump_for_mysql.py：

sqlite3 sample.db .dump | python dump_for_mysql.py > dump.sql

Which you can then import into mysql

然后您可以将其导入 mysql

note - you need to add foreign key constrains manually since sqlite doesn't actually support them

注意 - 您需要手动添加外键约束，因为 sqlite 实际上并不支持它们

here is the script:

这是脚本：

#!/usr/bin/env python

import re
import fileinput

def this_line_is_useless(line):
    useless_es = [
        'BEGIN TRANSACTION',
        'COMMIT',
        'sqlite_sequence',
        'CREATE UNIQUE INDEX',
        'PRAGMA foreign_keys=OFF',
    ]
    for useless in useless_es:
        if re.search(useless, line):
            return True

def has_primary_key(line):
    return bool(re.search(r'PRIMARY KEY', line))

searching_for_end = False
for line in fileinput.input():
    if this_line_is_useless(line):
        continue

    # this line was necessary because '');
    # would be converted to \'); which isn't appropriate
    if re.match(r".*, ''\);", line):
        line = re.sub(r"''\);", r'``);', line)

    if re.match(r'^CREATE TABLE.*', line):
        searching_for_end = True

    m = re.search('CREATE TABLE "?(\w*)"?(.*)', line)
    if m:
        name, sub = m.groups()
        line = "DROP TABLE IF EXISTS %(name)s;\nCREATE TABLE IF NOT EXISTS `%(name)s`%(sub)s\n"
        line = line % dict(name=name, sub=sub)
    else:
        m = re.search('INSERT INTO "(\w*)"(.*)', line)
        if m:
            line = 'INSERT INTO %s%s\n' % m.groups()
            line = line.replace('"', r'\"')
            line = line.replace('"', "'")
    line = re.sub(r"([^'])'t'(.)", "THIS_IS_TRUE", line)
    line = line.replace('THIS_IS_TRUE', '1')
    line = re.sub(r"([^'])'f'(.)", "THIS_IS_FALSE", line)
    line = line.replace('THIS_IS_FALSE', '0')

    # Add auto_increment if it is not there since sqlite auto_increments ALL
    # primary keys
    if searching_for_end:
        if re.search(r"integer(?:\s+\w+)*\s*PRIMARY KEY(?:\s+\w+)*\s*,", line):
            line = line.replace("PRIMARY KEY", "PRIMARY KEY AUTO_INCREMENT")
        # replace " and ' with ` because mysql doesn't like quotes in CREATE commands 
        if line.find('DEFAULT') == -1:
            line = line.replace(r'"', r'`').replace(r"'", r'`')
        else:
            parts = line.split('DEFAULT')
            parts[0] = parts[0].replace(r'"', r'`').replace(r"'", r'`')
            line = 'DEFAULT'.join(parts)

    # And now we convert it back (see above)
    if re.match(r".*, ``\);", line):
        line = re.sub(r'``\);', r"'');", line)

    if searching_for_end and re.match(r'.*\);', line):
        searching_for_end = False

    if re.match(r"CREATE INDEX", line):
        line = re.sub('"', '`', line)

    if re.match(r"AUTOINCREMENT", line):
        line = re.sub("AUTOINCREMENT", "AUTO_INCREMENT", line)

    print line,

回答by Richard Gourlay

Probably the quick easiest way is using the sqlite .dump command, in this case create a dump of the sample database.

可能最简单的方法是使用 sqlite .dump 命令，在这种情况下创建示例数据库的转储。

sqlite3 sample.db .dump > dump.sql

You can then (in theory) import this into the mysql database, in this case the test database on the database server 127.0.0.1, using user root.

然后（理论上）您可以使用 root 用户将其导入到 mysql 数据库中，在本例中是数据库服务器 127.0.0.1 上的测试数据库。

mysql -p -u root -h 127.0.0.1 test < dump.sql

I say in theory as there are a few differences between grammars.

我说的是理论上，因为语法之间存在一些差异。

In sqlite transactions begin

在sqlite事务开始

BEGIN TRANSACTION;
...
COMMIT;

MySQL uses just

MySQL 只使用

BEGIN;
...
COMMIT;

There are other similar problems (varchars and double quotes spring back to mind) but nothing find and replace couldn't fix.

还有其他类似的问题（varchars 和双引号回想起），但没有找到和替换无法解决的问题。

Perhaps you should ask why you are migrating, if performance/ database size is the issue perhaps look at reoginising the schema, if the system is moving to a more powerful product this might be the ideal time to plan for the future of your data.

也许你应该问你为什么要迁移，如果性能/数据库大小是问题，也许看看重新定义架构，如果系统正在转向更强大的产品，这可能是规划数据未来的理想时间。

回答by Dashamir Hoxha

• http://sqlfairy.sourceforge.net/
• http://search.cpan.org/dist/SQL-Translator/

• http://sqlfairy.sourceforge.net/
• http://search.cpan.org/dist/SQL-Translator/

aptitude install sqlfairy libdbd-sqlite3-perl

sqlt -f DBI --dsn dbi:SQLite:../.open-tran/ten-sq.db -t MySQL --add-drop-table > mysql-ten-sq.sql
sqlt -f DBI --dsn dbi:SQLite:../.open-tran/ten-sq.db -t Dumper --use-same-auth > sqlite2mysql-dumper.pl
chmod +x sqlite2mysql-dumper.pl
./sqlite2mysql-dumper.pl --help
./sqlite2mysql-dumper.pl --add-truncate --mysql-loadfile > mysql-dump.sql
sed -e 's/LOAD DATA INFILE/LOAD DATA LOCAL INFILE/' -i mysql-dump.sql

echo 'drop database `ten-sq`' | mysql -p -u root
echo 'create database `ten-sq` charset utf8' | mysql -p -u root
mysql -p -u root -D ten-sq < mysql-ten-sq.sql
mysql -p -u root -D ten-sq < mysql-dump.sql

回答by Mihkorz

If you are using Python/Django it's pretty easy:

如果您使用的是 Python/Django，则非常简单：

create two databases in settings.py (like here https://docs.djangoproject.com/en/1.11/topics/db/multi-db/)

在 settings.py 中创建两个数据库（就像这里https://docs.djangoproject.com/en/1.11/topics/db/multi-db/）

then just do like this:

然后就这样做：

objlist = ModelObject.objects.using('sqlite').all()

for obj in objlist:
    obj.save(using='mysql')

回答by Vincent Sit

I usually use the Export/import tablesfeature of IntelliJ DataGrip.

我通常使用IntelliJ DataGrip的导出/导入表功能。

You can see the progress in the bottom right corner.

您可以在右下角看到进度。

[]

[ ]

回答by Snips

I've just gone through this process, and there's a lot of very good help and information in this Q/A, but I found I had to pull together various elements (plus some from other Q/As) to get a working solution in order to successfully migrate.

我刚刚完成了这个过程，在这个 Q/A 中有很多非常好的帮助和信息，但我发现我必须将各种元素（加上一些来自其他 Q/A 的元素）整合在一起才能得到一个有效的解决方案才能成功迁移。

However, even after combining the existing answers, I found that the Python script did not fully work for me as it did not work where there were multiple boolean occurrences in an INSERT. See herewhy that was the case.

然而，即使在结合现有答案之后，我发现 Python 脚本并没有完全适合我，因为它在 INSERT 中出现多个布尔值时不起作用。看看这里为什么会这样。

So, I thought I'd post up my merged answer here. Credit goes to those that have contributed elsewhere, of course. But I wanted to give something back, and save others time that follow.

所以，我想我会在这里发布我的合并答案。当然，功劳归功于那些在其他地方做出贡献的人。但我想回馈一些东西，并节省其他人的时间。

I'll post the script below. But firstly, here's the instructions for a conversion...

我将在下面发布脚本。但首先，这是转换说明...

I ran the script on OS X 10.7.5 Lion. Python worked out of the box.

我在 OS X 10.7.5 Lion 上运行脚本。Python 开箱即用。

To generate the MySQL input file from your existing SQLite3 database, run the script on your own files as follows,

要从现有的 SQLite3 数据库生成 MySQL 输入文件，请在您自己的文件上运行脚本，如下所示，

Snips$ sqlite3 original_database.sqlite3 .dump | python ~/scripts/dump_for_mysql.py > dumped_data.sql

I then copied the resulting dumped_sql.sql file over to a Linux box running Ubuntu 10.04.4 LTS where my MySQL database was to reside.

然后我将生成的 dumped_sql.sql 文件复制到我的 MySQL 数据库所在的运行 Ubuntu 10.04.4 LTS 的 Linux 机器上。

Another issue I had when importing the MySQL file was that some unicode UTF-8 characters (specifically single quotes) were not being imported correctly, so I had to add a switch to the command to specify UTF-8.

导入 MySQL 文件时遇到的另一个问题是某些 unicode UTF-8 字符（特别是单引号）未正确导入，因此我必须向命令添加一个开关以指定 UTF-8。

The resulting command to input the data into a spanking new empty MySQL database is as follows:

将数据输入到一个全新的空 MySQL 数据库的结果命令如下：

Snips$ mysql -p -u root -h 127.0.0.1 test_import --default-character-set=utf8 < dumped_data.sql

Let it cook, and that should be it! Don't forget to scrutinise your data, before and after.

让它煮熟，就这样吧！不要忘记在前后仔细检查您的数据。

So, as the OP requested, it's quick and easy, when you know how! :-)

因此，正如 OP 所要求的那样，当您知道如何操作时，它既快捷又简单！:-)

As an aside, one thing I wasn't sure about before I looked into this migration, was whether created_at and updated_at field values would be preserved - the good news for me is that they are, so I could migrate my existing production data.

顺便说一句，在我研究此迁移之前，我不确定的一件事是是否会保留 created_at 和 updated_at 字段值 - 对我来说好消息是它们是，因此我可以迁移现有的生产数据。

Good luck!

祝你好运！

UPDATE

更新

Since making this switch, I've noticed a problem that I hadn't noticed before. In my Rails application, my text fields are defined as 'string', and this carries through to the database schema. The process outlined here results in these being defined as VARCHAR(255) in the MySQL database. This places a 255 character limit on these field sizes - and anything beyond this was silently truncated during the import. To support text length greater than 255, the MySQL schema would need to use 'TEXT' rather than VARCHAR(255), I believe. The process defined here does not include this conversion.

自从进行此切换后，我注意到了一个以前没有注意到的问题。在我的 Rails 应用程序中，我的文本字段被定义为“字符串”，这将贯穿到数据库模式。此处概述的过程导致这些在 MySQL 数据库中被定义为 VARCHAR(255)。这对这些字段大小设置了 255 个字符的限制 - 超出此限制的任何内容在导入期间都会被静默截断。为了支持大于 255 的文本长度，我相信 MySQL 架构需要使用“TEXT”而不是 VARCHAR(255)。此处定义的过程不包括此转换。

---

---

Here's the merged and revised Python script that worked for my data:

这是适用于我的数据的合并和修订的 Python 脚本：

#!/usr/bin/env python

import re
import fileinput

def this_line_is_useless(line):
    useless_es = [
        'BEGIN TRANSACTION',
        'COMMIT',
        'sqlite_sequence',
        'CREATE UNIQUE INDEX',        
        'PRAGMA foreign_keys=OFF'
        ]
    for useless in useless_es:
        if re.search(useless, line):
            return True

def has_primary_key(line):
    return bool(re.search(r'PRIMARY KEY', line))

searching_for_end = False
for line in fileinput.input():
    if this_line_is_useless(line): continue

    # this line was necessary because ''); was getting
    # converted (inappropriately) to \');
    if re.match(r".*, ''\);", line):
        line = re.sub(r"''\);", r'``);', line)

    if re.match(r'^CREATE TABLE.*', line):
        searching_for_end = True

    m = re.search('CREATE TABLE "?([A-Za-z_]*)"?(.*)', line)
    if m:
        name, sub = m.groups()
        line = "DROP TABLE IF EXISTS %(name)s;\nCREATE TABLE IF NOT EXISTS `%(name)s`%(sub)s\n"
        line = line % dict(name=name, sub=sub)
        line = line.replace('AUTOINCREMENT','AUTO_INCREMENT')
        line = line.replace('UNIQUE','')
        line = line.replace('"','')
    else:
        m = re.search('INSERT INTO "([A-Za-z_]*)"(.*)', line)
        if m:
            line = 'INSERT INTO %s%s\n' % m.groups()
            line = line.replace('"', r'\"')
            line = line.replace('"', "'")
            line = re.sub(r"(?<!')'t'(?=.)", r"1", line)
            line = re.sub(r"(?<!')'f'(?=.)", r"0", line)

    # Add auto_increment if it's not there since sqlite auto_increments ALL
    # primary keys
    if searching_for_end:
        if re.search(r"integer(?:\s+\w+)*\s*PRIMARY KEY(?:\s+\w+)*\s*,", line):
            line = line.replace("PRIMARY KEY", "PRIMARY KEY AUTO_INCREMENT")
        # replace " and ' with ` because mysql doesn't like quotes in CREATE commands

    # And now we convert it back (see above)
    if re.match(r".*, ``\);", line):
        line = re.sub(r'``\);', r"'');", line)

    if searching_for_end and re.match(r'.*\);', line):
        searching_for_end = False

    if re.match(r"CREATE INDEX", line):
        line = re.sub('"', '`', line)

    print line,

回答by Outlaw Programmer

I recently had to migrate from MySQL to JavaDB for a project that our team is working on. I found a Java library written by Apache called DdlUtilsthat made this pretty easy. It provides an API that lets you do the following:

我最近不得不从 MySQL 迁移到 JavaDB，用于我们团队正在进行的一个项目。我发现了一个由 Apache 编写的名为 DdlUtils的Java 库，它使这变得非常容易。它提供了一个 API，可让您执行以下操作：

1. Discover a database's schema and export it as an XML file.
2. Modify a DB based upon this schema.
3. Import records from one DB to another, assuming they have the same schema.

1. 发现数据库的架构并将其导出为 XML 文件。
2. 根据此架构修改数据库。
3. 将记录从一个数据库导入到另一个数据库，假设它们具有相同的架构。

The tools that we ended up with weren't completely automated, but they worked pretty well. Even if your application is not in Java, it shouldn't be too difficult to whip up a few small tools to do a one-time migration. I think I was able to pull of our migration with less than 150 lines of code.

我们最终使用的工具并未完全自动化，但它们运行良好。即使您的应用程序不是用 Java 编写的，使用一些小工具来进行一次性迁移也不难。我想我能够用不到 150 行代码完成我们的迁移。

回答by NavidIvanian

There is no need to any script,command,etc...

不需要任何脚本、命令等...

you have to only export your sqlite database as a .csvfile and then import it in Mysql using phpmyadmin.

您只需将 sqlite 数据库导出为.csv文件，然后使用 phpmyadmin 将其导入 Mysql。

I used it and it worked amazing...

我用过它，效果惊人……