MySQL ERROR 1048 (23000) 列不能为 NULL，但我正在插入有效数据
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/25154105/
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
ERROR 1048 (23000) Column cannot be NULL, however I am inserting valid data
提问by rustysys-dev
There are tons of these posts on Stack Overflow, however from the 20 or so that I looked at they were either coding errors faced when interfacing with MySQL (which I am not trying to do) or simply wanted null values but had their table defined incorrectly.
Stack Overflow 上有很多这样的帖子，但是从 20 篇左右开始，我看到它们要么是在与 MySQL 交互时面临编码错误（我不想这样做）要么只是想要空值但他们的表定义不正确.
I am seeing an error in MySQL 5.6.19 where I have a column that is not allowed to have a null value. This is fine as it shouldn't have a null value. Here is the table desc below.
我在 MySQL 5.6.19 中看到一个错误，我有一个不允许有空值的列。这很好，因为它不应该有空值。这是下面的表格说明。
mysql> describe z;
+-------+----------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+----------------+
| a | int(11) | NO | PRI | NULL | auto_increment |
| data | char(30) | NO | | NULL | |
| t | datetime | YES | | NULL | |
+-------+----------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
My problem is that I am inserting valid data....
我的问题是我正在插入有效数据....
mysql> insert into z (data, t) values('helloworld', sysdate());
ERROR 1048 (23000): Column 'data' cannot be null
There is one other piece of information that might be of some concern... or may not be.
还有另一条信息可能会引起一些关注……或者可能不会。
I have a trigger and procedure that execute upon the implementation of inserts into this column. However I don't see that it should be a problem due to the trigger being activated after the insert statement completes.
我有一个触发器和过程，它在执行插入到此列时执行。但是我不认为这应该是一个问题，因为在插入语句完成后触发器被激活。
Here is the trigger:
这是触发器：
mysql> show triggers\G
*************************** 1. row ***************************
Trigger: insertuser
Event: INSERT
Table: z
Statement: begin
call triggerproc(sysdate(),user(),(select data from z where a = last_insert_id()));
end
Timing: AFTER
Created: NULL
sql_mode: NO_ENGINE_SUBSTITUTION
Definer: root@localhost
character_set_client: utf8
collation_connection: utf8_general_ci
Database Collation: latin1_swedish_ci
1 row in set (0.00 sec)
And the Procedure:
和程序：
mysql> show create procedure triggerproc\G
*************************** 1. row ***************************
Procedure: triggerproc
sql_mode: NO_ENGINE_SUBSTITUTION
Create Procedure: CREATE DEFINER=`root`@`localhost` PROCEDURE `triggerproc`(in a datetime, in b char(30), in c char(30))
begin
insert into record (t,u,data) values(a,b,c);
end
character_set_client: utf8
collation_connection: utf8_general_ci
Database Collation: latin1_swedish_ci
1 row in set (0.00 sec)
Just for good measure I will include the definition for the record table as well.
为了更好地衡量，我还将包括记录表的定义。
mysql> desc record;
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| t | datetime | NO | | NULL | |
| u | char(30) | NO | | NULL | |
| data | char(30) | NO | | NULL | |
+-------+----------+------+-----+---------+-------+
3 rows in set (0.00 sec)
I have looked through the MySQL reference manual for anything that could be of use, however it doesn't seem to have any details on this other than the standard error and to check that your column is not defined as not null... or I missed it...
我已经查看了 MySQL 参考手册中可能有用的任何内容，但是除了标准错误之外，它似乎没有任何详细信息，并检查您的列是否未定义为非空......或者我错过了...
In any case I would be greatly appreciative if anyone can help me out with finding out either the reason for this error or how I can go about finding the reason.
无论如何，如果有人能帮助我找出此错误的原因或我如何找到原因，我将不胜感激。
Thanks in advance.
提前致谢。
EDIT: My question was answered wonderfully by TheConstructorhe informed me that to grab new information from a column that was just inserted through a trigger that the NEW.column operator may be used. Furthermore he followed up with documentation that helps to understand this issue located at Trigger Syntax.
编辑：TheConstructor很好地回答了我的问题，他告诉我从刚刚通过触发器插入的列中获取新信息，可以使用 NEW.column 运算符。此外，他跟进了有助于理解位于Trigger Syntax 中的这个问题的文档。
I only wonder why the trigger that I had wouldn't work with the insert statement even though it should activate after the previous statement, which makes me believe that it should (theoretically) work.
我只是想知道为什么我拥有的触发器不能与插入语句一起使用，即使它应该在前一个语句之后激活，这让我相信它（理论上）应该起作用。
采纳答案by TheConstructor
Reading the documentation on LAST_INSERT_ID()I would suggest that the value is only updated after the last trigger runs. I also created a trigger which inserts the result of LAST_INSERT_ID()into another table and it would always insert the id of the row inserted by the INSERTstatement before or 0if there was no previous INSERT.
阅读有关LAST_INSERT_ID()我的文档，建议该值仅在最后一次触发器运行后更新。我还创建了一个触发器，它将 的结果插入LAST_INSERT_ID()到另一个表中，并且它总是会插入INSERT之前语句插入的行的 id，或者0如果没有之前的INSERT.
From within an insert or update trigger you can always refer to the state after the statement by using NEW.columnwhere columnis a column-name of your table. See the documentation for examples
在插入或更新触发器中，您始终可以通过使用NEW.columnwherecolumn是表的列名来引用语句之后的状态。有关示例，请参阅文档
