NULL in query values resulting in 0.00 in MySQL

Question

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?

Answer 1

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.

Answer 2

You are not setting the fields to NULL but to strings ('NULL').

Answer 3

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.