MySQL vs. SQL Server vs. Oracle [closed]_问答_开发者

Closed. This question does not meet Stack Overflow guidelines. It is not currently accepting answers.

This question does not appear to be about programming within the scope defined in the help center.

Closed 8 years ago.

开发者_开发百科 Improve this question

I have always only used MySQL and no other database system.

A question came up at a company meeting today and I was embarrassed I did not know: To a developer, what earth-shaking functionality do MS or Oracle offer that MySQL lacks and which allows MS and Oracle to charge for their systems?

I think other commentators are quite right to highlight all of the extra non-core RDBMS abilities that are bundled with the commercial solutions.

Here is a matrix of Oracle editions and features that would be worth browsing just for an understanding of the "extra" features, particularly in the context of what it would cost to develop and support your own version of them (if that is even possible) on a product like MySQL: https://docs.oracle.com/cd/B28359_01/license.111/b28287/editions.htm#DBLIC116

For example, if you had a requirement that said: "Users shall be able to recover any previous versions of data that they have deleted/updated up to one week after the changes has been committed" then that implies a certain development overhead that I think would be much higher on a system that did not have a built-in feature like oracle's Flashback Query.

The pure RDBMS layers of Oracle and MSSQL offer mainly a more mature programmable environment than MySQL and InnoDB. T-SQL and PL/SQL can't be yet matched by MySQL stored procedures and triggers.

The other differences are syntactic and slight semantic differences which make things easier or harder (like top 500 versus limit/offset).

But the real killer is that there are a ton of integrated tools and services on top of the RDBMS layers of MSSQL (Reporting Services, Analysis Services) and Oracle (Data Warehousing, RAC) which MySQL doesn't have (yet).

MySQL is not free!

It is widely understood to be free, but if you are selling or distributing software packages that incorporate MySQL, it carries rather severe restrictions. If MySQL must be included in your software distribution (i.e. you are not using it only to serve remote clients), it is free only for Open Source projects or non-profit organizations. If you can manage to completely separate the database and drivers from the rest of your application, and force your customer to download and install MySQL on their own, you're probably okay.

Otherwise, it costs $2,000 / year for the "standard" edition, which is roughly comparable to the cost of licensing SQL Server.

Both are very good databases, but among SQL Server's advantages are:

A rich and expressive procedural language (T-SQL)
A better query optimizer, and better performance in write-intensive environments
A strong set of ancillary tools and capabilities, including a programming environment, an ETL tool (SSIS), dimensional modeling (SSAS), a reporting environment (SSRS), and a reasonably sophisticated job scheduler.
Interactive debugging of stored procedures and UDFs.
A relatively easy-to-use window-based management tool that lets you do most administration tasks in a "clicky" way.

Many people are ideologically opposed to using Microsoft tools, or being locked into a Windows environment, and I can understand that. But MySQL is NOT free for business usage, and that doesn't seem to be widely understood.

To review MySQL's licensing policy, take a look at it at their website: https://www.mysql.com/about/legal/licensing/oem/

Some other things which oracle has over mysql.

Queues
internal job scheduling
mature stored procedure language
patitioned tables
fine grained access control and auditing
strong recovery features ( eg flashback, rman, dataguard )
remote database links
application express

Until I administered RDBSes from other vendors Oracle oft repeated "readers don't block writers, writers don't block readers' meant little to me. I really didn't know much about handling locking problems in 8 years as an Oracle DBA. 2 years of Informix and 3 or SQL Server and trust me I know lots more about locking.

So I would say, in addition to the comments about support and non-RDBMS features, add locking behaviour.

There are too many functionality to list. See Wikipedia reference in nullptr's message. But I suppose that really question asked at meeting is "To a developer, what earth-shaking functionality do MS or Oracle offer that MySQL lags in which allows MS and Oracle to charge for their systems and which can be effectively utilized by our company?"

All advantages are really advantages if matched with your needs ...

Oracle table clusters and single table hash clusters (which you won't find in SQL Server either).

NOTE: I can't say anything about Oracle, so I will answer for SQL Server only.

Well, of course minor differences in the database engine, like MERGE statement, BULK INSERT, GROUPING SETs etc.

But I think the bigger part are things like Integration Services, Analysis Services and Reporting Services. Those are important technologies which in my opinion are absolutely worth the money they cost.

MySQL didn't offer the richer programming environment that Oracle and MSSQL offered, especially in early incarnations, when it didn't even have Stored Procedures. From my perspective I tend to put most of my business logic into the application, via DAO's or ORM mappers etc. Therefore the database engine underneath is less important and in theory the application is transportable. From a management perspective MSSQL offers a number of benefits (many already listed) that make the task of owning the database a lot easier. Microsoft also provide the excellent and lightweight SQLExpress for development, which I have found easy to work with and it integrates into Visual Studio nicely.

Inspired by ThinkJet's answer, I think that another factor that comes into play is, "To what extent are we willing to forgo database independence in order to provide faster, cheaper development of more performant applications?" If the company's emphasis is that all code must be portable between databases then there is little point in using anything other than the most simple RDBMS, and the improvements and advantages that Oracle and Microsoft have provided count for nothing.

It takes very little to break true database independence, and my philosophy is that you should throw yourself wholeheartedly into leveraging every feature that you've paid for -- SQL enhancements, PL/SQL, etc..

Others may differ, of course.

I think the question has the implicit assumption that it is development features that allows SQL Server/Oracle to charge. I suspect that it is more the implementation/support.

Hot backup is a major feature that is worth paying for, at least for most installations.

For Oracle, a big added value is RAC - multiple servers hitting the same data (ie same disks) without any messy replication involved. It (generally) isn't apparent to the developer.

Some Oracle features I really like are

Real Application Clusters, a quite powerful way to make database clusters
Fast refreshable materialized Views, a very efficient way to store and update query results
Analytic functions (aka window functions) which allow grouping functions on a subset of the query results in a efficient way. This can avoid self-joins in most cases.

As far as i I know, none of these is available in MySQL. And there are many more useful features, especially in the enterprise edition and its options.