What databases used with PHP share the same (or most) of the SQL syntax?

Question

I've read that although SQL is meant to be standardised, it is implemented different etc with different databases. I have only ever used MySQL for databases.

What I would like to know is what other databases share the same syntax? I am using PDO now, and I would like to set a flag to allow the user to specify which database they would like to use.

I would be interested in knowing which are the 'safe' databases that will handle most (if not all) my general MySQL queries. Mainly SELECT, INSERT, UPDATE, DROP, DELETE.

Thanks

Answer 1

There are several revisions of a such called ANSI SQL.

All major database engines (that is Oracle, MS SQL, PostgreSQL and MySQL) should (should) in theory support SQL-92.

This includes everything you've mentioned: SELECT, INSERT, UPDATE, DROP, DELETE, as long as you don't use complex clauses with them.

In practice:

1. Not all databases support the full set of SQL-92.

   Writing cross-platform SQL even in SQL-92 requires lots of testing.

2. Platform independency in SQL ends when you insert your 1001st row and need to optimize you queries.

   If you browse a little over StackOverflow questions tagged SQL, you will see that most of them say "help me to optimize this query", and most answers say "use this platform dependent hack"

Answer 2

You will find that some database store datatypes differently, for example, mysql stores Booleans as 1 and 0 and postgres stores them as 't' and 'f'.

As long as your database classes are aware of the need to convert data, you should be fine, probably 96.3482% of everyday CRUD will work pretty well across the board.

Even if you create database classes that directly call PDO, you can later on add some logic for data translation or query modification.

You could use the database abstraction layer ADOdb. ^{(its got what plants crave)}

I'd suggest making sure that your customers actually give a crap about which database they need to run before you spend a lot of time developing functionality you may not need.

Answer 3

A standardized SQL92 is pretty much the same in all RDBMS. The differences are in parts, that the standard doesn't define, like for example LIMIT or datetime handling functions and of course procedural languages. As for DBs popular with PHP: it not that hard make SQL portable between MySQL, SQLite and PostgreSQL. It won't be that easy with Oracle, Sybase and DB/2.