methods and considerations for null usage in MySQL

SELECT null =0, null =12345, null <>12345, null +12345, NULL | | ' abc ', NULL = NULL, NULL <> NULL, NULL and TRUE, NULL and FALSE, NULL or FALSE, NULL or TRUE, not (null);

If this is an interview question, I don't know how many programmers or even DBAs will be killed ...

What is the right answer? (in order to deepen the impression, it is recommended to copy SQL into MySQL to perform, look)

Here are some reasons to analyze:

So how do you avoid some of the problems with NULL in your application?

• Null as a special value, not equal to NULL, 0, FALSE, use is null/is not NULL to detect
• Declaring not NULL columns, giving default values

Two big traps to be aware of when NULL

Trap one: null value is not necessarily empty

A null value is a more specific field. In a MySQL database, in different situations, null values often represent different meanings. This is an attribute of the MySQL database. As in a normal field (character-type data), a null value represents a null value. However, if you insert data from a null value into a field of type timestamp, the null value is not necessarily null. What happens at this point (pictured below)?

I created a table first. There are two fields in this table: user_id (its data type is int), Date (its data type is timestamp). Now insert a record into this table, where a null null value is inserted into the Date field. But when we query, the result is the current time that the record was inserted. What is this all about? In fact, this is a trap that is often encountered when executing SQL statements in a MySQL database: Null values are not necessarily empty. When the operation, obviously inserted is a null value of data, but the last query is not a null value.

In a MySQL database, NULL is a special type of column that represents a particular meaning, not just a null value. For these special types of columns, the main reader is to remember two. One is the author of the above timestamp data type. If you insert a null value into a column of this data type, it represents the current time of the system. The other is a column with the Auto_increment attribute. If you insert a null value into a column of this property, a positive integer sequence is inserted. If you insert null data into a column of other data types, such as character data, it is inserted with a null value.

Trap two: null value is not necessarily equal to null character

In MySQL, does the null value (NULL) be the same as the null character (')? The answer is No. Please take a look at the demonstration of the following diagram first.

In the same database table, inserts a null value data and a ' null character ' data, and then queries using the SELECT statement. The results shown above are shown above. It is obvious that the results shown are not the same. As you can see from this result, null values are not equal to null characters. This is the second pitfall encountered in executing SQL statements in MySQL. In practice, null-value data and empty characters often represent different meanings. The database administrator can choose from the actual needs.

For fields such as phone numbers, you can default to a null value (indicating that you do not know the other person's phone number) or set to a null character (indicating that the number was later canceled), and so on. Because they have different representations in the database, the database administrator needs to be treated differently. I prefer to use null values rather than NULL characters. This is mainly because there are several special operator characters for the data type for null values. If a field is a null character, the database is replaced with a field name. Conversely, if a null value is inserted, the direct display is null. This is different from the way other databases are displayed.

The is null and is NOT NULL keyword. If you want to determine whether a field contains data with null values, you need to use a special keyword. The former indicates that the field is empty, and the latter indicates that the field is non-null. These two keywords are useful in the query conditions of the SELECT statement. If you need to query all users whose phone numbers are blank (requiring them to supplement their phone number information), you can add the is not NULL keyword in the query criteria.

The second is the statistic function of count, also has the special application in the null value. If you now need to count the number of users who have phone numbers in the User Information table, you can use the Count function and use the phone number as an argument at the same time. Because in the statistical process, this function will automatically ignore the null value of the data. At this point, the statistics are the user information with telephone numbers. If you are using a null character data, the function will count it in. As shown in the following illustration, when you count the two records that you have just established, the result of the system statistics is 1, not 2. The visible system automatically ignores data for null values.