Supporting both Oracle and MySQL: how similar is their SQL syntax?

Question

We use Oracle on a project and would like to also support MySQL. How close are their SQL dialects?

Is it perhaps even possible to use the same SQL source for both without too many gymnastics?

Details:

• We're using iBatis, a persistence manager that cleanly segregates the SQL statements into resource files. But we work at the SQL level, which has its advantages (and disadvantages).
• We'd prefer not to move to an object-relational mapper like Hibernate, which would fully shield us from dialect differences.
• We've tried hard to keep to a generic subset of Oracle SQL.
• There's no PL/SQL.
• We don't use stored procedures or triggers (yet, anyway).
• We use check constraints, unique constraints, and foreign key constraints.
• We use ON DELETE CASCADEs.
• We use transactions (done at the iBatis API level).
• We call a few Oracle timestamp functions in the queries.
• We would use the InnoDB storage engine with MySQL (it supports transactions and constraints).

So what are your thoughts? Would we need to maintain two different sets of iBatis SQL resource files, one for each dialect, or is it possible to have a single set of SQL supporting both MySQL and Oracle?

Final Update: Thanks for all the answers, and especially the pointers to Troels Arvin's page on differences. It's really regrettable that the standard isn't more, well, standard. For us the issues turn out to be the MySQL auto-increment vs. the Oracle sequence, the MySQL LIMIT vs. the Oracle Rowumber(), and perhaps the odd function or two. Most everything else ought to transfer pretty easily, modulo a few edits to make sure we're using SQL-92 as @mjv points out. The larger issue is that some queries may need to be hand-optimized differently in each DBMS.

Answer 1

Expect a few minor bumps on the road, but on whole should be relatively easy.

From the list of features you currently use, there should only be a few synctactic or semantic differences, in general easy to fix or account for. The fact that you do not use PL/SQL and/or Stored Procedures is a plus. A good rule of thumb is to try and stick to SQL-92 which most DBMSes support, in particular both Oracle and MySQL. (Note this is not the current SQL standard which is SQL-2008).

A few of the differences:

• "LIMIT" is a famous one: to limit the number of rows to retrieve in the results list, MySQL uses LIMIT n, at the end of the query, Oracle uses RowNumber() in the WHERE clause (which is pain, for you also need to reference it in the SELECT list...)
• Some datatypes are different. I think mostly BOOLEAN (but who uses this ;-) ) Also some I think subtle differences with the DATETIME type/format.
• Some function names are different (SUBSTRING vs. SUBSTR and such...)

Just found what seems to be a good resource about differences between SQL implementations.

Reading the responses from others, yeah, DDL, could be a problem. I discounted that probably because many applications do not require DDL, you just need to set the data schema etc. at once, and then just use SQL for querying, adding or updating the data.

Answer 2

You definitely won't be able to keep your DDL the same. As far as DML goes, there are many similarities (there's a core subset of ANSI SQL standard supported by every database) but there are some differences as well.

To start, MySQL uses auto increment values and Oracle uses sequences. It's possible to work around this (sequence + trigger on Oracle side to simulate auto increment), but it's there. Built-in functions are quite different.

Basically, depending on what exactly you intend to use it may or may not be possible to keep one set of statements for both. Incidentally, even with Hibernate dialects it's not always possible to have the same set of queries - HQL is great but not always enough.

Answer 3

I believe that maintaining a single set of SQL resource files with MySQL and Oracle, has several disadvantages as being caught between backward compatibility and solve a particular problem. it is best to have a sql for each SQL engine and thus maximize the capabilities of each.

Features that look identical in a brochure may be implemented very differently.

see these examples

Limiting result sets

MYSQL

SELECT columns
FROM tablename
ORDER BY key ASC
LIMIT n

ORACLE

SELECT * FROM (
  SELECT
    ROW_NUMBER() OVER (ORDER BY key ASC) AS rownumber,
    columns
  FROM tablename
)
WHERE rownumber <= n

Limit—with offset

MYSQL

SELECT columns
FROM tablename
ORDER BY key ASC
LIMIT n OFFSET skip

ORACLE

SELECT * FROM (
  SELECT
    ROW_NUMBER() OVER (ORDER BY key ASC) AS rn,
    columns
  FROM tablename
)
WHERE rn > skip AND rn <= (n+skip)

You can check this Comparison of different SQL implementations

Answer 4

Oracle treats empty strings as nulls. MySQL treats empty strings as empty strings and null strings as null strings.

Answer 5

In addition to the stuff others have mentioned, oracle and mysql handle outer joins quite differently. Actually, Oracle offers a syntax that mySql won't cope with, but Oracle will cope with the standard syntax.

Oracle only:

SELECT a.foo, b.bar
  FROM a, b
 WHERE a.foo = b.foo(+)

mySql and Oracle:

SELECT a.foo, b.bar
     FROM a 
LEFT JOIN b 
       ON (a.foo=b.foo)

So you may have to convert some outer joins.