Difference between null and null in MySQL

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

Trap 1: NULL values are not necessarily empty

A null value is a special field. In MySQL databases, null values often have different meanings under different circumstances. This is a feature of the MySQL database. For example, in a common field (metadata data), a null value indicates a null value. However, if a null value is inserted into a field of the timestamp type, the null value is not necessarily set as null. What is the situation?

I created a table first. The table has two fields: user_id (whose data type is int) and date (whose data type is timestamp ). Insert a record to this table, and insert a null value to the date field. However, when we query, the result shows the current time of the inserted record. What is this? In fact, this is a common trap encountered when executing SQL statements in the MySQL database: NULL values are not necessarily set to null. During the operation, the data is clearly inserted with a null value, but the final query result is not a null value.

In MySQL databases, null represents a special meaning for some special types of columns, not just a null value. For these special types of columns, you must remember two types. One is the timestamp Data Type I mentioned above. If a null value is inserted into this data type column, it indicates the current time of the system. The other is a column with the auto_increment attribute. If a null value is inserted into this attribute column, a positive integer sequence is inserted. If NULL data is inserted in columns of other data types, such as values, a null value is inserted.

Trap 2: NULL values are not necessarily equal to null characters

In MySQL, is the null value the same as the null character? The answer is no.

Insert a null value and a ''null character in the same database table, and then use the SELECT statement to query the data. Obviously, the displayed results are different. From this result, we can see that the null value is not equal to the null character. This is the second trap encountered when executing SQL statements in MySQL. In practice, NULL data and null characters often have different meanings. The database administrator can select based on actual needs. For example, for fields such as phone numbers, you can set it to a null value by default (indicating that you do not know the phone number of the other party at all) or an empty character (indicating that the phone number is canceled later. Because they have different forms in the database, the database administrator needs to treat them differently. I prefer null rather than empty characters. This is mainly because there are several special computation characters for the NULL data type. If a field is a null character, it is replaced by a field name in the database. On the contrary, if a null value is inserted, null is directly displayed. This is also different from the display methods of other databases.

The first is the keyword is null and is not null. If you want to determine whether a field contains NULL data, you need to use special keywords. The former indicates that this field is empty, and the latter indicates that this field is not empty. These two keywords are very useful in the query condition of the SELECT statement. To query all users whose phone numbers are empty (they need to add their phone number information), you can add the "is not null" keyword to the query conditions.

Second, statistical functions such as Count also have special applications on null values. If you want 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. This function automatically ignores NULL data during the statistical process. At this time, the statistics show the user information with phone numbers. If null characters are used, this function counts them. When counting the two records just created, the system's statistical result is 1 rather than 2. The system automatically ignores NULL values.

Determines whether null is null or is not null. The ifnull function can be used to process SQL statements.
To determine the Null String '', use ='' or <> ''. SQL statements can be processed with if (COL, Col, 0), that is, when col is true (non-null, and not ''), it is displayed; otherwise, 0 is printed.