Stored Procedure 資料庫欄位新增修改

建利Stored Procedure 判斷表格欄位存在與否
存在修改  不存在新增

CREATE DEFINER=`root`@`localhost` PROCEDURE `SP_ALTER_Schema`(
in dabebasename Varchar(100),
in tablename Varchar(100),
in columnname Varchar(100),
in scheme Varchar(100)
)
BEGIN     
SET @ALTERstrSQL = concat('ALTER TABLE ',  dabebasename ); 
SET @ALTERstrSQL = concat(@ALTERstrSQL,  '.' );     
SET @ALTERstrSQL = concat(@ALTERstrSQL, tablename);           
   
SET @AlterChangStrSQL = concat(@ALTERstrSQL,  ' CHANGE COLUMN ' );     
    SET @AlterChangStrSQL = concat(@AlterChangStrSQL,  '`'  ); 
SET @AlterChangStrSQL = concat(@AlterChangStrSQL,  columnname  ); 
SET @AlterChangStrSQL = concat(@AlterChangStrSQL,  '`'  ); 
   
SET @AlterChangStrSQL = concat(@AlterChangStrSQL,  ' '  );       
SET @AlterChangStrSQL = concat(@AlterChangStrSQL,  scheme   );         
   
    SET @AlterAddStrSQL = concat(@ALTERstrSQL,  ' ADD COLUMN ' );     
SET @AlterAddStrSQL = concat(@AlterAddStrSQL,  scheme   );                 

SET @strSQL = IF((SELECT count(*) FROM information_schema.columns WHERE `table_name` = tablename AND `column_name` = columnname),
    @AlterChangStrSQL,
    @AlterAddStrSQL);
     
PREPARE preSQL FROM @strSQL;
    EXECUTE preSQL;
    deallocate prepare preSQL;

END