Is there an "IF NOT EXISTS" clause for adding columns in MySql?

Question

I need to run this MySql code on some databases and I suspect that some of them to have already this column. Is there anything similar to if not exists for the below code?

ALTER TABLE 
    `comments` 
ADD COLUMN 
    `active` int(1) NOT NULL DEFAULT '0' AFTER `sid`

I'm considering this to prevent getting the duplicated column error.

you have more than one table with the same table name (in a single DB) ? — Srikar Appalaraju
– Srikar Appalaraju, Commented Oct 17, 2010 at 15:24
no just i want to know if there is any option to exclude those sites that has an active column actually to prevent getting duplicated column error ! — Mac Taylor
– Mac Taylor, Commented Oct 17, 2010 at 15:27

Jagmag · Accepted Answer · 2010-10-17 15:46:43Z

2

SELECT COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE table_name = 'tbl_name'
AND table_schema = 'db_name'
AND column_name = 'column_name'

should give you what you are looking for!!

answered Oct 17, 2010 at 15:46

Jagmag

10.4k1 gold badge37 silver badges60 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Sled · Accepted Answer · 2013-05-02 16:01:51Z

1

It's not built-in, but it can be accomplished using the information_schema database:

http://www.cryer.co.uk/brian/mysql/howto_add_column_unless_exists.htm

edited May 2, 2013 at 16:01

Sled

19.1k28 gold badges125 silver badges173 bronze badges

answered Oct 17, 2010 at 15:42

Brad Mace

28k19 gold badges110 silver badges152 bronze badges

Comments

DJ Far · Accepted Answer · 2014-01-10 13:31:37Z

1

Or: SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = 'tbl_name' AND table_schema = 'db_name' AND column_name = 'column_name'

This gives you a 1 or 0 result based on existence or not.

answered Jan 10, 2014 at 13:31

DJ Far

5065 silver badges12 bronze badges

Collectives™ on Stack Overflow

Is there an "IF NOT EXISTS" clause for adding columns in MySql?

3 Answers 3

Comments

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Linked

Related