mysql - Database design (scheme design), should NULL be avoided as much as possible?

Question

Actual Phenomenon

Learned:

1. NULL semantics are unclear

2. Query is not easy to optimize

3. If it represents a null value, there can be other alternatives (logically)

   • VARCHAR(100) NOT NULL DEFAULT '';

   • INT NOT NULL DEFAULT 0;

Expected Phenomenon

1. I hope to understand the attitude towards NULL when designing a scheme (I remember reading an article saying that you should try: NOT NULL DEFAULT XXX)

Answer 1

Yes, NULL columns should be avoided as much as possible, and default values ​​should be explicitly set as much as possible, especially for indexed columns. In mysql, null takes up space. If the value is indexed, its index is invalid.

Answer 2

Yes, when judging that a field is not empty, you also need to use is not null. NULL in mysql actually takes up space. NULL values ​​will not be stored in B-tree indexes, so if the indexed field can be NULL, the index The efficiency will drop a lot.