【Database】Mysql中生成PostgreSQL可执行的插入语句

前言

数据迁移在工作中是非常常见的事情，不同数据库之间的迁移也特别常见，并且有很多成熟的解决方案，例如ETL工具，常见的ETL工具如kettle、informatica、Datastage、Teradata、dataX等等；但是这些工具仅仅只能解决的连通网络的数据之间迁移，对于无法打通的网络就捉襟见肘了，当然也可以将数据生成CSV、文本文件等方式解决，但这涉及到人为的地方比较多，做成自动化比较困难。
所以博主就在想能不能通过SQL语句，在mysql数据库直接生成对应数据库可执行的sql语句，之后直接在PostgreSQL中执行，这样就可以直接将生成的数据文件直接在目标数据库执行了。

实现逻辑

在mysql中使用sql语句对每张表生成对应的一个语句，使用这个语句再次生成符合postgresql语法的插入语句，这个插入语句才是最终在pg目标库中执行的语句，即在mysql中生成“mysql生成postgresql插入语句”的语句。

转义字符

• 在mysql中是使用反斜杠\进行转义；
• 在PostgreSQL 9之前的版本，可以直接使用反斜杠\进行转义；
• 在PostgreSQL 9之后的版本，反斜杠已经变成了普通字符；如果想要使用反斜杠来转义字符，就必须在需要转义的字符串前面加上E（E就是Escape），如：select E'明\t天';
• PostgreSQL中对于单引号有两种转义方式，一是使用上面的方式来转义，如：select E'明\'天'; ； 二是使用单引号来转义，如：select E'明''天';

其他字符处理

• null值处理
  使用mysql拼接的话，需要使用到concat函数，当出现空值那么整个语句都会为空，所以需要对null值做处理；首先在获取字段值的时候将空值转为其他字符（mysql中不校验数据类型），再在最后拼接完整语句的时候将该字符替换回null值。
• 单引号处理
  如果字段中出现单引号不处理的话，那么语句在目标库中将无法使用，此处将字段中存在的一个单引号替换成两个。
• 双引号处理
  因为在PG中双引号只用来标记变量，在文本中无特殊含义，故而文本中不需要替换，只是在mysql中生成的语句需要注意到双引号的转义。
• 换行回车处理
  单条语句执行的时候不会有问题，但是当自动化批量执行的时候就可能出现问题，有的程序会把换行当做语句结束，所以此处也需要处理；将mysql字段中的char(10)替换成pg中的chr(10)，并且需要将该字符前后内容使用pg的连接符“||”进行连接，char(13)同理。
• blob字段处理
  如果是文本不处理问题不大，但如果是加密的数据，那么就会存在乱码问题，此处在mysql中将该字段内容转换成16进制字符，再在语句中拼接pg的decode函数对字符串按指定类型进行解码。
• 0x00字符处理
  在pg中，该不可见字符是无法入库的，故需要将该字符置空。

实现语句

1.因为用到group_concat函数，故而需先设定一个临时长度，否则默认是1024的字符长度。
2.pg中的语句必须带上模式名，该模式名应该是已知的。

set group_concat_max_len=102400;
select table_name
,concat("select \nreplace(replace(concat(\"insert into PG模式名.",table_name,"(" 
,group_concat(column_name order by ordinal_position)
,") values ( "  
,group_concat(
if(data_type regexp "blob"
	, " decode('\",hex(coalesce(" 
	, "'\"\n,coalesce(replace(replace(replace(replace("
)
	,column_name
,if(data_type regexp "blob"
	, ",\"null\")),\"','hex') "
	, ",\"'\",\"''\"),char(10),\"'||chr(10)||'\"),char(13),\"'||chr(13)||'\"),\"||''||\",\"||\"),\"null\") ,\"'"
) order by ordinal_position separator "," )
,");\") ,\"'null'\",\"null\"),char(0),'') sqlData \nfrom "
,table_name,";" ) insertCode
from information_schema.columns
where table_schema='mysql' 
group by table_name

效果截图

• mysql中生成“mysql生成postgresql插入语句”的语句
  

• mysql生成postgresql插入语句
  

• postgresql插入语句