delimiter 与 存储过程

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

  IF not EXISTS (
        SELECT
          *
        FROM
          information_schema. COLUMNS
        WHERE
          table_schema = 'thc_rcm'
          AND table_name = 'Cs_AccountBillDetail'
          AND column_name = 'shopSetItemId'
    ) THEN
    ALTER TABLE `thc_rcm`.`Cs_AccountBillDetail`
    ADD COLUMN `shopSetItemId` varchar(64) DEFAULT NULL COMMENT '套餐的订单明细ID' AFTER `itemId`;
 END IF;

 

 

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

DELIMITER //
  IF not EXISTS (
        SELECT
          *
        FROM
          information_schema. COLUMNS
        WHERE
          table_schema = 'thc_rcm'
          AND table_name = 'Cs_AccountBillDetail'
          AND column_name = 'shopSetItemId'
    ) THEN
    ALTER 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 ()
  BEGIN
  IF not EXISTS (
        SELECT
          *
        FROM
          information_schema. COLUMNS
        WHERE
          table_schema = 'thc_rcm'
          AND table_name = 'Cs_AccountBillDetail'
          AND column_name = 'shopSetItemId'
    ) THEN
    ALTER 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 ()
  BEGIN
  IF not EXISTS (
        SELECT
          *
        FROM
          information_schema. COLUMNS
        WHERE
          table_schema = 'thc_rcm'
          AND table_name = 'Cs_AccountBillDetail'
          AND column_name = 'shopSetItemId'
    ) THEN
    ALTER 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中执行不成功