时间戳

首页 > 解决方案 > mysql 5.7 使用时的过程语法错误不存在

mysql - mysql 5.7 使用时的过程语法错误不存在

问题描述

这是获取序列号的过程。我使用“如果不存在”来确定密钥是否存在，但它失败了：

DROP PROCEDURE IF EXISTS `pro_GetSysSerialnumber`;
CREATE PROCEDURE pro_GetSysSerialnumber(IN sName varchar(255), IN sTenantId int)
BEGIN
  if not exists(select 1 from sys_serialnumber where `name` = `sName` and `TenantId`=`sTenantId`) then
      insert into sys_serialnumber (`name`, `next`, `tenantId`) values (`sName`,1 ,`sTenantId`);
  end if;
  update sys_serialnumber set `next`=`next` + 1 where `name` = `sName` and `TenantId`=`sTenantId`;
  select `Id`, `Name`, `next` - 1 as `next`, `tenantId` from sys_serialnumber where `name` = `sName` 
     and `TenantId`=`sTenantId`;
END

错误：

DROP PROCEDURE IF EXISTS `pro_GetSysSerialnumber`
> OK
> 时间: 0.378s


CREATE PROCEDURE pro_GetSysSerialnumber(IN sName varchar(255), IN sTenantId int)
BEGIN

    if not exists(select 1 from sys_serialnumber where `name` = `sName` and `TenantId`=`sTenantId`) then
        insert into sys_serialnumber (`name`, `next`, `tenantId`) values (`sName`,1 ,`sTenantId`)
> 1064 - 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 '' at line 5
> 时间: 0.189s

如果我删除第 4 行中的“not”，脚本将成功：

DROP PROCEDURE IF EXISTS `pro_GetSysSerialnumber`;

CREATE PROCEDURE pro_GetSysSerialnumber(IN sName varchar(255), IN sTenantId int)
BEGIN

    if   exists(select 1 from sys_serialnumber where `name` = `sName` and `TenantId`=`sTenantId`) then
        insert into sys_serialnumber (`name`, `next`, `tenantId`) values (`sName`,1 ,`sTenantId`);
    end if;
  update sys_serialnumber set `next`=`next` + 1 where `name` = `sName` and `TenantId`=`sTenantId`;
    select `Id`, `Name`, `next` - 1 as `next`, `tenantId` from sys_serialnumber where `name` = `sName` and `TenantId`=`sTenantId`;
END

成功信息：

DROP PROCEDURE IF EXISTS `pro_GetSysSerialnumber`
> OK
> 时间: 0.377s


CREATE PROCEDURE pro_GetSysSerialnumber(IN sName varchar(255), IN sTenantId int)
BEGIN

    if   exists(select 1 from sys_serialnumber where `name` = `sName` and `TenantId`=`sTenantId`) then
        insert into sys_serialnumber (`name`, `next`, `tenantId`) values (`sName`,1 ,`sTenantId`);
    end if;
  update sys_serialnumber set `next`=`next` + 1 where `name` = `sName` and `TenantId`=`sTenantId`;
    select `Id`, `Name`, `next` - 1 as `next`, `tenantId` from sys_serialnumber where `name` = `sName` and `TenantId`=`sTenantId`;
END
> Affected rows: 0
> 时间: 0.189s

这很奇怪。有什么理想吗？

标签： mysql

解决方案

推荐阅读