Since when I remember (and I can remember the 3.23 version!) MySQL always supported the
IF EXISTS and
IF NOT EXISTS options for many DDL statements. They help developers in writing install and uninstall scripts: with
IF NOT EXISTS, a table is created if needed, and there is no error (so, the script’s execution goes on) if the table is already there.
However, the case when a table exists but must be modified is problematic, because the
ALTER statements never supported those options… until MariaDB 10. The old way to avoid errors was:
DROP TABLE IF EXISTS tab_name; CREATE TABLE tab_name ... ;
But this way it is not possible to preserve current table’s data.
The cleanest solution was probably writing a script, or a Stored Procedure, which queries the
information_schema database to check if a column exists, and conditionally
DROP it. Or, to avoid such queries, a more error-prone script could read the database version in use and upgrade it.
MariaDB 10.0.2 supports
IF EXISTS and
IF NOT EXISTS options for several
ALTER TABLE sub-commands. Here’s the list of those clauses (copied from the MariaDB KnowledgeBase):
ADD COLUMN ADD INDEX ADD FOREIGN KEY ADD PARTITION DROP COLUMN DROP INDEX DROP FOREIGN KEY DROP PARTITION CHANGE COLUMN MODIFY COLUMN
Note that the rarely used
ALTER COLUMN syntax has not an
IF EXISTS option, so the longer
MODIFY syntax must be used instead.
Also note that the PostgreSQL syntax is not supported; thus, this will not work:
ALTER TABLE IF EXISTS tab_name ... ;
At this point, you are probably wondering: “But then, is this feature totally useless for me? Is there still no way to alter a table only if it exists?”. Hey, don’t be dramatic! The solution is easy, and it’s probably better than Postgres-style:
-- create the table as we want it to be CREATE TABLE IF NOT EXISTS tab_name ... ; -- if an older table version exists, update it ALTER TABLE name DROP COLUMN IF NOT EXISTS col_name col, DROP INDEX IF EXISTS idx_name;
This feature is not compatible with Oracle MySQL, and MariaDB’s executable comments cannot help here. You may write two different upgrade scripts, one for MariaDB and one for Oracle MySQL… but this feature should simplify your work, not make it harder.
So you can just switch to MariaDB and leave Oracle MySQL. This is an option even for public software projects. MariaDB has more interesting features, it is more open, and is probably the future. Also, migrating to MariaDB is amazingly simple: in most cases, all you need to do is to install MariaDB. So, don’t be prisoner of Oracle just because MySQL is still the most widely used DBMS: things will change.