MySQL - decimal value cannot be NULL

Question

Every time I try to make an insert statement in my dastabase, I get "Invalid decimal value:" NULL "for column" bounty3 "with row 1 error." How to insert a null value in a decimal data type? Should I just make the default value of 0.00?

Incorrect decimal value: '' for column 'bounty3' at row 1 Whole query: INSERT INTO songs (userid, wavURL, mp3URL, genre, songTitle, BPM, insWanted, bounty, insWanted2, bounty2, insWanted3, bounty3, insWanted4, bounty4, insWanted5, bounty5, insWanted6, bounty6, insWanted7, bounty7, insWanted8, bounty8, insWanted9, bounty9, insWanted10, bounty10) VALUES ('12534545', '/audio/wav/jqmrgpfcMichael/135259578210secreason.wav', '/audio/mp3/jqmrgpfcMichael/135259578210secreason.mp3', 'Rock/Funk', 'titlee', '120', 'bass', '20.00', 'guitar', '20.00', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '') 

I also tried this statement with a NULL value. Here is the error:

 Incorrect decimal value: 'NULL' for column 'bounty3' at row 1 Whole query: INSERT INTO songs (userid, wavURL, mp3URL, genre, songTitle, BPM, insWanted, bounty, insWanted2, bounty2, insWanted3, bounty3, insWanted4, bounty4, insWanted5, bounty5, insWanted6, bounty6, insWanted7, bounty7, insWanted8, bounty8, insWanted9, bounty9, insWanted10, bounty10) VALUES ('12534545', '/audio/wav/jqmrgpfcMichael/143922765110secreason.wav', '/audio/mp3/jqmrgpfcMichael/143922765110secreason.mp3', 'Rock/Funk', 'title', '110', 'bass', '110.00', 'guitar', '20.00', 'NULL', 'NULL', 'NULL', 'NULL', 'NULL', 'NULL', 'NULL', 'NULL', 'NULL', 'NULL', 'NULL', 'NULL', 'NULL', 'NULL', 'NULL', 'NULL') 

Answer 1

use empty but not null if you want a default value.

Answer 2

Oh .. you are trying to insert an empty string '' in bounty3. Replace it with NULL. I also noticed empty strings for other possible numeric values ​​like bonty4. You must replace all empty strings with NULL for numeric values.

for example: mysql_query ("INSERT INTO empty_number, number VALUES (NULL, 1)");

EDIT : HEY HEY get the point, you cannot insert NULL into a numeric value as "NULL" because it is a string, you must insert as NULL without quotes

Answer 3

I just reviewed this issue in MySql 5.1.61. If the server is in strict mode, you must enter 0 instead of "for decimal fields." Disabling strict mode allows you to simply "fill in a null value."

Answer 4

You can run this query to allow NULL values ​​in the "bounty3" field.

ALTER TABLE songs CHANGE bounty3 bounty3 DECIMAL (10,0) NULL;

Answer 5

You need to make sure your database schema is nullable for this column. Run describe <tablename>; in your MySQL client to find out what the schema for this table is.

If the schema does not allow null, you can use alter table to change the schema. Make sure you do not specify NOT NULL for this column, and you should be fine.

Answer 6

Verify that the field type allows you to use NULL values ​​and that the default value is NULL .

Then use:

 UPDATE table_name SET date_field=IF('$date_value'='',NULL,'$date_value') 

This works for insertions or updates.

I tried using $date_value = NULL , and I even tried unset($date_value) before inserting or updating, and it never worked on my decimal fields. MySQL has always converted them to 0.00. The method above was the only solution that worked for me.

Answer 7

Thanks, it worked using NULL when it is empty:

 $conn->bindParam(':'.$valor, ( $key[0] ? $key[0] : NULL ), PDO::PARAM_STR); 

Answer 8

Another option is to ensure that empty cells or fields in the source data contain "NULL" before importing them into MySQL. Thus, MySQL recognizes these fields as truly NULL and does not convert them to 0.

Answer 9

1. Make sure the field is nullable (there is NOT NULL in its definition)

2. Set the field to the default value of NULL.

   Example: price decimal(12,2) DEFAULT NULL

3. Now, to check this, save some number, then `` (empty line) in this field - it should become NULL at the end.

Answer 10

The field is most likely numerical. If the field is numeric, only numbers are accepted. Empty line not accepted

 insert into songs (...,bounty3) values(...,'') 

no empty line

 insert into songs (...,bounty3) values(...,'NULL') 

("NULL" is just a string that has nothing to do with the actual NULL value, so "NULL" is like "HONEY BEE" if you want).

So if the column is numeric, it will only accept numbers. However, if the column is NULLABLE, it will also be NULL. This means that your insert must have

 insert into songs (...,bounty3) values (..., NULL) 

If you press NULL in such a column and the column has a default value, then the DEFAULT value will be used instead of the NULL that you click.