如何向 MySQL 中的整数列添加正整数约束？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13225461/
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
How to add a positive integer constraint to a integer column in MySQL?
提问by user462455
How can we add a constraint which enforces a column to have only positive values.
我们如何添加一个约束，强制列只有正值。
Tried the following mysql statement but it doesn't work
尝试了以下 mysql 语句，但不起作用
create table test ( test_column integer CONSTRAINT blah > 0);
回答by newfurniturey
You would use the keyword unsignedto signify that the integer doesn't allow a "sign" (i.e. - it can only be positive):
您将使用关键字unsigned来表示整数不允许“符号”（即 - 它只能是正数）：
CREATE TABLE test (
test_column int(11) unsigned
);
You can read more about the numeric data types (signed & unsigned) here.
您可以在此处阅读有关数字数据类型（有符号和无符号）的更多信息。
As far as an actual constraintto prevent the insertion-of negative values, MySQL has a CHECKclause that can be used in the CREATE TABLEstatement, however, according to the documentation:
至于防止插入负值的实际约束，MySQL 有一个CHECK可以在CREATE TABLE语句中使用的子句，但是，根据文档：
The CHECK clause is parsed but ignored by all storage engines.
CHECK 子句被解析但被所有存储引擎忽略。
For reference, here is how you would use it (and though it will execute absolutely fine, it just does nothing - as the manual states):
作为参考，这里是您将如何使用它（虽然它会执行得非常好，但它什么也不做 - 正如手册所述）：
CREATE TABLE test (
test_column int(11) unsigned CHECK (test_column > 0)
);
UPDATE(rejecting negative values completely)
I've noticed from a few of your comments that you want queries with negative values to be completely rejected and not set to 0(as a normal transaction into an unsignedcolumn would do). There is no constraint that can do this in-general (that I know of, at least), however, if you turn strict-mode on (with STRICT_TRANS_TABLES) any query that inserts a negative value into an unsigned column will fail with an error (along with any-other data-insertion errors, such as an invalid enumvalue).
更新（完全拒绝负值）
我从您的一些评论中注意到，您希望完全拒绝具有负值的查询而不将其设置为0（作为对unsigned列的正常事务会这样做）。通常没有任何约束可以做到这一点（至少我知道），但是，如果您打开（使用STRICT_TRANS_TABLES）严格模式，任何将负值插入无符号列的查询都将失败并显示错误（以及任何其他数据插入错误，例如无效enum值）。
You can test it by running the following command prior to your insert-commands:
您可以通过在插入命令之前运行以下命令来测试它：
SET @@SESSION.sql_mode = 'STRICT_TRANS_TABLES';
And if it works for you, you can either update your MySQL config with sql-mode="STRICT_TRANS_TABLES"or use SET @@GLOBAL.sql_mode = 'STRICT_TRANS_TABLES';(I'm not sure if the SETcommand will affect the global mysql config though, so it may be better to update the actual config-file).
如果它适合您，您可以使用sql-mode="STRICT_TRANS_TABLES"或使用更新您的 MySQL 配置SET @@GLOBAL.sql_mode = 'STRICT_TRANS_TABLES';（我不确定该SET命令是否会影响全局 mysql 配置，因此更新实际的配置文件可能会更好）。
回答by Eugene Yarmash
As of MySQL 8.0.16(MariaDB 10.2.1), CHECKconstraintsare enforced. (In earlier versions constraint expressions were accepted in the syntax but ignored).
从 MySQL 8.0.16(MariaDB 10.2.1) 开始，强制执行CHECK约束。（在早期版本中，约束表达式在语法中被接受但被忽略）。
Therefore you can use:
因此，您可以使用：
CREATE TABLE test (
test_column INT CHECK (test_column > 0)
);
or
或者
CREATE TABLE test (
test_column INT,
CONSTRAINT test_column_positive CHECK (test_column > 0)
);
回答by John Woo
just use unsignedto allow only positive values.
仅用于unsigned仅允许正值。
CREATE TABLE hello
(
world int unsigned
);
uncomment the line and you will see the error saying: Data truncation: Out of range value for column 'world' at row 1:
取消注释该行，您将看到错误消息：数据截断：第 1 行“世界”列的值超出范围：
回答by rharrison33
You should use UNSIGNED INTEGER data type. For more information, click here
您应该使用 UNSIGNED INTEGER 数据类型。欲了解更多信息，请单击此处
回答by Mark Richman
The way to fix this is to explicitly tell MySQL Server to create an Unsigned integer.
解决这个问题的方法是明确告诉 MySQL 服务器创建一个无符号整数。
CREATE TABLE tbl_example (
example_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
example_num INT UNSIGNED NOT NULL,
example_text TEXT
PRIMARY KEY (`example_id`)
);
