MySQL 将 NOT NULL 约束添加到列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10218252/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Add the NOT NULL constraint to a column
提问by Anon
I'm using PHPMyAdmin and I try to add the NOT NULL constraint to a column of my table.
我正在使用 PHPMyAdmin 并尝试将 NOT NULL 约束添加到我的表的列中。
PHPMyAdmin accepts my following query :
PHPMyAdmin 接受我的以下查询：
ALTER TABLE `wall` MODIFY `token_message` varchar(40) NOT NULL;
But I can still insert empty strings (=NULL), I don't understand why.
但是我仍然可以插入空字符串（=NULL），我不明白为什么。
PS : If you're going to give me some other queries to add this constraint, note I've have tried these 3 which don't work in my PHPMyAdmin (kind of error : #1064 - You have an error in your SQL syntax; check the manual) :
PS：如果你打算给我一些其他的查询来添加这个约束，请注意我已经尝试过这 3 个在我的 PHPMyAdmin 中不起作用的（错误类型：#1064 - 你的 SQL 语法有错误; 检查手册) :
ALTER TABLE `wall` ALTER COLUMN `token_message` SET NOT NULL;
ALTER TABLE `wall` ALTER COLUMN `token_message` varchar(40) NOT NULL;
ALTER TABLE `wall` MODIFY `token_message` CONSTRAINTS token_message_not_null NOT NULL;
回答by octern
You wrote, "I can still insert empty strings (=NULL)," which sounds like a misunderstanding. In SQL, an empty string does not evaluate to NULL, or vice versa. Try inserting an empty string and doing SELECT from wall where token_message is NULL. You should get zero rows back. Then try doing an insert where you specify NULL(unquoted) as the value for your column, and you should get the expected error message.
您写道，“我仍然可以插入空字符串 (=NULL)”，这听起来像是一种误解。在 SQL 中，空字符串不会计算为 NULL，反之亦然。尝试插入一个空字符串并执行SELECT from wall where token_message is NULL. 你应该得到零行。然后尝试在您指定NULL（未加引号）作为列值的位置执行插入操作，您应该会收到预期的错误消息。
If those tests work as expected, then everything is fine, and your problem is actually that you want to prevent blank strings from being inserted. Check out this questionfor suggestions, or just check for blank strings during validation, before the query.
如果这些测试按预期工作，那么一切都很好，而您的问题实际上是您想防止插入空白字符串。在查询之前查看此问题以获取建议，或者仅在验证期间检查空白字符串。
回答by Marc B
MySQL's column alter syntax requires you to completely re-specify the column. You can't just change one attribute of a column, you have to re-define it completely:
MySQL 的列更改语法要求您完全重新指定列。你不能只改变一列的一个属性，你必须完全重新定义它：
ALTER TABLE wall MODIFY token_message varchar(40) NOT NULL default ''
The only 'SET' version allowed is to change the default value.
唯一允许的“SET”版本是更改默认值。
ref: http://dev.mysql.com/doc/refman/5.1/en/alter-table.html
回答by Russell Fox
I think this is a matter of scrubbing your inputs. As octern mentioned, an empty string ('') is not a NULL value in sql. The best way to handle this is to only allow updates through a store procedure which strips out empty strings, even space characters:
我认为这是清理您的输入的问题。正如 octern 所提到的，空字符串 ('') 在 sql 中不是 NULL 值。处理此问题的最佳方法是仅允许通过删除空字符串甚至空格字符的存储过程进行更新：
CREATE PROC InsertIntoMyDb (@MyVarChar VARCHAR(2000)) AS
SET @MyVarChar = NULLIF(RTRIM(LTRIM(@MyVarChar)), '')
INSERT INTO [TBL] (MyVarChar)
VALUES @MyVarChar
This will truncate any number of spaces to an empty string, turn an empty string into a NULL, and then it will not allow the NULL value to be inserted based on the constraint you already have in place.
这会将任意数量的空格截断为空字符串，将空字符串转换为 NULL，然后将不允许根据您已有的约束插入 NULL 值。
回答by php
Try to use this query
尝试使用此查询
Alter table table_name
change column_name column_name datatype(length) definition
ie,
IE，
Alter table wall
change tocken_message tocken_message varchar(40) NOT NULL DEFAULT
