有人知道一个快速简单的方法来迁移SQLite3数据库到MySQL吗?
当前回答
最近，为了我们团队正在进行的一个项目，我不得不从MySQL迁移到JavaDB。我发现Apache编写的一个名为DdlUtils的Java库非常容易做到这一点。它提供了一个API，让你做以下事情:
发现数据库的模式并将其导出为XML文件。 根据此模式修改DB。 将记录从一个DB导入到另一个DB，假设它们具有相同的模式。
我们最终使用的工具并不是完全自动化的，但它们工作得很好。即使您的应用程序不是用Java编写的，使用一些小工具进行一次性迁移也不会太难。我认为我只用了不到150行代码就完成了迁移。
其他回答
下面是一个python脚本，基于Shalmanese的回答和Alex martelli在翻译Perl到python中的一些帮助
我让它成为社区维基，所以请随时编辑和重构，只要它不破坏功能(谢天谢地，我们可以回滚)-它很丑，但工作
像这样使用(假设脚本名为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,
这个脚本是可以的，除了这种情况，当然，我遇到过:
INSERT INTO "requestcomparison_stopword" VALUES(149,'f'); INSERT INTO "requestcomparison_stopword" VALUES(420,'t');
脚本应该给出这样的输出:
INSERT INTO requestcomparison_stopword VALUES(149,'f'); INSERT INTO requestcomparison_stopword VALUES(420,'t');
而是给出了输出:
INSERT INTO requestcomparison_stopword VALUES(1490; INSERT INTO requestcomparison_stopword VALUES(4201;
最后0和1周围有一些奇怪的非ascii字符。
当我注释以下代码行(43-46)时，这不再出现，但出现了其他问题:
行= re.sub (r”([^”)“t”(.)”、“this_is_true 1 \ \ 2”、线) Line = Line。替换(' THIS_IS_TRUE ', ' 1 ') 行= re.sub (r”([^])的f '(。)”、“this_is_false 1 \ \ 2”、线) Line = Line。替换(“THIS_IS_FALSE ', ' 0 ')
这只是一个特殊的情况，当我们想要添加一个值是“f”或“t”，但我不太习惯正则表达式，我只是想发现这种情况由某人纠正。
无论如何，非常感谢这个方便的脚本!!
我用Python3编写了这个简单的脚本。它可以作为包含的类或通过终端shell调用的独立脚本使用。默认情况下，它将所有整数导入为int(11)，将字符串导入为varchar(300)，但所有这些都可以分别在构造函数或脚本参数中进行调整。
注意:它需要MySQL连接器/Python 2.0.4或更高版本
如果你觉得下面的代码很难阅读，这是GitHub上的源代码链接:https://github.com/techouse/sqlite3-to-mysql
#!/usr/bin/env python3
__author__ = "Klemen Tušar"
__email__ = "techouse@gmail.com"
__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()
以下是转换器列表(自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)中得到这些
例如，你可以这样做:
使用ORM类从源数据库加载数据。 将数据存储在内存中或序列化到磁盘。 使用ORM类将数据存储到目标数据库中。
我刚刚经历了这个过程，在这个Q/ a中有很多非常好的帮助和信息，但我发现我必须将各种元素(加上来自其他Q/ a的一些元素)组合在一起，以获得一个有效的解决方案，以便成功迁移。
然而，即使在结合现有的答案后，我发现Python脚本并没有完全为我工作，因为它在INSERT中出现多个布尔值时不起作用。看看为什么会这样。
所以，我想把我合并后的答案贴在这里。当然，功劳归于那些在其他地方做出贡献的人。但我想回馈一些东西，并节省其他人随后的时间。
我将在下面发布脚本。但首先，这里是转换的说明…
我在OS X 10.7.5 Lion上运行该脚本。巨蟒开箱即用。
要从您现有的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字段值是否会被保留——对我来说，好消息是它们会被保留，所以我可以迁移现有的生产数据。
好运！
更新
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.
以下是对我的数据进行合并和修改后的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,
