IT评测·应用市场-qidao123.com

标题: Oracle/MySQL/PG/SQL Server关系数据库中NULL与空字符串的区别 [打印本页]
作者: 泉缘泉    时间: 2023-7-17 16:43
标题: Oracle/MySQL/PG/SQL Server关系数据库中NULL与空字符串的区别
Oracle数据库

在Oracle数据库中，''（空字符串）与null是什么关系呢？ ''（空字符串）是否会等同(或者说等价于)于null值呢？''跟' '（长度为零的空字符串或包含一个或多个空格的空字符串）是否又等价？下面我们测试一下
  1. SQL> create table test(id number not null, name varchar2(16));

  3. Table created.

  5. SQL> insert into test(id, name) values(1, null);

  7. 1 row created.

  9. SQL> insert into test(id, name) values(2, '');

  11. 1 row created.

  13. SQL> insert into test(id, name) values(3, ' '); --符号中' '有一个空格

  15. 1 row created.

  17. SQL> insert into test(id, name) values(4, '  '); --符号中'  '有两个空格


  20. 1 row created.

  22. SQL> commit;

  24. Commit complete.

  26. SQL> select * from test where name is null;

  28.         ID NAME
  29. ---------- ----------------
  30.          1
  31.          2

  33. 2 rows selected.

  35. ##三值逻辑，因为''等价与null，null跟任何非null的值比较都等于false
  36. SQL> select * from test where name='';

  38. no rows selected

  40. SQL> select * from test where name=' '; --符号中' '有一个空格

  42.         ID NAME
  43. ---------- ----------------
  44.          3

  46. 1 row selected.

  48. SQL> select * from test where name='  ';--符号中'  '有两个空格

  50.         ID NAME
  51. ---------- ----------------
  52.          4

  54. 1 row selected.

  56. SQL> select id ,dump(name) from test;

  58.         ID DUMP(NAME)
  59. ---------- --------------------
  60.          1 NULL
  61.          2 NULL
  62.          3 Typ=1 Len=1: 32
  63.          4 Typ=1 Len=2: 32,32

  65. 4 rows selected.

  67. SQL>
复制代码
如上所示，插入''时，Oracle数据库确实将其等同与null，但是，像' '这种长度不为零的空字符串，Oracle会存储其值，从上面实验就可以看出。另外官方文档[1]中关于''与Null的解释如下：
Oracle Database currently treats a character value with a length of zero as null. However, this may not continue to be true in future releases, and Oracle recommends that you do not treat empty strings the same as nulls.
翻译如下：
Oracle 数据库当前将长度为零的空字符值视为Null。但是，在将来的版本中，这种情况可能不会继续存在，Oracle 建议您不要将空字符串视为null值。
另外，还有一个有意思的现象，如果在查询条件中，包含一个空格与包含两个空格的对比，它们似乎又是等价的。如下所示：
  1. SQL> select * from test where ' ' = '  '; 

  3.         ID NAME
  4. ---------- ----------------
  5.          1
  6.          2
  7.          3
  8.          4

  10. 4 rows selected.

  12. SQL>
复制代码
SQL Server数据库

这个实验，我们在SQL Server数据库测试一下看看,你会看到不一样的现象。
  1. create table test(id int ,name varchar(16) );

  3. insert into test(id, name) values(1, null);
  4. insert into test(id, name) values(2, '');
  5. insert into test(id, name) values(3, ' ');--包含一个空格
  6. insert into test(id, name) values(4, '  ');--包含两个空格
复制代码
  1. SELECT * FROM TEST WHERE NAME IS NULL;
复制代码
  1. SELECT * FROM TEST WHERE NAME ='';
  2. SELECT * FROM TEST WHERE NAME =' ';--包含一个空格
  3. SELECT * FROM TEST WHERE NAME ='  ';--包含两个空格
复制代码
  1. SELECT ID, DATALENGTH(NAME) AS COLUMN_LEN FROM TEST;
复制代码
  1. SELECT * FROM TEST WHERE '' = ' ';
复制代码
如上测试，SQL Server跟Oracle不一样，在SQL Server中，’’跟null是不一样的，空字符串是空字符串，null是null，SQL Server数据库中不会将长度为零的空字符串视为null。但是SQL Server在查询的时候比较空字符串时，长度为零的空字符串与长度为1的字符串，长度为N的字符串，它们似乎是等价的。跟Oracle的行为完全不一样。这个的解释如下,详情参考官方文档[2]：
SQL Server follows the ANSI/ISO SQL-92 specification (Section 8.2, , General rules #3) on how to compare strings with spaces. The ANSI standard requires padding for the character strings used in comparisons so that their lengths match before comparing them. The padding directly affects the semantics of WHERE and HAVING clause predicates and other Transact-SQL string comparisons. For example, Transact-SQL considers the strings 'abc' and 'abc ' to be equivalent for most comparison operations.
The only exception to this rule is the LIKE predicate. When the right side of a LIKE predicate expression features a value with a trailing space, SQL Server does not pad the two values to the same length before the comparison occurs. Because the purpose of the LIKE predicate, by definition, is to facilitate pattern searches rather than simple string equality tests, this does not violate the section of the ANSI SQL-92 specification mentioned earlier.
SQL Server 遵循 ANSI/ISO SQL-92 规范（第8.2、、常规规则 #3），说明如何比较带有空格的字符串。 ANSI 标准要求在比较中使用空字符填充字符串，以便它们的长度与比较它们的长度相匹配。 填充直接影响 WHERE 和 HAVING 子句谓词以及其他 Transact-SQL 字符串比较的语义。 例如，Transact-SQL 会将字符串 "abc" 和 "abc " 视为对大多数比较操作等效。此规则的唯一例外是类似谓语。 当 LIKE 谓词表达式的右侧具有尾随空格的值时，SQL Server 不会在比较发生之前将这两个值填充到同一长度。 根据定义，LIKE 谓语的用途是促进模式搜索，而不是简单的字符串相等测试，这不违反前面提到的 ANSI SQL-92 规范的部分。
MySQL数据库

接下来，我们来看看MySQL数据库的测试情况。
  1. mysql> use test
  2. Database changed
  3. mysql> create table test(id int not null, name varchar(16));
  4. Query OK, 0 rows affected (0.01 sec)

  6. mysql> insert into test(id , name) values(1, null);
  7. Query OK, 1 row affected (0.01 sec)

  9. mysql> insert into test(id , name) values(2, '');
  10. Query OK, 1 row affected (0.01 sec)

  12. mysql> insert into test(id , name) values(3, ' ');
  13. Query OK, 1 row affected (0.00 sec)

  15. mysql> insert into test(id , name) values(4, '  ');
  16. Query OK, 1 row affected (0.00 sec)

  18. mysql> select * from test where name is null;
  19. +----+------+
  20. | id | name |
  21. +----+------+
  22. |  1 | NULL |
  23. +----+------+
  24. 1 row in set (0.00 sec)

  26. mysql> select * from test where name='';
  27. +----+------+
  28. | id | name |
  29. +----+------+
  30. |  2 |      |
  31. |  3 |      |
  32. |  4 |      |
  33. +----+------+
  34. 3 rows in set (0.00 sec)

  36. mysql>
  37. mysql> select * from test where name=' ';
  38. +----+------+
  39. | id | name |
  40. +----+------+
  41. |  2 |      |
  42. |  3 |      |
  43. |  4 |      |
  44. +----+------+
  45. 3 rows in set (0.00 sec)

  47. mysql>
复制代码
MySQL的对于null和空字符串的处理方式跟SQL Server是一致的。但是你会发现还是有不一样的地方，下面这个SQL语句， 在SQL Server和MySQL中有不一样的结果。
  1. mysql> select * from test where '' = ' ';
  2. Empty set (0.00 sec)
复制代码
PostgreSQL数据库
  1. postgres=# \c kerry
  2. You are now connected to database "kerry" as user "postgres".
  3. kerry=# create table test(id int not null, name varchar(16));
  4. CREATE TABLE
  5. kerry=# insert into test(id , name) values(1, null);
  6. INSERT 0 1
  7. kerry=# insert into test(id,  name) values(2, '');
  8. INSERT 0 1
  9. kerry=# insert into test(id,  name) values(3, ' ');
  10. INSERT 0 1
  11. kerry=# insert into test(id,  name) values(4, '  ');
  12. INSERT 0 1
  13. kerry=# select * from test where name is null;
  14.  id | name 
  15. ----+------
  16.   1 | 
  17. (1 row)

  19. kerry=# select * from test where name='';
  20.  id | name 
  21. ----+------
  22.   2 | 
  23. (1 row)

  25. kerry=# select * from test where name=' ';
  26.  id | name 
  27. ----+------
  28.   3 |  
  29. (1 row)

  31. kerry=# select * from test where name='  ';
  32.  id | name 
  33. ----+------
  34.   4 |   
  35. (1 row)

  37. kerry=#

  39. kerry=# select * from test where '' = ' ';
  40.  id | name 
  41. ----+------
  42. (0 rows)
复制代码
PostgreSQL的对于null和空字符串的处理方式跟MySQL是一致的.
总结：

几大关系数据库中，只有Oracle数据库会将''视为null，其它关系数据库中，null和''是不同的。另外，包含一个或多个空格的空字符串在细节上有所不同，上面简单实验已经展示了这些细微区别。
参考资料

[1] 1: https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/Nulls.html#:~:text=Oracle%20Database%20currently%20treats%20a%20character%20value%20with,expression%20containing%20a%20null%20always%20evaluates%20to%20null
[2] 2: https://support.microsoft.com/en-us/topic/inf-how-sql-server-compares-strings-with-trailing-spaces-b62b1a2d-27d3-4260-216d-a605719003b0
扫描上面二维码关注我如果你真心觉得文章写得不错，而且对你有所帮助，那就不妨帮忙“推荐"一下，您的“推荐”和”打赏“将是我最大的写作动力！本文版权归作者所有，欢迎转载，但未经作者同意必须保留此段声明，且在文章页面明显位置给出原文连接.
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！



欢迎光临 IT评测·应用市场-qidao123.com (https://dis.qidao123.com/) Powered by Discuz! X3.4