Oracle 模式和 MySQL 模式下空格、空串与 NULL 的处理差异说明

本文介绍 Oracle 租户和 MySQL 租户中 '',' ', NULL 的处理差异。

适用版本

OceanBase V2.x、V3.x 版本。

处理差异

出于兼容性考虑，OceanBase 数据库 Oracle 租户保持和 Oracle 一致，OceanBase 数据库 MySQL 租户保持和 MySQL 一致。

• Oracle 模式下:

  • 存储时，'' 在插入时会转换成NULL值，且不存储数据，结果为 NULL。

  • 存储时，' ' 会存储数据 ASCII 32。

  • 存储时，NULL 不存储数据，结果为 NULL。

  • 查询时，' '会作为有效字符，字符串尾部空格不会忽略。

  • 查询时，'' 和 ' ' 不等价。

  • 查询时，'' 和 NULL 不等价。 ​

• MySQL模式下：

  • 存储时，'' 存储为空，但不为 NULL。

  • 存储时，' ' 存储数据为 ASCII 32。

  • 存储时，NULL 不存储数据，结果为 NULL。

  • 查询时，字符串前的空格会作为有效字符，字符串尾部空格会自动忽略。

  • 查询时，'' 和 ' '等价。

  • 查询时'' 和 NULL 不等价。

  注意

  从 Oracle 迁移到 OceanBase 数据库 MySQL 租户时，要注意这种差异，特别是源端 Oracle 的 VARCHAR2 字段是主键，OceanBase 数据库 MySQL 租户端可能会出现主键冲突的情况。

测试

创建测试数据

Oracle 模式:

1. 创建测试表。

   obclient > CREATE TABLE t1 (id INT, col1 VARCHAR2(10));
   Query OK, 0 rows affected (0.076 sec)
   

2. 插入测试数据。

   obclient > INSERT INTO t1 (id,col1) VALUES (1,'A');
   Query OK, 0 rows affected (0.006 sec)
   

   obclient > INSERT INTO t1 (id,col1) VALUES (2,'A ');
   Query OK, 0 rows affected (0.004 sec)
   

   obclient > INSERT INTO t1 (id,col1) VALUES (3,' A');
   Query OK, 1 row affected (0.003 sec)
   

   obclient > INSERT INTO t1 (id,col1) VALUES (4,' A ');
   Query OK, 0 rows affected (0.002 sec)
   

   obclient > INSERT INTO t1 (id,col1) VALUES (5,' ');
   Query OK, 0 rows affected (0.001 sec)
   

   obclient > INSERT INTO t1 (id,col1) VALUES (6,'');
   Query OK, 0 rows affected (0.001 sec)
   

   obclient > INSERT INTO t1 (id,col1) VALUES (7,NULL);
   Query OK, 0 rows affected (0.000 sec)
   

   提交数据:

   obclient > COMMIT;
   

MySQL 模式:

1. 创建测试表。

   obclient > CREATE TABLE t1 (id INT, col1 VARCHAR(10));
   Query OK, 0 rows affected (0.060 sec)
   

2. 插入测试数据。

   obclient > INSERT INTO t1 (id,col1) VALUES (1,'A');
   Query OK, 0 rows affected (0.005 sec)
   

   obclient > INSERT INTO t1 (id,col1) VALUES (2,'A ');
   Query OK, 0 rows affected (0.003 sec)
   

   obclient > INSERT INTO t1 (id,col1) VALUES (3,' A');
   Query OK, 1 row affected (0.002 sec)
   

   obclient > INSERT INTO t1 (id,col1) VALUES (4,' A ');
   Query OK, 0 rows affected (0.001 sec)
   

   obclient > INSERT INTO t1 (id,col1) VALUES (5,' ');
   Query OK, 0 rows affected (0.001 sec)
   

   obclient > INSERT INTO t1 (id,col1) VALUES (6,'');
   Query OK, 0 rows affected (0.000 sec)
   

   obclient > INSERT INTO t1 (id,col1) VALUES (7,NULL);
   Query OK, 0 rows affected (0.000 sec)
   

   提交数据:

   obclient > COMMIT;
   

Oracle 模式测试

• OceanBase 数据库处于 Oracle 模式时存储 '' 时在插入时会转换成 NULL 值，且不存储数据，结果为 NULL。存储 ' ' 时会存储数据 ASCII 32。存储 NULL 时不存储数据，结果为 NULL。

  执行如下测试语句。

  obclient [OBORACLE]> SELECT id ,col1,dump(col1) FROM t1;
  

  输出结果如下:

  +------+------+------------------------+
  | ID   | COL1 | DUMP(COL1)             |
  +------+------+------------------------+
  |    1 | A    | Typ=22 Len=1: 65       |
  |    2 | A    | Typ=22 Len=2: 65,32    |
  |    3 |  A   | Typ=22 Len=2: 32,65    |
  |    4 |  A   | Typ=22 Len=3: 32,65,32 |
  |    5 |      | Typ=22 Len=1: 32       |
  |    6 | NULL | NULL                   |
  |    7 | NULL | NULL                   |
  +------+------+------------------------+
  7 rows in set (0.002 sec)
  

• OceanBase 数据库处于 Oracle 模式时查询 'A' ,' A',' A ' 时的结果均只查出完全匹配的行。

  1. 查询 'A'。

     执行如下测试语句:

     obclient [OBORACLE]> SELECT id ,col1 FROM t1 WHERE col1='A';
     

     输出结果如下:

     +------+------+
     | ID   | COL1 |
     +------+------+
     |    1 | A    |
     +------+------+
     1 row in set (0.003 sec)
     

  2. 查询 ' A'。

     执行如下测试语句:

     obclient [OBORACLE]> SELECT id ,col1 FROM t1 WHERE col1=' A';
     

     输出结果如下:

     +------+------+
     | ID   | COL1 |
     +------+------+
     |    3 |  A   |
     +------+------+
     1 row in set (0.003 sec)
     

  3. 查询时 ' A '。

     执行如下测试语句:

     obclient [OBORACLE]> SELECT id ,col1 FROM t1 WHERE col1=' A ';
     

     输出结果如下:

     +------+------+
     | ID   | COL1 |
     +------+------+
     |    4 |  A   |
     +------+------+
     1 row in set (0.009 sec)
     

• OceanBase 数据库处于 Oracle 模式时查询 ' ' 时会作为有效字符。

  1. 查询时，'' 和 ' ' 不等价。

     1. 测试 '', 执行如下测试语句:

        obclient [OBORACLE]> SELECT id ,col1 FROM t1 WHERE col1='';
        

        输出结果如下:

        Empty set (0.004 sec)
        

     2. 测试 ' '，执行如下测试语句:

        obclient [OBORACLE]> SELECT id ,col1 FROM t1 WHERE col1=' ';
        

        输出结果如下:

        +------+------+
        | ID   | COL1 |
        +------+------+
        |    5 |      |
        +------+------+
        1 row in set (0.002 sec)
        

• OceanBase 数据库处于 Oracle 模式时查询 '' 和 NULL 不等价。

  执行如下测试语句:

  obclient [OBORACLE]> SELECT id ,col1 FROM t1 WHERE col1 is null;
  

  输出结果如下:

  +------+------+
  | ID   | COL1 |
  +------+------+
  |    6 | NULL |
  |    7 | NULL |
  +------+------+
  2 rows in set (0.005 sec)
  

MySQL 模式测试

• OceanBase 数据库处于 MySQL 模式时存储 '' 存储为空，但不为NULL。存储时，' ' 存储数据为 ASCII 32。存储时，NULL 不存储数据，结果为 NULL。

  1. 执行如下测试语句:

     obclient [obmysql]> SELECT id ,col1,HEX(col1) FROM t1;
     

     输出结果如下:

     +------+------+-----------+
     | id   | col1 | HEX(col1) |
     +------+------+-----------+
     |    1 | A    | 41        |
     |    2 | A    | 4120      |
     |    3 |  A   | 2041      |
     |    4 |  A   | 204120    |
     |    5 |      | 20        |
     |    6 |      |           |
     |    7 | NULL | NULL      |
     +------+------+-----------+
     7 rows in set (0.00 sec)
     

• 字符串前的空格会作为有效字符，字符串后的空格会忽略。

  1. 查询'A'会把'A' 和'A ' 打印，不会打印' A'。

     执行如下测试语句:

     obclient [obmysql]> select id ,col1   from t1 where col1='A';
     

     输出结果如下:

     +------+------+
     | id   | col1 |
     +------+------+
     |    1 | A    |
     |    2 | A    |
     +------+------+
     2 rows in set (0.01 sec)
     

  2. 查询' A'会把'A' 和'A ' 打印，不会打印' A'。

     执行如下测试语句:

     obclient [obmysql]>   select id ,col1   from t1 where col1=' A';
     

     输出结果如下:

     +------+------+
     | id   | col1 |
     +------+------+
     |    3 |  A   |
     |    4 |  A   |
     +------+------+
     2 rows in set (0.00 sec)
     

  3. 查询' A' 和 ' A ' 的结果一致。

     执行如下测试语句:

     obclient [obmysql]> select id ,col1   from t1 where col1=' A ';
     

     输出结果如下:

     +------+------+
     | id   | col1 |
     +------+------+
     |    3 |  A   |
     |    4 |  A   |
     +------+------+
     2 rows in set (0.01 sec)
     

  4. 查询时，'' 和 ' '等价。

     执行如下测试语句:

     obclient [obmysql]> select id ,col1   from t1 where col1='';
     

     输出结果如下:

     +------+------+
     | id   | col1 |
     +------+------+
     |    5 |      |
     |    6 |      |
     +------+------+
     2 rows in set (0.00 sec)
     

  5. 查询时，'' 和 ' '等价。

     执行如下测试语句:

     obclient [obmysql]> select id ,col1   from t1 where col1=' ';
     

     输出结果如下:

     +------+------+
     | id   | col1 |
     +------+------+
     |    5 |      |
     |    6 |      |
     +------+------+
     2 rows in set (0.00 sec)
     

  6. 查询时'' 和 NULL 不等价。

     执行如下测试语句:

     obclient [obmysql]> select id ,col1   from t1 where col1 is null;
     

     输出结果如下:

     +------+------+
     | id   | col1 |
     +------+------+
     |    7 | NULL |
     +------+------+
     1 row in set (0.01 sec)