MySQL Default Value Issue

Question

Hello All,

In MySQL, the default value for a Integer Field is 0. During insertion the value for that field is NULL and the NULL gets inserted. I do some arithmetic manipulation like addition, multipilcation, division using that field's value. This results in error as NULL got inserted. How to resolve this problem? Don't the DB support default value for the fields like integer,decimal(may not be for string types, as NULL is valid for string) if their insertion value is null. If Not, then is it correct to have NULL values for Numeric types?

For some reason When i have a decimal field with NOT NULL and DEFAULT VALUE as 0, the insertion fails with the error message "Data truncated for column 'Column_Name' at row 1".

If i remove the NOT NULL property, the insertion succeeds the same message as warning.

The value that gets inserted into that decimal field is the result of AVG() MySql function

Really Clueless...

What could be the reason?

Answer 1

NULL is a correct value for any mysql column / type.

you can set the column to NOT NULL or you need to resolve it in your scripting language, so the null does not get inserted.

Answer 2

You can set a field as NOT NULL to avoid this problem completely. If a NULL value is unwanted, that is what you should do. When a column is NOT NULL, the default value for the type (0 for integers) is inserted instead of NULL.

Answer 3

You must specified the default value '0' to the table structure when you created it. Or you can alter it using:

ALTER TABLE `TableName` CHANGE `FieldName `FieldName` INT( 11 ) NULL DEFAULT '0'

Hope this helps.

Answer 4

IF there no problem with calculation You are doing, change column definition to NOT NULL DEFAULT 0 then You will always have proper integer (or decimal). But You need to remember to change old values from null to 0.

Answer 5

To insert the default value, you have to not specify any value for the column, not even NULL, because NULL is a specific value.