[thelist] MySQL data typesL

[Brent Rieck]

Noah,
  To my understanding null values can be the default for any column that
you specify as accepting null values.  I just ran a quick test to
confirm it for tinyints, here's the transcript:

    mysql> create table test (test tinyint default null);
    Query OK, 0 rows affected (0.01 sec)
    
    mysql> insert into test values ();
    Query OK, 1 row affected (0.00 sec)
    
    mysql> select * from test;
    +------+
    | test |
    +------+
    | NULL |
    +------+
    1 row in set (0.00 sec)
    
  The two things I'd check:  If the tinyint column is an index it cannot
contain null values[1] (my current best guess as where the problem
originates) - and if that's not the case do a "describe <tablename>" on
the problem table and make sure you see a "yes" in the null column and
"null" in the default column. 

  If it is the index problem, and you are using TinyInts as indexes into
other tables, are you sure you want only 256 possible values?  256 is a
pretty small number, using a SmallInt is only an extra byte per row and
will get you 2^16 possible values.

  For a decent MySQL book, the O'Reilly MySQL/mSQL book is a good, if
now somewhat dated, starting point (they may have an updated version out
by now, I haven't checked).  For reference information, I've found the
MySQL website to be excellent - I can almost always find an answer with
a few minutes of searching.  I also lurk on the MySQL mailing list - a
lot of good information flows through it regularly, but with ~100
messagse/day a threading mail reader and mail filters are your friend.

hope it helps,
Brent

[1] http://www.mysql.com/doc/P/r/Problems_with_NULL.html

On Wed, 2002-01-16 at 14:27, noah wrote:
> Sorry if this is a stupid question.
> 
> I've been using tinyint in MySQL tables in order to build relationships 
> between tables (i.e., to normalize).
> 
> The problem I've just encountered is that even if the default for a tinyint 
> column is set to be null, if a record is entered without information for 
> that column, the value is set to 0 (i.e., it isn't null), which causes 
> problems when extracting data from the database.
> 
> I'm surprised I haven't run into this before, but this is the first time 
> it's gotten in my way. Is there any way of dealing with this other than 
> changing the data type to char, or something like that? I've searched the 
> MySQL manual, but I can't find anything relating to this (probably just 
> because I can't get the right search phrase).
> 
> And so that I don't have to bug people with questions like this in the 
> future, can anyone recommend a good, thorough MySQL book?
> 
> Thanks,
> Noah
>