Re: Not null field with default value

[Tom Byars]

Peter & Jay, I did read that section. I just can't believe that it won't smoothly set the default value when handed a null as surely the prime use of having default values is to avoid nulls as well as being able to leave fields blank where the default value will suffice. I can't even see how I can set the sql mode to deal logically with this without having it convert true errors (such as`assigning a negative number to an unsigned integer) to zero.

I also noticed the following while messing about with this. I'm sure I read that MySQL sets a bit aside in each record for each field allowed a null value. Why it doesn't just make a specific value for the field denote the null value (e.g. tinyint null value would be 255) instead of wasting space , I don't know. Anyway, I recently used the mysql query browser to restructure a MYISAM table with fields that were allowed null values to a null free table where each field had a default value. I assumed existing null values would be converted to the default value but, after doing a select on the table in the query browser, the null values were still present. i.e. fields that were not null were showing null values. Why does MySQL allow this and how does it happen. Does it retain those bits set aside for null values in each record? i.e. that space can never be reclaimed?