From: Gilles MISSONNIER (gimiiap.fr)
Date: Wed Jul 16 2008 - 18:24:19 CDT

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]

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 - gimiiap.fr
01 44 32 81 36

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]