# 数据类型转换 不同的数据类型之间支持转换。有如下场景涉及到数据类型转换： - 操作符（比较操作符、运算操作符等）的操作数的数据类型不一致。常见于查询条件或者关联条件中的比较运算。 - 函数调用时实参和形参的数据类型不一致。 - DML语句要更新（包括INSERT、UPDATE、MERGE、REPLACE等）的目标列，数据的类型和列的定义类型不一致。 - 显式的类型转换：CAST(expr AS datatype)，将expr表达式类型转换为datatype类型。 - 集合运算（UNION以及EXCEPT）确定最终投影列的目标数据类型后，各个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 test_year 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都是填充空格。