我在 MySQL 中有一个存储函数：
CREATE FUNCTION `login_count`(o INT, start_date DATE, end_date DATE) RETURNS INT
READS SQL DATA
BEGIN
DECLARE total_count INT;
SELECT COUNT(*) INTO total_count FROM logins as l WHERE `order_id` = o && modified BETWEEN start_date AND end_date;
RETURN total_count;
END
非常基本，接受 id、开始日期和结束日期，并返回该日期范围内的登录计数。 每当我运行它时，我都会得到 0。 除非我删除了 where 子句的日期部分。 然后它返回一个实际数字。 或者，如果我只是手动将日期放入存储的函数中，它就可以工作...所以这不是日期的问题，但只有当我通过参数列表提供日期时，它才不喜欢它。
有什么想法会导致这种情况发生吗？ 事实上，我可以手动将日期放入存储的函数中并且它可以工作，这真的让我很烦恼。 这里并没有发生太多可能会搞砸的事情，所以我有点迷失了下一步该尝试什么。
另外，有没有办法调试存储的函数/过程。 我刚刚得到 0，但是有没有办法让我调试它以尝试找出可能发生的情况？
I have a stored function in MySQL:
CREATE FUNCTION `login_count`(o INT, start_date DATE, end_date DATE) RETURNS INT
READS SQL DATA
BEGIN
DECLARE total_count INT;
SELECT COUNT(*) INTO total_count FROM logins as l WHERE `order_id` = o && modified BETWEEN start_date AND end_date;
RETURN total_count;
END
Pretty basic, takes an id, start date, and an end date and returns the count of logins for that date range. Whenever I run it I get a 0 back. Unless I removed the date section of the where clause. Then it returns the an actual number. OR if I just manually put the dates in the stored function it works... So it's not an issue of dates, but only when I'm supplying the dates through the parameter list it doesn't like it.
Any thoughts as to what would cause this to happen? The fact that I can just manually put the dates in the stored function and it works really bugs me. It's not like a lot is happening here that could mess up so I'm kind of lost as what to try next.
Also, is there a way to debug stored functions/procedures. I'm just getting a 0 back, but is there a way for me to debug this to try and figure out what might be happening?
发布评论
评论（1）
我的第一个猜测是您提供的日期格式是 MySQL 无法识别的。 MySQL 对日期文字的格式非常挑剔。 它需要 YYYY-MM-DD、YY-MM-DD、YYYYMMDD 或 YYMMDD（您也可以使用任何其他标点字符代替破折号）。 有关更多详细信息，请参阅DATETIME、DATE 和 TIMESTAMP 类型 。
您还可以使用 STR_TO_DATE() 函数。
关于调试存储过程和存储函数，您始终可以设置带有
@前缀的用户定义变量，并且该变量在存储函数返回后变为全局可用。 就像副作用一样。 因此，您可以在运行存储的函数时使用它来设置诊断信息，然后进行检查。My first guess is that you're supplying dates in a format that MySQL doesn't recognize. MySQL is pretty picky about the format of date literals. It wants YYYY-MM-DD, YY-MM-DD, YYYYMMDD, or YYMMDD (you can also use any other punctuation character in place of the dashes). See The DATETIME, DATE, and TIMESTAMP Types for more details.
You can also convert more date formats to the MySQL-accepted format with the STR_TO_DATE() function.
Regarding debugging stored procedures and stored functions, you can always set a user-defined variable with the
@prefix, and this variable becomes globally available after the stored function returns. Like a side-effect. So you can use this for setting diagnostic information while running your stored function, and then check it afterward.