从条件表达式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的定义

1. COALESCE(expr1, expr2, ..., exprn)

复制代码

COALESCE返回它的第一个非NULL的参数值。如果参数都为NULL，则返回NULL。它常用于在表现数据时用缺省值替换NULL。和CASE表达式一样，COALESCE只盘算用来判断结果的参数，即在第一个非空参数右边的参数不会被盘算。
COALESCE的语法图如下

2、各数据库的差异表现

COALESCE的差异表现为入参类型和返回值类型，以下分别验证Oracle、Teradata、MySQL、PostgreSQL、GaussDB(DWS)数据库的表现结果。
（1）Oracle

1. -- number + char
2. SQL> select coalesce(123,'456') from dual;
3. select coalesce(123,'456') from dual
4.                     *
5. 第 1 行出现错误:
6. ORA-00932: 数据类型不一致: 应为 NUMBER, 但却获得 CHAR
7. -- char + number
8. SQL> select coalesce('123',456) from dual;
9. select coalesce('123',456) from dual
10.                       *
11. 第 1 行出现错误:
12. ORA-00932: 数据类型不一致: 应为 CHAR, 但却获得 NUMBER
13. -- number
14. SQL> create table tmp1 as (select coalesce(100.01,456000) as col_1 from dual);
15. 表已创建。
16. SQL> select COLUMN_NAME,DATA_TYPE,DATA_LENGTH from user_tab_columns where TABLE_NAME='TMP1';
17. COLUMN_NAME
18. --------------------------------------------------------------------------------
19. DATA_TYPE
20. --------------------------------------------------------------------------------
21. DATA_LENGTH
22. -----------
23. COL_1
24. NUMBER
25.          22

复制代码

Oracle对于COALESCE的入参要求比较严格，不支持入参混合类型，所有入参须为雷同类型。对于非数值类型，返回值类型雷同；对于数值类型，返回的类型为优先级较高的数值类型。
（2）Teradata

1. -- number + char
2. BTEQ -- Enter your SQL request or BTEQ command:
3. select coalesce(123,'456') as a,type(a);
4. *** Query completed. One row found. 2 columns returned.
5. *** Total elapsed time was 1 second.
6. a            Type(a)
7. ------------ --------------------------------------------------------------
8. 123         VARCHAR(4) CHARACTER SET UNICODE
9. -- char + number
10. BTEQ -- Enter your SQL request or BTEQ command:
11. select coalesce('123',456) b,type(b);
12. *** Query completed. One row found. 2 columns returned.
13. *** Total elapsed time was 1 second.
14. b                  Type(b)
15. ------------------ --------------------------------------------------------
16. 123                VARCHAR(6) CHARACTER SET UNICODE

复制代码

Teradata对混合类型的支持较好，返回值的类型是所有入参的相容聚集类型且精度足够，具体情况视其所在语境而定。如果入参均为非字符类型，且类型雷同，则返回该类型；如果入参均为字符类型，返回值为长度最长的字符类型；如果前者参数是数值类型，先确定优先级最高的类型，隐式转换其他参数为该类型，再返回该类型；其他情况不支持。
（3）MySQL

1. -- number + char
2. mysql> select coalesce(123,'456');
3. +---------------------+
4. | coalesce(123,'456') |
5. +---------------------+
6. | 123                 |
7. +---------------------+
8. 1 row in set (0.00 sec)
9. mysql> create table t1 as select coalesce(123,'456');
10. Query OK, 1 row affected (0.04 sec)
11. Records: 1  Duplicates: 0  Warnings: 0
12. mysql> desc t1;
13. +---------------------+------------+------+-----+---------+-------+
14. | Field               | Type       | Null | Key | Default | Extra |
15. +---------------------+------------+------+-----+---------+-------+
16. | coalesce(123,'456') | varchar(3) | NO   |     |         |       |
17. +---------------------+------------+------+-----+---------+-------+
18. 1 row in set (0.00 sec)
19. -- char + number
20. mysql> select coalesce('123',456);
21. +---------------------+
22. | coalesce('123',456) |
23. +---------------------+
24. | 123                 |
25. +---------------------+
26. 1 row in set (0.00 sec)
27. mysql> create table t2 as select coalesce('123',456);
28. Query OK, 1 row affected (0.03 sec)
29. Records: 1  Duplicates: 0  Warnings: 0
30. mysql> desc t2;
31. +---------------------+------------+------+-----+---------+-------+
32. | Field               | Type       | Null | Key | Default | Extra |
33. +---------------------+------------+------+-----+---------+-------+
34. | coalesce('123',456) | varchar(3) | NO   |     |         |       |
35. +---------------------+------------+------+-----+---------+-------+
36. 1 row in set (0.00 sec)

复制代码

MySQL对混合类型的入参支持度很高，返回值的类型是所有入参的相容聚集类型，但具体情况视其所在语境而定。如果用在字符串语境中，则返回结果为字符串；如果用在数值语境中，则返回结果为十进制值、实值或整数值，且精度足够的类型。
（4）PostgreSQL

1. -- number + char
2. postgres=# create table t1 as select coalesce(123,'456');
3. SELECT 1
4. postgres=# \d+ t1
5.                               数据表 "public.t1"
6.    栏位   |  类型   | Collation | Nullable | Default | 存储  | 统计目标 | 描述
7. ----------+---------+-----------+----------+---------+-------+----------+------
8. coalesce | integer |           |          |         | plain |          |
9. -- char + number
10. postgres=# create table t2 as select coalesce('123',456);
11. SELECT 1
12. postgres=# \d+ t2
13.                               数据表 "public.t2"
14.    栏位   |  类型   | Collation | Nullable | Default | 存储  | 统计目标 | 描述
15. ----------+---------+-----------+----------+---------+-------+----------+------
16. coalesce | integer |           |          |         | plain |          |

复制代码

PostgreSQL对混合类型的入参提供了一定的支持，返回类型的选择却与Teradata和MySQL的相容规则差别。如果所有入参都是雷同的类型，而且不是字符串常量，那么剖析成这种类型；否则，优先转换为首个非字符串常量参数的类型；如果从给定的输入到所选的类型不能转换，则返回错误。
（5）GaussDB(DWS)
GaussDB(DWS)基于PostgreSQL进行拓展，对于COALESCE表达式有两种差别的表现，可以在CREATE DATABASE时通过指定选项DBCOMPATIBILITY进行选择。

1. -- DBCOMPATIBILITY 默认选择 'ora'
2. postgres=# show sql_compatibility;
3. sql_compatibility
4. -------------------
5. ORA
6. (1 row)
7. -- number + char
8. postgres=# create table t1 as select coalesce(123,'456');
9. INSERT 0 1
10. postgres=# \d+ t1
11.                           Table "public.t1"
12.   Column  |  Type   | Modifiers | Storage | Stats target | Description
13. ----------+---------+-----------+---------+--------------+-------------
14. coalesce | integer |           | plain   |              |
15. Has OIDs: no
16. Distribute By: HASH(coalesce)
17. Location Nodes: ALL DATANODES
18. Options: orientation=row, compression=no
19. -- char + number
20. postgres=# create table t2 as select coalesce('123',456);
21. INSERT 0 1
22. postgres=# \d+ t2
23.                           Table "public.t2"
24.   Column  |  Type   | Modifiers | Storage | Stats target | Description
25. ----------+---------+-----------+---------+--------------+-------------
26. coalesce | integer |           | plain   |              |
27. Has OIDs: no
28. Distribute By: HASH(coalesce)
29. Location Nodes: ALL DATANODES
30. Options: orientation=row, compression=no

复制代码

第1种情况的表现和PostgreSQL规则一致，属于对Oracle的拓展。

1. -- DBCOMPATIBILITY 选择 'td'
2. postgres=# create database tddb dbcompatibility = 'td';
3. tddb=# show sql_compatibility;
4. sql_compatibility
5. -------------------
6. TD
7. (1 row)
8. -- number + char
9. postgres=# create table t1 as select coalesce(123,'456');
10. INSERT 0 1
11. postgres=# \d+ t1
12.                           Table "public.t1"
13.   Column  | Type | Modifiers | Storage  | Stats target | Description
14. ----------+------+-----------+----------+--------------+-------------
15. coalesce | text |           | extended |              |
16. Has OIDs: no
17. Distribute By: HASH(coalesce)
18. Location Nodes: ALL DATANODES
19. Options: orientation=row, compression=no
20. -- char + number
21. postgres=# create table t2 as select coalesce('123',456);
22. INSERT 0 1
23. postgres=# \d+ t2
24.                           Table "public.t2"
25.   Column  | Type | Modifiers | Storage  | Stats target | Description
26. ----------+------+-----------+----------+--------------+-------------
27. coalesce | text |           | extended |              |
28. Has OIDs: no
29. Distribute By: HASH(coalesce)
30. Location Nodes: ALL DATANODES
31. 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的返回值类型表现可以看出，基础功能表现一致，差异会集在入参类型的混合支持和返回值类型的规则选择。除了本文介绍的差异外，还有很多的差异点须要我们一点点去发掘、去吸收、去总结。对于每个开辟者，只有更好的了解数据库差异，才能更好的用好的数据库，制止在学习中、应用中、开辟中踩坑。

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。