Analysis of how to create a stored procedure in MySQL to add new fields to a data table

This article uses an example to describe how to create a stored procedure in MySQL to add new fields to a data table. Share with you for your reference, the details are as follows:

need:

Add a new field to a table in a database (if the field already exists, no operation is performed; if the field does not exist, a new field is added)

I searched Baidu for a long time, but there was no example that met the requirements. I could only refer to it and figure it out by myself. Finally, I figured it out. The following are the changes of several versions.

First Edition:

DELIMITER $$
CREATE PROCEDURE insert_column()
BEGIN
IF NOT EXISTS(SELECT 1 FROM information_schema.columns
WHERE table_schema='ltivalley' AND table_name='t_luck'
AND column_name='sss' ) THEN
alter table ltivalley.t_luck add sss varchar(255) not Null;
end if;
END
$$
DELIMITER ;

This stored procedure can add a new sss field to the t_luck table in the ltivalley database, but it is dead-coded 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 FROM
information_schema.columns WHERE table_schema=",dbname,"
AND table_name=",tbname," AND column_name=",colname,")
THEN
alter 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 a stored procedure, but an error will occur when calling it, reporting an error around "IF". The reason is that in MySQL, IF can only exist in stored procedures. Using EXECUTE stmt to execute here is equivalent to not executing the if statement in the stored procedure, so an error is reported and it cannot be executed! So it’s useless!

Third Edition

DELIMITER $$
CREATE PROCEDURE insert_column(in dbname varchar(255),in tbname varchar(255),in colname varchar(255))
BEGIN
SET @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 select;)THEN
EXECUTE addcol;
END IF;
END
$$
DELIMITER ;

Unable to create stored procedure because the EXECUTE select statement cannot be executed in IF NOT EXISTS() for unknown reasons. So it’s 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))
BEGIN
set @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 select;
DEALLOCATE PREPARE select;
SET @addcol=CONCAT('ALTER TABLE ',dbname,'.',tbname,' ADD ',colname,' VARCHAR(255)');
PREPARE addcol FROM @addcol;
IF(@iscolexists=0)THEN
EXECUTE addcol;
END IF;
END
$$
DELIMITER ;

For the final result, first execute EXECUTE selec and store the result in a variable @iscolexists . Then, judge the value of @iscolexists in IF() and perform corresponding operations. Available!

Readers who are interested in more MySQL-related content can check out the following topics on this site: "MySQL stored procedure 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 will be helpful to everyone's MySQL database design.