如何在MySQL中向整数列添加正整数约束? [英] How to add a positive integer constraint to a integer column in MySQL?

问题描述

我们如何添加将列强制为仅具有正值的约束.

How can we add a constraint which enforces a column to have only positive values.

尝试了以下mysql语句，但不起作用

Tried the following mysql statement but it doesn't work

create table test ( test_column integer CONSTRAINT blah > 0);

推荐答案

您将使用关键字unsigned表示整数不允许使用符号"(即-只能为正数):

You would use the keyword unsigned to signify that the integer doesn't allow a "sign" (i.e. - it can only be positive):

CREATE TABLE test (
    test_column int(11) unsigned
);

您可以在此处.

就防止插入负值的实际约束而言，MySQL具有

As far as an actual constraint to prevent the insertion-of negative values, MySQL has a CHECK clause that can be used in the CREATE TABLE statement, however, according to the documentation:

CHECK子句被解析，但被所有存储引擎忽略.

The CHECK clause is parsed but ignored by all storage engines.

作为参考，这是您将如何使用它(尽管它将执行得很好，但它什么也没做-如手册所述):

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)
);

更新(完全拒绝负值)
我从您的一些评论中注意到，您希望具有负值的查询被完全拒绝并且不设置为0(就像进入unsigned列的常规事务一样).一般而言，没有约束可以做到这一点(至少我知道)，但是，如果您打开严格模式(使用

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 unsigned column 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 enum value).

您可以通过在插入命令之前运行以下命令来对其进行测试:

You can test it by running the following command prior to your insert-commands:

SET @@SESSION.sql_mode = 'STRICT_TRANS_TABLES';

如果它对您有用，则可以使用sql-mode="STRICT_TRANS_TABLES"更新MySQL配置，也可以使用SET @@GLOBAL.sql_mode = 'STRICT_TRANS_TABLES';(我不确定SET命令是否会影响全局mysql配置，所以可能是最好更新实际的配置文件.

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 SET command will affect the global mysql config though, so it may be better to update the actual config-file).

这篇关于如何在MySQL中向整数列添加正整数约束?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！