从COALESCE看数据库差异
【摘要】 本文从条件表达式COALESCE为例，对比看各大数据库的类型规则差异。
在日常使用中，很多人都会接触和使用不只一种数据库，遇到最常见的问题就是各数据库之间的行为表现不一致，包括但不限于数据类型差异、语法差异、函数差异等等。尤其在数据库迁移的时候，这样的问题尤为明显。那为什么会有这样的差异呢？既然有SQL标准，为什么各大数据库还是会有行为差异呢？
在数据库的使用过程中，我也遇到了这样的问题，那便来说说自己的一点看法。
标准是什么？ 什么是标准？ 最多人使用的做法就是标准，不要削足适履。 最终呈现给用户的，一定是具体大量使用需求的功能集合。而对于各大数据库来说，受限于自身的历史、架构和技术路线，一些功能的出现，总是结合着当时的某些业务场景的，这就导致最终呈现的形式会有差异。而这种差异是应该被理解的，不能因此而忽略各大数据库的优点。
最近在使用条件表达式COALESCE的时候，发现这种差异尤为明显，类似的使用还有CASE、NVL、IF、IFNULL、NULLIF等。
| 调用方式 | Oracle | Teradata | MySQL | PostgreSQL | GaussDB(DWS) |
| COALESCE(expr1,expr2...) | √ | √ | √ | √ | √ |
| CASE... result_n |
√ | √ | √ | √ | √ |
| NVL(expr1,expr2) | √ | √ | √ | ||
| IF(bool_value,expr1,expr2) | √ | ||||
| IFNULL(expr1,expr2) | √ | ||||
| NULLIF(expr1,expr2) | √ | √ | √ | √ | √ |
接下来就以COALESCE为例，看下各数据库之间的差异表现。
1、回顾下COALESCE的定义
COALESCE(expr1, expr2, ..., exprn)
COALESCE返回它的第一个非NULL的参数值。如果参数都为NULL，则返回NULL。它常用于在显示数据时用缺省值替换NULL。和CASE表达式一样，COALESCE只计算用来判断结果的参数，即在第一个非空参数右边的参数不会被计算。
COALESCE的语法图如下
2、各数据库的差异表现
COALESCE的差异表现为入参类型和返回值类型，以下分别验证Oracle、Teradata、MySQL、PostgreSQL、GaussDB(DWS)数据库的表现结果。
（1）Oracle
-- number + char
SQL> select coalesce(123,'456') from dual;
select coalesce(123,'456') from dual
*
第 1 行出现错误:
ORA-00932: 数据类型不一致: 应为 NUMBER, 但却获得 CHAR
-- char + number
SQL> select coalesce('123',456) from dual;
select coalesce('123',456) from dual
*
第 1 行出现错误:
ORA-00932: 数据类型不一致: 应为 CHAR, 但却获得 NUMBER
-- number
SQL> create table tmp1 as (select coalesce(100.01,456000) as col_1 from dual);
表已创建。
SQL> select COLUMN_NAME,DATA_TYPE,DATA_LENGTH from user_tab_columns where TABLE_NAME='TMP1';
COLUMN_NAME
--------------------------------------------------------------------------------
DATA_TYPE
--------------------------------------------------------------------------------
DATA_LENGTH
-----------
COL_1
NUMBER
22
Oracle对于COALESCE的入参要求比较严格，不支持入参混合类型，所有入参须为相同类型。对于非数值类型，返回值类型相同；对于数值类型，返回的类型为优先级较高的数值类型。
（2）Teradata
-- number + char
BTEQ -- Enter your SQL request or BTEQ command:
select coalesce(123,'456') as a,type(a);
*** Query completed. One row found. 2 columns returned.
*** Total elapsed time was 1 second.
a Type(a)
------------ --------------------------------------------------------------
123 VARCHAR(4) CHARACTER SET UNICODE
-- char + number
BTEQ -- Enter your SQL request or BTEQ command:
select coalesce('123',456) b,type(b);
*** Query completed. One row found. 2 columns returned.
*** Total elapsed time was 1 second.
b Type(b)
------------------ --------------------------------------------------------
123 VARCHAR(6) CHARACTER SET UNICODE
Teradata对混合类型的支持较好，返回值的类型是所有入参的相容集合类型且精度足够，具体情况视其所在语境而定。如果入参均为非字符类型，且类型相同，则返回该类型；如果入参均为字符类型，返回值为长度最长的字符类型；如果前者参数是数值类型，先确定优先级最高的类型，隐式转换其他参数为该类型，再返回该类型；其他情况不支持。
（3）MySQL
-- number + char
mysql> select coalesce(123,'456');
+---------------------+
| coalesce(123,'456') |
+---------------------+
| 123 |
+---------------------+
1 row in set (0.00 sec)
mysql> create table t1 as select coalesce(123,'456');
Query OK, 1 row affected (0.04 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> desc t1;
+---------------------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------------------+------------+------+-----+---------+-------+
| coalesce(123,'456') | varchar(3) | NO | | | |
+---------------------+------------+------+-----+---------+-------+
1 row in set (0.00 sec)
-- char + number
mysql> select coalesce('123',456);
+---------------------+
| coalesce('123',456) |
+---------------------+
| 123 |
+---------------------+
1 row in set (0.00 sec)
mysql> create table t2 as select coalesce('123',456);
Query OK, 1 row affected (0.03 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> desc t2;
+---------------------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------------------+------------+------+-----+---------+-------+
| coalesce('123',456) | varchar(3) | NO | | | |
+---------------------+------------+------+-----+---------+-------+
1 row in set (0.00 sec)
MySQL对混合类型的入参支持度很高，返回值的类型是所有入参的相容集合类型，但具体情况视其所在语境而定。如果用在字符串语境中，则返回结果为字符串；如果用在数值语境中，则返回结果为十进制值、实值或整数值，且精度足够的类型。
（4）PostgreSQL
-- number + char
postgres=# create table t1 as select coalesce(123,'456');
SELECT 1
postgres=# \d+ t1
数据表 "public.t1"
栏位 | 类型 | Collation | Nullable | Default | 存储 | 统计目标 | 描述
----------+---------+-----------+----------+---------+-------+----------+------
coalesce | integer | | | | plain | |
-- char + number
postgres=# create table t2 as select coalesce('123',456);
SELECT 1
postgres=# \d+ t2
数据表 "public.t2"
栏位 | 类型 | Collation | Nullable | Default | 存储 | 统计目标 | 描述
----------+---------+-----------+----------+---------+-------+----------+------
coalesce | integer | | | | plain | |
PostgreSQL对混合类型的入参提供了一定的支持，返回类型的选择却与Teradata和MySQL的相容规则不同。如果所有入参都是相同的类型，并且不是字符串常量，那么解析成这种类型；否则，优先转换为首个非字符串常量参数的类型；如果从给定的输入到所选的类型不能转换，则返回错误。
（5）GaussDB(DWS)
GaussDB(DWS)基于PostgreSQL进行拓展，对于COALESCE表达式有两种不同的表现，可以在CREATE DATABASE时通过指定选项DBCOMPATIBILITY进行选择。
-- DBCOMPATIBILITY 默认选择 'ora'
postgres=# show sql_compatibility;
sql_compatibility
-------------------
ORA
(1 row)
-- number + char
postgres=# create table t1 as select coalesce(123,'456');
INSERT 0 1
postgres=# \d+ t1
Table "public.t1"
Column | Type | Modifiers | Storage | Stats target | Description
----------+---------+-----------+---------+--------------+-------------
coalesce | integer | | plain | |
Has OIDs: no
Distribute By: HASH(coalesce)
Location Nodes: ALL DATANODES
Options: orientation=row, compression=no
-- char + number
postgres=# create table t2 as select coalesce('123',456);
INSERT 0 1
postgres=# \d+ t2
Table "public.t2"
Column | Type | Modifiers | Storage | Stats target | Description
----------+---------+-----------+---------+--------------+-------------
coalesce | integer | | plain | |
Has OIDs: no
Distribute By: HASH(coalesce)
Location Nodes: ALL DATANODES
Options: orientation=row, compression=no第1种情况的表现和PostgreSQL规则一致，属于对Oracle的拓展。
-- DBCOMPATIBILITY 选择 'td'
postgres=# create database tddb dbcompatibility = 'td';
tddb=# show sql_compatibility;
sql_compatibility
-------------------
TD
(1 row)
-- number + char
postgres=# create table t1 as select coalesce(123,'456');
INSERT 0 1
postgres=# \d+ t1
Table "public.t1"
Column | Type | Modifiers | Storage | Stats target | Description
----------+------+-----------+----------+--------------+-------------
coalesce | text | | extended | |
Has OIDs: no
Distribute By: HASH(coalesce)
Location Nodes: ALL DATANODES
Options: orientation=row, compression=no
-- char + number
postgres=# create table t2 as select coalesce('123',456);
INSERT 0 1
postgres=# \d+ t2
Table "public.t2"
Column | Type | Modifiers | Storage | Stats target | Description
----------+------+-----------+----------+--------------+-------------
coalesce | text | | extended | |
Has OIDs: no
Distribute By: HASH(coalesce)
Location Nodes: ALL DATANODES
Options: orientation=row, compression=no
第2种情况的表现基于对Teradata的拓展。入参类型完全相同时，返回类型同入参类型；入参类型分类相同时，返回类型为优先级较高的类型；入参类型分类不同时，支持数值、字符、常量字符串的混合类型，返回类型的优先级为依次为数值、字符、text；如果从给定的输入到所选的类型不能转换，则返回错误。
3、总结
| 用例 | Oracle | Teradata | MySQL | PostgreSQL | Gaussdb(ora) | Gaussdb(td) |
| COALESCE(123,'456') | 类型不一致 | varchar | varchar | integer | integer | text |
| COALESCE('123',456) | 类型不一致 | varchar | varchar | integer | integer | text |
从各数据库对COALESCE的返回值类型表现可以看出，基础功能表现一致，差异集中在入参类型的混合支持和返回值类型的规则选择。除了本文介绍的差异外，还有很多的差异点需要我们一点点去发掘、去吸收、去总结。对于每个开发者，只有更好的了解数据库差异，才能更好的用好的数据库，避免在学习中、应用中、开发中踩坑。
【版权声明】本文为华为云社区用户原创内容，转载时必须标注文章的来源（华为云社区）、文章链接、文章作者等基本信息， 否则作者和本社区有权追究责任。如果您发现本社区中有涉嫌抄袭的内容，欢迎发送邮件进行举报，并提供相关证据，一经查实，本社区将立刻删除涉嫌侵权内容，举报邮箱：
cloudbbs@huaweicloud.com
- 点赞
- 收藏
- 关注作者
评论（0）