列可空性/可选性：NULL 与 NOT NULL
除了主/外键字段之外，是否有理由在 mysql 表中将某些字段设置为 NULL 或 NOT NULL ？
Is there a reason for or against setting some fields as NULL or NOT NULL in a mysql table, apart from primary/foreign key fields?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
老实说，这完全取决于您的领域。从功能上来说，它对数据库引擎影响不大，但如果您希望拥有一个明确定义的域，通常最好让数据库和应用程序层都反映您对用户提出的要求。
如果用户是否输入“显示名称”对您来说没有意义，那么请务必将该列标记为可为空。另一方面，如果您需要“显示名称”，您应该在数据库中将其标记为非空，并在应用程序中强制执行约束。通过加倍约束，您可以确保即使前端发生更改，该域仍然完全合格。
That completely depends on your domain to be honest. Functionally it makes little difference to the database engine, but if you're looking to have a well defined domain it is often best to have both the database and application layer mirror the requirements you are placing on the user.
If it's moot to you whether or not the user enters their "Display Name", then by all means mark the column as nullable. On the other hand, if you are going to require a "Display Name" you should mark it non null in the database as well as enforcing the constraint in the application. By doubling the constraint, you ensure that should your front-end change, the domain is still fully qualified.
MySQL 在字段上有一个 NOT NULL 条件，但这不会阻止您插入“空”数据。无法将字段标记为“必填”。
正如 Pekka 提到的，您应该在应用程序的更高级别上进行某种验证以防止这种情况发生。
MySQL has a
NOT NULLcondition on a field, but this will not stop you from inserting "empty" data. There is no way to flag a field as "required".As Pekka mentioned, you should be doing some sort of validation to prevent this at a higher level in your application.
这不是 MySQL 特有的事情 - 我所知道的每个数据库都允许定义具有约束的列，该约束要么允许列中存在 NULL 值，要么不允许这种情况发生。
将列定义为 NOT NULL 意味着始终必须存在与数据类型匹配的值。
NULL是一个哨兵值，其数据类型超越了为列定义的任何类型。如果该列是外键，则在将值插入当前表之前，该值还必须已经存在于相关表中。
DEFAULT约束在定义为NOT NULL的列上很常见，但不是必需的，以便在尝试将 NULL 插入这些列时，将用适当的值填充这些列。回到外键，外键列可以为空，这意味着关系是可选的 - 业务规则允许没有关系。何时应该为 NULL 和 NULL使用 NOT NULL 吗？
理想情况下，每一列都应该是
NOT NULL，但这实际上取决于业务规则的要求。It's not a MySQL specific thing - every database that I'm aware of allows for defining columns with a constraint that either allows a
NULLvalue in the column, or does not allow this to happen.Defining a column as
NOT NULLmeans there always has to be a value present that matches the data type.NULLis a sentinel value, and its' data type transcends whatever is defined for the column.If the column is a foreign key, the value also has to already exist in the related table before you insert the value into the current table.
DEFAULTconstraints are common, but not necessary, on columns defined asNOT NULLso that the columns will be populated with an appropriate value if NULL was attempted to be inserted into these columns. Getting back to foreign keys, a foreign key column can be nullable, which means the relationship is optional - the business rules allow for there to be no relationship.When Should NULL & NOT NULL be Used?
Ideally, every column should be
NOT NULLbut it really depends on what the business rules require.我不知道你如何在mySQL中定义必填字段，愿意启发我吗？我真的不知道。
不管怎样，即使这可以做到，我也很难想象出一个有意义的场景。 IMO，您必须更早地验证错误（=不正确的空）数据。验证、清理和切割应该在任何内容进入数据库之前就完成。数据库错误应该发生的唯一时间是发生异常情况时，例如当数据库物理上无法访问时。
I don't know how you would define a required field in mySQL, care to enlighten me? I really don't know.
Anyway, even if this can be done, I can hardly think of a scenario where it would make sense. IMO, you would have to validate faulty (=incorrectly empty) data much earlier. Validation, sanitation and cutting should be done long before anything enters the database. The only time a database error should occur is when something exceptional occurs, e.g. when the database is physically not reachable.