如何将 SQL Server 数据库转换为 MySQL 数据库
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7279303/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to convert SQL Server database to MySQL database
提问by Ishaq G
I have created an website using ASP.Net, a table using SQL Server, and I have published it to a provider.
我使用 ASP.Net 创建了一个网站，使用 SQL Server 创建了一个表，并且我已将其发布给提供者。
What I want is to convert the MSSQL.sqlfile to Mysql.sqlfile. Is there any free software, or some code to convert it to SQL Server to MySQL ?
我想要的是将MSSQL.sql文件转换为Mysql.sql文件。是否有任何免费软件或一些代码可以将其转换为 SQL Server 到 MySQL？
It would be much appreciated if someone could help me.
如果有人能帮助我，我将不胜感激。
回答by ShaneC
If you use phpMyAdminto manage your MySQL (which almost all web hosts use) you can simply import the file in compatibility modefor MSSQL.
如果您使用phpMyAdmin来管理您的 MySQL（几乎所有 Web 主机都使用），您可以简单地以 MSSQL 的兼容模式导入该文件。
To do this, go to Import -> Choose your file -> Then select "MSSQL" from SQL compatibility mode:under Format specific options.
为此，请转至导入 -> 选择您的文件 -> 然后从SQL 兼容模式中选择“MSSQL” ：在特定格式选项下。
If you don't have phpMyAdmin already installed, you can download it from the site I linked above for free. The instructions on their website are very clear for installation.
如果您还没有安装 phpMyAdmin，您可以从我上面链接的站点免费下载它。他们网站上的说明非常清楚安装。
After import, if you want to save a copy of the SQL file in MySQL's syntax simply use the Exportfeature.
导入后，如果您想以 MySQL 的语法保存 SQL 文件的副本，只需使用导出功能。
回答by ruchit
When migrating databases from MS SQL to MySQL server it is often necessary to translate MS SQL queries according to MySQL syntax as well. Syntax of SQL queries in MS SQL and MySQL are similar but not identical. This article discovers 10 most popular differences between MS SQL and MySQL syntax. The target audience for this guide should have general database management knowledge and experience in composing SQL queries.
将数据库从 MS SQL 迁移到 MySQL 服务器时，通常还需要根据 MySQL 语法转换 MS SQL 查询。MS SQL 和 MySQL 中 SQL 查询的语法相似但不完全相同。本文发现了 MS SQL 和 MySQL 语法之间 10 个最流行的差异。本指南的目标读者应该具有编写 SQL 查询的一般数据库管理知识和经验。
Sometime MS SQL table or column names are enclosed in square brackets in queries (e.g. if contains spaces or for some other reasons). MySQL does not allow square brackets around table of column names, they all must be replaced by ` symbol or cut off: [object]-> `object`.
有时 MS SQL 表名或列名在查询中被括在方括号中（例如，如果包含空格或出于某些其他原因）。MySQL 不允许在列名表周围使用方括号，它们都必须替换为 ` 符号或截断：[object]-> `object`。
MS SQL provides effective solution to avoid naming objects conflict and to manage user permissions on data access. This is schema, a logic container used to group and categorize objects inside the single database. When using schema the full name of database object in query may look like database.schema.object. However, there is no such semantic in MySQL, so all schema names must be cut off from queries.
MS SQL 提供了有效的解决方案来避免命名对象冲突和管理用户对数据访问的权限。这是模式，一种逻辑容器，用于对单个数据库内的对象进行分组和分类。使用模式时，查询中数据库对象的全名可能类似于 database.schema.object。但是，MySQL 中没有这样的语义，因此必须从查询中切断所有模式名称。
CONVERT() function is used to convert an expression of one data type to another in MS SQL. In MySQL CONVERT() function converts text data between different character sets. However, there is equivalent function CAST(), so every occurrence of convert(type, expression) in MS SQL query must be replaced by cast(expression AS type) in MySQL query. LEN() function returns length of string expression in MS SQL. MySQL equivalent for this function is LENGTH(). MS SQL function DATEADD() adds interval to the specified part of the date. MySQL operator '+' can do the same as follows:
CONVERT() 函数用于在 MS SQL 中将一种数据类型的表达式转换为另一种数据类型。在 MySQL CONVERT() 函数中，在不同字符集之间转换文本数据。但是，有等效的函数 CAST()，因此在 MS SQL 查询中每次出现 convert(type, expression) 都必须在 MySQL 查询中替换为 cast(expression AS type)。LEN() 函数返回 MS SQL 中字符串表达式的长度。这个函数的 MySQL 等价物是 LENGTH()。MS SQL 函数 DATEADD() 将时间间隔添加到日期的指定部分。MySQL 运算符“+”可以执行以下操作：
DATEADD(year, 1, $date$) -> $date$ + interval 1 year
DATEADD(month, 1, $date$) -> $date$ + interval 1 month
DATEADD(day, 1, $date$) -> $date$ + interval 1 day
where $date$ is an expression of DATE type.
Microsoft SQL and MySQL have different sets of date processing functions, although most of them can be replicated as follows:
Microsoft SQL 和 MySQL 具有不同的日期处理函数集，尽管它们中的大多数可以复制如下：
DATENAME(month, $date$) -> DATE_FORMAT($date$, '%M') or MONTHNAME(expression)
DATENAME(weekday, $date$) -> DATE_FORMAT($date$, '%W') or DAYNAME(expression)
DATEPART(year, $date$) -> DATE_FORMAT($date$, '%Y')
DATEPART(month, $date$) -> DATE_FORMAT($date$, '%m')
DATEPART(day, $date$) -> DATE_FORMAT($date$, '0')
GETDATE() -> NOW()
GETUTCDATE() -> UTC_TIMESTAMP()
where $date$ is an expression of DATE type.
其中 $date$ 是 DATE 类型的表达式。
MS SQL operator '+' allows to concatenate strings like this: 'string1' + 'string2'. In MySQL such expressions must be replaced by CONCAT('string1', 'string2'). MS SQL function CONTAINS(expression, template) searches for matches of template inside expression. MySQL has operator LIKE that implements the same semantics: expression LIKE %template% If MS SQL query contains 'TOP (100) PERCENT' pattern just cut it off when composing MySQL query. If there is another percentage amount in that pattern, it can be replace by the following code in MySQL (works in MySQL 5.0.7 and higher):
MS SQL 运算符“+”允许像这样连接字符串：“string1”+“string2”。在 MySQL 中，此类表达式必须替换为 CONCAT('string1', 'string2')。MS SQL 函数 CONTAINS(expression, template) 在表达式中搜索模板的匹配项。MySQL 具有实现相同语义的运算符 LIKE：表达式 LIKE %template% 如果 MS SQL 查询包含 'TOP (100) PERCENT' 模式，则在编写 MySQL 查询时将其切断。如果该模式中有另一个百分比量，则可以用 MySQL 中的以下代码替换它（适用于 MySQL 5.0.7 及更高版本）：
SET @amount =(SELECT COUNT(*) FROM %table name%) * %percentage% / 10;
PREPARE STMT FROM '%original query% FROM %table name% LIMIT ?';
EXECUTE STMT USING @amount;
Syntax of JOIN constructions are very similar in MS SQL and MySQL. The only difference is that MS SQL keyword WHERE is replaced by ON in MySQL. For example:
JOIN 结构的语法在 MS SQL 和 MySQL 中非常相似。唯一的区别是 MS SQL 关键字 WHERE 在 MySQL 中被 ON 替换。例如：
... table1 CROSS JOIN table2 WHERE condition
must be translated into
必须翻译成
... table1 CROSS JOIN table2 ON condition
回答by valueweaver
PHPmyadmin option is nice for doing this job. But sometimes you will see errors while converting. We actually studied the DB structure of the MSSQL and wrote our own mysql statements based on it and did our unit testing and learned few things. So apart from conversion if you also want to do hands-on this is a good approach.
PHPmyadmin 选项非常适合做这项工作。但有时您会在转换时看到错误。我们实际上研究了 MSSQL 的 DB 结构，并基于它编写了我们自己的 mysql 语句，并进行了我们的单元测试，学到了一些东西。因此，除了转换之外，如果您还想亲自动手，这是一个很好的方法。
