If exists (select * from information_lumns where table_name = 'table1' and column_name = 'column2') thenĪlter table table1 drop column `column2` Īlter table table1 add column `column1` varchar(255) NULL Īlter table table1 add column `column2` varchar(255) NULL If exists (select * from information_lumns where table_name = 'table1' and column_name = 'column1') thenĪlter table table1 drop column `column1`
However, because you need an IF statement, it will need to be a stored procedure. But you can fake it, at least in MySQL 5 or later, by querying the database meta-data do see if the column exists, and drop it if it does. MySQL has a built-in modifier for this.įor some reason, the same facility does not exist in MySQL for dropping a column if it exists. The most common operation you will want to do is to drop a table or column, but only if it exists.
DROP TABLE IF EXISTS MYSQL UPDATE
You could add yet another schema update to fix this, or if your script was idempotent, you could simply modify the original script and run it again. You actually want a column to be varchar(255), not a varchar(50).
Why might you want to code idempotent schema updates? Say your initial version of the schema update got it slightly wrong. you should be able to run the script more than once, no run should error out, and the end result should be the same as when you ran it the first time. When updating a database schema, it's very useful to make your SQL scripts idempotent. These drops are only logged when running statement or mixed mode replication. Dropping of TEMPORARY tables are prefixed in the log with TEMPORARY. Idempotence describes the property of operations in mathematics and computer science that means that multiple applications of the operation do not change the result. DROP TABLE without IF EXISTS for tables that dont exist are not written to the binary log.
0 kommentar(er)
