Suggest a feature
×

Welcome to TagMyCode

Please login or create account to add a snippet.
0
0
 
0
Language: SQL
Posted by: Adam Jackson
Added: Jun 19, 2020 8:34 AM
Views: 124
Tags: mysql
  1. DROP PROCEDURE IF EXISTS schema_change;
  2.  
  3. DELIMITER $$
  4.  
  5. CREATE PROCEDURE schema_change() BEGIN
  6.        
  7.         IF EXISTS (SELECT * FROM information_schema.columns WHERE TABLE_NAME = 'tb_warn' AND column_name = 'OPEARTION_TIME') THEN
  8.  
  9.         ALTER TABLE `tb_warn`
  10.                 CHANGE COLUMN `OPEARTION_TIME` `OPERATION_TIME` DATETIME NOT NULL COMMENT '发生时间' AFTER `EQUIPMENT_ID`,
  11.                 ADD COLUMN `CONFIRM_FLAG` TINYINT ZEROFILL NOT NULL DEFAULT '0' AFTER `CREATE_PERSONNEL`,
  12.                 CHANGE COLUMN `UPDATE_TIME` `CONFIRM_TIME` DATETIME NULL DEFAULT NULL COMMENT '更新时间' AFTER `CONFIRM_FLAG`,
  13.                 CHANGE COLUMN `UPDATE_PERSONNEL` `CONFIRM_PERSONNEL` VARCHAR(255) NULL DEFAULT NULL COMMENT '更新人员' COLLATE 'utf8_general_ci' AFTER `CONFIRM_TIME`,
  14.                 ADD INDEX `EQUIPMENT_ID` (`EQUIPMENT_ID`);
  15.        
  16.         END IF
  17.        
  18.        
  19. END $$
  20.  
  21. CALL schema_change() $$
  22.  
  23. DELIMITER ;
  24.  
  25. DROP PROCEDURE IF EXISTS schema_change;