SQL SERVER 自动生成 MySQL 表结构及索引 的建表SQL
SQL SERVER的表结构及索引转换为MySQL的表结构及索引，其实在很多第三方工具中有提供，比如navicat、sqlyog等，但是，在处理某些数据类型、默认值及索引转换的时候，总有些不尽人意并且需要安装软件，懒人开始想法子，所以基于SQL SERVER，写了一个存储过程，可以根据表名直接转换为MySQL的建表建索引的SQL脚本（针对 MySQL Innodb引擎）。目前不支持分区表的分区配置及区域数据类型的转换。
建表的SQL中，主要在数据类型转换、主键及索引的处理。
1 数据类型转换
数据类型转换表详见下表，这些数据类型的转换目前已测试过，均可正常使用。
但是注意两类数据库存储数据的一些差异，看下能否接受：
- SQL SERVER中的datetime，保留到微秒(秒后小数点3位)，而mysql仅保留到秒，转换后是否会影响业务，如果影响，需要新增一个字段专门来存储微秒或者毫秒，虽然mysql中没有时间数据类型的精度到达微秒或者毫秒，但是mysql提供对微秒的相关处理函数microsecond、extract跟date_format。
- MySQL使用tinyint代替SQL SERVER的bit
- SQL SERVER的money类型使用decimal替代
- timestamp的转换，SQL SERVER中是一个16进制的数字，代表时间戳，每次修改都会数值都会变大。
- 从功能考虑，转换为mysql的时候，处理为timestamp的数据类型，默认值为CURRENT_TIMESTAMP，行发生修改则定时修改这一列数据，如果这样转换，那么在SQL SERVER导入数据的时候，就要相应处理。（本文存储过程默认这么处理）
- 从数据考虑，转换为mysql的时候，处理为bigint的数据类型（修改存储过程case when b.name = 'timestamp' then ' timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ' 为case when b.name = 'timestamp' then ' bigint ' ）
- 自增处理，mysql的自增步长跟增量值是整个实例统一的，不能每个表格动态修改，所以这里在转化的过程中，为auto_increment，根据实例的设置来处理
| ID | SQL SERVER | MySQL | Description |
| 1 | bigint | bigint | |
| 2 | binary | binary | |
| 3 | bit | tinyint | SQL SERVER的bit类型，对于零，识别为False，非零值识别为True。 MySQL中没有指定的bool类型，一般都使用tinyint来代替 |
| 4 | char | char | |
| 5 | date | date | |
| 6 | datetime | datetime | 注意，mssql的保留到微秒(秒后小数点3位)，而mysql仅保留到秒 |
| 7 | datetime2 | datetime | 注意，mssql的保留到微秒(秒后小数点7位)，而mysql仅保留到秒 |
| 8 | datetimeoffset | datetime | 注意，mssql的保留时区，这个需要程序自己转换 mssql的保留到微秒(秒后小数点7位)，而mysql仅保留到秒 |
| 9 | decimal | decimal | |
| 10 | float | float | |
| 11 | int | int | |
| 12 | money | float | 默认转换为decimal(19,4) |
| 13 | nchar | char | SQL SERVER转MySQL按正常字节数转就可以 |
| 14 | ntext | text | |
| 15 | numeric | decimal | |
| 16 | nvarchar | varchar | |
| 17 | real | float | |
| 18 | smalldatetime | datetime | |
| 19 | smallint | smallint | |
| 20 | smallmoney | float | 默认转换为decimal(10,4) |
| 21 | text | text | |
| 22 | time | time | 注意，mssql的保留到秒后小数点8位，而mysql仅保留到秒 |
| 23 | timestamp | timestamp | 注意，mssql的行时间戳，处理为mysql的 timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP 。这会对后面导数据造成影响，从功能方面来看，可以按照上文转换；如果从数据来看，若需要转换16进制的文字存储到mysql中，则这里设置为bigint即可，在表格中的临时表中设置。 |
| 24 | tinyint | tinyint | |
| 25 | uniqueidentifier | varchar(40) | 对应mysql的UUID(),设置为文本类型即可。 |
| 26 | varbinary | varbinary | |
| 27 | varchar | varchar | |
| 28 | xml | text | mysql不支持xml，修改为text |
2 主键处理
MySQL不支持非主键的聚集索引，也就是聚集索引则是主键。故在转换的过程中，主键是根据SQL SERVER表格中的聚集索引来转换的。
--SQL SERVER根据聚集索引的列情况来创建mysql的主键 SELECT col_name(i.object_id,ik.column_id) pk_col FROM sys.indexes i JOIN sys.index_columns ik ON i.index_id=ik.index_id and i.object_id=ik.object_id WHERE i.type=1 and i.object_id=object_id('tb') ORDER BY key_ordinal
3 索引添加
由于聚集索引已处理添加为主键，在建表的SQL中已判断，这里则只处理非聚集索引。
处理过程中注意：
- MySQL不支持INCLUDE选项的包含索引，所以在处理的过程中，INCLUDE列添加到索引列中
- MySQL 不支持WHERE选项的过滤索引，所以在处理的过程中，WHERE选项去除
- 索引名字处理：包含列1-2个的，直接IX_表名，超过3个列的，取每列前3个字符，整个索引名长度不超过64个字符，超过截取前64个字符
4 测试
存储过程 [p_tb_mssqltomysql] 仅含一个参数 @tbsql，用于存储表格名字，多个表格名中间有逗号隔开，不要有空格或者其他符号。
这里，尝试创建一个新表来测试。
创建表格及对应索引信息测试如下：
1 CREATE TABLE tbtest( 2 id INT IDENTITY(1,1) NOT NULL , 3 name NVARCHAR(50) NOT NULL, 4 phone VARCHAR(11) NOT NULL, 5 age int default 99 , 6 birthday datetime default getdate(), 7 addresss text, 8 monyes money default 123456789012345.1234, 9 smonyes smallmoney, 10 nums int default 2, 11 moneys money, 12 smo smallmoney, 13 curversion timestamp 14 ) 15 16 ALTER TABLE tbtest ADD CONSTRAINT PK_tbtest PRIMARY KEY (ID,phone); 17 CREATE INDEX IX_NAME ON tbtest(NAME); 18 CREATE INDEX IX_phone_age ON tbtest(phone,age); 19 CREATE INDEX IX_nums ON tbtest(nums) WHERE nums>2; 20 CREATE INDEX IX_birthday ON tbtest(birthday) include (name,phone);
执行存储过程转化：
1 exec p_tb_mssqltomysql 'tbtest' 2 3 4 CREATE TABLE tbtest(id int not null auto_increment 5 ,name varchar(50) not null 6 ,phone varchar(11) not null 7 ,age int null 8 ,birthday datetime null 9 ,addresss text null 10 ,monyes decimal(19,4) null 11 ,smonyes decimal(10,4) null 12 ,nums int null 13 ,moneys decimal(19,4) null 14 ,smo decimal(10,4) null 15 ,curversion timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP not null 16 , primary key (id,phone) ); 17 CREATE INDEX IX_name ON tbtest( name ); 18 CREATE INDEX IX_phone_age ON tbtest( phone,age ); 19 CREATE INDEX IX_nums ON tbtest( nums ); 20 CREATE INDEX IX_bir_nam_pho ON tbtest( birthday,name,phone );
在mysql中创建正常，查看mysql的建表脚本如下：
5 存储过程脚本
SQL SERVER转换MySQL表结构及索引的脚本如下：
-- ============================================= -- Author: suxinyu -- Create date: 20170612 -- Description: 根据表名自动把表格的所有建表DDL SQL转化为 MySQL的建表SQL，不包含分区表，不处理区域数据类型；执行过程中，需要把存储过程建立在需要导出的数据库中。 -- Example: exec p_tb_mssqltomysql 'orders,ordernums,channels' -- ============================================= --存储过程建立在需要导出表结构的DB上
USE db
GO
CREATE PROC [dbo].[p_tb_mssqltomysql] @tbsql varchar(1000) AS SET NOCOUNT ON ; --处理tablename的字符串，把tablename字符串分割成每一行存储进入表变量中 DECLARE @tab_tablename table(tbname varchar(100)) DECLARE @tbname varchar(100) INSERT INTO @tab_tablename(tbname) SELECT SUBSTRING(@tbsql,NUMBER,CHARINDEX(',',@tbsql+',',NUMBER)-number) FROM master.dbo.spt_values WHERE TYPE='P' AND number>0 AND SUBSTRING(','+@tbsql,number,1)=',' --把mysql跟mssql的数据类型对应起来存储 --空间数据类型不处理 --money类型处理为float --timestamp处理为 timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP DECLARE @tbtype table(mssql varchar(20),mysql varchar(20)) INSERT INTO @tbtype(mssql,mysql) values( 'bigint','bigint'),('binary','binary'),('binary','binary'),('bit','tinyint'),('char','char'),('date','date'),('datetime','datetime'),('datetime2','datetime'),('datetimeoffset','datetime'),('decimal','decimal'),('float','float'),('int','int'),('money','decimal'),('nchar','char'),('ntext','text'),('numeric','decimal'),('nvarchar','varchar'),('real','float'),('smalldatetime','datetime'),('smallint','smallint'),('smallmoney','decimal'),('text','text'),('time','time'),('timestamp','timestamp'),('tinyint','tinyint'),('uniqueidentifier','varchar(40)'),('varbinary','varbinary'),('varchar','varchar'),('xml','text') DECLARE @tb_exec_sql table(tbname varchar(100),sql nvarchar(max),indexs nvarchar(max)) DECLARE @indexs_sql nvarchar(max) --转化表格SQL DECLARE NAME CURSOR FOR SELECT tbname FROM @tab_tablename OPEN NAME FETCH NEXT FROM name INTO @tbname WHILE @@FETCH_STATUS =0 BEGIN ;WITH data AS ( SELECT case when b.is_unique=1 then ' UNIQUE ' else ' ' end is_unique, OBJECT_NAME(A.OBJECT_ID) obj_name, COL_NAME(A.object_id,A.column_id) colname, SUBSTRING(COL_NAME(A.object_id,A.column_id),1,3) col_short, is_included_column, index_column_id, a.index_id, A.OBJECT_ID FROM SYS.index_columns A INNER JOIN SYS.INDEXES B ON A.OBJECT_ID=B.OBJECT_ID AND A.index_id=B.index_id WHERE b.type!=1 and OBJECT_NAME(A.OBJECT_ID)=@tbname ) SELECT @indexs_sql= REPLACE( (STUFF( ( SELECT ' CREATE ' + a.is_unique +' INDEX ' + CASE WHEN COUNT(*) >=3 THEN SUBSTRING(('IX_'+stuff((SELECT '_'+col_short FROM data b where a.object_id=b.object_id and a.index_id=b.index_id FOR XML PATH('')),1,1,'')),1,64) ELSE 'IX_'+stuff((SELECT '_'+colname FROM data b where a.object_id=b.object_id and a.index_id=b.index_id FOR XML PATH('')),1,1,'') END +' ON ' + a.obj_name +'( ' + stuff((SELECT ','+colname FROM data b where a.object_id=b.object_id and a.index_id=b.index_id FOR XML PATH('')),1,1,'') +' ); ' FROM data a GROUP BY a.is_unique,a.obj_name,a.object_id,a.index_id ORDER BY a.object_id,a.index_id FOR XML PATH('') ),1,1,'') ),'
','') INSERT INTO @tb_exec_sql(tbname,indexs,sql) SELECT @tbname,@indexs_sql, 'CREATE TABLE '+@tbname+'('+ REPLACE( STUFF( ( SELECT ','+a.name + case when b.name = 'timestamp' then ' timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ' when b.name = 'uniqueidentifier' then ' varchar(40) ' when b.name in ('char','nchar','nvarchar','varbinary','varchar') then ( case when a.length<0 then ' text ' else ' '+c.mysql+'('+ (case when b.name like 'n%' then cast(a.length/2 as varchar(10)) else cast(a.length as varchar(10)) end )+')' end ) when b.name in ('decimal','float','money','numeric','smallmoney') then ' '+c.mysql+'('+ cast(a.prec as varchar(10)) +','+ cast(a.scale as varchar(10)) +') ' else ' '+c.mysql+' ' end + case when a.isnullable=0 then ' not null ' else ' null ' end + case when COLUMNPROPERTY( A.ID,A.NAME,'ISIDENTITY')=1 then ' auto_increment ' else '' end + case when a.length<0 or b.name in ('text') then ' ' when e.text like ' ((%' then ' default '+substring(e.text,3,len(e.text)-4) when e.text like ' (''%' then ' default '+substring(e.text,2,len(e.text)-2) else ' ' end + ISNULL(' comment "'+cast(g.value as varchar(1000))+'" ',' ') FROM sys.syscolumns A LEFT JOIN sys.systypes B ON A.XUSERTYPE=B.XUSERTYPE LEFT JOIN @tbtype C ON b.name collate Chinese_PRC_CI_AS = c.mssql LEFT JOIN sys.sysobjects D ON A.ID=D.ID AND D.XTYPE='U' AND D.NAME<>'DTPROPERTIES' LEFT JOIN sys.syscomments E ON A.CDEFAULT=E.ID LEFT JOIN sys.extended_properties G ON A.ID=G.major_id AND A.COLID=G.minor_id WHERE D.NAME =@tbname order by a.colid FOR XML PATH('') ),1,1,'') ,'
','') + ISNULL( ( SELECT ', primary key ('+STUFF( ( SELECT ','+col_name(i.object_id,ik.column_id) FROM sys.indexes i JOIN sys.index_columns ik ON i.index_id=ik.index_id and i.object_id=ik.object_id WHERE i.type=1 and i.object_id=object_id(@tbname) ORDER BY key_ordinal FOR XML PATH('') ),1,1,'') +') ' ) ,'') + ')' + ISNULL( ( SELECT ' COMMENT "'+CAST(value AS VARCHAR(1000))+'"; ' FROM sys.extended_properties where major_id=object_id(@tbname) and minor_id=0 ),';') FETCH NEXT FROM NAME INTO @tbname END CLOSE NAME DEALLOCATE NAME SELECT * FROM @tb_exec_sql
如果转载，请注明博文来源： www.cnblogs.com/xinysu/ ，版权归 博客园 苏家小萝卜 所有。望各位支持！
