mysql中的CASE和IF有什么区别 [英] What's the difference between CASE and IF in mysql

问题描述

我发现了很奇怪的 MySQL 行为:

I found very strange MySQL behavior:

我有一个带有变音字母的字符串，我在它上面运行了一些 IF 表达式.类似的东西:

I have a string with umlaut letters, and I run some IF expression on it. Something like:

IF(length(field) < 10, '', replace(field, "\n", "<BR>"))

它工作正常.

但是，如果我用 CASE 替换它，那么结果会在第一个 unlaut 字母上被截断！

However, if I replace this if by CASE, then the result is cut on the first unlaut letter!

CASE WHEN length(field)<10 THEN '' ELSE replace(field, "\n", "<BR>") END

另外，我注意到只有当查询中还有 GROUP BY 部分时才会发生这种情况.

Also, I noticed that it happens only when there is also GROUP BY part in the query.

我无法理解 CASE 和 IF 之间的区别 - 从逻辑的角度来看，两者都应该完全返回相同的结果.

I can't understand what's the difference between CASE and IF - from logical point of view both should return the same result exactly.

有人知道为什么这两个命令有区别吗?

Anyone knows why the is difference between these two commands?

推荐答案

"IF 是单个 fork，"CASE" 可以是多个如果您有两个以上的可选值，请使用Case"，如果您只有两个值，请使用IF".

"IF is a single fork, "CASE" can be multiple Use "Case" if you have more than two values optional values, "IF" when you have only two values.

CASE的一般结构是:

CASE x
WHEN a THEN ..
WHEN b THEN ..
...
ELSE
END

IF 的一般结构:

IF (expr)
THEN...
ELSE...
END

所以，基本上 IF 是一个只有一个 'WHEN' 语句的 CASE.

So, basically IF is a CASE with only one 'WHEN' statement.

这篇关于mysql中的CASE和IF有什么区别的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！