背景
SQL查询结果的数据莫名被截断?
SQL
* select null as null_,null as null_, '' as string_, '2' as null_2
* union all
* select '8', '999','000',null
* union all
* select 'abc', 'ab', 'abc','abc'
* union all
* select '123456789' ,'123456789','123456789','123456789'\g
Executing . . .
+------+------+---------+---------+
|null_ |null_ |string_ |null_2 |
+------+------+---------+---------+
| | | |2 |
|8 |999 |000 | |
|a |ab |abc |abc |
|1 |123 |123456789|123456789|
+------+------+---------+---------+
(4 rows)
continue
* select null,1
* union all
* select null,1\g
Executing . . .
E_US0887 UNION: Improper use of NULL in the target list; use conversion
function to specify data type of the null value.
(Wed Apr 18 23:57:18 2018)
continue
*
现象
当多段SQL做union/union all时,如果第一段SQL中字段值为空,则会出现:
1. 其他SQL相应列的字符会被截断,截断长度为:union all中第一个非null类型的字段长度,见下图
2. 如果union all语句中的某一列都为空,则会报错
结论
在使用union all做结果集合时,一定要注意null值的处理
1. 每一列一定要有明确的数据类型,如果为空则用cast强制转换,否则就会报错
* select cast(null as varchar(10)),1
* union all
* select null,1\g
Executing . . .
+----------+------+
|col1 |col2 |
+----------+------+
| | 1|
| | 1|
+----------+------+
(2 rows)
continue
2. 避免null出现在集合查询中的第一段SQL里,如果有null,一定要用cast转换,否则数据会被截断