数据类型转换

不同的数据类型之间支持转换。有如下场景涉及到数据类型转换：

• 操作符（比较操作符、运算操作符等）的操作数的数据类型不一致。常见于查询条件或者关联条件中的比较运算。
• 函数调用时实参和形参的数据类型不一致。
• DML语句要更新（包括INSERT、UPDATE、MERGE、REPLACE等）的目标列，数据的类型和列的定义类型不一致。
• 显式的类型转换：cast(expr AS datatype)，将expr表达式类型转换为datatype类型。
• 集合运算（UNION、MINUS、EXCEPT、INTERSECT）确定最终投影列的目标数据类型后，各个SELECT查询的投影列的类型和目标数据类型不一致。
• 其他表达式计算场景，根据不同表达式的数据类型， 来决定用于比较或者最终结果的目标数据类型。
  • DECODE
  • CASE WHEN
  • lexpr [ NOT ] IN (expr_list)
  • BETWEEN AND
  • JOIN USING(a,b)
  • GREATEST和LEAST
  • NVL 和 COALESCE

GaussDB和MySQL数据库对于数据类型转换、转换的目标数据类型有着完全不同的规则。如下示例体现了两者处理的差异：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15

-- MySQL： in 执行结果为0，表示false。根据规则，会将'1970-01-01'与列表中的表达式依次比较，结果都为0，因此最终结果为0。
mysql> SELECT '1970-01-01' IN ('1970-01-02', 1, '1970-01-02');
+-------------------------------------------------+
| '1970-01-01' in ('1970-01-02', 1, '1970-01-02') |
+-------------------------------------------------+
|                                               0 |
+-------------------------------------------------+

-- GaussDB： in 执行结果为true，与MySQL结果相反：根据规则选定的公共类型为int类型，因此将左表达式'1970-01-01'转换为int类型与列表中的表达式转换为int类型后的值依次比较。
-- '1970-01-01'和'1970-01-02'转换为int类型时都为1970（兼容MySQL模式下，转换时遇到非法字符后忽略，将前面部分转换为int类型），比较结果为相等，因此返回结果为true。
gaussdb=# SELECT '1970-01-01' IN ('1970-01-02', 1::int, '1970-01-02') AS result;
 result 
--------
 t
(1 row)

数据类型转换规则的差异：

• GaussDB数据库对于不同数据类型之间的转换规则有明确的定义：
  • 是否支持转换：pg_cast系统表中是否定义两种类型的转换路径，没有定义则不支持。
  • 支持转换的场景：支持任意场景转换、仅支持显式（cast表达式）转换、仅支持赋值时转换。不支持的场景下即使定义了转换路径，也不能做数据类型转换。
• MySQL数据库支持任意两种数据类型之间做转换。

由于存在以上差异，基于MySQL数据库的应用程序向GaussDB数据库迁移时，SQL语句可能由于不支持不同数据类型之间的转换而报错。或者支持转换的场景下，转换的规则有差异导致SQL语句执行的结果不同。

推荐的做法是：SQL语句中尽量使用相同的数据类型做比较或者赋值等操作，避免因为数据类型转换导致非预期结果或者性能损耗。

选择目标数据类型的规则差异：

对于有些场景，比较的数据类型或者返回的数据类型需要综合考虑多个表达式的类型才能确定。比如UNION运算中，不同SELECT语句中相同位置的投影列具有不同的数据类型，查询结果的最终数据类型，需要由各个SELECT语句投影列的数据类型共同确定。

确定目标数据类型的规则，GaussDB数据库和MySQL数据库存在体系上的差异。

• GaussDB数据库规则：
  • 操作符的操作数类型不一致时，并不是将操作数的类型统一转换为目标类型再计算。而是直接注册两个数据类型的操作符，操作符处理中定义两个不同类型的处理规则。此方式不存在类型隐式转换，但自定义的处理规则隐含了转换的操作。
  • 集合运算和表达式场景，确定目标数据类型的规则：
    • 如果所有类型都相同，则此类型即为目标类型。
    • 两个数据类型如果不同，检查数据类型是否属于同一种类的数据类型，如数值类型、字符类型、日期时间类型等。不属于同一种类的数据类型，无法确定目标类型，此时SQL语句执行会报错。
    • 对于category属性（在pg_type系统表中定义）相同的数据类型，具有preferred属性（在pg_type系统表中定义）的数据类型会被选为目标类型。或者操作数1能转换为操作数2（没有转换路径），而操作数2无法转换为操作数1或数值类型优先级小于操作数2，则选择操作数2作为目标类型。
    • 如果涉及到3个及以上的数据类型，确定目标类型的规则为：common_type(type1,type2,type3) = common_type(common_type(type1,type2),type3)，依次迭代处理，得到最终的结果。
    • 对于IN和NOT IN表达式，如果根据以上规则无法确认目标类型，会将lexpr与expr_list中每一个表达式单独按照等值操作符（=）逐个比较。
    • 精度的确定：以最终选定的表达式的精度作为最终结果。
• MySQL数据库规则：
  • 操作符的操作数类型不一致时，先按照如下规则确定目标类型。确定后将类型不一致的操作数转换成目标类型后再做处理。
    • 两个参数都是string类型，则都按照string类型比较。
    • 两个参数都是integer类型，则都按照integer类型比较。
    • 十六进制数值如果不与数值比较，则当做二进制字符串比较。
    • 一个参数是datetime/timestamp类型，另一个参数是常量，将常量转换为时间戳类型然后比较。
    • 如果其中一个参数是decimal类型，比较时使用的数据类型取决于另外一个参数。另外一个是decimal或者integer类型时，按照decimal类型；另外一个是其他类型，按照real类型比较。
    • 其他场景都转换为 real 类型后比较。
  • 集合运算和表达式场景，确定目标数据类型的规则如下：
    • 建立任意两个类型之间的目标类型矩阵。给定两个类型，通过矩阵即可以确定目标类型。
    • 如果涉及到3个及以上的数据类型，确定目标类型的规则为：common_type(type1,type2,type3) = common_type(common_type(type1,type2),type3)，依次迭代处理，得到最终的结果。
    • 如果目标类型是integer类型，且各个表达式类型包含有符号和无符号的混合场景，则会将类型提升到更高精度的integer类型。符号的确定：所有表达式都是无符号时，结果才为无符号，否则结果为有符号。
    • 精度确定：以表达式中的最大精度作为最终结果。

从以上规则可知：GaussDB和MySQL数据库在数据类型的转换规则上有很大差异，不能直接对比。在上述场景下，SQL语句的执行结果可能和MySQL数据库不一致。当前版本推荐各个表达式使用相同的类型，或提前使用cast转换成需要的类型来规避差异。