if exists for Column and Index Migrations in MySQL

Unfortunately MySQL does not provide an SQL statement for conditionally creating a column if it does not already exist. There also does not appear to be an easy way for dropping a column without causing an error if the column doesn’t exist. The same problem also applies to indices. This functionality however is commonly used for idempotent schema updates. Without stored procedures which take care of the changes you are lost. I would like to share a Gist on GitHub which helps for the following schema migrations:

• Drop index if exists
• Create index if not exists
• Create unique index if not exists
• Drop column if exists
• Add column if not exists

I am using the INFORMATION_SCHEMA tables for testing for the existence of columns and indices. No special grant is necessary to query from those tables. The stored procedures have the following signature:
Drop index if exists
m_drop_index(table_name, index_name)
Create index if not exists
m_create_index(table_name, index_name, index_columns)
Create unique index if not exists
m_create_unique_index(table_name, index_name, index_columns)
Drop column if exists
m_drop_column(table_name, column_name)
Add column if not exists
m_add_column(table_name, column_name, column_definition)
After importing, you can use them instead of MySQL’s ALTER statements. Here is an example for adding the name column to the table person:

mysql> CALL m_add_column('person', 'name', 'varchar(255)');

Eric Lippmann

CTO

---

Eric kam während seines ersten Lehrjahres zu NETWAYS und hat seine Ausbildung bereits 2011 sehr erfolgreich abgeschlossen. Seit Beginn arbeitet er in der Softwareentwicklung und dort an den unterschiedlichen NETWAYS Open Source Lösungen, insbesondere inGraph und im Icinga Team an Icinga Web. Darüber hinaus zeichnet er für viele Kundenentwicklungen in der Finanz- und Automobilbranche verantwortlich.

Lies mehr von Eric und triff unser Team