Mysql Oracle Tidb对空值的处理

三种类型的数据库下面分别创建对应的测试表：

--Oracle 11.2
CREATE TABLE t_oracle (
  id int ,
  name varchar2(10),
  dept varchar2(20) not null);
--MySQL 5.7
 CREATE TABLE t_mysql (
  id int ,
  name varchar(10),
  dept varchar(20) not null); 
 --TiDB  3.0
  CREATE TABLE t_tidb (
  id int ,
  name varchar(10),
  dept varchar(20) not null); 

插入数据测试：

先说结论：oracle把''作为空值(null)处理；mysql和tidb是作为空字符串处理，''不是null值。

 

--oracle

insert into t_oracle values(1,'test','depttest');
insert into t_oracle values(2,'','depttest');
insert into t_oracle values(3,null,'depttest');
insert into t_oracle values(4,null,'');
commit;

结果如下：第四行插入失败，oracle把''作为空值处理

18:02:35 SYS@orcl11g>insert into t_oracle values(1,'test','depttest');

1 row created.

11:09:39 SYS@orcl11g>insert into t_oracle values(2,'','depttest');

1 row created.

11:09:39 SYS@orcl11g>insert into t_oracle values(3,null,'depttest');

1 row created.

11:09:39 SYS@orcl11g>insert into t_oracle values(4,null,'');
insert into t_oracle values(4,null,'')
                                   *
ERROR at line 1:
ORA-01400: cannot insert NULL into ("SYS"."T_ORACLE"."DEPT")


11:09:39 SYS@orcl11g>commit;

Commit complete.

--mysql

insert into t_mysql values(1,'test','depttest');
insert into t_mysql values(2,'','depttest');
insert into t_mysql values(3,null,'depttest');
insert into t_mysql values(4,null,'');

结果如下：都可以成功插入数据

11:13:31[5.7.25-log]root->127.0.0.1[mtest]> insert into t_mysql values(1,'test','depttest');
Query OK, 1 row affected (0.01 sec)

11:13:38[5.7.25-log]root->127.0.0.1[mtest]> insert into t_mysql values(2,'','depttest');
insert into t_mysql values(3,null,'depttest');
Query OK, 1 row affected (0.01 sec)

11:13:38[5.7.25-log]root->127.0.0.1[mtest]> insert into t_mysql values(3,null,'depttest');
Query OK, 1 row affected (0.01 sec)

11:13:38[5.7.25-log]root->127.0.0.1[mtest]> insert into t_mysql values(4,null,'');
Query OK, 1 row affected (0.00 sec)

11:13:38[5.7.25-log]root->127.0.0.1[mtest]> 

 

--tidb

insert into t_tidb values(1,'test','depttest');
insert into t_tidb values(2,'','depttest');
insert into t_tidb values(3,null,'depttest');
insert into t_tidb values(4,null,'');

结果如下：都可以成功插入数据

11:15:38[5.7.25-TiDB-v3.0.17]root->192.168.30.11[mtest]> insert into t_tidb values(1,'test','depttest');
insert into t_tidb values(2,'','depttest');
insert into t_tidb values(3,null,'depttest');
insert into t_tidb values(4,null,'');
Query OK, 1 row affected (0.07 sec)

11:15:40[5.7.25-TiDB-v3.0.17]root->192.168.30.11[mtest]> insert into t_tidb values(2,'','depttest');
Query OK, 1 row affected (0.02 sec)

11:15:40[5.7.25-TiDB-v3.0.17]root->192.168.30.11[mtest]> insert into t_tidb values(3,null,'depttest');
Query OK, 1 row affected (0.03 sec)

11:15:40[5.7.25-TiDB-v3.0.17]root->192.168.30.11[mtest]> insert into t_tidb values(4,null,'');
Query OK, 1 row affected (0.02 sec)

11:15:40[5.7.25-TiDB-v3.0.17]root->192.168.30.11[mtest]> 

 

空值(null) 转换处理

--oracle
select id,nvl(name,id),dept from t_oracle;
--mysql、tidb
select id,ifnull(name,id),dept from t_mysql;

空值(null)过滤筛选

在name列上分别创建索引

--oracle
create index idx_1 on t_oracle(name);
--mysql
create index idx_1 on t_mysql(name);
--tidb
create index idx_1 on t_tidb(name);

验证下筛选条件上有空值的过滤是否会走索引

先造一些测试数据,不同数据库相关表名替换下

for i in $(seq 1 100000)
do
   echo "insert into t_oracle values($(($RANDOM%1000)),'test${i}','depttest');" >> t.sql
done

--oracle,全表扫描
set autotrace on 
set timing on 
select * from t_oracle where name is null;

        ID NAME       DEPT
---------- ---------- --------------------
         2            depttest
         3            depttest

Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
Plan hash value: 286026154

------------------------------------------------------------------------------
| Id  | Operation         | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |          |     1 |    32 |     2   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T_ORACLE |     1 |    32 |     2   (0)| 00:00:01 |
------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("NAME" IS NULL)


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
        392  consistent gets
          0  physical reads
          0  redo size
        726  bytes sent via SQL*Net to client
        523  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          2  rows processed

--mysql,可以走索引
explain select * from t_mysql where name is null;
或者
explain select * from t_mysql where name <=> null;
+----+-------------+---------+------------+------+---------------+-------+---------+-------+------+----------+-----------------------+
| id | select_type | table   | partitions | type | possible_keys | key   | key_len | ref   | rows | filtered | Extra                 |
+----+-------------+---------+------------+------+---------------+-------+---------+-------+------+----------+-----------------------+
|  1 | SIMPLE      | t_mysql | NULL       | ref  | idx_1         | idx_1 | 43      | const |    2 |   100.00 | Using index condition |
+----+-------------+---------+------------+------+---------------+-------+---------+-------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

--tidb,可以走索引
13:40:28[5.7.25-TiDB-v3.0.17]root->192.168.30.11[mtest]> explain select * from t_tidb where name is null;
+-------------------+-------+------+-----------------------------------------------------------------------------+
| id                | count | task | operator info                                                               |
+-------------------+-------+------+-----------------------------------------------------------------------------+
| IndexLookUp_10    | 10.00 | root |                                                                             |
| ├─IndexScan_8     | 10.00 | cop  | table:t_tidb, index:name, range:[NULL,NULL], keep order:false, stats:pseudo |
| └─TableScan_9     | 10.00 | cop  | table:t_tidb, keep order:false, stats:pseudo                                |
+-------------------+-------+------+-----------------------------------------------------------------------------+
3 rows in set (0.00 sec)

--但是<=>这种写法，tidb中执行计划走的是全表扫描，与优化器的逻辑处理有关
13:52:41[5.7.25-TiDB-v3.0.17]root->192.168.30.11[mtest]> explain select * from t_tidb where name <=> null;
+---------------------+----------+------+-----------------------------------------------------------------+
| id                  | count    | task | operator info                                                   |
+---------------------+----------+------+-----------------------------------------------------------------+
| TableReader_7       | 8000.00  | root | data:Selection_6                                                |
| └─Selection_6       | 8000.00  | cop  | nulleq(mtest.t_tidb.name, NULL)                                 |
|   └─TableScan_5     | 10000.00 | cop  | table:t_tidb, range:[-inf,+inf], keep order:false, stats:pseudo |
+---------------------+----------+------+-----------------------------------------------------------------+
3 rows in set (0.00 sec)

mysql5.7中对<=>操作符的解释：

<=>

NULL-safe equal. This operator performs an equality comparison like the = operator, but returns 1 rather than NULL if both operands are NULL, and 0 rather than NULL if one operand is NULL.

The <=> operator is equivalent to the standard SQL IS NOT DISTINCT FROM operator.

mysql> SELECT 1 <=> 1, NULL <=> NULL, 1 <=> NULL;
        -> 1, 1, 0
mysql> SELECT 1 = 1, NULL = NULL, 1 = NULL;
        -> 1, NULL, NULL
For row comparisons, (a, b) <=> (x, y) is equivalent to:

(a <=> x) AND (b <=> y)

按照sql标准定义，空值肯定不等于空值，但mysql为了某种方便，使用<=>可以比较两个null值是否相等。

小结：对于null值的筛选请使用is null和is not null

 

总结：Oracle中name is null不会走索引，oracle数据库的普通索引结构中不会存储Null值。

mysql中的null值可以走索引，简单来讲InnoDB中规定NULL值必须用整数个字节的位表示，如果使用的二进制位个数不是整数个字节，则在字节的高位补0，代表该值为null。

本文地址：https://blog.csdn.net/u010033674/article/details/108978034