There are two pitfalls to be aware of when dealing with Null in MySQL.

Abstract: As MySQL database administrators, we often have to fight Null. But there are two major pitfalls you need to pay attention to.

MySQL database is an open source database based on structured data. SQL statements are the core language in the MySQL database. However, when executing SQL statements in a MySQL database, you need to be careful of two pitfalls.

Trap 1: A null value is not necessarily null

The null value is a special field. In the MySQL database, null values ​​often represent different meanings in different situations. This is a feature of the MySQL database. For example, in ordinary fields (character data), a null value represents a null value. But if you insert a null value into a TimesTamp type field, the null value is not necessarily null. What happens at this time (as shown 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, in which a NULL value is inserted into the Date field. But when we query, the result shows the current time when the record was inserted. What is going on? In fact, this is a trap often encountered when executing SQL statements in a MySQL database: a null value is not necessarily null. During the operation, it is clear that a null value is inserted, but the final query result is not a null value.

In the MySQL database, NULL represents a special meaning for some special types of columns, not just a null value. For these special types of columns, there are two main things that readers should remember. One is the TimesTamp data type mentioned above. 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 the column of this attribute, the system will insert a sequence of positive integers. If Null data is inserted into a column of other data types, such as character data, a null value will be inserted.

Trap 2: Null values ​​are not necessarily equal to null characters

In MySQL, are null values ​​(Null) the same as null characters ('')? The answer is no. Please take a look at the demonstration below.

In the same database table, insert a Null value data and a '' null character data at the same time, and then use the Select statement to query. The final displayed result is shown in the picture above. Obviously the results displayed are different. It can be seen from this result that the null value is not equal to the null character. This is the second trap encountered when executing SQL statements in MySQL. In actual work, null data and null characters often have different meanings. Database administrators can make choices based on actual needs.

For fields such as phone numbers, they can be set to null by default (indicating that the other party’s phone number is not known at all) or set to empty characters (indicating that the number was later cancelled), etc. Since they will have different representations in the database, database administrators need to treat them differently. The author prefers to use null values ​​rather than null characters. This is mainly because there are several special operation characters for the data type of null value. If a field has a null character, the database uses the field name instead. On the contrary, if a null value is inserted, NULL is displayed directly. This is also different from the way other databases are displayed.

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

#The second is statistical functions such as Count, which also have special applications for null values. If you now need to count the number of users with phone numbers in the user information table, you can use the count function and use the phone number as a parameter. Because during the statistical process, this function will automatically ignore null value data. What is counted at this time is the user information with phone numbers. If data with null characters is used, this function will count it. As shown in the figure below, when counting the two records just created, the system statistics result is 1, not 2. It can be seen that the system automatically ignores Null value data.

The above are the two pitfalls to pay attention to when dealing with Null in MySQL. For more related content, please pay attention to the PHP Chinese website (www.php.cn)!