Handling and capitalization problems for null field values in MySQL

In MySQL, the processing of NULL fields, it is important to note that when there is null in the processing query condition, it is possible that the value you get is not desired because, in MySQL, the null value equals (=) is judged equal (=) or unequal (! =) will return FALSE. The main occurrences are in the common select and where sentences.

To deal with this particular situation, MySQL provides special handling of the following keywords:

• is null: This operator returns True when the value of the column is null.
• is not null: The operator returns True when the value of the column is not NULL.
• <=>: The comparison operator (unlike the = operator) returns True when the two value of the comparison is null.

The conditional comparison operation on NULL is quite special. You cannot use = NULL or! = NULL to find a null value in the column.
In MySQL, acomparison of null values with any other value (even null) always returns false, that is, NULL = NULL returns FALSE.

Take a look at the example below, and it's clear what that means.

First create a table checknull in the test database.

1 mysql> use test2 Database changed3 mysql> show tables;4 Empty Set (0.00 sec) 5 6 mysql> CREATE TABLE Checknull (7 -     name varchar (9) Not null,8-age     int); 0 rows affected (0.11 sec)

Let's look at the basic information for creating this table, with show and DESC separately:

 1 mysql> show create TABLE checknull; 2 +-----------+------------------------------------------------------------------------------------------------- ------------------------------+ 3 | Table |                                                                                                                  Create Table | 4 +-----------+------------------------------------------------------------------------------------------------- ------------------------------+ 5 | Checknull | CREATE TABLE ' Checknull ' (6 ' name ' varchar () not NULL, 7 ' age ' int (one) default NULL 8) Engine=myisam default CHARS Et=latin1 | 9 +-----------+------------------------------------------------------------------------------------------------- ------------------------------+10 1 row in Set (0.01 sec) mysql> desc checknull;13 +-------+-------------+------+ -----+---------+-------+14 | Field | Type | Null | Key | Default | Extra |15 +-------+-------------+------+-----+---------+-------+16 |name | varchar (30) |     NO | |       NULL | |17 | Age | Int (11) |     YES | |       NULL | |18 +-------+-------------+------+-----+---------+-------+19 2 rows in Set (0.00 sec)

Let's start by testing the first step of inserting data into this table:

1 mysql> Insert Checknull (name, age) VALUES ("Water", 30); 2 Query OK, 1 row Affected (0.00 sec) 3  4 mysql>  5 mysql> Insert Checknull (name, age) VALUES ("Shihuc", NULL ); 6 Query OK, 1 row Affected (0.00 sec) 7  8 mysql>  9 mysql> Select * from checknull;10 +--------+------+11 | n Ame   | Age  |12 +--------+------+13 | water  |   30 |14 | Shihuc | NULL |15 +--------+------+16 2 rows in Set (0.00 sec)

Next, look at the user information that all the age fields are null before querying:

Age = NULL; 2 Empty Set (0.00 sec) 3 Age is  NULL, 5 +--------+------+ 6 | name   | Age  | 7 +--------+------+ 8 | sh Ihuc | NULL | 9 +--------+------+10 1 row in Set (0.00 sec)

!= NULL;  2 Empty Set (0.00 sec) 3 is not  NULL, 5 +-------+------+ 6 | name  | Age  | 7 +-------+------+ 8 | Water |   30 | 9 +-------+------+10 1 row in Set (0.00 sec)

Did you find that the results were different? So I'm now looking at the results of the query by modifying the case:

Age is null; 2 +--------+------+ 3 | Name   | Age  | 4 +--------+------+ 5 | shihuc | NULL | 6 +--------+------+ 7 1 row in Set (0.00 sec) 8 Age  = null; Empty Set (0.00 sec)

Age! = null; 2 Empty Set (0.00 sec) 3 is not  null, 5 +-------+------+ 6 | name  | Age  | 7 +-------+------+ 8 | wa ter |   30 | 9 +-------+------+10 1 row in Set (0.00 sec)

The results are the same as those found in the above logs.

Here's a little tip, that is, under Linux,MySQL by default, the name of the database, the nameof the table, and the name of the field are case-sensitive, but the value of the field is case- insensitive.

The name of the table and the name of the field are case-sensitive, you can view the value of the database variable lower_case_table_names, 0 is case-sensitive, 1 means no distinction, and the uniform is treated in lowercase.

1 mysql> show variables like "%case%"; 2 +------------------------+-------+3 | Variable_name          | Value |4 +------------------------+-------+5 | Lower_case_file_system | OFF   |6 | lower_case_table_names | 0     |7 +------------------------+-------+8 2 rows in Set (0.00 sec)

For the value of the field, to be case sensitive, you can use binary to limit it. It can be used either when creating a table or in a query's conditional sentence.

1 mysql> CREATE TABLE Lowupper (2, name varchar () NOT NULL, 3--age int, 4-Home varchar (40 )binary); 5 Query OK, 0 rows affected (0.10 sec) 6 7 mysql> Show create TABLE lowupper; 8 +----------+-------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------+ 9 | Table |                                                                                                                                                                                            Create Table |10 +----------+---------------------------- --------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------+11 | Lowupper | CREATE TABLE ' Lowupper ' ("Name ' varchar") not null,13 ' age ' int (one) DEFAULT null,14 ' home ' varchar (+) CHARACTE R SET Latin1COLLATE Latin1_binDefault NULL15) Engine=myisam default charset=latin1 |16 +----------+----------------------------------------------- --------------------------------------------------------------------------------------------------------------- -------------------------------------------+17 1 row in Set (0.00 sec)

In the log above, you can see that when you create a table,

After the home field is constrained binary, show can see that the home field has a proofing rules latin1_bin,
Note that this will be case-sensitive when inserting/querying data.

Insert the following data to do some validation:

 1 mysql> Insert Lowupper (name, age, Home) VALUES ("Shihuc", "a", "Beijing, China"); 2 Query OK, 1 row Affected (0.00 sec) 3 4 mysql> 5 mysql> Insert Lowupper (name, age, Home) VALUES ("Water", 33, " BEIJING, China "); 6 Query OK, 1 row Affected (0.00 sec) 7 8 mysql> Insert Lowupper (name, age, Home) VALUES ("Xiaocheng", "K", "Hubei"); 9 Query OK, 1 row Affected (0.00 sec) mysql> Insert Lowupper (name, age, Home) VALUES ("Zhangsan", NULL, "China"); Query OK, 1 row Affected (0.00 sec) mysql> Insert Lowupper (name, age, Home) VALUES ("Lisi", NULL, "China"); 15 Query OK, 1 row Affected (0.00 sec) mysql> Insert Lowupper (name, age, Home) VALUES ("Wangwu", NULL, "China") Uery OK, 1 row Affected (0.00 sec) mysql> Select * from lowupper;21 +-----------+------+----------------+22 | name | Age | Home |23 +-----------+------+----------------+24 |   Shihuc | 30 | BEIJING, China |25 |   Water | 33 | BEIJING, China |26 | XiaochEng | 33 | Hubei |27 | Zhangsan | NULL | China |28 | Lisi | NULL | China |29 | Wangwu | NULL | China |30 +-----------+------+----------------+31 6 rows in Set (0.00 sec)

Below, check again to see if there is a distinction:

1 mysql> SELECT * from lowupper where home = "China"; 2 +----------+------+-------+3 | Name     | Age  | home  |4 +----------+------+-------+5 | zhangsan | NULL | China |6 +----------+------+-------+7 1 row in Set (0.00 sec)

Then manipulate the Checknull table above, insert a new data into it, and make a query to see if it is case-sensitive:

1 mysql> Insert Checknull (name, age) VALUES ("Shihuc", null); 2 Query OK, 1 row Affected (0.00 sec) 3  4 mysql> select * from Checknull; 5 +--------+------+ 6 | name   | age
   | 7 +--------+------+ 8 | Water  |   name = "Shihuc"; +--------+------+16 | Name   | Age  binary  name = "Shihuc", +--------+------+25 | name   | Age  |26 +-------- +------+27 | Shihuc | NULL |28 +--------+------+29 1 row in Set (0.00 sec)

It is not obvious that the problem with the case of MySQL, is still very interesting, need attention, in the Linux environment. In the Windows environment, there is no test, not very sure. Have the experience to share a bit!

Http://www.cnblogs.com/shihuc/p/5165169.html

Handling and capitalization problems for null field values in MySQL