Shulou(Shulou.com)05/31 Report--

This article introduces the relevant knowledge of "two problems that need to be paid attention to when dealing with null values in MySQL". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

MySQL is an open source database based on structured data. SQL statement is the core language in MySQL database. However, there are two traps to be careful when executing SQL statements in an MySQL database.

Trap 1: null values are not necessarily empty

Null value is a special field. In MySQL databases, null values often have different meanings in different situations. This is a feature of MySQL databases. For example, in an ordinary field (character data), a null value represents a null value. But if you insert data with a null value into a field of type TimesTamp, the null value is not necessarily empty. What happens at this time (as shown in the following figure)?

I first created a table. There are two fields in this table: User_id (whose data type is int) and Date (whose data type is TimesTamp). Now insert a record into the table with a null NULL value inserted into the Date field. However, when we query, the result shows the current time when the record was inserted. What's going on? In fact, this is a trap often encountered when executing SQL statements in an MySQL database: null values are not necessarily empty. In the operation, obviously insert a null value of the data, but the final query 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, the main thing for readers to remember is two. One is the TimesTamp data type cited by the author 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 the column with the auto_increment attribute. If you insert a null value into the column of this property, a sequence of positive integers is inserted. If you insert Null data into a column of other data types, such as character data, it inserts a null value.

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

In MySQL, is the null value (Null) the same as the empty character ('')? The answer is no. Please take a look at the demonstration of the following picture first.

In the same database table, insert data with a null value and a''empty character at the same time, and then use the Select statement to query. The final result is shown in the figure above. Obviously, the results shown are not the same. You can see from this result that a null value is not equal to a null character. This is the second trap encountered in executing SQL statements in MySQL. In practical work, null data and null characters often have different meanings. Database administrators can make choices according to their actual needs. For fields such as the phone number, it can be set to a null value by default (indicating that the other party's phone number is not known at all) or an empty character (indicating that the number was later cancelled), and so on. Because they will have different manifestations in the database, so the database administrator needs to be treated differently. I prefer to use null values rather than empty characters. This is mainly because there are several special operational characters for the null data type. If a field is an empty character, the field name is used in the database instead. Conversely, if a null value is inserted, the 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 indicates that the field is empty, while the latter indicates that the field is non-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 (you need them to supplement the phone number information), you can add the is not null keyword to the query criteria.

The second is Count and other statistical functions, which also have special applications in 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 in the statistical process, this function automatically ignores null data. At this time, the statistics is the user information with the phone number. If you are using data with empty characters, this function counts it in. As shown in the following figure, when counting the two records just established, the result of the system statistics is 1, not 2. It can be seen that the system automatically ignores the data of Null value.

This is the end of the content of "two issues that you need to pay attention to when dealing with null values in MySQL". Thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.