数据迁移总结_mysql

一、写在前面的

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

二、经常使用的SQL语法

插入

1. 一次插入一条记录：INSERT INTO tablename(列名…) VALUES(列值);
2. 一次插入多条记录：INSERT INTO tablename(列名…) VALUES(列值1)，(列值2);
3. 插入结果集：INSERT INTO tablename(列名…) SELECT 字段1,字段2… FROM tablename

删除

1. 清空表：TRUNCATE TABLE [表名]
2. 删除表：DROP TABLE [表名]
3. 删除记录：DELETE * FROM 表名 WHERE…

更新

1. 单表更新1：UPDATE 表名 SET 字段1=值1,字段2=值2 WHERE …
2. 单表更新2：UPDATE 表1 SET 字段=(SELECT 字段1 FROM 表2 WHER…)
3. 多表关联更新1：UPDATE 表1 a,表2 b SET a.字段1=b.字段1,a.字段2=b.字段2 WHERE…
4. 多表关联更新2：UPDATE 表1 a LEFT JOIN 表2 b ON [表关联条件] SET a.字段1=b.字段1,a.字段 2=b.字段2 WHERE…

查询

1. 在查询时尽量不要用select *，需要什么字段就查询什么字段
2. 对于数据量比较大的表，查看该表的数据是什么内容最好添加limit ，已避免客户端因返回数据量太多而卡死

建表语句

1. 一般建表语句：CREATE TABLE table_name (column_name column_type);
2. 多用于创建临时表的语句：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一下优化查询语句是一种思路，而重新检查一下表关联的字段类型是否一致又是另一种思路。希望大家遇到类似问题的时候能有所受益。
总结：

1. 表关联时要注意检查关联字段类型是否一致
2. 进行插入操作时，注意检查插入字段的类型是否一致

表连接相关

1. 注意表关联时避免产生笛卡尔积
2. 进行表的左连接时，注意筛选掉没有关联上的数据，左连接时以左表为主，即使是两个表关联不上右边的表也会返回一个空的记录

总结：
通过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中迁移的数据量不是很大（几万），感觉这种方法还可行，

六、结尾

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