delimiter 与 存储过程

1.如此执行语句不行，需要在 delimiter

  IF not EXISTS (SELECT*FROMinformation_schema. COLUMNSWHEREtable_schema = 'thc_rcm'AND table_name = 'Cs_AccountBillDetail'AND column_name = 'shopSetItemId') THENALTER TABLE `thc_rcm`.`Cs_AccountBillDetail`ADD COLUMN `shopSetItemId` varchar(64) DEFAULT NULL COMMENT '套餐的订单明细ID' AFTER `itemId`;END IF;

 

 

2.这样写也不行，因为这样的语句必须在存储过程里执行

DELIMITER //IF not EXISTS (SELECT*FROMinformation_schema. COLUMNSWHEREtable_schema = 'thc_rcm'AND table_name = 'Cs_AccountBillDetail'AND column_name = 'shopSetItemId') THENALTER TABLE `thc_rcm`.`Cs_AccountBillDetail`ADD COLUMN `shopSetItemId` varchar(64) DEFAULT NULL COMMENT '套餐的订单明细ID' AFTER `itemId`;END IF;END//
DELIMITER ;

 

 

 

3.OK

 

DELIMITER //
CREATE PROCEDURE thc_rcm_change ()BEGINIF not EXISTS (SELECT*FROMinformation_schema. COLUMNSWHEREtable_schema = 'thc_rcm'AND table_name = 'Cs_AccountBillDetail'AND column_name = 'shopSetItemId') THENALTER TABLE `thc_rcm`.`Cs_AccountBillDetail`ADD COLUMN `shopSetItemId` varchar(64) DEFAULT NULL COMMENT '套餐的订单明细ID' AFTER `itemId`;END IF;END//
DELIMITER ;
CALL thc_rcm_change ();
DROP PROCEDURE
IF EXISTS thc_rcm_change;

 

 

 

 4.将添加字段变为删除字段则执行不通过，待解？？？？？？？？？？？

 

DELIMITER //
CREATE PROCEDURE thc_rcm_change ()BEGINIF not EXISTS (SELECT*FROMinformation_schema. COLUMNSWHEREtable_schema = 'thc_rcm'AND table_name = 'Cs_AccountBillDetail'AND column_name = 'shopSetItemId') THENALTER TABLE `thc_rcm`.`Cs_AccountBillDetail`drop COLUMN shopSetItemId;END IF;END//
DELIMITER ;
CALL thc_rcm_change ();
DROP PROCEDURE
IF EXISTS thc_rcm_change;

 

---

错误提示

Query execution failed

原因：
SQL 错误 [1064] [42000]: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ') THEN
ALTER TABLE `thc_rcm`.`Cs_AccountBillDetail` drop column setMealDetai' at line 1

Query execution failed

---

 

注：

windows navicat 中可以执行成功

Mac DBeaver中执行不成功