NULL in query values ​​resulting in 0.00 in MySQL

Question

I have a query written dynamically (OO PHP via Joomla) to insert some values ​​into a MySQL database. The form filled out by the user has a field for it for the amount in dollars, and if they leave this gap, I want the value included in the system to be NULL. I wrote a request to the error log as it started; it looks like this:

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','')

If I look in the database table, though, although ActivatedDT is correctly set to NULL, BalanceInit and BalanceCurrent are 0.00. The ActivatedDT field is datetime, and the other two are decimal (18.2), and all three of them are set in the table structure as the default value is NULL.

If I run a query like this:

UPDATE arrc_Voucher 
   SET BalanceInit = null 
WHERE BalanceInit like "0%"

... it sets the value to null, so why is the original insert request not executed? Is it because null is in quotation marks? And if so, why is it configured correctly for ActivatedDT?

Answer 1

remove the quotes around NULL. Actually, an attempt is made to insert a string 'NULL'into a number, and since it cannot be converted to a number, it uses the default value 0.

, ActivatedDT, , . 0 ( "1969-12-31" ), NO_ZERO_DATE , NULL.

, MySQL , , STRICT_ALL_TABLES STRICT_TRANS_TABLES (, ) , TRADITIONAL.

SET sql_mode='TRADITIONAL' sql-mode="TRADITIONAL" my.cnf.

Answer 2

NULL MySQL, . varchar 'NULL'. , NULL .

, NULL.

Answer 3

You do not set fields in NULL, but in lines ( 'NULL').