What is the influence of NULL on the index in MySQL

Editor to share with you what is the impact of NULL on the index in MySQL. I hope you will get something after reading this article. Let's discuss it together.

It works under mysql5.6 and 5.7.The storage engine is InnoDB.

The data are as follows:

1. Single column index

Index the name column in a single column:

Query the row whose name is null:

Query rows whose name is' test0' or null:

It can be found that indexes can be used.

two。 Joint index

Add a federated index to age and name:

Query the row with age 14 and name null:

You can see that the index is also used.

3. Other

Although MySQL can use indexes on columns that contain null, it does not mean that null and other data are the same in the index.

Empty allowed on the column is not recommended. It is best to limit not null and set a default value, such as 0 and''empty string, etc. If it is a datetime type, it can be set to a special value such as' 1970-01-01 00 00'.

For MySQL, null is a special value, Conceptually, NULL means "a missing unknown value" and it is treated somewhat differently from other values. For example, you cannot use operators such as =, and the result of arithmetic operations on null is null,count, which does not include null lines, and null requires more storage space than empty strings.

After reading this article, I believe you have a certain understanding of "what is the impact of NULL on the index in MySQL". If you want to know more about it, you are welcome to follow the industry information channel. Thank you for reading!