查询值中的 NULL 导致 MySQL 中的 0.00
我有一个动态编写的查询（通过 Joomla 的 OO PHP）将一些值插入 MySQL 数据库。用户填写的表格上有一个美元金额字段，如果他们将该字段留空，我希望进入系统的值为 NULL。我已将运行时的查询写到错误日志中；查询如下所示：
INSERT INTO arrc_Voucher
(VoucherNbr,securityCode,sequentialNumber, TypeFlag, CreateDT, ActivatedDT, BalanceInit, BalanceCurrent, clientName)
VALUES
('6032100199108006', '99108006','12','V','2010-10-29 12:50:01','NULL','NULL','NULL','')
但是，当我查看数据库表时，尽管 ActivatedDT 已正确设置为 NULL，但 BalanceInit 和 BalanceCurrent 均为 0.00。 ActivatedDT 字段是日期时间，而其他两个字段是十进制（18,2），并且所有三个字段在表结构中都设置为默认值 NULL。
如果我运行这样的查询：
UPDATE arrc_Voucher
SET BalanceInit = null
WHERE BalanceInit like "0%"
...它确实将值设置为 null，那么为什么初始插入查询不这样做呢？是因为 null 是在引号中吗？如果是这样，为什么它的 ActivatedDT 设置正确？
I have a query that's written dynamically (OO PHP via Joomla) to insert some values into a MySQL database. The form that a user fills out has a field on it for dollar amount, and if they leave that blank I want the value going into the system to be NULL. I've written out the query to the error log as it's running; this is what the query looks like:
INSERT INTO arrc_Voucher
(VoucherNbr,securityCode,sequentialNumber, TypeFlag, CreateDT, ActivatedDT, BalanceInit, BalanceCurrent, clientName)
VALUES
('6032100199108006', '99108006','12','V','2010-10-29 12:50:01','NULL','NULL','NULL','')
When I look in the database table, though, although ActivatedDT is set correctly to NULL, BalanceInit and BalanceCurrent are both 0.00. The ActivatedDT field is a datetime, while the other two are decimal(18,2), and all three are set in the table structure as default value NULL.
If I run a query like this:
UPDATE arrc_Voucher
SET BalanceInit = null
WHERE BalanceInit like "0%"
...it does set the value to null, so why isn't the initial insert query doing so? Is it because null is in quotes? And if so, why is it setting correctly for ActivatedDT?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
删除
NULL周围的引号。实际发生的情况是，它尝试将字符串'NULL'作为数字插入，并且由于它无法转换为数字，因此它使用默认值0。至于为什么ActivatedDT有效，我猜那是一个日期字段。未能将字符串转换为日期通常会导致将值设置为
0（其格式类似于“1969-12-31”），但如果您有NO_ZERO_DATE模式启用，然后就可以了将被设置为NULL。如果你希望 MySQL 在这种情况下抛出错误，当传递无效值时，你可以设置
STRICT_ALL_TABLES或STRICT_TRANS_TABLES（确保您阅读了有关它们之间差异的部分）或其中一种模拟模式，例如传统。您可以使用命令
SET sql_mode='TRADITIONAL'尝试此操作，或在my.cnf中添加sql-mode="TRADITIONAL"。remove the quotes around
NULL. What's actually happening is it's trying to insert the string'NULL'as a number, and since it can't be converted to a number it uses the default value0.As for why ActivatedDT works, I'm guessing that's a date field. Failure to convert a string into a date would normally result in setting the value to
0(which gets formatted as something like '1969-12-31'), but if you haveNO_ZERO_DATEmode enabled, then it would be set toNULLinstead.If you'd like MySQL to throw an error in cases like this, when invalid values are passed, you can set
STRICT_ALL_TABLESorSTRICT_TRANS_TABLES(make sure you read the part about the difference between them) or one of the emulation modes, likeTRADITIONAL.You can try this with the command
SET sql_mode='TRADITIONAL', or by addingsql-mode="TRADITIONAL"inmy.cnf.当您将 NULL 插入 MySQL 数据库时，不能在插入时用引号引起来。它尝试插入 varchar 'NULL'。如果您的想法可行，您将永远无法将实际的单词 NULL 插入数据库中。
当您想要插入 NULL 时，请删除单引号。
When you insert NULL into a MySQL database, you cannot insert it with quotes around it. It tries to insert the varchar 'NULL'. If your idea worked, you would never be able to insert the actual word NULL into the DB.
Remove the single quotes when you want to insert NULL.
您没有将字段设置为
NULL，而是设置为字符串（'NULL'）。You are not setting the fields to
NULLbut to strings ('NULL').