Details
Description
MySQL / MariaDB SQL is great as a functional language: you tell what you want
and not how to do it. This includes suppressing of errors when the situation you
want already exists. So there is a CREATE TABLE IF NOT EXISTS statement, for
example (you say that you want the table to exist, not that it has to be built
right now). It would be nice if altering a table could be done the same way:
ALTER TABLE SomeTable ADD COLUMN IF NOT EXISTS SomeColumn ... ALTER TABLE SomeTable DROP COLUMN IF EXISTS SomeColumn ... ALTER TABLE SomeTable DROP FOREIGN KEY IF EXISTS SomeConstraint ...
This would make definition scripts more legible and more friendly to existing
databases. It would facilitate what I call "repeatable scripts", that can both
create a new database and update an existing one to the latest structure.
Mind you, this is already possible using temporary stored procedures (as shown
in http://www.howtoforge.com/node/4833), but it would make definition scripts
more maintainable and the SQL language more consistent.
Specific plan for this task:
extend the ALTER TABLE statement with the IF [NOT] EXISTS options.
That involves:
ALTER TABLE ADD/DROP COLUMN
ALTER TABLE ADD/DROP INDEX
ALTER TABLE ADD/DROP FOREIGN KEY
ALTER TABLE ADD/DROP PARTITION
ALTER TABLE CHANGE COLUMN
ALTER TABLE MODIFY COLUMN
Like CREATE TABLE IF NOT EXISTS, and similar existing commands,
the ALTER TABLE ... IF [NOT] EXISTS will return warnings instead of errors
if the modified object (column, index or partition) already exists (doesn't exist yet).
If the ALTER TABLE command consists of multiple operations, it's not interrupted in the IF [NOT] EXISTS case, but succeedes with the appropriate
amount of warnings.
Gliffy Diagrams
Attachments
Issue Links
- relates to
-
MDEV-4440 IF NOT EXISTS in multi-action ALTER does not work when the problem is created by a previous part of the ALTER
-
- Closed
-
-
-
- Closed
-
-
-
- Closed
-
-
-
- Closed
-
-
-
- Closed
-
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
As a first part create a detailed implementation description/specification and a rough estimate. Let's then ask for feedback on that before starting implementation.
This feature is wanted for both 5.3 and 5.5. Added fix versions accordingly.
Patch for this task:
http://lists.askmonty.org/pipermail/commits/2012-July/003510.html
Pushed into the 10-hf.
(Mdcallag - 2012-06-04 23:51:43)
This feature will make online schema change easier to do for large sharded deployments