phpMyAdmin创建转账存储过程报MySQL 1064语法错误如何解决？

问题原因 #

• 核心错误是创建包含多条SQL语句的存储过程时，没有用BEGIN...END代码块包裹逻辑体，且没有临时修改MySQL的语句分隔符。
  MySQL默认语句分隔符为;，你编写的存储过程内两条UPDATE语句末尾都带有;，解析器遇到第一个;时会判定CREATE PROCEDURE语句已经结束，后续的第二条UPDATE会被识别为独立执行的语句，直接触发语法报错。
• 额外注意：你当前的转账逻辑没有加事务控制，一旦第一条UPDATE执行成功、第二条执行失败，会出现资金不一致的问题，必须补充事务处理。

正确操作方法 #

方案1：phpMyAdmin界面操作 #

1. 在SQL输入框下方找到「分隔符」设置项，将默认的;修改为//
2. 执行以下创建语句：

CREATE DEFINER=`root`@`localhost` PROCEDURE `ExecDebitCredit`(IN `accNum1` VARCHAR(150), IN `accNum2` VARCHAR(150), IN `amount` DECIMAL(18,2))
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
BEGIN
    -- 开启事务保证操作原子性
    START TRANSACTION;
    UPDATE financeplusacct SET bal = bal - amount WHERE accNum = accNum1;
    UPDATE financeplusacct SET bal = bal + amount WHERE accNum = accNum2;
    -- 所有语句执行成功后提交
    COMMIT;
END //

3. 存储过程创建完成后，将分隔符改回默认的;即可。

方案2：直接执行SQL脚本 #

手动声明分隔符修改逻辑即可：

-- 临时修改分隔符为//
DELIMITER //

CREATE DEFINER=`root`@`localhost` PROCEDURE `ExecDebitCredit`(IN `accNum1` VARCHAR(150), IN `accNum2` VARCHAR(150), IN `amount` DECIMAL(18,2))
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
BEGIN
    START TRANSACTION;
    UPDATE financeplusacct SET bal = bal - amount WHERE accNum = accNum1;
    UPDATE financeplusacct SET bal = bal + amount WHERE accNum = accNum2;
    COMMIT;
END //

-- 改回默认分隔符
DELIMITER ;

优化建议 #

可以新增余额校验逻辑，避免扣款账户余额为负，同时返回执行结果标识：

DELIMITER //
CREATE DEFINER=`root`@`localhost` PROCEDURE `ExecDebitCredit`(IN `accNum1` VARCHAR(150), IN `accNum2` VARCHAR(150), IN `amount` DECIMAL(18,2), OUT `exec_result` TINYINT)
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
BEGIN
    DECLARE acc1_balance DECIMAL(18,2);
    -- 加行锁避免并发操作下的余额计算错误
    SELECT bal INTO acc1_balance FROM financeplusacct WHERE accNum = accNum1 FOR UPDATE;
    IF acc1_balance >= amount THEN
        START TRANSACTION;
        UPDATE financeplusacct SET bal = bal - amount WHERE accNum = accNum1;
        UPDATE financeplusacct SET bal = bal + amount WHERE accNum = accNum2;
        COMMIT;
        -- 返回1表示执行成功
        SET exec_result = 1;
    ELSE
        -- 返回0表示余额不足
        SET exec_result = 0;
    END IF;
END //
DELIMITER ;

内容的提问来源于stack exchange，提问作者MikeBlue