mysql和oracle的null 和 ‘‘

mysql和oracle的null 和 ‘’

mysql建表规范见:

https://mp.weixin.qq.com/s?__biz=MzU4ODI1MjA3NQ==&mid=2247490967&idx=2&sn=fc92dc46ad2b6b2c2964c06f6277c4f7&chksm=fddecb53caa94245107a004935add8c0ccb059830f61d31ef404d172c3b07f87c0f576aa9a62&mpshare=1&scene=23&srcid=0923GipnyxKQhFHoObIlFU9W&sharer_sharetime=1600911081363&sharer_shareid=9d1e76e919cc0b2f3ca23ed1f5ef67a8#rd

说明:

mysql和oracle对null和’‘以及’ '处理方式是不同的

1.mysql

-- 建表语句
create table demo ( id int(11),name varchar(50))
-- 插入测试数据
insert into demo select 1 , null  from dual;
insert into demo select 2 , ''    from dual;
insert into demo select 3 , '   '  from dual;  # 说明:''中间是三个空格
-- 查询测试
select * from demo where name is null;
select * from demo where name ='';
select * from demo where name ='   ';

MariaDB [test_study]> select * from demo where name is null;
±-----±-----+
| id | name |
±-----±-----+
| 1 | NULL |
±-----±-----+
1 row in set (0.000 sec)

MariaDB [test_study]> select * from demo where name = ‘’;
±-----±-----+
| id | name |
±-----±-----+
| 2 | |
| 3 | |
±-----±-----+
2 rows in set (0.000 sec)

MariaDB [test_study]> select * from demo where name = ’ ';
±-----±-----+
| id | name |
±-----±-----+
| 2 | |
| 3 | |
±-----±-----+
2 rows in set (0.000 sec)

2.oracle

-- 建表语句
create table demo(id number(2,0),name varchar2(50) ); 
-- 插入测试数据
insert into demo select 1 , '' from dual;
commit;  #oracle数据库要手动提交事务
insert into demo select 2 , null from dual;
commit;
insert into demo select 3 ,  '   ' from dual;
commit;
-- 查询测试
select * from demo where name is null;
select * from demo where name ='';
select * from demo where name ='   ';

select * from demo where name is null;

    ID NAME

---

     1
     2

SQL>
SQL> select * from demo where name =’’;

no rows selected

SQL> select * from demo where name =’ ';

    ID NAME

---

     3

SQL> select * from demo;

    ID NAME

---

     1
     2
     3