MySQL index null problem

This is the case:

There are several fields in the user table
usernamechar (+) not NULL COMMENT ' user name ',
passwordchar (+) not NULL COMMENT ' password ',
emailchar (+) not NULL COMMENT ' user mailbox ',
mobilechar (+) not NULL COMMENT ' user phone ',

Username Email Mobile is unique, so I want to create a unique index on them

But when registering, the user may only need the mobile phone number to be able, does not have to fill in the mailbox and the user name, at this time has the question, if registers only the mobile phone number, but the user name, the mailbox default empty string, but because has the unique index, therefore cannot insert, I want to set the default null, is allowed to null But then I saw this sentence:

Try to avoid null: The column should be specified as NOT NULL unless you want to store null. In MySQL, columns with null values are difficult to query optimization because they complicate indexing, index statistics, and comparison operations. You should use 0, a special value, or an empty string instead of a null value.

If I have null in my username and mailbox, I don't have an index, can this field be indexed? Have a performance problem?

Ask the great God to tell me how to solve this problem.

Reply content:

This is the case:

There are several fields in the user table
usernamechar (+) not NULL COMMENT ' user name ',
passwordchar (+) not NULL COMMENT ' password ',
emailchar (+) not NULL COMMENT ' user mailbox ',
mobilechar (+) not NULL COMMENT ' user phone ',

Username Email Mobile is unique, so I want to create a unique index on them

But when registering, the user may only need the mobile phone number to be able, does not have to fill in the mailbox and the user name, at this time has the question, if registers only the mobile phone number, but the user name, the mailbox default empty string, but because has the unique index, therefore cannot insert, I want to set the default null, is allowed to null But then I saw this sentence:

Try to avoid null: The column should be specified as NOT NULL unless you want to store null. In MySQL, columns with null values are difficult to query optimization because they complicate indexing, index statistics, and comparison operations. You should use 0, a special value, or an empty string instead of a null value.

If I have null in my username and mailbox, I don't have an index, can this field be indexed? Have a performance problem?

Ask the great God to tell me how to solve this problem.

The posts you see on the V-station may help you with your doubts.
https://www.v2ex.com/t/60437

