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中执行不成功