MySQL 和 SQL Server 之间的差异
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10616/
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
Differences between MySQL and SQL Server
提问by sestocker
I'm an ASP.NETdeveloper who has used Microsoft SQL Serverfor all my database needs (both at work and for personal projects).
我是一名ASP.NET开发人员，他Microsoft SQL Server满足了我所有的数据库需求（在工作中和个人项目中）。
I am considering trying out the LAMPstack for some of my personal projects.
我正在考虑为我的一些个人项目试用LAMP堆栈。
What are some of the main differences between MySQLand SQL Server? Is using stored proceduresa common practice in MySQL?
有哪些之间的主要差异MySQL和SQL Server？使用存储过程是一种常见的做法MySQL吗？
Any advice or resources you'd recommend to help me with the switch?
您有什么建议或资源可以推荐来帮助我进行转换吗？
To those who have experience with both, are there any missing features from MySQL?
对于那些对两者都有经验的人，是否有任何缺失的功能MySQL？
采纳答案by Jeff Atwood
One thing you have to watch out for is the fairly severe differences in the way SQL Server and MySQL implement the SQL syntax.
您必须注意的一件事是 SQL Server 和 MySQL 在实现 SQL 语法的方式上存在相当严重的差异。
Here's a nice Comparison of Different SQL Implementations.
这是不同 SQL 实现的一个很好的比较。
For example, take a look at the top-n section. In MySQL:
例如，看看 top-n 部分。在 MySQL 中：
SELECT age
FROM person
ORDER BY age ASC
LIMIT 1 OFFSET 2
In SQL Server (T-SQL):
在 SQL Server (T-SQL) 中：
SELECT TOP 3 WITH TIES *
FROM person
ORDER BY age ASC
回答by Remy
Lots of comments here sound more like religious arguments than real life statements. I've worked for years with both MySQL and MSSQL and both are good products. I would choose MySQL mainly based on the environment that you are working on. Most open source projects use MySQL, so if you go into that direction MySQL is your choice. If you develop something with .Net I would choose MSSQL, not because it's much better, but just cause that is what most people use. I'm actually currently on a Project that uses ASP.NET with MySQL and C#. It works perfectly fine.
这里的许多评论听起来更像是宗教争论，而不是现实生活中的陈述。我已经使用 MySQL 和 MSSQL 工作了多年，它们都是很好的产品。我会主要根据您正在使用的环境来选择 MySQL。大多数开源项目都使用 MySQL，所以如果你朝这个方向发展，MySQL 是你的选择。如果你用 .Net 开发一些东西，我会选择 MSSQL，不是因为它更好，而是因为这是大多数人使用的。我实际上目前正在使用 ASP.NET 与 MySQL 和 C# 的项目。它工作得很好。
回答by ely
I can't believe that no one mentioned that MySQL doesn't support Common Table Expressions (CTE) / "with" statements. It's a pretty annoying difference.
我不敢相信没有人提到 MySQL 不支持 Common Table Expressions (CTE) / "with" 语句。这是一个非常烦人的差异。
回答by Jon Galloway
MySQL is more likely to have database corruption issues, and it doesn't fix them automatically when they happen. I've worked with MSSQL since version 6.5 and don't remember a database corruption issue taking the database offline. The few times I've worked with MySQL in a production environment, a database corruption issue took the entire database offline until we ran the magic "please fix my corrupted index" thing from the commandline.
MySQL 更有可能出现数据库损坏问题，并且在它们发生时它不会自动修复它们。我从 6.5 版开始使用 MSSQL，不记得使数据库脱机的数据库损坏问题。我在生产环境中使用 MySQL 的几次，数据库损坏问题使整个数据库脱机，直到我们从命令行运行神奇的“请修复我损坏的索引”的事情。
MSSQL's transaction and journaling system, in my experience, handles just about anything - including a power cycle or hardware failure - without database corruption, and if something gets messed up it fixes it automatically.
根据我的经验，MSSQL 的事务和日志系统几乎可以处理任何事情——包括电源循环或硬件故障——而不会损坏数据库，如果出现问题，它会自动修复。
This has been my experience, and I'd be happy to hear that this has been fixed or we were doing something wrong.
这是我的经验，我很高兴听到这个问题已经解决或者我们做错了什么。
http://dev.mysql.com/doc/refman/6.0/en/corrupted-myisam-tables.html
http://dev.mysql.com/doc/refman/6.0/en/corrupted-myisam-tables.html
http://www.google.com/search?q=site%3Abugs.mysql.com+index+corruption
http://www.google.com/search?q=site%3Abugs.mysql.com+index+corruption
回答by Abdu
Frankly, I can't find a single reason to use MySQL rather than MSSQL. The issue before used to be cost but SQL Server 2005 Express is free and there are lots of web hosting companies which offer full hosting with sql server for less than $5.00 a month.
坦率地说，我找不到使用 MySQL 而不是 MSSQL 的单一理由。以前的问题是成本问题，但 SQL Server 2005 Express 是免费的，并且有许多网络托管公司以每月不到 5.00 美元的价格提供带有 sql server 的完整托管服务。
MSSQL is easier to use and has many features which do not exist in MySQL.
MSSQL 更易于使用，并且具有许多 MySQL 不存在的功能。
回答by dkretz
Everything in MySQL seems to be done closer to the metal than in MSSQL, And the documentation treats it that way. Especially for optimization, you'll need to understand how indexes, system configuration, and the optimizer interact under various circumstances.
MySQL 中的一切似乎都比 MSSQL 中的更接近金属，并且文档是这样处理的。特别是对于优化，您需要了解索引、系统配置和优化器在各种情况下是如何交互的。
The "optimizer" is more a parser. In MSSQL your query plan is often a surprise (usually good, sometimes not). In MySQL, it pretty much does what you asked it to do, the way you expected it to. Which means you yourself need to have a deep understanding of the various ways it might be done.
“优化器”更像是一个解析器。在 MSSQL 中，您的查询计划经常出人意料（通常很好，有时不好）。在 MySQL 中，它几乎按照您的要求完成了您的要求。这意味着您自己需要深入了解可能完成的各种方式。
Not built around a good TRANSACTION model (default MyISAM engine).
不是围绕良好的 TRANSACTION 模型（默认 MyISAM 引擎）构建的。
File-system setup is your problem.
文件系统设置是您的问题。
All the database configuration is your problem - especially various cache sizes.
所有的数据库配置都是你的问题——尤其是各种缓存大小。
Sometimes it seems best to think of it as an ad-hoc, glorified isam. Codd and Date don't carry much weight here. They would say it with no embarrassment.
有时，最好将其视为一种临时的、美化的伊斯兰教义。Codd 和 Date 在这里没有太大影响。他们会毫不客气地说出来。
回答by Jon Limjap
I think one of the major things to watch out for is that versions prior to MySQL 5.0 did not have views, triggers, and stored procedures.
我认为需要注意的主要事情之一是 MySQL 5.0 之前的版本没有视图、触发器和存储过程。
More of this is explained in the MySQL 5.0 Download page.
MySQL 5.0 下载页面中对此进行了更多解释。
回答by Cebjyre
@abdu
@阿卜杜
The main thing I've found that MySQL has over MSSQL is timezone support - the ability to nicely change between timezones, respecting daylight savings is fantastic.
我发现 MySQL 对 MSSQL 的主要支持是时区支持 - 在时区之间很好地改变的能力，尊重夏令时非常棒。
Compare this:
比较一下：
mysql> SELECT CONVERT_TZ('2008-04-01 12:00:00', 'UTC', 'America/Los_Angeles');
+-----------------------------------------------------------------+
| CONVERT_TZ('2008-04-01 12:00:00', 'UTC', 'America/Los_Angeles') |
+-----------------------------------------------------------------+
| 2008-04-01 05:00:00 |
+-----------------------------------------------------------------+
to the contortions involved at this answer.
到这个答案所涉及的扭曲。
As for the 'easier to use' comment, I would say that the point is that they are different, and if you know one, there will be an overhead in learning the other.
至于“更容易使用”的评论，我想说的是它们是不同的，如果你知道一个，学习另一个就会有开销。
回答by prince
Both are DBMS's Product Sql server is an commercial application while MySql is an opensouces application.Both the product include similar feature,however sql server should be used for an enterprise solution ,while mysql might suit a smaller implementation.if you need feature like recovery,replication,granalar security and significant,you need sql server
两者都是 DBMS 的产品 Sql server 是一个商业应用程序，而 MySql 是一个 opensouces 应用程序。这两个产品都包含类似的功能，但是 sql server 应该用于企业解决方案，而 mysql 可能适合较小的实现。如果您需要恢复等功能，复制，粒度安全和重要，你需要sql server
MySql takes up less spaces on disk, and uses less memory and cpu than does sql server
MySql占用磁盘空间少，占用内存和cpu比sql server少
回答by dlinsin
Anyone have any good experience with a "port" of a database from SQL Server to MySQL?
任何人对从 SQL Server 到 MySQL 的数据库“端口”有任何好的经验吗？
This should be fairly painful! I switched versions of MySQL from 4.x to 5.x and various statements wouldn't work anymore as they used to. The query analyzer was "improved" so statements which previously were tuned for performance would not work anymore as expected.
这应该是相当痛苦的！我将 MySQL 的版本从 4.x 切换到 5.x，各种语句不再像以前那样工作了。查询分析器得到了“改进”，因此以前针对性能进行了调整的语句将不再按预期工作。
The lesson learned from working with a 500GB MySQL database: It's a subtle topic and anything else but trivial!
从使用 500GB MySQL 数据库中吸取的教训：这是一个微妙的话题，除了微不足道的其他任何东西！
