mysql数据迁移方案总结_数据迁移总结_mysql

一、写在前面的

这里的数据迁移是一次性的数据迁移，即将旧系统中的数据迁移到新系统的数据库中，在迁移过程中也并没有使用任何ETL工具，只是通过sql脚本按业务类型进行的分批次迁移。在此特将工作中踩过的坑和一些经验分享一下。

二、经常使用的SQL语法

插入

一次插入一条记录：INSERT INTO tablename(列名…) VALUES(列值);

一次插入多条记录：INSERT INTO tablename(列名…) VALUES(列值1)，(列值2);

插入结果集：INSERT INTO tablename(列名...) SELECT 字段1,字段2... FROM tablename

删除

清空表：TRUNCATE TABLE [表名]

删除表：DROP TABLE [表名]

删除记录：DELETE * FROM 表名 WHERE...

更新

单表更新1：UPDATE 表名 SET 字段1=值1,字段2=值2 WHERE ...

单表更新2：UPDATE 表1 SET 字段=(SELECT 字段1 FROM 表2 WHER...)

多表关联更新1：UPDATE 表1 a,表2 b SET a.字段1=b.字段1,a.字段2=b.字段2 WHERE...

多表关联更新2：UPDATE 表1 a LEFT JOIN 表2 b ON [表关联条件] SET a.字段1=b.字段1,a.字段 2=b.字段2 WHERE...

查询

在查询时尽量不要用select *，需要什么字段就查询什么字段

对于数据量比较大的表，查看该表的数据是什么内容最好添加limit ，已避免客户端因返回数据量太多而卡死

建表语句

一般建表语句：CREATE TABLE table_name (column_name column_type);

多用于创建临时表的语句：CREATE TABLE 表名 AS SELECT 字段1,字段2.. FROM 表名 WHERE

添加索引

添加索引：ALTER TABLE 表名 ADD 索引类型 (unique,primary key,fulltext,index)[索引名](字段名)

删除索引方式1：DROP INDEX index_name ON table_name ;

删除索引方式2：ALTER TABLE table_name DROP INDEX index_name ;

删除索引方式3：ALTER TABLE table_name DROP PRIMARY KEY ;

(莫名其妙的想到了孔乙己"回"字的多种写法....)

修改字段

修改类型：ALTER TABLE 表名 MODIFY COLUMN 字段名 新数据类型 新类型长度 新默认值 新注释;

修改字段名：ALTER TABLE 表名 CHANGE 旧字段名 新字段名 新数据类型;

修改字段名时不能省略新数据类型

三、经验与踩过的坑

插入/更新

在插入更新时，查看目标表的表结构，看索引是否存在以及有多少，在插入/更新数据时，如果插入的数据量太多，索引会很明显的降低插入/更新的效率。表中索引的存在就好比是一本书的目录一样，在一本书中添加一个章节的同时它的目录也应该要同步更新。

总结：

大批量进行更新插入操作时，最好先去掉目标表中的索引，等到插入更新操作结束后再添加上

笛卡尔积的坑

刚毕业时，曾经写过一个sql产生了笛卡尔积，从那以后凡是表关联的情况，我都会尽量使用左连接，并尽可能多的加上一些限制条件。

总结：

能加上的查询条件最好不要少些，特别是多表关联的时候

临时表的使用

在数据迁移后，双方都比较关注哪些数据已经迁移，哪些数据没有迁移，需要将数据整理出来，在工作过程中，我是通过建立临时表来存储一些需要统计的数据的，这样让其他人来排查问题时也比较方便。

总结：

可以通过create table 表名 as select 字段... from 的语法快速建立临时表并存放数据(这语法不同于insert语法，比先建表，后插入效率要高)

字段类型的坑

在两个表之间进行插入操作时，不要仅仅从业务上比较两个表的字段是否相对应，两个表中字段的类型同样要一致，不然很有可能插入的值跟预期值就不一样；在表连接时也要注意，有一次我在进行两个表关联(数据量都不大，50万以内的那种)，表A和表B的字段是同名的，但表A的字段类型是bigint类型，表B为varchar类型，然后两个表进行左连接查询时就一直在执行，后来将两个字段类型统一后，查询结果就秒出现了，所以，当查询“很慢”的时候，尝试去explain一下优化查询语句是一种思路，而重新检查一下表关联的字段类型是否一致又是另一种思路。希望大家遇到类似问题的时候能有所受益。

总结：

表关联时要注意检查关联字段类型是否一致

进行插入操作时，注意检查插入字段的类型是否一致

表连接相关

注意表关联时避免产生笛卡尔积

进行表的左连接时，注意筛选掉没有关联上的数据，左连接时以左表为主，即使是两个表关联不上右边的表也会返回一个空的记录

总结：

通过SELECT * FORM A LEFT JOIN B ON A.字段1=B.字段1 WHERE B.字段1 IS NOT NULL筛选掉没有关联上的记录

四、导出表结构脚本

让做数据迁移，结果连个表结构和pdm都没有，真是让人吐槽，如果一个表一个表的查看表结构的话就实在太麻烦了，这里利用python写了一个导出表结构的小脚本，可以根据自己需要,修改下方程序的相关参数，生成更丰富更适合自己使用的表结构

import pymysql

import pprint

# 导出表结构

# 设置连接的数据库

db_name='test'

# 导出文件路径

url=r'E:\表结构.md'

# 将下面参数改成自己的就好

# host:数据库地址;port:端口(不要加引号);user:登录名;passwd:密码

con=pymysql.connect(host='192.168.1.23',port=3306,user='test',passwd='test1234',db='%s' %db_name)

cursor=con.cursor()

table_list=[]

getTableSql="select distinct table_name from information_schema.tables where table_schema='%s'" %db_name

cursor.execute(getTableSql)

for row in cursor.fetchall():

table_list.append(row[0])

pass

with open(file=url,mode='a+') as f:

f.write("# "+db_name+"库\n")

for table in table_list:

with open(file=url,mode='a+') as f:

print("---开始写入%s表---" %table)

f.write("## "+table+"表\n")

# 相关字段

# COLUMN_NAME 列名, COLUMN_TYPE 数据类型, DATA_TYPE 字段类型,

# CHARACTER_MAXIMUM_LENGTH 长度, IS_NULLABLE 是否为空, COLUMN_DEFAULT 默认值, COLUMN_COMMENT 备注

cursor.execute("SELECT distinct COLUMN_NAME,DATA_TYPE,column_comment FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = '%s'" %table)

for row in cursor.fetchall():

with open(file=url,mode='a+') as f:

f.write("\t%-30s" %row[0]+"\t%-20s" %row[1]+"\t"+row[2]+'\n')

pass

print("---%s表写入完毕---" %table)

pass

五、mysql向mongoDB导数据

由于一些数据是在mongo中存储的，而数据源是mysql上的表，于是初步制定了从mysql向mongo中导入数据的方案：

第一步，在mysql上建立相应的表A，表A与mongo中collection存储的数据结构保持一致；

第二步，用sql语句将数据抽取到表A中，然后将表A的结果导出为JSON文件(Navicat中有个导出向导，可以导出为json文件)；

第三步，写脚本处理json文件中需要特殊处理的字段，mongo中存储类型要比mysql丰富，比如会有数组类型的数据，mongo中数据的存储实质上是存储的json串，所以对于这种不常见的类型，我们需要在json文件中进行处理，举个例子:数组类型的数据在json文件中就可以处理为-->"键：list类型"这种形式，当然还有其他的跟mysql对应不起来的数据类型，通过分析mongo上存储的json串，都可以得到相应的处理方案；

第四步，通过kettle将json串导入到mongo，详细步骤如下：新建一个转换，主要利用核心对象中的JSON Input(在input中)和MongoDB OUTPUT(在Big Data中)，建立如下图所示的转换：

在这里插入图片描述

JSON Input中的详细配置：

1.选择需要导入的json文件

在这里插入图片描述

2.建立相应的字段映射，路径那一列在kettle中是有特殊语法规定的，具体的可以查查

在这里插入图片描述

MongoDB OUTPUT中的详细配置：

1.配置mongo连接

在这里插入图片描述

2.选择需要插入的collection

在这里插入图片描述

3.选择需要插入的字段

在这里插入图片描述

4.最后执行该转换就可以了，由于此次向mongo中迁移的数据量不是很大(几万)，感觉这种方法还可行，

六、结尾

感觉这次总结的都是一些基础东西，也有很多废话，希望自己在总结的同时也能让大家有所收益。