Avoid NULL if possible in mysql-优快云博客

本文探讨了在数据库设计中尽量定义字段为NOT NULL的重要性。文章指出，许多应用程序并不需要存储NULL值，但出于默认设置，很多表格包含了可为空的列。这不仅增加了存储空间的需求，还使得MySQL在优化查询时更加复杂，尤其是在处理索引和值比较时。使用NULL值还可能导致固定大小的索引转换为可变大小。文章建议，除非确实需要存储NULL值，否则应避免使用，可以考虑使用零、特殊值或空字符串代替。

You should define fields as NOT NULL whenever you can. A lot of tables include
nullable columns even when the application does not need to store NULL (the
absence of a value), merely because it’s the default. You should be careful to
specify columns as NOT NULL unless you intend to store NULL in them.
It’s harder for MySQL to optimize queries that refer to nullable columns,
because they make indexes, index statistics, and value comparisons more complicated.
A nullable column uses more storage space and requires special processing
inside MySQL. When a nullable column is indexed, it requires an extra
byte per entry and can even cause a fixed-size index (such as an index on a single
integer column) to be converted to a variable-sized one in MyISAM.
Even when you do need to store a “no value” fact in a table, you might not need
to use NULL. Consider using zero, a special value, or an empty string instead.
The performance improvement from changing NULL columns to NOT NULL is usually
small, so don’t make finding and changing them on an existing schema a priority
unless you know they are causing problems. However, if you’re planning to
index columns, avoid making them nullable if possible.