how to add a field if not exists in mysql table

Question

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 ...  

Answer 1

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;

Answer 2

why using if here ?

try this

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

Answer 3

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

see here