将 T-SQL 转换为 MySQL

Convert T-SQL to MySQL

提问by Derek Adair

Is there an easy way to convert Transact-SQLto MySQL?

有没有一种简单的方法可以将Transact-SQL转换为 MySQL？

---

---

I am attempting to convert a database archive of stock ticker symbols and company names.

我正在尝试转换股票代码和公司名称的数据库存档。

回答by tylerl

The short answer: NO

简短的回答：不

The medium answer: MAYBE

中等答案：可能

The long answer: That depends on what's in your TSQL and how much time and effort you want to put into it.

答案很长：这取决于您的 TSQL 中有什么内容以及您想在其中投入多少时间和精力。

TSQL is not a subset of MySQL's dialect. So there exists some TSQL for which there is no MySQL conversion. However, the overlap between the two languages is pretty significant, and to a certain degree the conversion is just a matter of syntax.

TSQL 不是 MySQL 方言的子集。所以存在一些没有 MySQL 转换的 TSQL。然而，两种语言之间的重叠非常显着，在某种程度上，转换只是语法问题。

This isn't a new problem, and some people have tried to tackle it. A quick google search for "tsql to mysql" yields some promising results, notably this project here, which attempts to convert stored procedures from TSQL to MySQL:

这不是一个新问题，有些人已经尝试解决它。在谷歌上快速搜索“ tsql to mysql”会产生一些有希望的结果，特别是这里的这个项目，它试图将存储过程从 TSQL 转换为 MySQL：

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

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

This probably won't solve your problem completely, but it's at least a start.

这可能不会完全解决您的问题，但至少是一个开始。

回答by D. Kermott

This website converts to/from many sql database versions: http://www.sqlines.com/online

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

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

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

回答by Madagaga

I've just achieved a tsql script.

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

My solution is :

我的解决方案是：

1. export table informations (schema pk)
2. export table data
3. export FK

1. 导出表信息（schema pk）
2. 导出表数据
3. 出口FK

I'm not very proud of my code, but it worked for me.

我对我的代码不是很自豪，但它对我有用。

4 files wich are in the same folder:

4个文件在同一个文件夹中：

Batch wich call sqlcmd

批量调用 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" 

then tsql script to export table schema mysql_export_table.sql

然后 tsql 脚本导出表模式 mysql_export_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;

then script for data mysql_export_data.sql

然后为数据 mysql_export_data.sql 编写脚本

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;

Finally FK script

最后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;

I know, i know... it's very ugly ...

我知道，我知道……它很丑……

The goal of this script is not to convert TSQL to Mysql but to export database from MSSQL to Mysql

此脚本的目标不是将 TSQL 转换为 Mysql，而是将数据库从 MSSQL 导出到 Mysql

On the table result you'll have to execute a regex replace (notepad++) replace ",\r\n\r\n)" by "\r\n\r\n)"

在表结果上，您必须执行正则表达式替换 (notepad++) 将 ",\r\n\r\n)" 替换为 "\r\n\r\n)"

On the data result replace ",\r\n\r\n/" by ";\r\n\r\n/"

在数据结果上将 ",\r\n\r\n/ "替换为 ";\r\n\r\n/"

Execution order : Table -> Data -> FK

执行顺序：表 -> 数据 -> FK

回答by Madagaga

How about using SQL Server Linked Servers ? You can SELECT, INSERT, UPDATE and DELETE data from MySQL using TSQL

如何使用 SQL Server 链接服务器？您可以使用 TSQL 从 MySQL 中选择、插入、更新和删除数据

回答by Mawg says reinstate Monica

Are the others say, it depends how long your particular piece of string is.

其他人是否说，这取决于您的特定字符串有多长。

But, I would suggest that you do NOT want to convert Transact-SQL to MySQL.

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

If you think about it, I think that you will find that you want to convert it to ODBC.

如果你仔细想想，我想你会发现你想把它转换成ODBC。

And then next year, when the company wants you to move it to Oracle, or Access ...

然后明年，当公司希望您将其转移到 Oracle 或 Access 时……