Analysis of the method of adding fields to the data table by creating stored procedures in mysql

This article illustrates how mysql creates stored procedures to add fields to a data table. Share it for your reference, as follows:

Requirements:

Add a field to a table in a database (if the field already exists, do nothing; if the field does not exist, add)

Baidu n long time, no examples that meet the requirements, only reference plus their own thinking, finally got out, the following are several versions of the change

First edition:

DELIMITER $$CREATE PROCEDURE insert_column()BEGINIF NOT EXISTS(SELECT 1 FROM information_schema.columnsWHERE table_schema='ltivalley' AND table_name='t_luck'AND column_name='sss' ) THENalter table ltivalley.t_luck add sss varchar(255) not Null;end if;END$$DELIMITER ;

This stored procedure can add an sss field to the t_luck table of the ltivalley database, but it is written dead and has no flexibility, so it is useless!

Second edition:

DELIMITER $$CREATE PROCEDURE insert_column(in dbname varchar(255),in tbname varchar(255),in colname varchar(255))set @sql_1= concat("IF NOT EXISTS(SELECT 1 FROMinformation_schema.columns WHERE table_schema=",dbname,"AND table_name=",tbname," AND column_name=",colname,")THENalter table ",dbname,". ",tbname," add ",colname,"varchar(255) not null;end if;");PREPARE stmt FROM @sql_1;EXECUTE stmt;DEALLOCATE PREPARE stmt;END$$DELIMITER ;

There is no problem in creating stored procedures, but when calling them, there will be an error, reporting "IF" errors, because IF can only exist in stored procedures in mysql, and EXECUTE stmt is used here to execute, which means that if statements are not executed in stored procedures, so errors are reported, and execution cannot be performed! So useless!

the third edition

DELIMITER $$CREATE PROCEDURE insert_column(in dbname varchar(255),in tbname varchar(255),in colname varchar(255))BEGINSET @selec=CONCAT('SELECT 1 FROM information_schema.columns WHERE table_schema="',dbname,'" AND table_name="',tbname,'" AND column_name="',colname,'"');PREPARE selec FROM @selec;SET @addcol=CONCAT('ALTER TABLE ',dbname,'. ',tbname,' ADD ',colname,' VARCHAR(255)');PREPARE addcol FROM @addcol;IF NOT EXISTS(EXECUTE selec;)THENEXECUTE addcol;END IF;END$$DELIMITER ;

Cannot create stored procedure because EXECUTE selecc statement cannot be executed in IF NOT EXISTS() for unknown reason. So useless!

Ultimate Edition

DROP PROCEDURE IF EXISTS insert_column;DELIMITER $$CREATE PROCEDURE insert_column(in dbname varchar(255),in tbname varchar(255),in colname varchar(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,'"');PREPARE selec FROM @selec;EXECUTE selec;DEALLOCATE PREPARE selec;SET @addcol=CONCAT('ALTER TABLE ',dbname,'. ',tbname,' ADD ',colname,' VARCHAR(255)');PREPARE addcol FROM @addcol;IF(@iscolexists=0)THENEXECUTE addcol;END IF;END$$DELIMITER ;

For the final result, execute EXECUTE selecc, store the result in a variable @iscolexists, and then judge the value of @iscolexists in IF() and do the corresponding operation. Available!

More about MySQL related content interested readers can view this site topic: MySQL storage process skills, MySQL common function summary, MySQL log operation skills, MySQL transaction operation skills summary and MySQL database lock related skills summary

I hope this article is helpful for MySQL database.