On Fri, Mar 14, 2008 at 12:04 PM, Les Fletcher <les@affinitycircles.com> wrote:
> | Field | Type | Null | Key | Default | Extra |
> | intfield1 | smallint(6) | YES | | 0 | |
> | intfield2 | smallint(6) | YES | | NULL | |
> | intfield3 | smallint(6) | YES | | NULL | |
> UPDATE table SET intfield1='', intfield2='', intfield3='' WHERE ...;
>
> It seems to me that if the value that is being set is invalid for the
> field it should set it to NULL or at least the default value for the
> field instead of 0. Why is it set to 0 regardless?
>
> Les

1. http://dev.mysql.com/doc/refman/5.0/...lid-data.html\
"If you try to store a string that doesn't start with a number into a
numeric column, MySQL Server stores 0. "
2. You are treating an integer like a string. An integer does not need
apostrophes. Without the apostrophes you would get an error. Type cast
it to an int. Better yet use a bind variable.
3. I suggest using a strict sql_mode. You would then get an error for
the blank string.



--
Rob Wultsch