避免隐式类型转换

本章内容将介绍 TiDB 中的隐式类型转换规则、可能带来的后果及避免方法。

转换规则

当 SQL 中谓词两侧的数据类型不一致时,TiDB 将隐式地将一侧或两侧的数据类型进行转换,将其变为兼容的数据类型,以进行谓词运算。

TiDB 中隐式类型转换规则如下:

  • 如果一个或两个参数都是 NULL,比较的结果是 NULL(NULL 安全的 <=> 相等比较运算符除外,对于 NULL <=> NULL,结果为 true,不需要转换)。
  • 如果比较操作中的两个参数都是字符串,则将它们作为字符串进行比较。
  • 如果两个参数都是整数,则将它们作为整数进行比较。
  • 如果不与数字进行比较,则将十六进制值视为二进制字符串。
  • 如果其中一个参数是十进制值,则比较取决于另一个参数。如果另一个参数是十进制或整数值,则将参数与十进制值进行比较,如果另一个参数是浮点值,则将参数与浮点值进行比较。
  • 如果其中一个参数是 TIMESTAMP 或 DATETIME 列,另一个参数是常量,则在执行比较之前将常量转换为时间戳。
  • 在所有其他情况下,参数都是作为浮点数(DOUBLE 类型)比较的。

隐式类型转换引起的后果

隐式类型转换增强了人机交互的易用性,但在应用代码中,应尽量避免隐式类型转换出现,这是由于隐式类型转换会导致:

  • 索引失效
  • 精度丢失

索引失效

如下案例,account_id 为主键,其数据类型为 varchar。通过执行计划可见,该 SQL 发生了隐式类型转换,无法使用索引。

  1. DESC SELECT * FROM `account` WHERE `account_id`=6010000000009801;
  2. +-------------------------+----------------+-----------+---------------+------------------------------------------------------------+
  3. | id | estRows | task | access object | operator info |
  4. +-------------------------+----------------+-----------+---------------+------------------------------------------------------------+
  5. | TableReader_7 | 8000628000.00 | root | | data:Selection_6 |
  6. | └─Selection_6 | 8000628000.00 | cop[tikv] | | eq(cast(findpt.account.account_id), 6.010000000009801e+15) |
  7. | └─TableFullScan_5 | 10000785000.00 | cop[tikv] | table:account | keep order:false |
  8. +-------------------------+----------------+-----------+---------------+------------------------------------------------------------+
  9. 3 rows in set (0.00 sec)

运行结果简述:从以上执行计划中,可见 Cast 算子。

精度丢失

如下案例,字段 a 的数据类型为 decimal(32,0),从执行计划可以得知,出现了隐式类型转换,decimal 字段和字符串常值都被转换为 double 类型,而 double 类型的精度没有 decimal 高,出现了精度丢失,在这个 case 中,造成了筛选出范围之外的结果集的错误。

  1. DESC SELECT * FROM `t1` WHERE `a` BETWEEN '12123123' AND '1111222211111111200000';
  2. +-------------------------+---------+-----------+---------------+-------------------------------------------------------------------------------------+
  3. | id | estRows | task | access object | operator info |
  4. +-------------------------+---------+-----------+---------------+-------------------------------------------------------------------------------------+
  5. | TableReader_7 | 0.80 | root | | data:Selection_6 |
  6. | └─Selection_6 | 0.80 | cop[tikv] | | ge(cast(findpt.t1.a), 1.2123123e+07), le(cast(findpt.t1.a), 1.1112222111111112e+21) |
  7. | └─TableFullScan_5 | 1.00 | cop[tikv] | table:t1 | keep order:false, stats:pseudo |
  8. +-------------------------+---------+-----------+---------------+-------------------------------------------------------------------------------------+
  9. 3 rows in set (0.00 sec)

运行结果简述:从以上执行计划中,可见 Cast 算子。

  1. SELECT * FROM `t1` WHERE `a` BETWEEN '12123123' AND '1111222211111111200000';
  2. +------------------------+
  3. | a |
  4. +------------------------+
  5. | 1111222211111111222211 |
  6. +------------------------+
  7. 1 row in set (0.01 sec)

运行结果简述:以上执行出现了错误结果。