MySQL--The stored procedure adds fields to the data table

Demand:

Add a field to a table in a database (if the field already exists, do not do so; if the field does not exist, the new one)

Baidu has been n long, did not meet the requirements of the example, only the reference to add their own pondering, finally to get out, the following is a few versions of the change

First edition:

DELIMITER $$CREATE PROCEDUREInsert_column ()BEGINIF  not EXISTS(SELECT 1  fromInformation_schema.columnsWHERETable_schema='Ltivalley'  andtable_name='T_luck'  andcolumn_name='SSS') Then Alter TableLtivalley.t_luckAddSssvarchar(255) not Null;End if;END$ $DELIMITER;

This stored procedure can be done to add a SSS field to the T_luck table in the Ltivalley database, but it is useless to write dead, without flexibility !

Second Edition:

DELIMITER $$CREATE PROCEDUREInsert_column (inchdbnamevarchar(255),inchTbnamevarchar(255),inchColNamevarchar(255))Set @sql_1=Concat ("IF  not EXISTS(SELECT 1  fromInformation_schema.columnsWHERETable_schema=", dbname," andtable_name=", Tbname," andcolumn_name=", ColName,") Then Alter Table", dbname,". ", Tbname,"Add", ColName,"varchar(255) not NULL;End if;");PREPAREstmt from @sql_1;EXECUTEstmt;deallocate PREPAREstmt;END$ $DELIMITER;

There is no problem creating the stored procedure, but when the call will be error, reported "if" error left and right, the reason is that in MySQL if only exist in the stored procedure, here with execute stmt to execute, is not in the stored procedure executes the IF statement, so error, can not execute! so useless!

Third edition

DELIMITER $$CREATE PROCEDUREInsert_column (inchdbnamevarchar(255),inchTbnamevarchar(255),inchColNamevarchar(255))BEGINSET @selec=CONCAT ('Select 1 from information_schema.columns WHERE table_schema= "', dbname,'"and Table_name="', Tbname,'"and Column_name="', ColName,'"');PREPARESelec from @selec; SET @addcol=CONCAT ('ALTER TABLE', dbname,'.', Tbname,'ADD', ColName,'VARCHAR (255)');PREPAREAddcol from @addcol; IF  not EXISTS(EXECUTESelec;) ThenEXECUTEAddcol;END IF;END$ $DELIMITER;

The stored procedure cannot be created because the Execute SELEC statement cannot be executed in the If not EXISTS (), because the reason is unknown. so useless!

Ultimate Edition

DELIMITER $$CREATE PROCEDUREInsert_column (inchdbnamevarchar(255),inchTbnamevarchar(255),inchColNamevarchar(255))BEGINSet @iscolexists = 0;SET @selec=CONCAT ('Select COUNT (*) into @iscolexists from Information_schema.columns WHERE table_schema= "', dbname,'"and Table_name="', Tbname,'"and Column_name="', ColName,'"');PREPARESelec from @selec; EXECUTESelec;deallocate PREPARESelec;SET @addcol=CONCAT ('ALTER TABLE', dbname,'.', Tbname,'ADD', ColName,'VARCHAR (255)');PREPAREAddcol from @addcol; IF(@iscolexists=0) ThenEXECUTEAddcol;END IF;END$ $DELIMITER;

The final result, execute execute selec First, the result is stored in a variable @iscolexists, and then in the if () to determine the value of @iscolexists, do the corresponding operation. available!

MySQL-Stored Procedures add fields to the datasheet