mysql Method Analysis of Adding Fields to Data Table by Creating Stored Procedures

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

Requirements:

Add 1 field to a table in a database (if the field already exists, no action will be taken; If the field does not exist, add)

Baidu has been n for a long time, and there is no example that meets the requirements. Only reference and self-pondering are finally made. The following are the changes of several versions

Version 1:

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 1 sss field to the t_luck table of ltivalley database, but it is dead and has no flexibility, so it is useless!

Version 2:

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 when it is called, it will make an error, and it will make an error when reporting "IF". The reason is that IF can only exist in the stored procedure in mysql, and it is executed with EXECUTE stmt here, which is equivalent to not executing if statements in the stored procedure, so it will make an error and can't be executed! So it's useless!

Version 3

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 selec;)THEN
EXECUTE addcol;
END IF;
END
$$
DELIMITER ;

The stored procedure cannot be created because the EXECUTE selec statement cannot be executed in IF NOT EXISTS () for an unknown reason. 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 selec;
DEALLOCATE PREPARE selec;
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 ;

The final result, execute first EXECUTE selec Save the result to 1 variable @iscolexists In, and then in IF() Middle judgment @iscolexists The value of, and do the corresponding operation. Available!

More readers interested in MySQL can check out the topics on this site: "MySQL Stored Procedure Skills Encyclopedia", "MySQL Common Function Summary", "MySQL Log Operation Skills Encyclopedia", "MySQL Transaction Operation Skills Summary" and "MySQL Database Lock Related Skills Summary"

I hope this article is helpful to everyone's MySQL database.