MySQL列设置为NOT NULL但仍允许NULL值 [英] MySQL column set to NOT NULL but still allowing NULL values

问题描述

我将每一列设置为NOT NULL，但是由于某些原因，我仍然能够在每一列中添加NULL值.这是我的表格信息(创建语法):

I have every column set to NOT NULL but for some reason I am still able to add a NULL value in each column. Here is my table info (create syntax):

CREATE TABLE `addresses` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `address` varchar(100) NOT NULL,
    `city` varchar(100) NOT NULL,
    `state` varchar(4) NOT NULL,
    `zip` varchar(30) NOT NULL,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4545 DEFAULT CHARSET=utf8;

这是一个有效的示例INSERT:

INSERT INTO `addresses` (`street`, `city`, `state`, `zip`) VALUES ('', '', '', '');

关于为什么发生这种情况的任何想法吗?

Any ideas as to why this is happening?

推荐答案

您要插入空字符串，并且空字符串不是NULL，以检查是否出现NULL错误:

You are inserting empty strings, and empty string are not NULL, to check for NULL error do:

INSERT INTO `addresses` (`street`, `city`, `state`, `zip`) VALUES (NULL, NULL, NULL, NULL);

，您将看到错误. NOT NULL仅检查不是NULL的值.

and you will see error. The NOT NULL checks only for values that are not NULL.

要防止使用空字符串，您必须使用触发 ，或在执行INSERT查询之前对服务器端编程语言进行检查，以将空字符串转换为NULL. INSERT的示例触发器可能类似于:(这只是一个示例)

To prevent empty string either you have to use triggers, or do the checks on server side programming language to convert empty strings to NULL before performing INSERT query. An example trigger for INSERT may be like: (this is just an example)

CREATE TRIGGER avoid_empty
    BEFORE INSERT ON addresses
        FOR EACH ROW
        BEGIN
        IF street = '' THEN SET street = NULL END IF;
END; 

这篇关于MySQL列设置为NOT NULL但仍允许NULL值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！