一个炒鸡简单的 SQL 为什么执行没返回结果？？

V2EX = way to explore

V2EX 是一个关于分享和探索的地方

现在注册

已注册用户请登录

这是一个创建于 1966 天前的主题，其中的信息可能已经有所发展或是发生改变。

SELECT * FROM LAOHU WHERE time <= 'DATE('2012-12-31')' 测试有返回结果，但是:

    SELECT
   *
FROM
    LAOHU 
WHERE
time >= 'DATE(\'2012-12-31\')'
就没返回结果了，可两个语句几乎一毛一样啊？？究竟怎么一回事，有知道的给解析下么？

9 条回复 • 2019-07-17 16:58:13 +08:00

az402

2019-07-07 02:20:24 +08:00 via Android

首先要看数据量

Coande

2019-07-07 02:46:24 +08:00 via Android

一个小于等于，一个大于等于，条件不一样吧

congeec

2019-07-07 03:55:12 +08:00

两个语句几乎一毛一样

几乎

做程序员要严谨

首先 date 外的单引号拿掉，其次二楼说的对

watzds

2019-07-07 05:40:45 +08:00 via Android

这瞎写能用就怪了

tomczhen

2019-07-07 07:35:02 +08:00 via Android

列出所有大于 2 的正整数，这个问题很简单。按楼主的逻辑，列出所有小于 2 的正整数，也应该一样简单，麻烦回答一下吧。

Juszoe

2019-07-07 09:44:16 +08:00

不懂时间和一个字符串比较有什么意义，不是应该 time >= DATE('2012-12-31')

skull

2019-07-07 12:07:54 +08:00

请尽量让自己的回复能够对别人有帮助

littlewing

2019-07-07 14:43:42 +08:00 via iPhone

The following rules describe how conversion occurs for comparison operations:

If one or both arguments are NULL, the result of the comparison is NULL, except for the NULL-safe <=> equality comparison operator. For NULL <=> NULL, the result is true. No conversion is needed.

If both arguments in a comparison operation are strings, they are compared as strings.

If both arguments are integers, they are compared as integers.

Hexadecimal values are treated as binary strings if not compared to a number.

If one of the arguments is a TIMESTAMP or DATETIME column and the other argument is a constant, the constant is converted to a timestamp before the comparison is performed. This is done to be more ODBC-friendly. This is not done for the arguments to IN(). To be safe, always use complete datetime, date, or time strings when doing comparisons. For example, to achieve best results when using BETWEEN with date or time values, use CAST() to explicitly convert the values to the desired data type.

A single-row subquery from a table or tables is not considered a constant. For example, if a subquery returns an integer to be compared to a DATETIME value, the comparison is done as two integers. The integer is not converted to a temporal value. To compare the operands as DATETIME values, use CAST() to explicitly convert the subquery value to DATETIME.

If one of the arguments is a decimal value, comparison depends on the other argument. The arguments are compared as decimal values if the other argument is a decimal or integer value, or as floating-point values if the other argument is a floating-point value.

In all other cases, the arguments are compared as floating-point (real) numbers.

For information about conversion of values from one temporal type to another, see Section 11.3.7, “ Conversion Between Date and Time Types ”.

cway

2019-07-17 16:58:13 +08:00

首先感谢大家回去我测试了下 @az402
@Coande
@congeec 跟条件貌似没关系，单引号也是没影响，只是发现用非日期字符串例如非 yyyy-MM-dd 之类的字符串识别不了，虽然<=2012 但是返回值把所有记录全返回了。但是<='2012-12-31'就没问题
@watzds 也不是瞎写的，去掉转义符跟外面引号肯定可以，mybatis plus 中 gt 条件传 Date 类型数据会自动转成这种带转义的形式，后来我直接传字符串了
@tomczhen
@Juszoe 亲自试验下会发现 time <= '2018-02-01' 这种也是可以的之前我也怀疑，
@skull @littlewing 感谢