Some common mistakes with MySQL null

According to null-values, the value of null in MySQL just means that there is no data. The null value is different from the zero value of some types. For example, the zero value of int type is 0, and the zero value of string is "", but they still have data, not null.

When we save data, we habitually record the temporarily unavailable data as null, indicating that we cannot provide valid information at present.

However, when using null, we need to pay attention to some problems. The MySQL documentation explains this as follows: problems-with-null

Error-prone points when using null

Below I will explain the error-prone points of null given by MySQL officials.

People who are not familiar with MySQL can easily confuse null and zero values.

The concept of the NULL value is a common source of confusion for newcomers to SQL

For example, the data generated by the following two SQL statements are independent

mysql> INSERT INTO my_table (phone) VALUES (NULL);
mysql> INSERT INTO my_table (phone) VALUES ('');

The first SQL statement only indicates that the phone number is not known at the moment, and the second statement indicates that the phone number is known and recorded as ''

Both statements insert a value into the phone column, but the first inserts a NULL value and the second inserts an empty string. The meaning of the first can be regarded as “phone number is not known” and the meaning of the second can be regarded as “the person is known to have no phone, and thus no phone number.”

Logical judgment of null should be handled separately

To determine whether a value is null, you must use the special syntax IS NULL , IS NOT NULL , IFNULL() .

To help with NULL handling, you can use the IS NULL and IS NOT NULL operators and the IFNULL() function.

If you use = to judge, it will always be false

In SQL, the NULL value is never true in comparison to any other value, even NULL

To search for column values ​​that are NULL, you cannot use an expr = NULL test. The following statement returns no rows, because expr = NULL is never true

For example, if you write it like this, the result of the where test will never be true:

SELECT * FROM my_table WHERE phone = NULL;

If you use null and other data to do calculations, the result will always be null, unless the MySQL documentation makes additional special instructions for certain operations.

An expression that contains NULL always produces a NULL value unless otherwise indicated in the documentation for the operators and functions involved in the expression

For example:

mysql> SELECT NULL, 1+NULL, CONCAT('Invisible',NULL);
+------+--------+--------------------------+
| NULL | 1+NULL | CONCAT('Invisible',NULL) |
+------+--------+--------------------------+
| NULL | NULL | NULL |
+------+--------+--------------------------+
1 row in set (0.00 sec)

So if you want to make a logical judgment on null, just use IS NULL

To look for NULL values, you must use the IS NULL test

Indexing columns with null values ​​requires additional anticipation of hidden details

Only InnoDB, MyISAM, and MEMORY storage engines support indexing columns with null values.

You can add an index on a column that can have NULL values ​​if you are using the MyISAM, InnoDB, or MEMORY storage engine. Otherwise, you must declare an indexed column NOT NULL, and you cannot insert NULL into the column.

The length of the index will be 1 larger than the normal index, which means it consumes slightly more memory.

Due to the key storage format, the key length is one greater for a column that can be NULL than for a NOT NULL column.

Grouping, deduplication, and sorting of null values ​​will be treated specially

Contrary to the above statement that =null is always false, null is considered equal.

When using DISTINCT, GROUP BY, or ORDER BY, all NULL values ​​are regarded as equal.

Sorting null values ​​is treated specially.

Null values ​​are either sorted first or last.

When using ORDER BY, NULL values ​​are presented first, or last if you specify DESC to sort in descending order.

Null is ignored during aggregation operations

Aggregate (group) functions such as COUNT(), MIN(), and SUM() ignore NULL values

For example, count(*) will not count data with a null value.

The exception to this is COUNT(*), which counts rows and not individual column values. For example, the following statement produces two counts. The first is a count of the number of rows in the table, and the second is a count of the number of non-NULL values ​​in the age column:

mysql> SELECT COUNT(*), COUNT(age) FROM person;

The above are the details of some common mistakes in MySQL null. For more information about MySQL null, please pay attention to other related articles on 123WORDPRESS.COM!