MySQL empty-value traps

Transfer from http://blog.csdn.net/eroswang/article/details/8529817

The MySQL database is an open source database based on structured data. The SQL statement is the core language in the MySQL database. However, there are two pitfalls to take care of when executing SQL statements in a MySQL database.

Trap one: null value is not necessarily empty

A null value is a relatively special field. In the MySQL database, in different situations, null values often represent different meanings. This is a feature of the MySQL database. For example, in a normal field (character data), a null value represents a null value. However, if you insert a null value into a field of type timestamp, the null value is not necessarily empty. What's happening now?

I created a table first. There are two fields in this table: user_id (whose data type is int), Date (whose data type is timestamp). Now insert a record into this table, where a null null value is inserted into the Date field. When we query, however, the results show the current time of the inserted record. What's the matter? This is a trap that is often encountered when executing SQL statements in a MySQL database: Null values are not necessarily empty. When the operation, obviously inserted is a null value of the data, but the last query is not a null value.

In the MySQL database, NULL is said for some special types of columns, which represent a special meaning, not just a null value. For these special types of columns, the main reader is to remember two. One is the author above the timestamp data type. 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 property, the system inserts a sequence of positive integers. If you insert null data in a column of other data types, such as character data, it inserts a null value.

Trap two: Null values are not necessarily equal to null characters

In MySQL, is null (NULL) the same as the null character (' ')? The answer is No.

In the same database table, insert both a null value data and a ' null character ' of the data, and then use the SELECT statement to query. It is obvious that the results shown are not the same. As you can see from this result, the null value is not equal to the null character. This is the second pitfall encountered in executing SQL statements in MySQL. In practice, NULL data and null characters often represent different meanings. The database administrator can choose according to the actual needs. For fields such as phone numbers, you can set the default to a null value (indicating that the phone number is not known at all) or to a null character (indicating that the number was later canceled), and so on. Because they have different representations in the database, the database administrator needs to be treated differently. I prefer to use NULL instead of NULL characters. This is mainly due to the fact that the data type has several special arithmetic characters for the null value. If a field is a null character, the database is replaced with a field name. Conversely, if you insert a null value, the direct display is null. This is different from the way other databases are displayed.

One is the null and is-NOT NULL keyword. If you want to determine whether a field contains data with null values, you need to use a special keyword. The former indicates that the field is empty, and the latter indicates that the field is non-empty. These two keywords are very useful in the query condition of a SELECT statement. If you need to query all users whose phone numbers are empty (they need to be supplemented by phone number information), you can include the is not NULL keyword in the query criteria.

The other is statistical functions such as count, and there are special applications on the null value. If you now need to count the number of users with a phone number in the User Information table, you can use the Count function and use the phone number as a parameter at the same time. Because this function automatically ignores null values during the statistical process. At this point the statistics are the user information with the telephone number. If you are using a null character's data, this function will count it in. When counting the two records just established, the results of the system statistics are 1, not 2. The visible system automatically ignores the data for null values.

Null is determined by null or is not NULL. SQL statements can be handled using the Ifnull function.
To judge an empty string ', use = ' or <> '. SQL statements can be handled with if (col,col,0), that is, when Col is true (non-null, and non-"), otherwise printed 0

MySQL Empty value trap (GO)