MySQL存储过程需要比较具有不同排序顺序的2列

Question

我有一个像这样的 MySql 存储过程

CREATE DEFINER=`uwcrossword`@`%` PROCEDURE `sp_UpdCodePage`(IN in_id int, 
IN nm varchar(25), IN nis varchar(25), IN lang varchar(25), 
IN stpt varchar(5), IN endpt varchar(5))
BEGIN
UPDATE `uwcrossword`.`CODEPAGE`
SET
`ID` = in_id,
`Name` = nm,
`NameInScript` = nis,
`Language` = lang,
`StartPoint` = stpt,
`EndPoint` = endpt
WHERE [`ID` = in_id COLLATE utf8-general-ci] 
AND [Language = lang COLLATE utf8-unicode-ci];
END

ID 列没有特定的排序规则。 Language 列具有排序规则 utf8-unicode-ci。

 我收到错误

MySQL 说:

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 '[`ID` = in_id COLLATE utf8-general-ci] AND [Language = lang COLLATE utf8-unicode' at line 11

当我尝试在没有任何排序规则信息的情况下运行相同的代码时

CREATE DEFINER=`uwcrossword`@`%` PROCEDURE `sp_UpdCodePage`(IN in_id int, 
IN nm varchar(25), IN nis varchar(25), IN lang varchar(25), 
IN stpt varchar(5), IN endpt varchar(5))
BEGIN
UPDATE `uwcrossword`.`CODEPAGE`
SET
`ID` = in_id,
`Name` = nm,
`NameInScript` = nis,
`Language` = lang,
`StartPoint` = stpt,
`EndPoint` = endpt
WHERE [`ID` = in_id] 
AND [Language = lang];
END

MySQL 说:

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 '[`ID` = in_id ] AND [Language = lang]' at line 13

Answer 1

如 Expression Syntax 下所述，simple_expr 语法生成式可以生成 (expr [, expr] ...)。请注意，括号是圆形的，而不是方形的。

但是，在这种情况下，括号不是必需的:

CREATE DEFINER=`uwcrossword`@`%` PROCEDURE sp_UpdCodePage(
  IN in_id INT,
  IN nm    VARCHAR(25),
  IN nis   VARCHAR(25),
  IN lang  VARCHAR(25), 
  IN stpt  VARCHAR(5),
  IN endpt VARCHAR(5)
)

  UPDATE uwcrossword.CODEPAGE
  SET    ID           = in_id,
         Name         = nm,
         NameInScript = nis,
         Language     = lang,
         StartPoint   = stpt,
         EndPoint     = endpt
  WHERE  ID           = in_id COLLATE utf8-general-ci
     AND Language     = lang  COLLATE utf8-unicode-ci

;