数据库隐式转换

今天在查询数据库时发现一个问题,错误把char类型的id写成了int

 但是却查询出来许多并不相等的数据,最后查阅资料发现,数据库存在一个隐式转换规则

The following rules describe how conversion occurs for comparison operations:
1.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.
2.If both arguments in a comparison operation are strings, they are compared as strings.
3.If both arguments are integers, they are compared as integers. Hexadecimal values are treated as binary strings if not compared to a
number.
4.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.
5.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.
6.In all other cases, the arguments are compared as floating-point (real) numbers. For example, a comparison of string and numeric
operands takes place as a comparison of floating-point numbers.

  1. 当操作符与不同类型的操作数一起使用时,会发生类型转换以使操作数兼容。则会发生转换隐式
  2. 发生隐式转换的条件:
  • 两个参数至少有一个是 NULL 时,比较的结果也是 NULL,例外是使用 <=> 对两个 NULL 做比较时会返回 1,这两种情况都不需要做类型转换
  • 两个参数都是字符串,会按照字符串来比较,不做类型转换
  • 两个参数都是整数,按照整数来比较,不做类型转换
  • 十六进制的值和非数字做比较时,会被当做二进制串
  • 有一个参数是 TIMESTAMP 或 DATETIME,并且另外一个参数是常量,常量会被转换为 timestamp
  • 有一个参数是 decimal 类型,如果另外一个参数是 decimal 或者整数,会将整数转换为 decimal 后进行比较,如果另外一个参数是浮点数,则会把 decimal 转换为浮点数进行比较
  • 所有其他情况下,两个参数都会被转换为浮点数再进行比较

最后将比较的值进行转换发现,每个数都是9999999999

 

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值