为什么在MySQL函数中使用CAST()无法正常工作？

Question

I have the following function:

CREATE FUNCTION test_rule (value TEXT, greater_than DECIMAL(10,4))
RETURNS BOOL
RETURN greater_than IS NULL OR CAST(value AS DECIMAL(10,4)) > greater_than;

如果我使用一个空字符串作为value参数调用此函数，我会收到一个错误：

SELECT test_rule('',10)

（1366）：行-1的列“”的值无效。

但是不使用函数时，相同的表达式可以正常工作。

SELECT '' IS NULL OR CAST('' AS DECIMAL(10,4)) > 10

0

这是为什么呢？

我正在使用 MySQL 5.6.22 版本。

- Michael Härtl

---

两者都对我来说可行，MySQL 5.6.28。 - jonasfh

你能检查一下服务器的 sql_mode 设置吗？尝试将当前会话的 sql_mode 设置为'' (空字符串)，然后再次执行存储过程。 - Shadow

您能否检查一下存储函数所应用的 SQL 模式，通过从 INFORMATION_SCHEMA.ROUTINES 表中获取该函数的 sql_mode 值呢？ - Shadow

您的输入值类型为TEXT与空字符串''严格不同。您是否尝试使用VARCHAR创建函数？ - jonasfh

我准备了一个详细的答案，如果还有不清楚的地方请告诉我。 - Shadow

Answer 1

mysql处理某些数据转换的方式也受sql模式设置（特别是严格SQL模式部分）和CAST函数的影响。

关于这些限制如何应用于表达式计算，文档有些含糊不清，但是CAST函数文档确实提到：

“SQL模式会影响转换操作的结果。”

评论中提到sql_mode被设置为STRICT_TRANS_TABLES, NO_ENGINE_SUBSTITUTION。 STRICT_TRANS_TABLES表示启用了事务表的严格SQL模式，可能也适用于强制转换操作。 ''（空字符串）不能正确地转换为数字。 如果未启用严格SQL模式，则MySQL会生成警告并使用给定数值类型的默认值-0进行评估。 但是，如果启用了严格的SQL模式，则此类转换将导致错误。

您可以通过将适当的值设置为sql_mode变量来在全局或会话级别上更改sql模式。

存储程序还有一个复杂性。存储程序在编译时间使用有效的sql模式设置：

MySQL在创建或更改例程时存储sql_mode系统变量设置，并始终以此设置执行例程，而不管例程开始执行时当前服务器SQL模式如何。

如果您希望不同的存储过程在不同的sql模式设置下运行，则

1. 删除现有的存储例程
2. 将sql模式更改为所需的设置
3. 重新创建存储程序

您可以通过列出INFORMATION_SCHEMA.ROUTINES表（sql_mode列）来查询任何存储例程的sql模式设置。

然而，在这种情况下，我会考虑重新编写代码，以便检查value参数是否等于空字符串''，而不是开始使用sql模式设置。问题在于，这些特殊的sql模式设置要求可能会在系统更改或迁移期间被轻易地遗忘或忽视，现有代码可能会开始抛出错误，并且很难追溯问题的源头。

- Shadow

---

谢谢。我也尝试过先让我的代码适用于空字符串 ''，但它仍然失败了。所以我放弃了，现在有一个可行的解决方案，我在 SET SESSION sql_mode='' 之后创建例程。这似乎起作用了。 - Michael Härtl

然后，请确保在代码和操作指南中正确记录这一点。曾经有一个案例，当应用程序迁移时，这些细节没有被注意到。调试所有存储过程并确定哪些需要不同的SQL模式设置是相当痛苦的。 - Shadow