default, Nullable and NULL : confused

Hello,

I do not understand the behavior of a simple table :
from what I red, in the following exemple the Null column tells the value
can be set to NULL, and the Default value is NULL.
It doesn't seem to work that way.

Some one could explain it ?
I run on a linux debian/etch 5.0.32 MySQL release.


I have a table named "t" like :

mysql> describe t;
+-------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| n | double | YES | | NULL | |
| c | varchar(5) | YES | | NULL | |
+-------+------------+------+-----+---------+-------+

Now I load data infile like this :
load data infile'/data/foo' into table t fields terminated by';';

with /data/foo containing :
0.12345;qwer
1.2345;
;asdf

I get
Records: 3 Deleted: 0 Skipped: 0 Warnings: 1

mysql> show warnings;
+---------+------+----------------------------------------+
| Level | Code | Message |
+---------+------+----------------------------------------+
| Warning | 1265 | Data truncated for column 'n' at row 3 |
+---------+------+----------------------------------------+

from now, I expect to have NULL where the fields are empty, but
instead I get
'' in the 2nd row, columb 'c'
'0' in the last row, column 'n'

mysql> select * from t;
+---------+------+
| n | c |
+---------+------+
| 0.12345 | qwer |
| 1.2345 | |
| 0 | asdf |
+---------+------+


mysql> select * from t where c is null or n is null;
Empty set (0.00 sec)

For my purpose, '0' , '' and NULL

Thank you for any help.
regards,

_-¯-_-¯-_-¯-_-¯-_
Gilles Missonnier
IAP - gimi@iap.fr
01 44 32 81 36

in your data file use this for inserting null values '\N'

0.12345;qwer
1.2345;\N
\N;asdf



On 7/17/08, Gilles MISSONNIER <gimi@iap.fr> wrote:
>
> Hello,
>
> I do not understand the behavior of a simple table :
> from what I red, in the following exemple the Null column tells the value
> can be set to NULL, and the Default value is NULL.
> It doesn't seem to work that way.
>
> Some one could explain it ?
> I run on a linux debian/etch 5.0.32 MySQL release.
>
>
> I have a table named "t" like :
>
> mysql> describe t;
> +-------+------------+------+-----+---------+-------+
> | Field | Type | Null | Key | Default | Extra |
> +-------+------------+------+-----+---------+-------+
> | n | double | YES | | NULL | |
> | c | varchar(5) | YES | | NULL | |
> +-------+------------+------+-----+---------+-------+
>
> Now I load data infile like this :
> load data infile'/data/foo' into table t fields terminated by';';
>
> with /data/foo containing :
> 0.12345;qwer
> 1.2345;
> ;asdf
>
> I get
> Records: 3 Deleted: 0 Skipped: 0 Warnings: 1
>
> mysql> show warnings;
> +---------+------+----------------------------------------+
> | Level | Code | Message |
> +---------+------+----------------------------------------+
> | Warning | 1265 | Data truncated for column 'n' at row 3 |
> +---------+------+----------------------------------------+
>
> from now, I expect to have NULL where the fields are empty, but
> instead I get
> '' in the 2nd row, columb 'c' '0' in the last row, column 'n'
>
> mysql> select * from t;
> +---------+------+
> | n | c |
> +---------+------+
> | 0.12345 | qwer |
> | 1.2345 | |
> | 0 | asdf |
> +---------+------+
>
>
> mysql> select * from t where c is null or n is null;
> Empty set (0.00 sec)
>
> For my purpose, '0' , '' and NULL
>
> Thank you for any help.
> regards,
>
> _-¯-_-¯-_-¯-_-¯-_
> Gilles Missonnier
> IAP - gimi@iap.fr
> 01 44 32 81 36
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=anandkl@gmail.com
>