[thelist] MYSQL column type properties

[rudy]

> In the mysql manual it states, "If neither NULL nor NOT NULL is
> specified, the column is treated as though NULL had been specified."
> I'm still not sure if this then means I should specify NOT NULL.

you should specify NOT NULL if you don't want nulls in the column

i do realize that this appears to be tautological

in the specific example of web forms, you would, as Anthony suggested,
validate the contents of the form field carefully in your application script

if an input form field (text box) has nothing in it, the field still gets
submitted along with the form

your choice is then to store a zero-length string or a null, assuming, of
course, that the column allows nulls

a zero-length string and a null are not the same thing

the following is a rather trivial example, but suppose you have fields for
first name, last name, and middle name, and you wanted to find out the
average length of the middle names in your table

the sql for that is pretty straightforward --

   select avg(len(middlename)) from yourtable

however, the "gotcha" here is that this assumes nulls in the middle name
field if no middle name was supplied

if you've stored zero-length strings instead, the average is skewed, i.e.
wrong

oh, you can still get it, but it's messier --

   select avg(len(middlename)) from yourtable
        where middlename <> ''

like i said, a trivial example


rudy