CODEWITHSUNDEEP
mysql
linjuming
linjuming

Reputation: 2157

how to add a field if not exists in mysql table

table orders

customers_id | name
1 | jimmy

I want to add a field 'refer_id' in table orders if it is not exists

customers_id | name | refer_id
1 | jimmy | 0

how to write if conditions?

alter table orders add refer_id int(11) default 0 if ...

Upvotes: 0

Views: 122

Answers (3)

Travis G
Travis G

Reputation: 1602

Try this 

delimiter '//'

CREATE PROCEDURE addcol() BEGIN

IF NOT EXISTS(
    SELECT * FROM information_schema.COLUMNS
    WHERE COLUMN_NAME='refer_id' AND TABLE_NAME='orders' 
    )
    THEN
        alter table orders add refer_id int(11) default 0 
END IF;
END;
//

delimiter ';'

CALL addcol();

DROP PROCEDURE addcol;

Upvotes: 1

PSR
PSR

Reputation: 40318

It's not built it but it can be accomplished using the information_schema database

see here

Upvotes: 0

echo_Me
echo_Me

Reputation: 37233

why using if here ?

try this

   ALTER TABLE orders ADD refer_id int(11)  default 0  AFTER name;

Upvotes: 1

Related Questions