`
supportopensource
  • 浏览: 522011 次
  • 性别: Icon_minigender_1
  • 来自: 杭州
社区版块
存档分类
最新评论

Working with NULL Values

 
阅读更多
The NULL value can be surprising until you get used to it. Conceptually, NULL means “a missing unknown value” and it is treated somewhat differently from other values.
To test for NULL, you cannot use the arithmetic comparison operators such as =, <, or <>. To demonstrate this for yourself, try the following query:
mysql> SELECT 1 = NULL, 1 <> NULL, 1 < NULL, 1 > NULL;
+----------+-----------+----------+----------+
| 1 = NULL | 1 <> NULL | 1 < NULL | 1 > NULL |
+----------+-----------+----------+----------+
|     NULL |      NULL |     NULL |     NULL |
+----------+-----------+----------+----------+

Clearly you get no meaningful results from these comparisons. Use the IS NULL and IS NOT NULL operators instead:
mysql> SELECT 1 IS NULL, 1 IS NOT NULL;
+-----------+---------------+
| 1 IS NULL | 1 IS NOT NULL |
+-----------+---------------+
|         0 |             1 |
+-----------+---------------+

In MySQL, 0 or NULL means false and anything else means true. The default truth value from a boolean operation is 1.

Two NULL values are regarded as equal in a GROUP BY.

When doing an ORDER BY, NULL values are presented first if you do ORDER BY ... ASC and last if you do ORDER BY ... DESC.

A common error when working with NULL is to assume that it is not possible to insert a zero or an empty string into a column defined as NOT NULL, but this is not the case. These are in fact values, whereas NULL means “not having a value.” You can test this easily enough by using IS [NOT] NULL as shown:
mysql> SELECT 0 IS NULL, 0 IS NOT NULL, '' IS NULL, '' IS NOT NULL;
+-----------+---------------+------------+----------------+
| 0 IS NULL | 0 IS NOT NULL | '' IS NULL | '' IS NOT NULL |
+-----------+---------------+------------+----------------+
|         0 |             1 |          0 |              1 |
+-----------+---------------+------------+----------------+

Thus it is entirely possible to insert a zero or empty string into a NOT NULL column, as these are in fact NOT NULL.



分享到:
评论

相关推荐

    Kotlin in Action

    The goal of this part of the book is to get you productive writing ... Finally, in chapter 6, you’ll become familiar with one of the key Kotlin specialties: its support for deal- ing with null values.

    UniDAC 7.1.4

    Bug with processing NULL values in the Loader component is fixed Bug with executing a query after executing a batch command is fixed Bug with executing a batch operation inside an explicitly started...

    Python Cookbook, 2nd Edition

    Associating Multiple Values with Each Key in a Dictionary Recipe 4.16. Using a Dictionary to Dispatch Methods or Functions Recipe 4.17. Finding Unions and Intersections of Dictionaries Recipe ...

    C# - CSharp 12 in a Nutshell The Definitive Reference

    These operators are particularly useful when working with nullable types or when you want to provide default values. **Statements** Statements in C# are instructions that perform some action. They ...

    Microsoft Visual C# 2013 Step by Step,最新资料

    Understanding null values and nullable types 189 Using nullable type 190 Understanding the properties of nullable types .191 Using ref and out parameters .192 Creating ref parameters 193 Creating out ...

    Microsoft Codeview and Utilities User's Guide

    1.7 Working with Older Versions of the Assembler Chapter 2 The CodeView Display 2.1 Using Window Mode 2.1.1 Executing Window Commands with the Keyboard 2.1.2 Executing Window Commands with the Mouse ...

    ZendFramework中文文档

    9.4.2. Working with Date Values 9.4.3. Basic Zend_Date Operations Common to Many Date Parts 9.4.3.1. List of Date Parts 9.4.3.2. List of Date Operations 9.4.4. Comparing Dates 9.4.5. Getting ...

    Sql for mysql

    5.12 The Null Value as an Expression . . . . . . . . . . . . . . . . . . . . . . 114 5.13 The Compound Scalar Expression . . . . . . . . . . . . . . . . . . . . 115 5.14 The Aggregation Function and ...

    2009 达内Unix学习笔记

    集合了 所有的 Unix命令大全 ...telnet 192.168.0.23 自己帐号 sd08077-you0 ftp工具 192.168.0.202 tools-toolss ... 各个 shell 可互相切换 ksh:$ sh:$ csh:guangzhou% bash:bash-3.00$ ... 命令和参数之间必需用空格隔...

    Oracle sqldeveloper without jdk (win+linux)

    If the settings are changed with the ALTER SESSION command, SQL Developer reads the new values from the database and use them for subsequent formatting. Do not change the session time zone with ...

    微软内部资料-SQL性能优化3

    Another type of table lock is a schema stability lock (Sch-S) and is compatible with all table locks except the schema modification lock (Sch-M). The schema modification lock (Sch-M) is incompatible ...

    spring-boot-reference.pdf

    4. Working with Spring Boot 5. Learning about Spring Boot Features 6. Moving to Production 7. Advanced Topics II. Getting Started 8. Introducing Spring Boot 9. System Requirements 9.1. Servlet ...

    opensc-0.12.0.tar.gz

    * updated opensc.conf with new default values * fix firefox problems (no real fix, only ugly workaround) * add cardos M4.2 support New in 0.10.0; 2005-10-31; Andreas Jellinghaus * released rc2 ...

    Bochs - The cross platform IA-32 (x86) emulator

    - Added support for VGA graphics mode with 400 lines (partial fix for SF bug #2948724) - NE2K: Fixed "send buffer" command issue on big endian hosts - USB - converted common USB code plus devices ...

    BobBuilder_app

    Theoretically a b+tree is O(N log k N) or log base k of N, now for the typical values of k which are above 200 for example the b+tree should outperform any binary tree because it will use less ...

    java 一手画方一手画圆 多线程

    Based on the given title, description, tags, and partial content, we can extract several ... Understanding these concepts is crucial for developers working with GUI applications and concurrency in Java.

    python3.6.5参考手册 chm

    Python参考手册,官方正式版参考手册,chm版。以下摘取部分内容:Navigation index modules | next | Python » 3.6.5 Documentation » Python Documentation contents What’s New in Python ...PEP 343: The ‘with...

    Google C++ Style Guide(Google C++编程规范)高清PDF

    We can declare (but not define) functions with arguments, and/or return values, of type Foo. (One exception is if an argument Foo or const Foo& has a non-explicit, one-argument constructor, in which ...

Global site tag (gtag.js) - Google Analytics