Mysql null considerations

NULL values are not necessarily empty

A null value is a special field. In the mysql tutorial database tutorial, 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 (for example )?

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. Let's take a look at the demo.

Insert a null value and a ''null character in the same database table, and then use the select statement to query the data. The final result is shown in. 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.