Clever Geek Handbook

MySQL default error

In MySQL, the default value for an integer field is 0. On insertion, the value for that field is NULL and NULL will be inserted. I am doing some arithmetic manipulation like addition, animation, division using this field value. This results in an error when inserting NULL. How to solve this problem? Don't support the default database value for fields like integer, decimal (maybe not for string types, since NULL is valid for a string) if their insert value is null. If not, 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 insert fails with the error "Data is truncated for column 'Column_Name' on row 1".

If I remove the NOT NULL property, the insert is done with the same message as the warning.

The value that is inserted in this decimal field is the result of the AVG () MySql function

Not surprising...

What could be the reason?

+2


source to share


5 answers


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



you can set the column to NOT NULL, or you need to allow it in your scripting language, so no null will be inserted.

+4


source


You must specify the default value "0" in the table structure when you create it. Or you can change it using:

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


Hope it helps.

+3


source


To insert the default, you don't need to supply any value for the column, even NULL, because NULL is a specific value.

+3


source


You can set the field as NOT NULL

to completely avoid this problem. If the value is NULL

undesirable, this is what you should do. When a column NOT NULL

, the NULL

default value for the type (0 for integers) is inserted instead .

+2


source


IF no computation problem. You do, change the column definition to NOT NULL DEFAULT 0, then you will always have your own integer (or decimal). But you need to remember to change the old values ​​from zero to 0.

+1


source






More articles:


All Articles