MySQL ALTER TABLE does not have IF EXISTS specification.
You can do the following through using a stored proc or a programming language if this is something that you’ll need to do on a regular basis.
Pseudocode:
- Find if the column exists
- If the query below returns a result then it means the column exists, otherwise you can go ahead and create the column
|
1 2 3 4 5 6 7 |
SELECT column_name FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA='[Database Name]' AND TABLE_NAME='[Table Name]' AND COLUMN_NAME='[Column Name]'; |
Note: For MariaDB 10.219 onwards IF EXISTS is now available for ALTER TABLE … ADD/MODIFY COLUMN
|
1 2 3 4 5 |
ALTER TABLE t1 ADD COLUMN IF NOT EXISTS col1 VARCHAR(100); ALTER TABLE t1 MODIFY IF EXISTS col1 VARCHAR(150); |