SQL中的1总是等于'1'吗？ [英] Does 1 always equal '1' in SQL?

问题描述

我试图确定标准的SQL行为，用于比较一个数字与相同数字的字符或字符串版本。 SELECT 1 ='1'（或类似）总是返回某种真实值（ true 1 ，'t'等）？我已经确认在PostgreSQL和MySQL，但我不能找到一个资源的SQL作为一个整体。

更新：问题是，我想弄清楚如果使用一个数字，没有引号，将在选择/插入/更新/等工作。

SELECT 1 ='1'

/ code>给出 TRUE ，因为'1'是 INT的正确构造函数

但是SQL使用严格的类型，请参阅：

 ＃SELECT 1 = CAST（'1'AS TEXT）; 
错误：操作符不存在：integer = text 
 LINE 1：SELECT 1 = CAST（'1'AS TEXT）; 
 ^ 
提示：没有运算符匹配给定的名称和参数类型。您可能需要添加显式类型转换。关于标准（SQL 92,99& 2003），似乎是错误的：
 > 
 
 
 < literal> :: = 
< signed numeric literal> 
 | <一般文字> 
 
< general literal> :: = 
< character string literal> 
 | <国家字符串文字> 
 | < bit string literal> 
 | < hex string literal> 
 | < datetime literal> 
 | < interval literal> 
 
< signed numeric literal> :: = 
 [< sign> ]<无符号数字文字> 
 
< unsigned numeric literal> :: = 
< exact numeric literal> 
 | <近似数字文字> 
 
< exact numeric literal> :: = 
< unsigned integer> [<周期> [<无符号整数> ]] 
 | < period> <无符号整数> 
 
< unsigned integer> :: =< digit> ... 
 
<字符串literal> :: = 
 [< introducer>< character set specification> ] 
< quote> [< character representation> ...]< quote> 
 [{< separator> ...< quote> [< character representation> ...]< quote> } ...] 
  
因为< quote> 仅包含在< bit string literal> ，< hex string literal> ，...不是数字文字... 
 
I am trying to determine that standard SQL behaviour for comparing a number to a character or string version of the same number. Does SELECT 1 = '1' (or the like) always return some sort of "truthy" value (true, 1, 't', etc.)? I have confirmed as much on PostgreSQL and MySQL, but I cannot find a resource for SQL as a whole.

Update: The purpose for the question is that I'm trying to figure out if using a number, without the quotes, will work when selecting/inserting/updating/etc. from a non-numeric field whose value is a number.
解决方案
SELECT 1='1' gives TRUE since '1' is a correct constructor for INT in all implementation known to me.

But SQL uses strict typing, see that:
# SELECT 1=CAST('1' AS TEXT);
ERROR:  operator does not exist: integer = text
LINE 1: SELECT 1=CAST('1' AS TEXT);
                ^
HINT:  No operator matches the given name and argument type(s). You might need to add  explicit type casts.
Regarding the standard (SQL 92, 99 & 2003) it seems to be wrong:
     <literal> ::=
            <signed numeric literal>
          | <general literal>

     <general literal> ::=
            <character string literal>
          | <national character string literal>
          | <bit string literal>
          | <hex string literal>
          | <datetime literal>
          | <interval literal>

     <signed numeric literal> ::=
          [ <sign> ] <unsigned numeric literal>

     <unsigned numeric literal> ::=
            <exact numeric literal>
          | <approximate numeric literal>

     <exact numeric literal> ::=
            <unsigned integer> [ <period> [ <unsigned integer> ] ]
          | <period> <unsigned integer>

     <unsigned integer> ::= <digit>...

     <character string literal> ::=
          [ <introducer><character set specification> ]
          <quote> [ <character representation>... ] <quote>
            [ { <separator>... <quote> [ <character representation>... ] <quote> }... ]
because <quote> is only contained in <bit string literal>, <hex string literal>, ... but not in numeric literals... 

                        
这篇关于SQL中的1总是等于'1'吗？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！