What is the meaning of mysql null

mysql The meaning of null: 1. In an ordinary field, a null value means a null value; 2. If a null value is inserted into a TimesTamp type field, the null value is not necessarily null.

The meaning of mysql null:

Null values ​​do not occupy space, but NULL in MySQL actually occupies space. spatial.

However, when executing SQL statements in a MySQL database, you need to be careful of two traps:

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

I first created a table. 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.

2. The null value is not necessarily equal to the null character

In MySQL, are the null value (Null) the same as the null character ('')? The answer is no.

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. 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 example, fields such as phone numbers can be set to a null value by default (indicating that the other party's phone number is not known at all) or set to an empty character (indicating that the number was later cancelled), etc.

Because 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. 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.

Related free learning recommendations: mysql database(Video)

The above is the detailed content of What is the meaning of mysql null. For more information, please follow other related articles on the PHP Chinese website!