更新时间：2025-06-30 GMT+08:00
数据类型转换
不同的数据类型之间支持转换。有如下场景涉及到数据类型转换：
- 操作符（比较操作符、运算操作符等）的操作数的数据类型不一致。常见于查询条件或者关联条件中的比较运算。
- 函数调用时实参和形参的数据类型不一致。
- DML语句要更新（包括INSERT、UPDATE、MERGE、REPLACE等）的目标列，数据的类型和列的定义类型不一致。
- 集合运算（UNION以及EXCEPT）确定最终投影列的目标数据类型后，各个SELECT查询的投影列的类型和目标数据类型不一致。
- 其他表达式计算场景，根据不同表达式的数据类型， 来决定用于比较或者最终结果的目标数据类型。
- 普通的字符串类型当字符序为BINARY时，将转换成对应的二进制类型（TEXT转换成BLOB，VARCHAR转换成VARBINARY等）。
数据类型转换差异点主要分为：隐式转换，UNION/CASE、decimal类型。
隐式类型转换差异点
- GaussDB中统一平铺成小类型到小类型的转换规则，MySQL中使用小类型转大类型，大类型转小类型的转换规则。
- WHERE条件子句中只有字符串的场景，GaussDB中't'、'true'、'y'、'yes'、'on'、'1'以及其大写版本会被识别为TRUE，能得到查询结果，'f'、'false'、'n'、'no'、'off'、'0'以及其大写版本会被识别为FALSE，无法得到查询结果。除上述字符串外，其余字符串均报错。MySQL中字符串首位为非0的数字即可得到查询结果，其余场景均无法得到查询结果。
-- GaussDB m_db=# CREATE TABLE test_where(a int); CREATE TABLE m_db=# INSERT INTO test_where VALUES(1); INSERT 0 1 m_db=# SELECT * FROM test_where WHERE 't'; a --- 1 (1 row) m_db=# SELECT * FROM test_where WHERE '1'; a --- 1 (1 row) m_db=# SELECT * FROM test_where WHERE '1a'; ERROR: invalid input syntax for type boolean: "1a" LINE 1: SELECT * FROM test_where WHERE '1a'; ^ m_db=# SELECT * FROM test_where WHERE 'f'; a --- (0 rows) m_db=# SELECT * FROM test_where WHERE '0'; a --- (0 rows) m_db=# DROP TABLE test_where; DROP TABLE -- MySQL mysql> CREATE TABLE test_where(a int); Query OK, 0 rows affected (0.02 sec) mysql> INSERT INTO test_where VALUES(1); Query OK, 1 row affected (0.00 sec) mysql> SELECT * FROM test_where WHERE 't'; Empty set, 1 warning (0.00 sec) mysql> SELECT * FROM test_where WHERE '1'; +------+ | a | +------+ | 1 | +------+ 1 row in set (0.00 sec) mysql> SELECT * FROM test_where WHERE '1a'; +------+ | a | +------+ | 1 | +------+ 1 row in set, 1 warning (0.00 sec) mysql> SELECT * FROM test_where WHERE 'f'; Empty set, 1 warning (0.00 sec) mysql> SELECT * FROM test_where WHERE '0'; Empty set (0.00 sec) mysql> DROP TABLE test_where; Query OK, 0 rows affected (0.01 sec) - 对于YEAR类型的表，输入字符串时若遇到'e'或'E'，MySQL会以科学计数法来处理，GaussDB直接报错或截断。
-- GaussDB m_db=# SET SQL_MODE=''; SET m_db=# CREATE TABLE test_year(a year); CREATE TABLE m_db=# INSERT INTO test_year VALUES('2E3'); WARNING: Data truncated for column. LINE 1: INSERT INTO test_year VALUES('2E3'); ^ CONTEXT: referenced column: a INSERT 0 1 m_db=# SELECT * FROM test_year; a ------ 2002 (1 row) m_db=# DROP TABLE test_year; DROP TABLE -- MySQL mysql> CREATE TABLE test_year(a year); Query OK, 0 rows affected (0.02 sec) mysql> INSERT INTO test_year VALUES('2E3'); Query OK, 1 row affected (0.00 sec) mysql> SELECT * FROM test_year; +------+ | a | +------+ | 2000 | +------+ 1 row in set (0.00 sec) mysql> DROP TABLE test_year; Query OK, 0 rows affected (0.01 sec)
- GaussDB中函数嵌套场景下，涉及到聚合函数（如max、min、sum和avg）中存在字符串类型包含非数值字符，隐式转换到数值类型发生截断或置零，且包含操作符比较、having比较的场景时，GaussDB统一进行类型转换并产生告警，MySQL在相同场景下不会全部产生告警。
-- GaussDB m_db=# SET m_format_behavior_compat_options= 'enable_precision_decimal'; SET m_db=# SELECT max(c4) <> 0 FROM ((SELECT 2.22 id, '2006-04-27 20:19:02.132' c4)) tb_1; ?column? ---------- t (1 row) m_db=# SELECT sum(c4) <> 0 FROM ((SELECT 2.22 id, '2006-04-27 20:19:02.132' c4)) tb_1; WARNING: The double value '2006-04-27 20:19:02.132' is incorrect. ?column? ---------- t (1 row) m_db=# SELECT (SELECT max(c4) f5 FROM ((SELECT 2.22 id, '2006-04-27 20:19:08.132' c4) UNION ALL (SELECT 2.22 id, '1985-09-01 07:59:59' c4)) tb_1 WHERE EXISTS (SELECT max(c4) FROM ((SELECT 2.22 id, '2006-04-27 20:19:08.132' c4) UNION ALL (SELECT 2.22 id, '1985-09-01 07:59:59' c4)) tb_2) GROUP BY id WITH rollup HAVING f5<>0 LIMIT 0,1) + INTERVAL '33.22' SECOND_MICROSECOND col5; col5 ---------------------------- 2006-04-27 20:19:41.352000 (1 row) m_db=# SELECT (SELECT sum(c4) f5 FROM ((SELECT 2.22 id, '2006-04-27 20:19:08.132' c4) UNION ALL (SELECT 2.22 id, '1985-09-01 07:59:59' c4)) tb_1 WHERE EXISTS (SELECT sum(c4) FROM ((SELECT 2.22 id, '2006-04-27 20:19:08.132' c4) UNION ALL (SELECT 2.22 id, '1985-09-01 07:59:59' c4)) tb_2) GROUP BY id WITH rollup HAVING f5<>0 LIMIT 0,1) + INTERVAL '33.22' SECOND_MICROSECOND col5; WARNING: The double value '2006-04-27 20:19:08.132' is incorrect. CONTEXT: referenced column: col5 WARNING: The double value '1985-09-01 07:59:59' is incorrect. CONTEXT: referenced column: col5 WARNING: The double value '2006-04-27 20:19:08.132' is incorrect. CONTEXT: referenced column: col5 WARNING: The double value '2006-04-27 20:19:08.132' is incorrect. CONTEXT: referenced column: col5 WARNING: The double value '1985-09-01 07:59:59' is incorrect. CONTEXT: referenced column: col5 WARNING: The double value '1985-09-01 07:59:59' is incorrect. CONTEXT: referenced column: col5 WARNING: Incorrect datetime value: '3991' CONTEXT: referenced column: col5 col5 ------ (1 row) -- MySQL mysql> SELECT max(c4) <> 0 FROM ((SELECT 2.22 id, '2006-04-27 20:19:02.132' c4)) tb_1; +--------------+ | max(c4) <> 0 | +--------------+ | 1 | +--------------+ 1 row in set (0.00 sec) mysql> SELECT sum(c4) <> 0 FROM ((SELECT 2.22 id, '2006-04-27 20:19:02.132' c4)) tb_1; +--------------+ | sum(c4) <> 0 | +--------------+ | 1 | +--------------+ 1 row in set, 1 warning (0.00 sec) mysql> SHOW warnings; +---------+------+-------------------------------------------------------------+ | Level | Code | Message | +---------+------+-------------------------------------------------------------+ | Warning | 1292 | Truncated incorrect DOUBLE value: '2006-04-27 20:19:02.132' | +---------+------+-------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> SELECT (SELECT max(c4) f5 FROM ((SELECT 2.22 id, '2006-04-27 20:19:08.132' c4) UNION ALL (SELECT 2.22 id, '1985-09-01 07:59:59' c4)) tb_1 -> WHERE EXISTS (SELECT max(c4) FROM ((SELECT 2.22 id, '2006-04-27 20:19:08.132' c4) UNION ALL (SELECT 2.22 id, '1985-09-01 07:59:59' c4)) tb_2) -> GROUP BY id WITH rollup HAVING f5<>0 limit 0,1) + INTERVAL '33.22' SECOND_MICROSECOND col5; +----------------------------+ | col5 | +----------------------------+ | 2006-04-27 20:19:41.352000 | +----------------------------+ 1 row in set (0.00 sec) mysql> SELECT (SELECT sum(c4) f5 FROM ((SELECT 2.22 id, '2006-04-27 20:19:08.132' c4) UNION ALL (SELECT 2.22 id, '1985-09-01 07:59:59' c4)) tb_1 -> WHERE EXISTS (SELECT sum(c4) FROM ((SELECT 2.22 id, '2006-04-27 20:19:08.132' c4) UNION ALL (SELECT 2.22 id, '1985-09-01 07:59:59' c4)) tb_2) -> GROUP BY id WITH rollup HAVING f5<>0 LIMIT 0,1) + INTERVAL '33.22' SECOND_MICROSECOND col5; +------+ | col5 | +------+ | NULL | +------+ 1 row in set, 7 warnings (0.01 sec) mysql> SHOW warnings; +---------+------+-------------------------------------------------------------+ | Level | Code | Message | +---------+------+-------------------------------------------------------------+ | Warning | 1292 | Truncated incorrect DOUBLE value: '2006-04-27 20:19:08.132' | | Warning | 1292 | Truncated incorrect DOUBLE value: '1985-09-01 07:59:59' | | Warning | 1292 | Truncated incorrect DOUBLE value: '2006-04-27 20:19:08.132' | | Warning | 1292 | Truncated incorrect DOUBLE value: '2006-04-27 20:19:08.132' | | Warning | 1292 | Truncated incorrect DOUBLE value: '1985-09-01 07:59:59' | | Warning | 1292 | Truncated incorrect DOUBLE value: '1985-09-01 07:59:59' | | Warning | 1292 | Incorrect datetime value: '3991' | +---------+------+-------------------------------------------------------------+ 7 rows in set (0.00 sec)
UNION，CASE和相关构造差异点
- POLYGON + NULL、POINT + NULL、POLYGON + POINT组合在MySQL中均返回GEOMETRY类型，GaussDB中未涉及，暂时当做报错处理。
- SET和ENUM两种类型暂未支持，暂时当做报错处理。
- JSON和二进制类型（BINARY、VARBINARY、TINYBLOB、BLOB、MEDIUMBLOB、LONGBLOB）、BIT或者YEAR类型的UNION和UNION ALL组合，MySQL中返回LONGBLOB或者LONGTEXT类型，GaussDB中返回JSON类型，同时支持二进制类型（BINARY、VARBINARY、TINYBLOB、BLOB、MEDIUMBLOB、LONGBLOB）、BIT或者YEAR类型到JSON的隐式类型转换。
- 未设置m_format_behavior_compat_options为enable_precision_decimal时，常量类型和其他类型做类型聚合的时候，输出类型的精度为其他类型的精度。如“SELECT "helloworld" UNION SELECT p FROM t;”的结果的精度为属性p的精度。
- 未设置m_format_behavior_compat_options为enable_precision_decimal时，定点常量和不带精度约束的类型（非字符串类型如int、bool、year等，聚合结果类型为定点类型）聚合时，精度约束会按照定点数默认精度31输出。
- merge rule差异：
MySQL 5.7中存在部分不合理的类型推导，如BIT类型和整型/YEAR类型推导会得出VARBINARY类型，UNSIGNED类型和非UNSIGNED类型推导会得到带UNSIGNED的类型等，同时CASE WHEN和UNION的聚合结果也存在差异，类型推导结果太小时存在数据溢出风险。在MySQL 8.0版本修复了上述相关的问题，因此merge rule聚合规则以8.0为准。
- MySQL中BINARY和CHAR填充字符不相同，BINARY填充'\0'，CHAR填充空格，GaussDB中BINARY和CHAR都是填充空格。
- 在精度传递场景下，使用CASE WHEN语句时，会进行类型转换和精度重新计算，导致最终的输出结果与CASE子句对比会出现末尾多零场景或末尾少零场景：
- 末尾多零场景：CASE节点会根据CASE子句的精度计算CASE节点精度，当THEN子句的精度比CASE节点的精度小时，会在CASE节点末尾补零。
- 末尾少零场景：多层CASE WHEN嵌套时，内层CASE执行类型转换之后，只保留内层CASE的精度，外层CASE无法得到THEN子句的精度信息，因此外层CASE会根据内层CASE的精度计算的精度进行类型转换。当外层CASE类型转换时内层CASE精度比THEN子句少，会出现末尾少零场景。
示例：
- 末尾多零少零场景。
-- 末尾多零场景。 m_db=# SELECT 15.6 AS result; result -------- 15.6 (1 row) m_db=# SELECT CASE WHEN 1 < 2 THEN 15.6 ELSE 23.578 END AS result; result -------- 15.600 (1 row) m_db=# SELECT greatest(12, 3.4, 15.6) AS result; result -------- 15.6 (1 row) m_db=# SELECT CASE WHEN 1 < 2 THEN greatest(12, 3.4, 15.6) ELSE greatest(123.4, 23.578, 36) END AS result; result -------- 15.600 (1 row) -- 末尾少零场景。 m_db=# CREATE TABLE t1 AS SELECT (false/-timestamp '2008-12-31 23:59:59.678') AS result; INSERT 0 1 m_db=# DESC t1; Field | Type | Null | Key | Default | Extra --------+-------------+------+-----+---------+------- result | double(8,7) | YES | | | (1 row) m_db=# SELECT (false/-timestamp '2008-12-31 23:59:59.678') AS result; result ------------ -0.0000000 (1 row) m_db=# CREATE TABLE t1 AS SELECT (CASE WHEN 1<2 THEN false/-timestamp '2008-12-31 23:59:59.678' ELSE 0016.11e3/'22.2' END) AS result; INSERT 0 1 m_db=# DESC t1; Field | Type | Null | Key | Default | Extra --------+--------+------+-----+---------+------- result | double | YES | | | (1 row) m_db=# SELECT (CASE WHEN 1<2 THEN false/-timestamp '2008-12-31 23:59:59.678' ELSE 0016.11e3/'22.2' END) AS result; result -------- -0 (1 row) m_db=# DROP TABLE t1; DROP TABLE m_db=# CREATE TABLE t1 AS SELECT (CASE WHEN 1+1=2 THEN CASE WHEN 1<2 THEN false/-timestamp '2008-12-31 23:59:59.678' ELSE 0016.11e3/'22.2' END ELSE 'test' END) AS result; INSERT 0 1 m_db=# DESC t1; Field | Type | Null | Key | Default | Extra --------+-------------+------+-----+---------+------- result | varchar(23) | YES | | | (1 row) m_db=# SELECT (CASE WHEN 1+1=2 THEN CASE WHEN 1<2 THEN false/-timestamp '2008-12-31 23:59:59.678' ELSE 0016.11e3/'22.2' END ELSE 'test' END) AS result; result -------- -0 (1 row) - 在开启精度传递的场景下，使用集合运算（UNION以及EXCEPT），如果参与集合运算的查询语句，其查询的字段为函数、表达式而不是直接使用表中的字段，且查询的结果数据类型为INT/INT UNSIGNED，则最后返回的数据类型存在差异。在MySQL中，返回的数据类型为BIGINT/BIGINT UNSIGNED；在GaussDB中，返回的数据类型为INT/INT UNSIGNED。
-- GaussDB执行结果。 m_db=# SET m_format_behavior_compat_options='select_column_name,enable_precision_decimal'; SET m_db=# DROP TABLE IF EXISTS t1,t2,ctas1,ctas2; DROP TABLE m_db=# CREATE TABLE t1(a INT, b INT); CREATE TABLE m_db=# CREATE TABLE t2(c INT UNSIGNED, d INT UNSIGNED); CREATE TABLE m_db=# CREATE TABLE ctas1 AS (SELECT a, ABS(a) FROM t1) UNION (SELECT b, ABS(b) FROM t1); INSERT 0 0 m_db=# DESC ctas1; Field | Type | Null | Key | Default | Extra --------+-------------+------+-----+---------+------- a | integer(11) | YES | | | ABS(a) | integer(11) | YES | | | (2 rows) m_db=# CREATE TABLE ctas2 AS (SELECT c, ABS(c) FROM t2) UNION (SELECT d, ABS(d) FROM t2); INSERT 0 0 m_db=# DESC ctas2; Field | Type | Null | Key | Default | Extra --------+----------------------+------+-----+---------+------- c | integer(11) unsigned | YES | | | ABS(c) | integer(11) unsigned | YES | | | (2 rows) m_db=# DROP TABLE IF EXISTS t1,t2,ctas1,ctas2; DROP TABLE -- MySQL执行结果。 mysql> DROP TABLE IF EXISTS t1,t2,ctas1,ctas2; Query OK, 0 rows affected, 4 warnings (0.00 sec) mysql> CREATE TABLE t1(a INT, b INT); Query OK, 0 rows affected (0.05 sec) mysql> CREATE TABLE t2(c INT UNSIGNED, d INT UNSIGNED); Query OK, 0 rows affected (0.03 sec) mysql> CREATE TABLE ctas1 AS (SELECT a, ABS(a) FROM t1) UNION (SELECT b, ABS(b) FROM t1); Query OK, 0 rows affected (0.03 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> DESC ctas1; +--------+------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------+------------+------+-----+---------+-------+ | a | int(11) | YES | | NULL | | | ABS(a) | bigint(20) | YES | | NULL | | +--------+------------+------+-----+---------+-------+ 2 rows in set (0.01 sec) mysql> CREATE TABLE ctas2 AS (SELECT c, ABS(c) FROM t2) UNION (SELECT d, ABS(d) FROM t2); Query OK, 0 rows affected (0.05 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> DESC ctas2; +--------+---------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------+---------------------+------+-----+---------+-------+ | c | int(11) unsigned | YES | | NULL | | | ABS(c) | bigint(20) unsigned | YES | | NULL | | +--------+---------------------+------+-----+---------+-------+ 2 rows in set (0.00 sec) mysql> DROP TABLE IF EXISTS t1,t2,ctas1,ctas2; Query OK, 0 rows affected (0.07 sec)
- 在开启精度传递的场景下，CASE WHEN被嵌套场景的结果与MySQL保持差异。MySQL的类型可以透过多层直接转换，而GaussDB结果精度是逐层确定并且逐层转换的，因此可能导致结果小数位或进位和MySQL不一致。
-- GaussDB: m_db=# SET m_format_behavior_compat_options='enable_precision_decimal'; SET m_db=# SELECT (CASE WHEN 1+1=3 THEN 'test' ELSE CASE WHEN 1>2 THEN '-1.5'%06.6600e1 ELSE -TIME '10:10:10.456'%2.2 END END) RES; res --------------------- -1.8559999999974321 (1 row) -- MySQL: mysql> SELECT (CASE WHEN 1+1=3 THEN 'test' ELSE CASE WHEN 1>2 THEN '-1.5'%06.6600e1 ELSE -TIME '10:10:10.456'%2.2 END END) RES; +--------+ | res | +--------+ | -1.856 | +--------+ 1 row in set (0.00 sec) - 对于需要int类型的运算符（如 ~，&，|，<<，>>）嵌套CASE WHEN语句，若CASE WHEN语句返回的是varchar类型，则实际情况可以会发生截断（根据原表数据分析是否会发生截断），GaussDB会报出相应错误（SELECT查询warning告警，CREATE建表error报错），MySQL不会报错。若GaussDB想要完成CREATE TABLE建表操作，可以通过设置sql_mode关闭严格模式。
-- GaussDB: m_db=# CREATE TABLE t_base (num_var numeric(20, 10), time_var time(6)); CREATE TABLE m_db=# INSERT INTO t_base VALUES ('-2514.1441000000','12:10:10.125000'),('-417.2147000000',' 11:30:25.258000'); INSERT 0 2 m_db=# SELECT (~(CASE WHEN false THEN time_var ELSE num_var END)) AS res2 FROM t_base; WARNING: Truncated incorrect INTEGER value: '-2514.1441000000' CONTEXT: referenced column: res2 WARNING: Truncated incorrect INTEGER value: '-417.2147000000' CONTEXT: referenced column: res2 res2 ------ 2513 416 (2 rows) m_db=# CREATE TABLE t1 AS SELECT (~(CASE WHEN false THEN time_var ELSE num_var END)) AS res2 FROM t_base; ERROR: Truncated incorrect INTEGER value: '-2514.1441000000' CONTEXT: referenced column: res2 m_db=# SET sql_mode=""; SET m_db=# CREATE TABLE t1 AS SELECT (~(CASE WHEN false THEN time_var ELSE num_var END)) AS res2 FROM t_base; WARNING: Truncated incorrect INTEGER value: '-2514.1441000000' CONTEXT: referenced column: res2 WARNING: Truncated incorrect INTEGER value: '-417.2147000000' CONTEXT: referenced column: res2 INSERT 0 2 m_db=# DESC t1; Field | Type | Null | Key | Default | Extra -------+---------------------+------+-----+---------+------- res2 | bigint(21) unsigned | YES | | | (1 row) -- MySQL: mysql> CREATE TABLE t_base (num_var numeric(20, 10), time_var time(6)); Query OK, 0 rows affected (0.01 sec) mysql> INSERT INTO t_base VALUES ('-2514.1441000000','12:10:10.125000'),('-417.2147000000',' 11:30:25.258000'); Query OK, 2 rows affected (0.00 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> SELECT (~(CASE WHEN false THEN time_var ELSE num_var END)) AS res2 FROM t_base; +------+ | res2 | +------+ | 2513 | | 416 | +------+ 2 rows in set (0.00 sec) mysql> CREATE TABLE t1 AS SELECT (~(CASE WHEN false THEN time_var ELSE num_var END)) AS res2 FROM t_base; Query OK, 2 rows affected (0.01 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> DESC t1; +-------+---------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+---------------------+------+-----+---------+-------+ | res2 | bigint(21) unsigned | YES | | NULL | | +-------+---------------------+------+-----+---------+-------+ 1 row in set (0.00 sec)
- 在开启精度传递的场景下，对于CREATE VIEW AS SELECT CASE WHEN语句和SELECT CASE WHEN语句嵌套常量（包括常量计算、函数嵌套常量等）的情况，GaussDB在该情况下值保持一致，MySQL在SELECT CASE WHEN语句中可能会丢失部分精度。
-- GaussDB： m_db=# CREATE OR REPLACE VIEW test_view AS m_db-# SELECT (CASE WHEN 1<2 THEN 3.33/4.46 ELSE 003.3630/002.2600 END) c1,(CASE WHEN 1>2 THEN IFNULL(null,3.363/2.2) ELSE NULLIF(3.33/4.46,3.363/2.2) END) c2; CREATE VIEW m_db=# SELECT * FROM test_view; c1 | c2 ------------+----------- 0.74663677 | 0.7466368 (1 row) m_db=# SELECT (CASE WHEN 1<2 THEN 3.33/4.46 ELSE 003.3630/002.2600 END) c1,(CASE WHEN 1>2 THEN IFNULL(null,3.363/2.2) ELSE NULLIF(3.33/4.46,3.363/2.2) END) c2; c1 | c2 ------------+----------- 0.74663677 | 0.7466368 (1 row) -- MySQL： mysql> CREATE OR REPLACE VIEW test_view AS -> SELECT (CASE WHEN 1<2 THEN 3.33/4.46 ELSE 003.3630/002.2600 END) c1,(CASE WHEN 1>2 THEN IFNULL(null,3.363/2.2) ELSE NULLIF(3.33/4.46,3.363/2.2) END) c2; Query OK, 0 rows affected (0.00 sec) mysql> SELECT * FROM test_view; +------------+-----------+ | c1 | c2 | +------------+-----------+ | 0.74663677 | 0.7466368 | +------------+-----------+ 1 row in set (0.00 sec) mysql> SELECT (CASE WHEN 1<2 THEN 3.33/4.46 ELSE 003.3630/002.2600 END) c1,(CASE WHEN 1>2 THEN IFNULL(null,3.363/2.2) ELSE NULLIF(3.33/4.46,3.363/2.2) END) c2; +----------+----------+ | c1 | c2 | +----------+----------+ | 0.746637 | 0.746637 | +----------+----------+ 1 row in set (0.00 sec) - 在开启精度传递的场景下，GaussDB数据库支持UNION/CASE WHEN语句建表，但是由于架构不同，GaussDB数据库无法保证创建的表的所有类型与MySQL 8.0完全相同。MySQL返回字符串、二进制相关类型的场景，以及部分函数嵌套场景，与GaussDB存在不一致。
-- GaussDB: m_db=# CREATE TABLE IF NOT EXISTS testcase (id int, col_text1 tinytext, col_text2 text, col_blob1 tinyblob, col_blob2 blob, col_blob3 mediumblob, col_blob4 longblob); CREATE TABLE m_db=# CREATE TABLE t1 AS SELECT id,(CASE WHEN id=2 THEN col_text1 ELSE 'test' END) f35, (CASE WHEN id=2 THEN col_text2 ELSE 'test' END) f36,(CASE WHEN id=2 THEN col_blob1 ELSE 'test' END) f41, (CASE WHEN id=2 THEN col_blob2 ELSE 'test' END) f42, (CASE WHEN id=2 THEN col_blob3 ELSE 'test' END) f43, (CASE WHEN id=2 THEN col_blob4 ELSE 'test' END) f44 FROM testcase; INSERT 0 0 m_db=# DESC t1; Field | Type | Null | Key | Default | Extra -------+----------------+------+-----+---------+------- id | integer(11) | YES | | | f35 | varchar(255) | YES | | | f36 | mediumtext | YES | | | f41 | varbinary(255) | YES | | | f42 | blob | YES | | | f43 | mediumblob | YES | | | f44 | longblob | YES | | | (7 rows) m_db=# CREATE TABLE IF NOT EXISTS testtext1 (col10 text); CREATE TABLE m_db=# CREATE TABLE IF NOT EXISTS testtext2 (col10 text); CREATE TABLE m_db=# CREATE TABLE testtext AS (SELECT * FROM testtext1) UNION (SELECT * FROM testtext2); CREATE TABLE m_db=# DESC testtext; m_db=# Field | Type | Null | Key | Default | Extra -------+------+------+-----+---------+------- col10 | text | YES | | | (1 row) m_db=# CREATE TABLE testchar AS SELECT (SELECT lcase(-6873.4354)) a, (SELECT sec_to_time(-485769.567)) b UNION ALL SELECT (SELECT bin(-58768923.21321)), (SELECT asin(-0.7237465)); INSERT 0 2 m_db=# DESC testchar; Field | Type | Null | Key | Default | Extra -------+-------------+------+-----+---------+------- a | text | YES | | | b | varchar(23) | YES | | | (2 rows) m_db=# CREATE TABLE test_func (col_text char(29)); CREATE TABLE m_db=# CREATE TABLE test1 AS SELECT * FROM ( SELECT GREATEST(2.22, col_text) f1, LEAST(2.22, col_text) f2, ADDDATE(col_text, INTERVAL '1.28.16.31' HOUR_MICROSECOND) f3, SUBDATE(col_text, INTERVAL '39.49.15' MINUTE_MICROSECOND) f4, DATE_SUB(col_text, INTERVAL '45' MICROSECOND) f5, DATE_ADD(col_text, INTERVAL '12.00.00.00.001' DAY_MICROSECOND) f6, ADDTIME(col_text, '8:20:20.3554') f7, SUBTIME(col_text, '8:20:20.3554') f8 FROM test_func) t1 UNION ALL SELECT * FROM ( SELECT GREATEST(2.22, col_text) f1, LEAST(2.22, col_text) f2, ADDDATE(col_text, INTERVAL '1.28.16.31' HOUR_MICROSECOND) f3, SUBDATE(col_text, INTERVAL '39.49.15' MINUTE_MICROSECOND) f4, DATE_SUB(col_text, INTERVAL '45' MICROSECOND) f5, DATE_ADD(col_text, INTERVAL '12.00.00.00.001' DAY_MICROSECOND) f6, ADDTIME(col_text, '8:20:20.3554') f7, SUBTIME(col_text, '8:20:20.3554') f8 FROM test_func) t2; INSERT 0 0 m_db=# DESC test1; Field | Type | Null | Key | Default | Extra -------+-------------+------+-----+---------+------- f1 | double | YES | | | f2 | double | YES | | | f3 | varchar(29) | YES | | | f4 | varchar(29) | YES | | | f5 | varchar(29) | YES | | | f6 | varchar(29) | YES | | | f7 | varchar(29) | YES | | | f8 | varchar(29) | YES | | | (8 rows) -- MySQL: mysql> CREATE TABLE IF NOT EXISTS testcase (id int, col_text1 tinytext, col_text2 text, col_blob1 tinyblob, col_blob2 blob, col_blob3 mediumblob, col_blob4 longblob); Query OK, 0 rows affected (0.01 sec) mysql> CREATE TABLE t1 AS SELECT id,(CASE WHEN id=2 THEN col_text1 ELSE 'test' END) f35, (CASE WHEN id=2 THEN col_text2 ELSE 'test' END) f36,(CASE WHEN id=2 THEN col_blob1 ELSE 'test' END) f41, (CASE WHEN id=2 THEN col_blob2 ELSE 'test' END) f42, (CASE WHEN id=2 THEN col_blob3 else 'test' END) f43, (CASE WHEN id=2 THEN col_blob4 ELSE 'test' END) f44 FROM testcase; Query OK, 0 rows affected (0.00 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> DESC t1; +-------+----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+----------+------+-----+---------+-------+ | id | int | YES | | NULL | | | f35 | longtext | YES | | NULL | | | f36 | longtext | YES | | NULL | | | f41 | longblob | YES | | NULL | | | f42 | longblob | YES | | NULL | | | f43 | longblob | YES | | NULL | | | f44 | longblob | YES | | NULL | | +-------+----------+------+-----+---------+-------+ 7 rows in set (0.00 sec) mysql> CREATE TABLE IF NOT EXISTS testtext1 (col10 text); Query OK, 0 rows affected (0.01 sec) mysql> CREATE TABLE IF NOT EXISTS testtext2 (col10 text); Query OK, 0 rows affected (0.02 sec) mysql> CREATE TABLE testtext AS (SELECT * FROM testtext1) UNION (SELECT * FROM testtext2); Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> DESC testtext; +-------+------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+------------+------+-----+---------+-------+ | col10 | mediumtext | YES | | NULL | | +-------+------------+------+-----+---------+-------+ 1 row in set (0.00 sec) mysql> SET sql_mode=''; Query OK, 0 rows affected, 1 warning (0.01 sec) mysql> CREATE TABLE testchar AS SELECT (SELECT lcase(-6873.4354)) a, (SELECT sec_to_time(-485769.567)) b UNION ALL SELECT (SELECT bin(-58768923.21321)), (SELECT asin(-0.7237465)); Query OK, 2 rows affected, 1 warning (0.02 sec) Records: 2 Duplicates: 0 Warnings: 1 mysql> DESC testchar; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | a | varchar(21) | YES | | NULL | | | b | varchar(53) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+ 2 rows in set (0.00 sec) mysql> CREATE TABLE test_func (col_text char(29)); Query OK, 0 rows affected (0.02 sec) mysql> CREATE TABLE test1 AS SELECT * FROM ( SELECT -> GREATEST(2.22, col_text) f1, LEAST(2.22, col_text) f2, -> ADDDATE(col_text, INTERVAL '1.28.16.31' HOUR_MICROSECOND) f3, -> SUBDATE(col_text, INTERVAL '39.49.15' MINUTE_MICROSECOND) f4, -> DATE_SUB(col_text, INTERVAL '45' MICROSECOND) f5, -> DATE_ADD(col_text, INTERVAL '12.00.00.00.001' DAY_MICROSECOND) f6, -> ADDTIME(col_text, '8:20:20.3554') f7, -> SUBTIME(col_text, '8:20:20.3554') f8 FROM test_func) t1 -> UNION ALL -> SELECT * FROM ( SELECT -> GREATEST(2.22, col_text) f1, LEAST(2.22, col_text) f2, -> ADDDATE(col_text, INTERVAL '1.28.16.31' HOUR_MICROSECOND) f3, -> SUBDATE(col_text, INTERVAL '39.49.15' MINUTE_MICROSECOND) f4, -> DATE_SUB(col_text, INTERVAL '45' MICROSECOND) f5, -> DATE_ADD(col_text, INTERVAL '12.00.00.00.001' DAY_MICROSECOND) f6, -> ADDTIME(col_text, '8:20:20.3554') f7, -> SUBTIME(col_text, '8:20:20.3554') f8 FROM test_func) t2; -> SUBTIME(col_text, '8:20:20.3554') f8 FROM test_func) t1 -> UNION ALL -> SELECT * FROM ( SELECT -> GREATEST(2.22, col_text) f1, LEAST(2.22, col_text) f2, -> ADDDATE(col_text, INTERVAL '1.28.16.31' HOUR_MICROSECOND) f3, -> SUBDATE(col_text, INTERVAL '39.49.15' MINUTE_MICROSECOND) f4, -> DATE_SUB(col_text, INTERVAL '45' MICROSECOND) f5, -> DATE_ADD(col_text, INTERVAL '12.00.00.00.001' DAY_MICROSECOND) f6, -> ADDTIME(col_text, '8:20:20.3554') f7, -> SUBTIME(col_text, '8:20:20.3554') f8 FROM test_func) t2; Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> DESC test1; +-------+------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+------------+------+-----+---------+-------+ | f1 | binary(23) | YES | | NULL | | | f2 | binary(23) | YES | | NULL | | | f3 | char(29) | YES | | NULL | | | f4 | char(29) | YES | | NULL | | | f5 | char(29) | YES | | NULL | | | f6 | char(29) | YES | | NULL | | | f7 | char(29) | YES | | NULL | | | f8 | char(29) | YES | | NULL | | +-------+------------+------+-----+---------+-------+ 8 rows in set (0.01 sec) - 在开启精度传递的场景下，对于CREATE TABLE AS SELECT A % (CASE WHEN)语句，如果A是DECIMAL类型，CASE WHEN结果为日期类型（DATE、TIME、DATETIME），两者进行取模运算（%）得到的精度保持差异。GaussDB得到的精度跟decimal类型与日期类型直接取模运算得到的精度保持一致。
-- GaussDB：(decimal % date类型case)与(numeric%date)， 精度一致，都是decimal(24,10)。 m_db=# SET m_format_behavior_compat_options = 'enable_precision_decimal'; SET m_db=# DROP TABLE IF EXISTS t1, t2; DROP TABLE m_db=# CREATE TABLE t1 (num_var numeric(20, 10), date_var date, time_var time(6), dt_var datetime(6)); CREATE TABLE m_db=# CREATE TABLE t2 AS SELECT num_var % (CASE WHEN true THEN dt_var ELSE dt_var END) AS res1 FROM t1; INSERT 0 0 m_db=# DESC t2; Field | Type | Null | Key | Default | Extra -------+----------------+------+-----+---------+------- res1 | decimal(24,10) | YES | | | (1 row) m_db=# DROP TABLE IF EXISTS t1, t2; DROP TABLE m_db=# CREATE TABLE t1 (num_var numeric(20, 10), date_var date, time_var time(6), dt_var datetime(6)); CREATE TABLE m_db=# CREATE TABLE t2 AS SELECT num_var % dt_var AS res1 FROM t1; INSERT 0 0 m_db=# DESC t2; Field | Type | Null | Key | Default | Extra -------+----------------+------+-----+---------+------- res1 | decimal(24,10) | YES | | | (1 row) -- MySQL 5.7，精度存在差异。(decimal % date类型case)精度为decimal(65,10)，(numeric%date)精度为decimal(24,10)。 mysql> DROP TABLE IF EXISTS t1, t2; Query OK, 0 rows affected (0.02 sec) mysql> CREATE TABLE t1 (num_var numeric(20, 10), date_var date, time_var time(6), dt_var datetime(6)); Query OK, 0 rows affected (0.02 sec) mysql> CREATE TABLE t2 AS SELECT num_var % (CASE WHEN true THEN dt_var ELSE dt_var END) AS res1 FROM t1; Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> DESC t2; +-------+----------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+----------------+------+-----+---------+-------+ | res1 | decimal(65,10) | YES | | NULL | | +-------+----------------+------+-----+---------+-------+ 1 row in set (0.00 sec) mysql> DROP TABLE IF EXISTS t1, t2; Query OK, 0 rows affected (0.02 sec) mysql> CREATE TABLE t1 (num_var numeric(20, 10), date_var date, time_var time(6), dt_var datetime(6)); Query OK, 0 rows affected (0.02 sec) mysql> CREATE TABLE t2 AS SELECT num_var % dt_var AS res1 FROM t1; Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> DESC t2; +-------+----------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+----------------+------+-----+---------+-------+ | res1 | decimal(24,10) | YES | | NULL | | +-------+----------------+------+-----+---------+-------+ 1 row in set (0.00 sec) -- MySQL 8.0，(decimal % date类型case)和(numeric%date)精度都为decimal(20,10)。 mysql> DROP TABLE IF EXISTS t1, t2; Query OK, 0 rows affected (0.02 sec) mysql> CREATE TABLE t1 (num_var numeric(20, 10), date_var date, time_var time(6), dt_var datetime(6)); Query OK, 0 rows affected (0.02 sec) mysql> CREATE TABLE t2 AS SELECT num_var % (CASE WHEN true THEN dt_var ELSE dt_var END) AS res1 FROM t1; Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> DESC t2; +-------+----------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+----------------+------+-----+---------+-------+ | res1 | decimal(20,10) | YES | | NULL | | +-------+----------------+------+-----+---------+-------+ 1 row in set (0.00 sec) mysql> DROP TABLE IF EXISTS t1, t2; Query OK, 0 rows affected (0.02 sec) mysql> CREATE TABLE t1 (num_var numeric(20, 10), date_var date, time_var time(6), dt_var datetime(6)); Query OK, 0 rows affected (0.02 sec) mysql> CREATE TABLE t2 AS SELECT num_var % dt_var AS res1 FROM t1; Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> DESC t2; +-------+----------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+----------------+------+-----+---------+-------+ | res1 | decimal(20,10) | YES | | NULL | | +-------+----------------+------+-----+---------+-------+ 1 row in set (0.00 sec)
- 在开启精度传递的场景下，使用UNION，如果参与集合运算的查询语句，其查询的字段为常量，且查询的结果数据类型为INT/DECIMAL，则最后返回的精度存在差异。在MySQL 5.7中，返回的精度与UNION左右两侧的顺序有关；在MySQL 8.0中修复了这个问题，返回的精度与UNION左右两侧的顺序无关；在GaussDB中，返回的精度与UNION左右两侧的顺序无关，与MySQL 8.0一致，与MySQL 5.7不一致。
-- GaussDB: m_db=# CREATE TABLE t1 AS (SELECT -23.45 c2) UNION ALL (SELECT -45.678 c2); INSERT 0 2 m_db=# DESC t1; Field | Type | Null | Key | Default | Extra -------+--------------+------+-----+---------+------- c2 | decimal(5,3) | YES | | | (1 row) m_db=# CREATE TABLE t2 AS (SELECT -45.678 c2) UNION ALL (SELECT -23.45 c2); INSERT 0 2 m_db=# DESC t2; Field | Type | Null | Key | Default | Extra -------+--------------+------+-----+---------+------- c2 | decimal(5,3) | YES | | | (1 row) -- MySQL 5.7: mysql> CREATE TABLE t1 AS (SELECT -23.45 c2) UNION ALL (SELECT -45.678 c2); Query OK, 2 rows affected (2.28 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> DESC t1; +-------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+--------------+------+-----+---------+-------+ | c2 | decimal(6,3) | NO | | 0.000 | | +-------+--------------+------+-----+---------+-------+ 1 row in set (0.00 sec) mysql> CREATE TABLE t2 AS (SELECT -45.678 c2) UNION ALL (SELECT -23.45 c2); Query OK, 2 rows affected (2.22 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> DESC t2; +-------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+--------------+------+-----+---------+-------+ | c2 | decimal(5,3) | NO | | 0.000 | | +-------+--------------+------+-----+---------+-------+ 1 row in set (0.00 sec) -- MySQL 8.0: mysql> CREATE TABLE t1 AS (SELECT -23.45 c2) UNION ALL (SELECT -45.678 c2); Query OK, 2 rows affected (0.02 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> DESC t1; +-------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+--------------+------+-----+---------+-------+ | c2 | decimal(5,3) | NO | | 0.000 | | +-------+--------------+------+-----+---------+-------+ 1 row in set (0.03 sec) mysql> CREATE TABLE t2 AS (SELECT -45.678 c2) UNION ALL (SELECT -23.45 c2); Query OK, 2 rows affected (0.03 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> DESC t2; +-------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+--------------+------+-----+---------+-------+ | c2 | decimal(5,3) | NO | | 0.000 | | +-------+--------------+------+-----+---------+-------+ 1 row in set (0.02 sec)
双冒号转换差异点
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)
decimal类型差异点
在CREATE TABLE ... AS (SELECT ...)语句中，使用decimal数据类型，若含有前缀0，GaussDB数据库下忽略前缀0，长度计算不包括0，在MySQL 5.7下，长度计算加上前缀0的数量，在MySQL 8.0下，无论存在多少个前缀0，长度计算只加上1。
-- GaussDB m_db=# CREATE TABLE test AS SELECT 004.01 col1; INSERT 0 1 m_db=# DESC test; Field | Type | Null | Key | Default | Extra -------+--------------+------+-----+---------+------- col1 | decimal(3,2) | YES | | | (1 row) -- MySQL 5.7 mysql> CREATE TABLE test AS SELECT 004.01 col1; Query OK, 1 row affected (0.02 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql> DESC test; +-------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+--------------+------+-----+---------+-------+ | col1 | decimal(5,2) | NO | | 0.00 | | +-------+--------------+------+-----+---------+-------+ 1 row in set (0.00 sec) -- MySQL 8.0 mysql> CREATE TABLE test AS SELECT 004.01 col1; Query OK, 1 row affected (0.23 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql> DESC test; +-------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+--------------+------+-----+---------+-------+ | col1 | decimal(4,2) | NO | | 0.00 | | +-------+--------------+------+-----+---------+-------+ 1 row in set (0.01 sec)
父主题： 数据类型
