查询值为NULL，导致MySQL中为0.00 [英] NULL in query values resulting in 0.00 in MySQL

问题描述

我有一个动态编写的查询(通过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正确设置?

解决方案

删除NULL周围的引号.实际发生的是它试图将字符串'NULL'作为数字插入，并且由于无法将其转换为数字，因此使用默认值0.

至于ActivatedDT为何起作用，我猜这是一个日期字段.未能将字符串转换为日期通常会导致将值设置为0(其格式类似于"1969-12-31")，但是如果您具有来尝试此操作.

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?

解决方案

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 value 0.

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 have NO_ZERO_DATE mode enabled, then it would be set to NULL instead.

If you'd like MySQL to throw an error in cases like this, when invalid values are passed, you can set STRICT_ALL_TABLES or STRICT_TRANS_TABLES (make sure you read the part about the difference between them) or one of the emulation modes, like TRADITIONAL.

You can try this with the command SET sql_mode='TRADITIONAL', or by adding sql-mode="TRADITIONAL" in my.cnf.

这篇关于查询值为NULL，导致MySQL中为0.00的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！