[thelist] MySQL data typesL

[noah]

At 06:47 PM 16/01/2002, Brent Rieck wrote:
>   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.

This all checks out. When I add a record, though, and specify "" for this 
column (from PHP), MySQL puts a 0 in there.

At 05:37 PM 16/01/2002, Mark Howells wrote:
>I'm assuming that the column isn't "NOT NULL"? The integer equivalent of 
>null is 0, which may be where the value comes from.

That must be what's going on - thanks.

(Brent Rieck again)
>   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.

I use it for things like province/state, where I know there will only be 64 
records.

Thanks for that, and the book recommendations.

Cheers,
Noah