数据类型转换
不同的数据类型之间支持转换。有如下场景涉及到数据类型转换:
- 操作符(比较操作符、运算操作符等)的操作数的数据类型不一致。常见于查询条件或者关联条件中的比较运算。
- 函数调用时实参和形参的数据类型不一致。
- 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都是填充空格。