将T-SQL转换为MySQL

简而言之: 不

答案很长:

TSQL不是MySQL方言的子集。因此,存在一些没有MySQL转换的TSQL。然而,这两种语言之间的重叠是相当重要的,在某种程度上,转换只是一个语法问题。

这不是一个新问题,有些人已经尝试解决它。在谷歌上快速搜索“ tsql to mysql

http://sourceforge.net/projects/tsql2mysql/

这也许不能完全解决你的问题,但至少是个开始。

此网站转换为许多sql数据库版本: http://www.sqlines.com/online

TSQL、MySql、Oracle、DB2、Sybase、Informix、Hive、MariaDB、PostgreSQL、RedShift、TeraData、Greenplum、Netezza

其他人说,这取决于你的一根绳子有多长。

但是,我建议您不要将Transact-SQL转换为MySQL。

如果你仔细想想,我想你会发现你想把它变成 ODBC .

然后明年,当公司希望你把它转移到甲骨文,或访问。。。

我刚刚实现了一个tsql脚本。

我的解决方案是:

1. 导出表信息(schema pk)
2. 导出表数据
3. 导出FK

我对我的代码不是很自豪,但它对我起了作用。

同一文件夹中有4个文件:

批处理调用sqlcmd

@echo off

set host= (local)
set schema=database
set user=user
set pass=pass

cd %cd%
rem tables 
SQLCMD -S %host% -d %schema% -U %user% -P %pass% -s "" -h-1 -W -i "%CD%\mysql_export_table.sql" -o "%CD%\%schema%_tables.sql" 
rem data
SQLCMD -S %host% -d %schema% -U %user% -P %pass% -s "" -h-1 -W -i "%CD%\mysql_export_data.sql" -o "%CD%\%schema%_data.sql" 
rem fk
SQLCMD -S %host% -d %schema% -U %user% -P %pass% -s "" -h-1 -W -i "%CD%\mysql_export_fk.sql" -o "%CD%\%schema%_fk.sql" 

然后用tsql脚本导出表schema mysql\u export\u table.sql

SET NOCOUNT ON;
SET QUOTED_IDENTIFIER ON;
DECLARE @table_name as varchar(max)



DECLARE view_cursor CURSOR FOR 
SELECT Table_name FROM information_schema.tables where TABLE_TYPE = 'BASE TABLE' and table_name not like 'sys%'

OPEN view_cursor

FETCH NEXT FROM view_cursor 
INTO @table_name

WHILE @@FETCH_STATUS = 0
BEGIN


     select ''
     select '/*** TABLE '+@table_name+' ***/ '
     select 'DROP TABLE IF EXISTS ' + QUOTENAME(@table_name, '`') + ';'
     select ''
     select 'CREATE TABLE ' + QUOTENAME(@table_name, '`') + ' ('

-- column declaration 

     select  
     CHAR(9)
+ QUOTENAME(Column_Name, '`') + ' ' +
DATA_TYPE
+
coalesce( '(' + cast(coalesce(replace(CHARACTER_MAXIMUM_LENGTH, -1, 2500), null) as varchar) + ')', '')
+ ' ' +
case IS_NULLABLE WHEN 'NO' then 'NOT ' else '' end + 'NULL'
+ ' ' +
case when COLUMNPROPERTY(object_id(TABLE_NAME), COLUMN_NAME, 'IsIdentity') = 1 then 'AUTO_INCREMENT' else '' end
--coalesce( 'DEFAULT ' + replace(replace(replace(COLUMN_DEFAULT, '(', ''), ')', ''), 'getdate', null), '')
+','
FROM information_schema.COLUMNS where TABLE_NAME = @table_name
-- PK
select  coalesce('PRIMARY KEY (' +STUFF((
SELECT distinct ', ' + QUOTENAME(Col.Column_Name,'`') from
    INFORMATION_SCHEMA.TABLE_CONSTRAINTS Tab inner join  
    INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE Col  on Col.Constraint_Name = Tab.Constraint_Name AND Col.Table_Name = Tab.Table_Name
WHERE        
    Constraint_Type = 'PRIMARY KEY '
    AND Col.Table_Name = @table_name
    FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)') , 1, 1, '')+ ')', '') 

select ') Engine InnoDB;'

    FETCH NEXT FROM view_cursor 
    INTO @table_name
END 
CLOSE view_cursor;
DEALLOCATE view_cursor;

SET NOCOUNT ON;
SET QUOTED_IDENTIFIER ON;
DECLARE @table_name as varchar(max)
declare @column_names as varchar(max)


DECLARE view_cursor CURSOR FOR 
SELECT Table_name FROM information_schema.tables where TABLE_TYPE = 'BASE TABLE' and table_name not like 'sys%'

OPEN view_cursor

FETCH NEXT FROM view_cursor 
INTO @table_name

WHILE @@FETCH_STATUS = 0
BEGIN
     select ''
     select '/*** TABLE '+@table_name+' ***/ '

     select @column_names = STUFF(( SELECT ', ' + QUOTENAME(Column_Name, '`') from INFORMATION_SCHEMA.COLUMNS WHERE  Table_Name = @table_name  ORDER BY ORDINAL_POSITION FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)') , 1, 1, '')

     select 'REPLACE INTO '+ QUOTENAME(@table_name, '`') +'('+ @column_names+ ') VALUES '
     select @column_names = 'SELECT DISTINCT ''('+ STUFF(( SELECT ', '','''''' + coalesce(replace(cast(' + QUOTENAME(Column_Name)  +' as varchar(200)), '''''''',''''), '''') + ''''''''' from INFORMATION_SCHEMA.COLUMNS WHERE  Table_Name = @table_name  ORDER BY ORDINAL_POSITION FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)') , 1, 4, '') + '+''),'' FROM ' + QUOTENAME(@table_name)
     exec (@column_names)

    FETCH NEXT FROM view_cursor 
    INTO @table_name
END 
CLOSE view_cursor;
DEALLOCATE view_cursor;

最后是FK脚本

SET NOCOUNT ON;
SET QUOTED_IDENTIFIER ON;
-- FK
-- foreign keys
SELECT 
     'ALTER TABLE',
     ' '+ QUOTENAME(OBJECT_NAME(fkcol.[object_id]), '`'),
--ADD CONSTRAINT `recherche_ibfk_1` FOREIGN KEY (`notaire_compte_id`) REFERENCES `notaire_compte` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;
    ' ADD CONSTRAINT',
    ' ' + QUOTENAME(fk.name, '`'), 
    ' FOREIGN KEY',
    ' (' + QUOTENAME(fkcol.name, '`') +')',
    ' REFERENCES',    
    ' ' + QUOTENAME(OBJECT_NAME(pkcol.[object_id]), '`'),
    ' (' + QUOTENAME(pkcol.name, '`') + ');',
    CHAR(13)

FROM sys.foreign_keys AS fk
INNER JOIN sys.foreign_key_columns AS fkc
ON fk.[object_id] = fkc.constraint_object_id
INNER JOIN sys.columns AS fkcol
ON fkc.parent_object_id = fkcol.[object_id]
AND fkc.parent_column_id = fkcol.column_id
INNER JOIN sys.columns AS pkcol
ON fkc.referenced_object_id = pkcol.[object_id]
AND fkc.referenced_column_id = pkcol.column_id
ORDER BY fkc.constraint_column_id;

我知道,我知道。。。很难看。。。

这个脚本的目标不是将TSQL转换成Mysql,而是将数据库从MSSQL导出到Mysql

将“,\r\n\r\n)”替换为“\r\n\r\n)”

在数据结果中,替换“,\r\n\r\n/ “通过”\r\n\r\n/ "

执行顺序:表->数据->FK公司