SQLite3转MySQL的一些坑

SQLite3转MySQL的一些坑

六月 21, 2021

SQLite3 转 MySQL的一些坑

对本文帮助极大的项目: https://github.com/techouse/sqlite3-to-mysql

案例1: bitwarden

暴力解法

直接使用上面的项目的时候可能会报错:

1089 (HY000): Incorrect prefix key; the used key part isn’t a string, the used length is longer than the key part, or the storage engine doesn’t support unique prefix keys.

原因是索引的字段不是字符串且长度过短，这里使用了比较暴力的方法: 报错退出的时候把出错的索引中使用到的int类型的字段改成varchar(255)，能跑，over。

案例2: gitea

naive解法

报了和案例1一样的错，尝试同样的方法解决，结果由于前面的语句重复执行太多次索引超出了数量限制报错:

1069: Too many keys specified; max 64 keys allowed.

使用以下方法解决:

1
2
3
4
5
6
7
8
9
10
# 生成删除所有索引用的语句
SELECT CONCAT('ALTER TABLE `',i.TABLE_NAME,'` DROP INDEX `',i.INDEX_NAME,'` ;') 
FROM INFORMATION_SCHEMA.STATISTICS i
WHERE TABLE_SCHEMA = 'dbname' AND i.INDEX_NAME <> 'PRIMARY';

# 使用vscode用以下正则去重
# 查找: ^(.+)((?:\r?\n.*)*)(?:\r?\n\1)$
# 替换: $1$2
# 记得多应用几次
# 然后复制结果并执行即可删除所有索引

在案例1解法的基础上每次报错删一次索引，以为就要成功了，发现自己还是太年轻了:

1071 (42000): Specified key was too long; max key length is 3072 bytes.

一看，索引的字段包含4个varchar(255)+两个text，直接长度爆炸

尝试多种解决方案未果，直接删库跑路删除该索引

转换成功了，但是并没有什么卵用

1
2
3
4
5
6
7
8
9
10
11
2021/06/20 10:03:31 ...om/urfave/cli/app.go:524:HandleAction() [W] Table reaction column id db type is INT(11), struct type is BIGINT(20)
2021/06/20 10:03:31 ...om/urfave/cli/app.go:524:HandleAction() [W] Table reaction column type db type is TEXT, struct type is VARCHAR(255)
2021/06/20 10:03:31 ...om/urfave/cli/app.go:524:HandleAction() [W] Table reaction column issue_id db type is INT(11), struct type is BIGINT(20)
2021/06/20 10:03:31 ...om/urfave/cli/app.go:524:HandleAction() [W] Table reaction column comment_id db type is INT(11), struct type is BIGINT(20)
2021/06/20 10:03:31 ...om/urfave/cli/app.go:524:HandleAction() [W] Table reaction column user_id db type is INT(11), struct type is BIGINT(20)
2021/06/20 10:03:31 ...om/urfave/cli/app.go:524:HandleAction() [W] Table reaction column original_author_id db type is INT(11), struct type is BIGINT(20)
2021/06/20 10:03:31 ...om/urfave/cli/app.go:524:HandleAction() [W] Table reaction Column original_author_id db default is , struct default is 0
2021/06/20 10:03:31 ...om/urfave/cli/app.go:524:HandleAction() [W] Table reaction column original_author db type is TEXT, struct type is VARCHAR(255)
2021/06/20 10:03:31 ...om/urfave/cli/app.go:524:HandleAction() [W] Table reaction column created_unix db type is INT(11), struct type is BIGINT(20)
2021/06/20 10:03:31 models/models.go:231:NewEngine() [I] [SQL] CREATE UNIQUE INDEX `UQE_reaction_s` ON `reaction` (`type`,`issue_id`,`comment_id`,`user_id`,`original_author_id`,`original_author`) [] - 6.427735ms
2021/06/20 10:03:31 routers/init.go:102:GlobalInit() [F] ORM engine initialization failed: sync database struct error: Error 1170: BLOB/TEXT column 'type' used in key specification without a key length

因为gitea用的gorm启动应用的时候会帮你把索引加上，而且字段数据类型不对绑不上bitwarden牛逼，rust牛逼

可能是正解

1、新开一个使用MySQL的gitea

2、使用Navicat的结构同步功能将新的数据库的结构同步到naive解法生成的数据库中，但是不要部署

3、把2中SQL语句复制到文本编辑器中，去除所有DROP、ADD.*INDEX、ADD PRIMARY的行

4、对naive解法生成的数据库执行3处理完的SQL语句

  1. 1. SQLite3 转 MySQL的一些坑
    1. 1.1. 案例1: bitwarden
      1. 1.1.1. 暴力解法
    2. 1.2. 案例2: gitea
      1. 1.2.1. naive解法
      2. 1.2.2. 可能是正解