更新时间:2024-11-11 GMT+08:00
分享

数据类型转换

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

  • 操作符(比较操作符、运算操作符等)的操作数的数据类型不一致。常见于查询条件或者关联条件中的比较运算。
  • 函数调用时实参和形参的数据类型不一致。
  • DML语句要更新(包括INSERT、UPDATE、MERGE、REPLACE等)的目标列,数据的类型和列的定义类型不一致。
  • 显式的类型转换:CAST(expr AS datatype),将expr表达式类型转换为datatype类型。
  • 集合运算(UNION、MINUS、EXCEPT、INTERSECT)确定最终投影列的目标数据类型后,各个SELECT查询的投影列的类型和目标数据类型不一致。
  • 其他表达式计算场景,根据不同表达式的数据类型, 来决定用于比较或者最终结果的目标数据类型。

数据类型转换差异点主要分为三类:隐式转换,显式转换和UNION/CASE。

双冒号转换差异点

  • GaussDB中使用双冒号将函数入参转换为期望类型可能导致结果超出预期,且MySQL中无双冒号功能。
    示例:
    m_db=# SELECT POW("12"::VARBINARY,"12"::VARBINARY);
    ERROR:  value out of range: overflow
    CONTEXT:  referenced column: pow
    
    varbinary col
    m_db=# CREATE TABLE test_varbinary (
            A VARBINARY(10)
    );
    m_db=# INSERT INTO test_varbinary VALUES ('12');
    m_db=# SELECT POW(A, A) FROM test_varbinary;
          pow      
    ---------------
     8916100448256
    (1 row)
隐式类型转换差异点:
  • GaussDB中统一平铺成小类型到小类型的转换规则,MySQL中使用小类型转大类型,大类型转小类型的转换规则。
  • GaussDB中隐式转换因数据类型本身差异点,输出格式存在部分行为不一致。
  • GaussDB中的隐式转换,BIT数据类型到字符数据类型和二进制数据类型转换,输出存在部分行为不一致。GaussDB输出为十六进制,MySQL中根据ASCII码表转义,无法转义的输出为空。

    示例:

    m_db=# CREATE TABLE bit_storage (
    	VS_COL1 BIT(4),
    	VS_COL2 BIT(4),
    	VS_COL3 BIT(4),
    	VS_COL4 BIT(4),
    	VS_COL5 BIT(4),
    	VS_COL6 BIT(4),
    	VS_COL7 BIT(4),
    	VS_COL8 BIT(4)
    ) DISTRIBUTE BY REPLICATION;
    m_db=# CREATE TABLE string_storage (
    	VS_COL1 BLOB,
    	VS_COL2 TINYBLOB,
    	VS_COL3 MEDIUMBLOB,
    	VS_COL4 LONGBLOB,
    	VS_COL5 TEXT,
    	VS_COL6 TINYTEXT,
    	VS_COL7 MEDIUMTEXT,
    	VS_COL8 LONGTEXT
    ) DISTRIBUTE BY REPLICATION;
    m_db=# INSERT INTO bit_storage VALUES(B'101', B'101', B'101', B'101', B'101', B'101', B'101', B'101');
    m_db=# INSERT INTO string_storage SELECT * FROM bit_storage;
    m_db=# SELECT * FROM string_storage;
     VS_COL1 | VS_COL2 | VS_COL3 | VS_COL4 | VS_COL5 | VS_COL6 | VS_COL7 | VS_COL8 
    ---------+---------+---------+---------+---------+---------+---------+---------
     \x05    | \x05    | \x05    | \x05    | \x05    | \x05    | \x05    | \x05
    (1 row)
    m_db=# DROP TABLE bit_storage, string_storage;
    
    mysql> CREATE TABLE bit_storage (
    	VS_COL1 BIT(4),
    	VS_COL2 BIT(4),
    	VS_COL3 BIT(4),
    	VS_COL4 BIT(4),
    	VS_COL5 BIT(4),
    	VS_COL6 BIT(4),
    	VS_COL7 BIT(4),
    	VS_COL8 BIT(4)
    );
    mysql> CREATE TABLE bit_storage (
    	VS_COL1 BIT(4),
    	VS_COL2 BIT(4),
    	VS_COL3 BIT(4),
    	VS_COL4 BIT(4),
    	VS_COL5 BIT(4),
    	VS_COL6 BIT(4),
    	VS_COL7 BIT(4),
    	VS_COL8 BIT(4)
    );
    mysql> INSERT INTO bit_storage VALUES(B'101', B'101', B'101', B'101', B'101', B'101', B'101', B'101');
    mysql> INSERT INTO string_storage SELECT * FROM bit_storage;
    mysql> SELECT * FROM string_storage;
    +---------+---------+---------+---------+---------+---------+---------+---------+
    | VS_COL1 | VS_COL2 | VS_COL3 | VS_COL4 | VS_COL5 | VS_COL6 | VS_COL7 | VS_COL8 |
    +---------+---------+---------+---------+---------+---------+---------+---------+
    |        |        |        |        |        |        |        |        |
    +---------+---------+---------+---------+---------+---------+---------+---------+
    1 row in set (0.00 sec)
    mysql> DROP TABLE bit_storage, string_storage;
  • 中间带有0x00的二进制或十六进制字符串插入二进制数据类型时,GaussDB部分插入,0x00后的字符会截断,MySQL可以完整插入。
    示例:
    m_db=# CREATE TABLE blob_storage (
            A BLOB
    ) DISTRIBUTE BY REPLICATION;
    m_db=# INSERT INTO blob_storage VALUES (0xBB00BB);
    m_db=# SELECT hex(A) FROM blob_storage;
     hex 
    -----
     BB
    (1 row)
    m_db=# DROP TABLE blob_storage;
    
    mysql> CREATE TABLE blob_storage (
            A BLOB
    );
    mysql> INSERT INTO blob_storage VALUES (0xBB00BB);
    mysql> SELECT hex(A) FROM blob_storage;
    +--------+
    | hex(a) |
    +--------+
    | BB00BB |
    +--------+
    1 row in set (0.01 sec)
    mysql> DROP TABLE blob_storage;
  • 中间带有0x00的二进制或十六进制字符串插入字符串数据类型时,GaussDB部分插入,0x00后的字符会截断,MySQL严格模式不允许插入,宽松模式插入空字符串。
    示例:
    m_db=# CREATE TABLE text_storage (
            A TEXT
    );
    m_db=# INSERT INTO text_storage VALUES (b'101110110000000010111011');
    m_db=# SELECT hex(A) FROM text_storage;
     hex 
    -----
     BB
    (1 row)
    m_db=# DROP TABLE text_storage;
    
    mysql> CREATE TABLE text_storage (
            A TEXT
    );
    mysql> INSERT INTO text_storage VALUES (b'101110110000000010111011');
    ERROR 1366 (HY000): Incorrect string value: '\xBB\x00\xBB' for column 'A' at row 1
    mysql> SELECT hex(A) FROM text_storage;
    Empty set (0.00 sec)
    mysql> SET SQL_MODE='';
    mysql> INSERT INTO text_storage VALUES (b'101110110000000010111011');
    mysql> SELECT hex(A) FROM text_storage;
    +--------+
    | hex(A) |
    +--------+
    |        |
    +--------+
    1 row in set (0.01 sec)
    mysql> DROP TABLE text_storage;
  • WHERE子句中只带有普通字符串,GaussDB中't'、'true'、'yes'、 'y'、'on'返回TRUE,'no'、'f'、'off'、'false'、'n'返回FALSE,其余字符串报错。MySQL通过字符串转换为INT1判断返回TRUE/FALSE。

    示例:

    m_db=# CREATE TABLE test_where (
            A INT
    );
    m_db=# INSERT INTO test_where VALUES (1);
    m_db=# SELECT * FROM test_where WHERE '111';
    ERROR:  invalid input syntax for type boolean: "111"
    LINE 1: select * from test_where where '111';
    m_db=# DROP TABLE test_where;
    
    mysql> CREATE TABLE test_where (
            A INT
    );
    mysql> INSERT INTO test_where VALUES (1);
    mysql> SELECT * FROM test_where WHERE '111';
    +------+
    | a    |
    +------+
    |    1 |
    +------+
    1 row in set (0.01 sec)
    mysql> DROP TABLE test_where;
  • 对于YEAR类型的输入,在将字符串转换为整型的过程中,MySQL考虑科学计数法,GaussDB暂不支持,统一做截断处理。

    示例:

    m_db=# CREATE TABLE test_year (
            A YEAR
    );
    m_db=# SET sql_mode = '';
    m_db=# INSERT INTO test_year VALUES ('2E3x');
    WARNING:  Data truncated for column.
    LINE 1: insert into t1 values ('2E3x');
                                   ^
    CONTEXT:  referenced column: a
    m_db=# SELECT * FROM test_year ORDER BY A;
      a   
    ------
     2002
    (1 row)
    m_db=# DROP TABLE test_year;
    
    mysql> CREATE TABLE test_year (
            A YEAR
    );
    mysql> INSERT INTO test_year VALUES ('2E3x');
    mysql> SELECT * FROM test_year ORDER BY A;
    +------+
    | a    |
    +------+
    | 2000 |
    +------+
    1 row in set (0.01 sec)
    mysql> DROP TABLE test_year;

显式类型转换差异点:

  • GaussDB中平铺成对各目标类型的转换规则,MySQL中使用C++多态重载函数,在嵌套场景中存在不一致行为。
    示例:
    m_db=# SELECT CAST(GREATEST(date'2023-01-01','2023-01-01') AS SIGNED);
    WARNING:  Truncated incorrect INTEGER value: '2023-01-01'
    CONTEXT:  referenced column: cast
     cast 
    ------
     2023
    (1 row)
    
    mysql> SELECT CAST(GREATEST(date'2023-01-01','2023-01-01') AS SIGNED);
    +---------------------------------------------------------+
    | CAST(GREATEST(date'2023-01-01','2023-01-01') AS SIGNED) |
    +---------------------------------------------------------+
    |                                                20230101 |
    +---------------------------------------------------------+

UNION,CASE和相关构造差异点:

  • POLYGON + NULL、POINT + NULL、POLYGON + POINT组合在MySQL中均返回GEOMETRY类型,GaussDB中未涉及,暂时当做报错处理。
  • SET和ENUM两种类型暂未支持,暂时当做报错处理。
  • 常量类型和其他类型做类型聚合的时候,输出类型的精度为其他类型的精度。如“select "helloworld" union select p from t;”的结果的精度为属性p的精度。
  • 定点常量和不带精度约束的类型(非字符串类型如int、bool、year等,聚合结果类型为定点类型)聚合时,精度约束会按照定点数默认精度31输出。
  • merge rule差异:

    MySQL 5.7中YEAR和TINYINT、INT、MEDIUMINT、BIGINT、BOOL聚合的结果类型为带UNSIGNED的类型,GaussDB的结果类型为不带UNSIGNED的类型;MySQL中BIT和INT、NUMERIC、FLOAT、DOUBLE等数值类型的聚合类型为VARBINARY类型,GaussDB中BIT和INT|NUMERIC聚合为NUMERIC类型,和FLOAT或者DOUBLE类型聚合时,结果为DOUBLE类型,和无符号整型聚合时为UINT8类型。

  • MySQL中BINARY和CHAR填充字符不相同,BINARY填充'\0',CHAR填充空格,GaussDB中BINARY和CHAR都是填充空格。

相关文档