tidb到mysql迁移的问题总结

这里mysql 指mysql5.7

字符集问题

tidb 默认处理为utfmb4 ，有些表迁移到mysql 需要手动更改字符集为utf8

字符串长度问题

CREATE TABLE `testing`.`testing_test_case`(
`id`  bigint(20)   auto_increment    NOT NULL   , 
`name`  varchar(65535)  CHARSET `utf8mb4` COLLATE `utf8mb4_bin`      NOT NULL   DEFAULT '' , 
`strategys`  varchar(255)  CHARSET `utf8mb4` COLLATE `utf8mb4_bin`    COMMENT 'json string format'   NOT NULL   DEFAULT '' , 
`defaultstrategy`  varchar(10)  CHARSET `utf8mb4` COLLATE `utf8mb4_bin`      NOT NULL   DEFAULT '' , 
`usertype`  tinyint(3) unsigned     COMMENT '0: new rt user; 1: all user'   NOT NULL   DEFAULT 0 , 
`ut`  bigint(20)       NOT NULL   , 
`ct`  bigint(20)       NOT NULL   , 
, PRIMARY KEY (`id`), UNIQUE  INDEX `idx_name` (`name`)
) engine=innodb AUTO_INCREMENT=1 DEFAULT CHARSET=`utf8mb4` DEFAULT COLLATE `utf8mb4_bin`;

这张表在tidb 存储没有问题 迁移到mysql 需要更 name 字段类型为text。因为varchar 字段是将实际内容单独存储在聚簇索引之外，内容开头用1到2个字节表示实际长度（长度超过255时需要2个字节），因此最大长度不能超过65535
注意：
text 不可以设置默认值，需要结合业务观察。

索引长度问题

tidb 无索引长度问题
索引长度限制 MySQL Innodb 对于索引长度的限制为767 字节，并且UTF8mb4字符集是4字节字符集，则767字节/ 4字节每字符= 191字符(默认索引最大长度)，所以在varchar(255)或char(255) 类型字段上创建索引会失败，提示最大索引长度为767字节
注意：
字段设计时要保证前缀字符串的区别性，需要结合业务观察。

json 排序问题

json 格式字段在tidb 中存入顺序等于查询展示顺序
mysql 中json 要根据字符串a-z进行排序
注意：
两者只是展示效果不同，实际使用中暂未发现明显问题

锁问题

tidb 使用乐观锁
mysql 使用悲观锁
两者区别不在这里过多阐述，看下面一段业务 代码执行逻辑

func resetAliveUser(ctx context.Context, a int64) (err error) {
	fun := "rtcsync.resetAliveUser -->"

	for retry := 1; retry <= 5; retry++ {
		err = b.c(ctx, bInsName, bDbName, func(db *b.DBX, tables []interface{}) (sqlerr error) {
			trans, err := db.Beginx()
			if err != nil {
				slog.Warnf(ctx, "%s beginx fail:%v", fun, err)
				return err
			}

			defer func() {
				if sqlerr != nil {
					trans.Rollback()
				} else {
					sqlerr = trans.Commit()
					if sqlerr != nil {
						slog.Warnf(ctx, "%s trans commit fail:%v", fun, sqlerr)
					}
				}
			}()

			var s []*slice1
			err = trans.Select(&s, "select sdkid, ver  from "+d()+" where a = ? and used = 1 for update", a)
			if err != nil && err != sql.ErrNoRows {
				slog.Warnf(ctx, "%s get room sdk avi fail:%v a:%v", fun, err, a)
				return err
			}

			if len(s) == 0 {
				return nil
			}

			query := fmt.Sprintf("update %s set used = 0 where a = %v ",
				d(), a, e())
			result, err := db.Exec(query)
			//由于业务代码不严谨这里使用db.Exec 使得trans 不完整
			if err != nil {
				slog.Warnf(ctx, "%s update used fail:%v query:%v", fun, err, query)
				return err
			}

			if n, err := result.RowsAffected(); err != nil {
				slog.Warnf(ctx, "%s rowaffected err:%v query:%v n:%v", fun, err, query, n)
				return err
			}

			return nil
		}, d())

		if err != nil {
			slog.Warnf(ctx, "%s err:%v a:%v retry:%v", fun, err, a, retry)
			time.Sleep(time.Millisecond * 3)
			continue
		}
		break
	}

	if err != nil {
		slog.Errorf(ctx, "%s err:%v a:%v ", fun, err, a)
	}
	return
}

这种情况下 tidb 执行时间正常数据被更改成功
mysql 由于查询加锁，事物不完整。引起超时问题

explicit_defaults_for_timestamp = OFF

在默认情况下，如果timestamp列没有显式的指明null属性，那么该列会被自动加上not null属性(而其他类型的列如果没有被显式的指定not null，那么是允许null值的)，如果往这个列中插入null值，会自动的设置该列的值为current timestamp值。 tidb 与mysql参数 保持一致