我设置一个MySQL列为“NOT NULL”但仍然可以插入一个空值 [英] I set a MySQL column to "NOT NULL" but still I can insert an empty value

问题描述

在MySQL中，我有一个表Column1作为NOT NULL：

In MySQL I have a table with Column1 as NOT NULL:

create table myTable 
(
    Column1 int not null,
    Column2 int not null
)

仍插入一个空值，如下所示：

I can still insert an empty value like this:

INSERT INTO `myTable` ( `Column1` ,  `Column2` )
VALUES ( '66', '' );

如何使MySQL列也不允许blankstring？

How can I make the MySQL column also disallow blankstring?

推荐答案

EMPTY STRINGS

在ORACLE中，表示 NULL。然而，在其他一切，一个空字符串仍然是一个字符串，所以不是NULL。

In ORACLE an empty string is used to represent NULL. In virtually everything else, however, an empty string is still a string, and so not NULL.

INTS

在您的案例中，您实际上是在INT列中插入STRINGS。

In your case you're actually inserting STRINGS into an INT column. This forces an implicit CAST operation.

当你的RDBMS转换字符串''到一个INT，它必须得到值0.因为0不为NULL，所以插入。

When your RDBMS is converting the string '' to an INT it must get the value 0. As 0 is not NULL, this gets inserted.

一个更有效的测试将是：

A more valid test would be:

INSERT INTO `plekz`.`countries` (`Column1 ` , `Column2`)
VALUES (66, NULL);

EDIT

对不起，我只读了一半您的问题。

Sorry, I only half read your question. You also ask how to stop '' being inserted.

您的第一个问题是您正在插入STRINGS和该表定义为具有INT字段。您可以对插入的数据设置约束，但这些约束将应用之后的值转换为INT。除非您希望防止还插入值 0 ，否则您无法对表执行此操作以防止此情况。

Your first problem is that you're inserting STRINGS and the table is defined as having INT fields. You can put constraints on the data that gets inserted, but these constraints will apply the the value after an conversion to an INT. Unless you want to prevent the value 0 from also being inserted, there is nothing you can do to the table to prevent this scenario.

你最好的打赌是解决你为什么要插入字符串的第一位。您可以使用一个存储过程，在将字符串转换为INT然后插入它们之前，它们会接受并检查这些字符串。

Your better bet is to address why you are inserting strings in the first place. You could use a stored procedure that takes, and checks, the strings before converting them to INTs and then inserting them. Or, better still, you could make the checks in your client application.

一个技术上可用的选项是使字段为CHAR字段，然后对字段设置约束，防止插入''。我会强烈 建议这样做。

A technically available option is to make the fields CHAR fields, then put a constraint on the fields, preventing '' from being inserted. I would strongly recommend against this.

这篇关于我设置一个MySQL列为“NOT NULL”但仍然可以插入一个空值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！