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

Question

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

- Stephen Cox

Answer 1

每个人似乎都以几个greps和perl表达式开始，你会得到适用于自己特定数据集的东西，但你不知道它是否正确地导入了数据。我非常惊讶为什么没有人建立一个可以在两者之间进行转换的稳定库。

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

BEGIN TRANSACTION COMMIT sqlite_sequence CREATE UNIQUE INDEX

在MySQL中不使用。

SQLite使用CREATE TABLE/INSERT INTO "table_name"，而MySQL使用CREATE TABLE/INSERT INTO table_name。 MySQL不在模式定义内使用引号。 MySQL在INSERT INTO子句中使用单引号表示字符串。 SQLite和MySQL有不同的方法来转义INSERT INTO子句中的字符串。 SQLite使用't'和'f'表示布尔值，而MySQL使用1和0（当您在INSERT INTO中有像"I do, you don't"这样的字符串时，简单的正则表达式可能会失败）。 SQLLite使用AUTOINCREMENT，MySQL使用AUTO_INCREMENT。

这是一个非常基本的perl脚本，可适用于我的数据集，并检查其他我在网上找到的perl脚本中的更多条件。不能保证它适用于您的数据，但请随意修改并在此处发布。

#! /usr/bin/perl

while ($line = <>){
    if (($line !~  /BEGIN TRANSACTION/) && ($line !~ /COMMIT/) && ($line !~ /sqlite_sequence/) && ($line !~ /CREATE UNIQUE INDEX/)){
        
        if ($line =~ /CREATE TABLE \"([a-z_]*)\"(.*)/i){
            $name = $1;
            $sub = $2;
            $sub =~ s/\"//g;
            $line = "DROP TABLE IF EXISTS $name;\nCREATE TABLE IF NOT EXISTS $name$sub\n";
        }
        elsif ($line =~ /INSERT INTO \"([a-z_]*)\"(.*)/i){
            $line = "INSERT INTO $1$2\n";
            $line =~ s/\"/\\\"/g;
            $line =~ s/\"/\'/g;
        }else{
            $line =~ s/\'\'/\\\'/g;
        }
        $line =~ s/([^\\'])\'t\'(.)/$1THIS_IS_TRUE$2/g;
        $line =~ s/THIS_IS_TRUE/1/g;
        $line =~ s/([^\\'])\'f\'(.)/$1THIS_IS_FALSE$2/g;
        $line =~ s/THIS_IS_FALSE/0/g;
        $line =~ s/AUTOINCREMENT/AUTO_INCREMENT/g;
        print $line;
    }
}

- Shalmanese

---

Alex Martelli在https://dev59.com/cXNA5IYBdhLWcg3wKai3上做了出色的工作，将其改写为Python。 - Jiaaro

COMMIT 和 CREATE UNIQUE INDEX 是有效的 MySQL 命令，请进行修正。 - niutech

注意：默认的十进制数是（10,0），因此如果您需要其他格式的十进制字段，则应手动插入。 - Bula

我知道你的脚本是“快速而粗糙”的，但它非常有用，所以这里有一些添加/错误修正：

• 在 && ($line !~ /CREATE UNIQUE INDEX/) 后添加 && ($line !~ /PRAGMA foreign_keys=OFF/)
• 表名匹配的正则表达式会漏掉数字，因此应将 $line =~ /INSERT INTO \"([a-z_]*)\"(.*)/ 改为 $line =~ /INSERT INTO \"([a-z_1-9]*)\"(.*)/

希望这可以帮助未来的读者。 - Michał Leon

使用Perl脚本时，我遇到了一个错误：ERROR 1064 (42000)：您的SQL语法有误；请检查与您的MariaDB服务器版本相对应的手册以获取正确的语法，在第6行附近使用'FFT，Comment TEXT，FOREIGN KEY（SerialNumberRecordID）REFERENCES SerialNumTable'。 我使用.\script.pl foo.sql > out.sql作为它的用法？ - Jon Weinraub

Answer 2

这里是一份转换器列表（自2011年以来未更新）：

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

---

一个很少被提及但很好用的替代方法是：使用ORM类来抽象特定数据库差异。例如，在PHP中你可以使用RedBean，在Python中你可以使用Django的ORM层、Storm和SqlAlchemy，在Ruby on Rails中你可以使用ActiveRecord，在Cocoa中你可以使用CoreData。

也就是说，你可以这样做：

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

- David d C e Freitas

---

我们需要创建模型才能使用ORM吗？ - cikatomo

@cikatomo 是的，特别是 Django 有一个 inspect_db 函数。 - marcus jwt

对于那些已经运行了shell脚本但仍然遇到错误的人，你可以查看这个软件包： https://pypi.org/project/sqlite3-to-mysql/ 它在2023年运行良好！ - moon548834

Answer 3

这里有一个Python脚本，基于Shalmanese的答案和Alex martelli在Translating Perl to Python上的帮助。

我把它变成了社区wiki，所以请随意编辑和重构，只要不破坏功能（谢天谢地我们可以回滚） - 它看起来很丑但是可用。

使用方法如下（假设脚本名为dump_for_mysql.py）：

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

然后您可以将其导入MySQL中

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

这是脚本：

#!/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'(.)", "\1THIS_IS_TRUE\2", line)
    line = line.replace('THIS_IS_TRUE', '1')
    line = re.sub(r"([^'])'f'(.)", "\1THIS_IS_FALSE\2", 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,

- Jiaaro

---

嗨，吉姆，我的数据集中，每个第一个INSERT语句都用反引号而不是单引号括起来：__ DROP TABLE IF EXISTS schema_migrations; CREATE TABLE IF NOT EXISTS schema_migrations (version varchar(255) NOT NULL); INSERT INTO schema_migrations VALUES(20100714032840); INSERT INTO schema_migrations VALUES('20100714033251'); __ - David

Mysql中的AUTOINCREMENT是AUTO_INCREMENT。该脚本没有考虑到这一点。 - gdm

这对于媒体维基数据库不起作用。许多错误：Blobvar 数据类型，在 CREATE 语句中使用反引号... - Frank Hintsch

无法工作。可能没有考虑到所有条件... - Himanshu Bansal

令人惊讶的是，那个版本比Alex的代码有更多问题。 - JackTheKnife

Answer 4

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

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

[]

- Vincent Sit

---

该方法不会将索引迁移到新的数据库表中。 - Rexave

Answer 5

如果您正在使用Python/Django，这将非常容易：

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

然后只需像这样操作：

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

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

- Mihkorz

---

很好的建议，但我需要在开头添加以下内容：import django django.setup() from .models import ModelObject - borko

Answer 6

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

sqlite3 sample.db .dump > dump.sql

理论上，您可以将此文件导入到MySQL数据库中，本例中使用的是位于数据库服务器127.0.0.1上的测试数据库，并使用用户名root。

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

我说“理论上”因为语法之间存在一些差异。

在SQLite中，事务开始于

BEGIN TRANSACTION;
...
COMMIT;

MySQL只使用

BEGIN;
...
COMMIT;

还有其他类似的问题（比如变量和双引号），但使用“查找和替换”功能就能解决。

也许你应该问一下自己为什么要进行迁移。如果性能/数据库大小是问题所在，也许可以考虑重新组织架构；如果系统正在升级到更强大的产品，那么现在正是规划数据未来的理想时机。

- rsg

---

但最困难的任务是语法之间的差异。 - francois

Answer 7

我刚刚经历了这个过程，在这个问答中有很多非常好的帮助和信息，但我发现我必须整合各种元素（加上其他问答中的一些元素）才能成功迁移。

然而，即使组合了现有的答案，我发现Python脚本对于在INSERT中有多个布尔值出现的情况并没有完全起作用。请参见这里为什么会这样。

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

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

我在OS X 10.7.5 Lion上运行了该脚本。 Python可以直接使用。

要从现有的SQLite3数据库生成MySQL输入文件，请按以下方式运行脚本：

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

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

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

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

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

让它煮一会儿，就可以了！在迁移前后，不要忘记仔细审查您的数据。

所以，正如OP所请求的那样，当您知道如何操作时，这很快也很容易！ :-)

顺便说一下，在我调查这个迁移之前，我不确定created_at和updated_at字段值是否会被保留 - 对我来说好消息是它们会被保留，所以我可以迁移我的现有生产数据。

祝你好运！

更新：

自从进行了这个转换后，我注意到了一个之前没有注意到的问题。在我的Rails应用程序中，我的文本字段被定义为“string”，并且这一点体现在数据库模式中。这里概述的过程会导致这些字段在MySQL数据库中被定义为VARCHAR(255)。这对这些字段大小施加了255个字符的限制 - 超出这个限制的任何内容都将在导入过程中被默默截断。为了支持大于255的文本长度，MySQL模式需要使用“TEXT”而不是VARCHAR(255)，我认为。这里定义的过程不包括此转换。

以下是已合并和修订的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,

- Snips

---

谢谢。目前上面写的脚本中有一个语法错误；第41行的 "else："没有正确的缩进级别。我不清楚它上面的行是否应该缩进，或者是否还有其他问题。可以更新一下吗？ - Dan Tenenbaum

Answer 8

• 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

- Dashamir Hoxha

Answer 9

我用Python3编写了这个简单的脚本。它可以作为包含的类或通过终端shell单独调用的脚本来使用。默认情况下，它将所有整数导入为int(11)，并将字符串导入为varchar(300)，但可以在构造函数或脚本参数中进行相应调整。

注意：需要MySQL Connector/Python 2.0.4或更高版本。

如果您发现以下代码难以阅读，这里是GitHub上源代码的链接：https://github.com/techouse/sqlite3-to-mysql。

#!/usr/bin/env python3

__author__ = "Klemen Tušar"
__email__ = "[e-mail]"
__copyright__ = "GPL"
__version__ = "1.0.1"
__date__ = "2015-09-12"
__status__ = "Production"

import os.path, sqlite3, mysql.connector
from mysql.connector import errorcode


class SQLite3toMySQL:
    """
    Use this class to transfer an SQLite 3 database to MySQL.

    NOTE: Requires MySQL Connector/Python 2.0.4 or higher (https://dev.mysql.com/downloads/connector/python/)
    """
    def __init__(self, **kwargs):
        self._properties = kwargs
        self._sqlite_file = self._properties.get('sqlite_file', None)
        if not os.path.isfile(self._sqlite_file):
            print('SQLite file does not exist!')
            exit(1)
        self._mysql_user = self._properties.get('mysql_user', None)
        if self._mysql_user is None:
            print('Please provide a MySQL user!')
            exit(1)
        self._mysql_password = self._properties.get('mysql_password', None)
        if self._mysql_password is None:
            print('Please provide a MySQL password')
            exit(1)
        self._mysql_database = self._properties.get('mysql_database', 'transfer')
        self._mysql_host = self._properties.get('mysql_host', 'localhost')

        self._mysql_integer_type = self._properties.get('mysql_integer_type', 'int(11)')
        self._mysql_string_type = self._properties.get('mysql_string_type', 'varchar(300)')

        self._sqlite = sqlite3.connect(self._sqlite_file)
        self._sqlite.row_factory = sqlite3.Row
        self._sqlite_cur = self._sqlite.cursor()

        self._mysql = mysql.connector.connect(
            user=self._mysql_user,
            password=self._mysql_password,
            host=self._mysql_host
        )
        self._mysql_cur = self._mysql.cursor(prepared=True)
        try:
            self._mysql.database = self._mysql_database
        except mysql.connector.Error as err:
            if err.errno == errorcode.ER_BAD_DB_ERROR:
                self._create_database()
            else:
                print(err)
                exit(1)

    def _create_database(self):
        try:
            self._mysql_cur.execute("CREATE DATABASE IF NOT EXISTS `{}` DEFAULT CHARACTER SET 'utf8'".format(self._mysql_database))
            self._mysql_cur.close()
            self._mysql.commit()
            self._mysql.database = self._mysql_database
            self._mysql_cur = self._mysql.cursor(prepared=True)
        except mysql.connector.Error as err:
            print('_create_database failed creating databse {}: {}'.format(self._mysql_database, err))
            exit(1)

    def _create_table(self, table_name):
        primary_key = ''
        sql = 'CREATE TABLE IF NOT EXISTS `{}` ( '.format(table_name)
        self._sqlite_cur.execute('PRAGMA table_info("{}")'.format(table_name))
        for row in self._sqlite_cur.fetchall():
            column = dict(row)
            sql += ' `{name}` {type} {notnull} {auto_increment}, '.format(
                name=column['name'],
                type=self._mysql_string_type if column['type'].upper() == 'TEXT' else self._mysql_integer_type,
                notnull='NOT NULL' if column['notnull'] else 'NULL',
                auto_increment='AUTO_INCREMENT' if column['pk'] else ''
            )
            if column['pk']:
                primary_key = column['name']
        sql += ' PRIMARY KEY (`{}`) ) ENGINE = InnoDB CHARACTER SET utf8'.format(primary_key)
        try:
            self._mysql_cur.execute(sql)
            self._mysql.commit()
        except mysql.connector.Error as err:
            print('_create_table failed creating table {}: {}'.format(table_name, err))
            exit(1)

    def transfer(self):
        self._sqlite_cur.execute("SELECT name FROM sqlite_master WHERE type='table' AND name NOT LIKE 'sqlite_%'")
        for row in self._sqlite_cur.fetchall():
            table = dict(row)
            # create the table
            self._create_table(table['name'])
            # populate it
            print('Transferring table {}'.format(table['name']))
            self._sqlite_cur.execute('SELECT * FROM "{}"'.format(table['name']))
            columns = [column[0] for column in self._sqlite_cur.description]
            try:
                self._mysql_cur.executemany("INSERT IGNORE INTO `{table}` ({fields}) VALUES ({placeholders})".format(
                    table=table['name'],
                    fields=('`{}`, ' * len(columns)).rstrip(' ,').format(*columns),
                    placeholders=('%s, ' * len(columns)).rstrip(' ,')
                ), (tuple(data) for data in self._sqlite_cur.fetchall()))
                self._mysql.commit()
            except mysql.connector.Error as err:
                print('_insert_table_data failed inserting data into table {}: {}'.format(table['name'], err))
                exit(1)
        print('Done!')


def main():
    """ For use in standalone terminal form """
    import sys, argparse
    parser = argparse.ArgumentParser()
    parser.add_argument('--sqlite-file', dest='sqlite_file', default=None, help='SQLite3 db file')
    parser.add_argument('--mysql-user', dest='mysql_user', default=None, help='MySQL user')
    parser.add_argument('--mysql-password', dest='mysql_password', default=None, help='MySQL password')
    parser.add_argument('--mysql-database', dest='mysql_database', default=None, help='MySQL host')
    parser.add_argument('--mysql-host', dest='mysql_host', default='localhost', help='MySQL host')
    parser.add_argument('--mysql-integer-type', dest='mysql_integer_type', default='int(11)', help='MySQL default integer field type')
    parser.add_argument('--mysql-string-type', dest='mysql_string_type', default='varchar(300)', help='MySQL default string field type')
    args = parser.parse_args()

    if len(sys.argv) == 1:
        parser.print_help()
        exit(1)

    converter = SQLite3toMySQL(
        sqlite_file=args.sqlite_file,
        mysql_user=args.mysql_user,
        mysql_password=args.mysql_password,
        mysql_database=args.mysql_database,
        mysql_host=args.mysql_host,
        mysql_integer_type=args.mysql_integer_type,
        mysql_string_type=args.mysql_string_type
    )
    converter.transfer()

if __name__ == '__main__':
    main()

- Klemen Tusar

---

嘿@Klemen Tušar，为什么你的解决方案需要数据库连接？它能否用于将sqllite.db文件简单地转换为mysql格式？我尝试使用它，但没有成功，我只有文件，无法访问实时数据库。 - Wordpressor

嘿，@Wordpressor，是的，它需要一个正在运行的数据库。 - Klemen Tusar

Answer 10

获取SQL转储

moose@pc08$ sqlite3 mySqliteDatabase.db .dump > myTemporarySQLFile.sql

将导出文件导入MySQL

对于小型导入：

moose@pc08$ mysql -u <username> -p
Enter password:
....
mysql> use somedb;
Database changed
mysql> source myTemporarySQLFile.sql;

或者

mysql -u root -p somedb < myTemporarySQLFile.sql

这将提示您输入密码。请注意：如果要直接输入密码，则必须在-p后紧接着输入，不能有空格：

mysql -u root -pYOURPASS somedb < myTemporarySQLFile.sql

对于较大的数据转储：

使用mysqlimport或其他导入工具，例如BigDump。

BigDump可以为您提供进度条：

- Martin Thoma

---

由于 SQLite 和 MySQL 中存在轻微的语法差异和标志差异，因此这种方法无法奏效。您仍需要手动进行转换。 - dlite922