Oracle - ascii为0的陷阱

ascii0是个空字符,如果将这个字符插入到oracle数据库中会是什么现象,是null吗?

二、正式实验

创建一张测试表
create table test(id int, name varchar2(10), comm varchar2(30));

向这张测试表中插入以下数据
insert into test values(1, null, 'null'); --直接插入null
insert into test values(2, '', 'empty string'); --插入空字符
insert into test values(3, ' ', 'blank space'); --插入空格
insert into test values(4, chr(0), 'ascii0'); --插入ascii为0的字符

查测试表,可以看到第2列似乎都是空的

SQL> select * from test; ID NAME COMM ------ ---------- --------------- 1 null 2 empty string 3 blank space 4 ascii0

查看第2列不为空值的行

SQL> select * from test where name is not null; ID NAME COMM ------ ---------- --------------- 3 blank space 4 ascii0

加trim函数再查

SQL> select * from test where trim(name) is not null; ID NAME COMM ------ ---------- --------------- 4 ascii0

那么这个第4行第2列到底存的是什么呢,我们可以通过dump函数去查看

微信截图_20201013105630.png

可以看到第4行的第2列跟第3行的第2列的空格一样,都是占了一个字节,而且他的ASCII码为0,那么我们可以通过下面的方式将第4行查出来

SQL> select * from test where name = chr(0); ID NAME COMM ------ ---------- --------------- 4 ascii0

我们将这张表通过plsql developer工具导出来,看是个什么情况

p5.png

微信截图_20201013105751.png


可以看到通过工具导出来的时候,第4行的第2列会直接转换成null

三、总结

ascii为0插入到oracle中,并不是null,它占用一个字节,要查询出来只能用=chr(0)

尽量别插入ascii为0的字符到数据库中,可以用插入空字符串或者null代替,否则检索起来不方便

碰到实在是不知道这个字段到底存的是什么的时候,可以使用dump函数去查看它具体的ascii码

四、后记

这里分享下这个实验的由来。
因为要对比两个库的同一张表的数据差异性,表数据量不大,而且仅相差三条数据,肉眼看肯定不靠谱,很自然的就想到在一个库上建一张临时表,将另外一个库的这张表导过来,然后做个minus。但minus出来的结果竟然是第一张表的所有行,当时气氛一度很尴尬。
后面经过仔细分析,才发现原来是chr(0)在搞鬼,通过工具导出chr(0)的时候,它会自动转换成null,然而数据库中chr(0)并不是null,也就是说导出来再导进去的数据已经跟原始表的数据不一样了。

内容版权声明:除非注明,否则皆为本站原创文章。

转载注明出处:https://www.heiqu.com/wpgyxf.html