4

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?

Improve this question

5 Answers 5

Reset to default
8

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

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

In my case, I have a spring data object that get saved into the db. The default value for any field is null. Whenever the repo saves the object into the db, it saves the value null instead of the default I specified in the schema, and I kept getting SQL errors complaining inserting null values. Then I finally come to the realization that the default is used by the absence of the field not the field being null.
6

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.

Improve this answer

3 Comments

When i set the NOT NULL property, during insertion is throws the "Data truncated for the column_name at row1" error and no rows are getting inserted
right, as null is not allowed, mysql will truncate the inserted row. you need to resolve this in your script or configure mysql to allow for this (although i am not sure if there is such an option in mysql config)
A field with NOT NULL and a default value should not throw error as i have set a default value if NOT NULL fails. Should't it try to insert the default value?
3

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.

Improve this answer

1 Comment

When i set the NOT NULL property, during insertion is throws the "Data truncated for the column_name at row1" error and no rows are getting inserted
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.

Improve this answer

4 Comments

You have an extra single quote in your statement, otherwise +1.
But still having the default value did't resolve this issue..!
Do you means, the value that is already there? If you, you can use Update to change then all, something like this: "UPDATE TableName SET FieldName = '0' WHERE FieldName IS NULL".
I was able to change the existing null values but when i insert there are error related to truncation as i mentioned in other comments
1

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.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.