是否可以通过存储过程更改mySql中的表? [英] Is it possible to alter a table in mySql via stored procedures?

问题描述

这就是我想在mySQL中做的事情...我感觉这根本不可行，但是很乐意做错...

here's what I'd like to do in mySQL... I'm getting the feeling that this is simply not feasible, but would love to be wrong...

create procedure foo(IN MYTABLE varchar(50) , IN COLNAME varchar (50), IN MYTYPE varchar(50)) 
begin 
IF (select count(*) from information_schema.columns where table_name =MYTABLE and column_name = COLNAME) = 0 
THEN
alter table MYTABLE add column MYNAME MYTYPE; 
end;

call foo( 'table_foo' , 'column_bar' , 'varchar(100)' );

推荐答案

不知道为什么在地球上会想要它，但是有可能:

Don't know why on Earth you would want it, but it's possible:

DELIMITER //
DROP PROCEDURE foo//
CREATE PROCEDURE foo(IN MYTABLE varchar(50) , IN COLNAME varchar (50), IN MYTYPE varchar(50))
BEGIN
  SET @ddl = CONCAT('alter table ', MYTABLE, ' add column (', COLNAME, ' ', MYTYPE, ')');
  PREPARE STMT FROM @ddl;
  EXECUTE STMT;
END;
//

这篇关于是否可以通过存储过程更改mySql中的表?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！