令人沮丧的 MySQL CREATE PROCEDURE 错误

发布于 2024-08-13 10:07:27 字数 670 浏览 8 评论 0原文

希望这是一个很快就能解决的问题。

这是我的 .sql 文件：

USE my_db;

DELIMITER $$
CREATE PROCEDURE searchLocation(IN argQuery VARCHAR(32), IN argLimit INT)
BEGIN
  SELECT DISTINCT `suburb`, `postcode`
  FROM `location`
  WHERE `suburb` LIKE '%argQuery%'
  OR `postcode` LIKE 'argQuery%'
  LIMIT argLimit
  ;
END
$$
DELIMITER ;

这是输出：

ERROR 1064 (42000) at line 4: 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 'argLimit
  ;
END' at line 8

所以它似乎不喜欢我的参数 argLimit，但我无法弄清楚为什么。我一定是在做一些愚蠢的事情。

我正在使用 MySQL 5.0.51。

谢谢。

原文

Hopefully this is a nice quick one to resolve.

Here is my .sql file:

USE my_db;

DELIMITER $
CREATE PROCEDURE searchLocation(IN argQuery VARCHAR(32), IN argLimit INT)
BEGIN
  SELECT DISTINCT `suburb`, `postcode`
  FROM `location`
  WHERE `suburb` LIKE '%argQuery%'
  OR `postcode` LIKE 'argQuery%'
  LIMIT argLimit
  ;
END
$
DELIMITER ;

This is the output:

ERROR 1064 (42000) at line 4: 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 'argLimit
  ;
END' at line 8

So it appears to not like my parameter argLimit, but I can't work out why. I must be doing something silly.

I'm using MySQL 5.0.51.

Thanks.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

亣腦蒛氧 2024-08-20 10:07:27

LIMIT 必须是常量，不能在 MySQL 的过程或函数内进行参数化。但是，可以通过使用 PREPARE ... EXECUTE ... USING 语法。

它最终可能看起来像这样：

...
SET @qry= argQuery;
SET @lmt= argLimit;

PREPARE stmt FROM 'SELECT ... LIKE ? ... LIKE ? ... LIMIT ?';
EXECUTE stmt USING @qry, @qry, @lmt;
DEALLOCATE PREPARE stmt;

LIMIT must be a constant and cannot be parametrized inside a procedure or function in MySQL. However, it's possible to workaround this issue by using the PREPARE ... EXECUTE ... USING syntax.

It might end up looking somewhat like this:

...
SET @qry= argQuery;
SET @lmt= argLimit;

PREPARE stmt FROM 'SELECT ... LIKE ? ... LIKE ? ... LIMIT ?';
EXECUTE stmt USING @qry, @qry, @lmt;
DEALLOCATE PREPARE stmt;

回复收藏 0 原文