V2EX = way to explore
V2EX 是一个关于分享和探索的地方
现在注册
已注册用户请  登录
cway
V2EX  ›  程序员

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

  •  
  •   cway · 2019-07-07 01:45:43 +08:00 · 2886 次点击
    这是一个创建于 1727 天前的主题,其中的信息可能已经有所发展或是发生改变。

    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
        1
    az402  
       2019-07-07 02:20:24 +08:00 via Android
    首先要看数据量
    Coande
        2
    Coande  
       2019-07-07 02:46:24 +08:00 via Android
    一个小于等于,一个大于等于,条件不一样吧
    congeec
        3
    congeec  
       2019-07-07 03:55:12 +08:00
    两个语句几乎一毛一样

    几乎

    做程序员要严谨

    首先 date 外的单引号拿掉,其次二楼说的对
    watzds
        4
    watzds  
       2019-07-07 05:40:45 +08:00 via Android
    这瞎写能用就怪了
    tomczhen
        5
    tomczhen  
       2019-07-07 07:35:02 +08:00 via Android
    列出所有大于 2 的正整数,这个问题很简单。按楼主的逻辑,列出所有小于 2 的正整数,也应该一样简单,麻烦回答一下吧。
    Juszoe
        6
    Juszoe  
       2019-07-07 09:44:16 +08:00
    不懂时间和一个字符串比较有什么意义,不是应该 time >= DATE('2012-12-31')
    skull
        7
    skull  
       2019-07-07 12:07:54 +08:00
    请尽量让自己的回复能够对别人有帮助
    littlewing
        8
    littlewing  
       2019-07-07 14:43:42 +08:00 via iPhone   ❤️ 1
    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
        9
    cway  
    OP
       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 感谢
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   我们的愿景   ·   实用小工具   ·   951 人在线   最高记录 6543   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 27ms · UTC 21:34 · PVG 05:34 · LAX 14:34 · JFK 17:34
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.