数据类型转换规则

表达式求值的类型转换

当表达式中的运算符与不同类型的操作数一起使用时，会发生数据类型转换以使操作数兼容。

转换方式

OceanBase 数据库支持显式数据类型转换和隐式数据类型转换。

隐式数据类型转换发生在这样的场景中：一个操作需要一个指定数据类型的参数，但语句的实际参数值并不是指定数据类型，这时 OceanBase 数据库会将实际参数值转为指定数据类型后，再进行后续操作。

如下例所示，OceanBase 数据库会根据需要自动将字符串转换为数字，反之亦然。

obclient> SELECT 1+'1';
+-------+
| 1+'1' |
+-------+
|     2 |
+-------+
1 row in set

OceanBase 数据库的显示数据类型转换通过 CAST 函数实现。如下例所示：

obclient> SELECT 31.4, CAST(31.4 AS TIME);
+------+--------------------+
| 31.4 | CAST(31.4 AS TIME) |
+------+--------------------+
| 31.4 | 00:00:31           |
+------+--------------------+
1 row in set

有关 CAST 函数的相关信息，请参见 CAST。

转换规则

比较运算的转换规则

• 如果一个或两个参数为 NULL，则比较结果为 NULL，但 <=> 比较运算符除外。对于 NULL <=> NULL，结果为真，不需要转换。

• 如果比较运算中的两个参数都是字符串，则将它们作为字符串进行比较。

• 如果两个参数都是整数，则将它们作为整数进行比较。

• 如果不与数字进行比较，十六进制值将被视为二进制字符串。

• 来自一个或多个表的单行子查询不被视为常量。例如，如果子查询返回的整数要与 DATETIME 值进行比较，则将两个操作数作为整数进行。由于整数不会被转换为时间值，所以要将操作数作为 DATETIME 值进行比较，请使用 CAST() 将子查询值显式转换为 DATETIME。

  如果其中一个参数是 TIMESTAMP 或 DATETIME 列，而另一个参数是常量，则在执行比较之前，该常量将转换为时间戳。为安全起见，在进行比较时始终使用完整的日期时间、日期或时间字符串。例如，在 BETWEEN 与日期或时间值一起使用时，要想获得最佳结果，请使用 CAST() 将值显式转换为所需的数据类型。

• 如果其中一个参数是十进制值，则比较类型取决于另一个参数。如果另一个参数是十进制或整数值，则将参数作为十进制值进行比较，如果另一个参数是浮点值，则作为浮点值进行比较。

• 除上述以外的情况，参数将作为浮点（实数）数进行比较。例如，字符串和数字操作数的比较是作为浮点数的比较进行的。

  浮点数和 INTEGER 类型的大值之间的比较是近似的，因为整数在比较之前被转换为双精度浮点数，无法准确表示所有 64 位整数。

类型降级

在满足比较语义的条件下，尽可能让常量往列类型对齐，从而实现高效的比较。目前支持标准语义下绝大部分列和常量的比较，剩余会在列上加 cast 的比较，后面单独列出:

• int 或 uint 列，支持常量的是字符类型、decimal 类型、浮点数（double/float）类型降级到整型列。
• decimal 列，支持浮点数（double/float） 类型降级到 decimal 列。
• year 列，支持数值类型（包含整型、浮点数、decimal 类型）、字符类型、时间类型（包含 date、datetime、timestamp、time 等）降级到 year 列。
• date 列，支持字符类型、datetime、timestamp、time 类型降级到 date 列。
• timestamp 列，支持 datetime 降级到 timestamp。

目前会在列上加 cast 的场景（不考虑 array，xml，json 等复杂类型）如下:

• float 列，float 列的比较总是使用 double 进行比较，所以 float 列和任意常量比较都会加 cast。
• fixed double 列，即建表指定 ps，比如 double(10, 2), 当常量的小数位更多时，会对齐精度比较，此时会在列上加cast。
• 字符列，字符和数值类型的比较是按照数值序比较，会把列 cast 成数值，另外字符集的比较规则也可能在列上加上 cast。

非标比较

非标比较的逻辑和类型降级处理类似，都是只处理常量和列的比较，如果是列和列的比较，则不会处理。目前仅支持整型常量和字符列的比较，注意，如果设置成 range，可能排序的结果不符合标准语义，配置时需要根据数据特征选取。

下面示例说明：

1. 创建了一个名为 t1 的表，其中有一个 biz_day 列，类型为 varchar(10)。

   obclient> CREATE TABLE t1 (biz_day varchar(10));
   Query OK, 0 rows affected (0.216 sec)
   

2. 查看默认情况下的执行计划。

   obclient> EXPLAIN SELECT * FROM t1 WHERE biz_day BETWEEN 20200101 AND 20200201;
   +-------------------------------------------------------------------------------------------------------------------------------------------------+
   | Query Plan                                                                                                                                      |
   +-------------------------------------------------------------------------------------------------------------------------------------------------+
   | ======================================================                                                                                          |
   | |ID|OPERATOR              |NAME|EST.ROWS|EST.TIME(us)|                                                                                          |
   | ------------------------------------------------------                                                                                          |
   | |0 |COLUMN TABLE FULL SCAN|T1  |2       |3           |                                                                                          |
   | ======================================================                                                                                          |
   | Outputs & filters:                                                                                                                              |
   | -------------------------------------                                                                                                           |
   |   0 - output([T1.BIZ_DAY]), filter([cast(T1.BIZ_DAY, NUMBER(-1, -85)) >= 20200101], [cast(T1.BIZ_DAY, NUMBER(-1, -85)) <= 20200201]), rowset=16 |
   |       access([T1.BIZ_DAY]), partitions(p0)                                                                                                      |
   |       is_index_back=false, is_global_index=false, filter_before_indexback[false,false],                                                         |
   |       range_key([T1.__pk_increment]), range(MIN ; MAX)always true                                                                               |
   +-------------------------------------------------------------------------------------------------------------------------------------------------+
   11 rows in set (0.004 sec)
   

   • 查询计划：默认情况下，查询优化器会将 biz_day 列从字符串类型转换为数字类型，然后进行比较。
   • 过滤条件：filter([cast(T1.BIZ_DAY, NUMBER(-1, -85)) >= 20200101], [cast(T1.BIZ_DAY, NUMBER(-1, -85)) <= 20200201]) 表示将 biz_day 转换为数字后进行范围比较。

3. 设置 non_standard_comparison_level 为 equal 然后查看执行计划。

   obclient> EXPLAIN SELECT /*+opt_param('non_standard_comparison_level', 'equal')*/ * FROM t1 WHERE biz_day BETWEEN 20200101 AND 20200201;
   +----------------------------------------------------------------------------------------------------------------------------------------------------------------+
   | Query Plan                                                                                                                                                     |
   +----------------------------------------------------------------------------------------------------------------------------------------------------------------+
   | ======================================================                                                                                                         |
   | |ID|OPERATOR              |NAME|EST.ROWS|EST.TIME(us)|                                                                                                         |
   | ------------------------------------------------------                                                                                                         |
   | |0 |COLUMN TABLE FULL SCAN|t1  |1       |3           |                                                                                                         |
   | ======================================================                                                                                                         |
   | Outputs & filters:                                                                                                                                             |
   | -------------------------------------                                                                                                                          |
   |   0 - output([t1.biz_day]), filter([cast(t1.biz_day, DECIMAL(-1, -1)) >= cast(20200101, DECIMAL(20, 0))], [cast(t1.biz_day, DECIMAL(-1, -1)) <= cast(20200201, |
   |        DECIMAL(20, 0))]), rowset=16                                                                                                                            |
   |       access([t1.biz_day]), partitions(p0)                                                                                                                     |
   |       is_index_back=false, is_global_index=false, filter_before_indexback[false,false],                                                                        |
   |       range_key([t1.__pk_increment]), range(MIN ; MAX)always true                                                                                              |
   +----------------------------------------------------------------------------------------------------------------------------------------------------------------+
   12 rows in set (0.003 sec)
   

   • 查询计划：设置 non_standard_comparison_level 为 equal 后，查询优化器将 biz_day 列从字符串类型转换为十进制类型，然后进行比较。
   • 过滤条件：filter([cast(t1.biz_day, DECIMAL(-1, -1)) >= cast(20200101, DECIMAL(20, 0))], [cast(t1.biz_day, DECIMAL(-1, -1)) <= cast(20200201, DECIMAL(20, 0))]) 表示将 biz_day 转换为十进制后进行范围比较。由于 between 是范围查询，所以 equal 的等值比较规则不会转换常量类型。

4. 设置 non_standard_comparison_level 为 range 然后查看执行计划。

   obclient> EXPLAIN SELECT /*+opt_param('non_standard_comparison_level', 'range')*/ * FROM t1 WHERE biz_day BETWEEN 20200101 AND 20200201;
   +-------------------------------------------------------------------------------------------------------------------------------------------------------+
   | Query Plan                                                                                                                                            |
   +-------------------------------------------------------------------------------------------------------------------------------------------------------+
   | ======================================================                                                                                                |
   | |ID|OPERATOR              |NAME|EST.ROWS|EST.TIME(us)|                                                                                                |
   | ------------------------------------------------------                                                                                                |
   | |0 |COLUMN TABLE FULL SCAN|t1  |1       |3           |                                                                                                |
   | ======================================================                                                                                                |
   | Outputs & filters:                                                                                                                                    |
   | -------------------------------------                                                                                                                 |
   |   0 - output([t1.biz_day]), filter([t1.biz_day >= demote_cast(20200101, VARCHAR(10))], [t1.biz_day <= demote_cast(20200201, VARCHAR(10))]), rowset=16 |
   |       access([t1.biz_day]), partitions(p0)                                                                                                            |
   |       is_index_back=false, is_global_index=false, filter_before_indexback[false,false],                                                               |
   |       range_key([t1.__pk_increment]), range(MIN ; MAX)always true                                                                                     |
   +-------------------------------------------------------------------------------------------------------------------------------------------------------+
   11 rows in set (0.004 sec)
   

   • 查询计划：设置 non_standard_comparison_level 为 range 后，查询优化器将整型常量转换为字符串类型，然后进行比较。
   • 过滤条件：filter([t1.biz_day >= demote_cast(20200101, VARCHAR(10))], [t1.biz_day <= demote_cast(20200201, VARCHAR(10))]) 表示将整型常量转换为字符串后进行范围比较。非标比较后按照字符序比较，比如 '202002010' 属于字符序的 ['20200101', '20200201']，但是不属于数值序 [20200101, 20200201] 的范围里。

其他相关转换规则

• 从字符串到浮点数和从整数到浮点数的转换方式不一定相同。结果可能会受到编译器版本等因素的影响。避免此类问题的一种方法是使用 CAST() 以防止该值不会隐式转换为浮点数。

• 将数值或时间值隐式转换为字符串会产生一个值，该值具有由 character_set_connection 和 collation_connection 系统变量确定的字符集和字符序。

  这意味着此类转换会生成非二进制字符串（CHAR、VARCHAR 或 LONGTEXT 值），如果将连接字符集设置为二进制，则转换结果是二进制字符串（BINARY、VARBINARY 或 LONGBLOB 值）。

• 对于整数表达式求值时的类型转换有些不同。例如，当您用一个整数表达式作为 CREATE TABLE 语句的一部分时，根据表达式的结果类型，所创建的新表中将包含类型为 INT 或 BIGINT 的列：

  CREATE TABLE t SELECT integer_expr;
  

  如果表达式的最大长度不适合 INT，则使用 BIGINT。但可以通过使用足够长的表达式来强制使用 BIGINT 而不是 INT：

  obclient> CREATE TABLE t SELECT 000000000000000000000;
  Query OK, 1 row affected
  

隐式数据类型转换规则

当数据类型转换有意义时，OceanBase 数据库会自动将一个值从一种数据类型转换为另一种数据类型。

下表为所有数据类型隐式转换矩阵，不需要考虑转换的方向或转换的上下文。

隐式数据类型转换示例

obclient> SELECT CAST(BOOL_COLUMN AS YEAR) FROM YOUR_TABLE;
+---------------------------+
| CAST(BOOL_COLUMN AS YEAR) |
+---------------------------+
|                      2001 |
+---------------------------+
2 rows in set (0.001 sec)