long-time Oracle user switching to MySQL, any gotchas to be aware of?

It happened that all projects during the last 10 years were with Oracle as a database server. Now I'm starting on a side project on my own with MySQL (the latest stable).

Are there any gotchas (things that do not work as expected by an Oracle user)? Anything to do with transaction management, locking, is开发者_StackOverflowolation levels, indexes, that kind of stuff.

I'm mainly a Java developer so I'm interested how the database is seen from an application server. Stored procedures, (complex) views or any fancy stuff are not on my radar for this project.

Thank you.

MySQL has various engines - the primary ones being INNODB and MyISAM. MyISAM does not support transactions, nor foreign key constraints. Isolation levels are similar - these seem to be relatively standard between databases these days.

Indexes

Indexes are different - MySQL has clustered and non-clustered indexes. Clustered indexes are typically for the primary key, but not necessarily. There's also a limit on the space for defining indexes - 767 for INNODB, 1,000 for MyISAM. Covering indexes are supported, no support for function based indexes...

The optimizer can only use one index per SELECT clause - check the EXPLAIN PLAN output. There is syntax for specifiying an index to be used, but it's a hint & can still be disregarded by the optimizer.

Constraints

MySQL has CHECK constraint syntax, but no engine enforces it currently. The only option is to use triggers. Unique constraints are implemented in MySQL as indexes.

Custom Error Handling

You need to declare handlers for Custom Error handling: http://dev.mysql.com/doc/refman/5.0/en/declare-handler.html

Previously -
MySQL doesn't have any support for defining custom errors to differentiate between say data integrity and business rule errors.

Analytic/Ranking/WIndowing functionality

MySQL does not have any such functionality - no ROW_NUMBER, NTILE, RANK or DENSE_RANK. You can create psuedo functionality using variables - there're numerous examples on SO if you check the tags "mysql", "rank".

WITH syntax - Subquery Factoring

This is another thing MySQL does not have.

Hierarchical Query Support

You guessed it - No recursive support for hierarchical queries. Oracle's had CONNECT BY since v2 (!!), but the ANSI standard is to use the WITH clause like you see in SQL Server 2005+.

Views

MySQL does not support materialized views, and the view support is crippled - can't use subqueries, for example.

PIVOT/UNPIVOT

This is ANSI standard syntax; Oracle started support in 11g but MySQL again does not support this. Only option is CASE expressions and aggregate functions, which remains the most portable means of implementing this functionality (SQL Server 2005+ supports PIVOT/UNPIVOT).

Sequences

MySQL doesn't support sequences, and the closest thing is defining an INT column as auto_increment. This makes it incredibly difficult to use the same sequence of values across two or more tables (not that you really want to if you don't have to). Also, only one auto_increment column can be defined per table. The increment and offset is instance-wide - change it, and you effect every auto_increment column in every database the instance serves. Resetting the auto_increment value requires ALTER TABLE privilege; deleting/truncating data will not alter the current value.

On that note, MySQL doesn't support the RETURNING clause. You need to use LAST_INSERT_ID() to retrieve the auto_increment value for a newly created row.

Data Types

MySQL doesn't have a NUMBER data type - it splits numerics into INT, MEDIUMINT, etc.. MySQL is very similar to SQL Server in this regard. MySQL's TEXT (TINYTEXT, TEXT, MEDIUMTEXT, and LONGTEXT) data type is more accommodating than the 4000 limit of Oracle's VARCHAR2. MySQL supports CLOB and BLOB...

Packages, Stored Procedures, Functions

MySQL supports User Defined Functions and Stored Procedures - I have yet to encounter a database that supports something similar to Oracle Packages. SQL Server has CLR assemblies, but it requires using .NET CLR code rather than native TSQL/PLSQL.

MySQL is one of my favorite DBs. I have used it for years with great results. Having switched shortly after a large Oracle tuning effort, I have some good side by side thoughts.

I would watch the memory levels carefully. They are not self tuning and the system wide and per thread memory caches and usages may need to be carefully tuned.

The query cache has for the most part been global. This causes a global locking condition when the cache is enabled.

For most instances and much of MySQL's life, you have been limited to one index per table occurrence per query. This increases the number of indexes you would need in high performance situations.

Database replication on the slave side is single threaded. This means that the master can write far faster than the slaves can write with identical hardware.

Also, watch out for case sensitivity. Sometimes it is there, mostly it is not. This can get confusing.

With the current MySQL - Basic SQL will be exactly the same, but MySQL does not handle large tables/db's very well. ~10 million entries on MyISAM on a mid-range Intel Xeon server, data errors start arising.

But, since Oracle has purchase MySQL, these differences might eventually dissipate.