C.R.Vegelin wrote:
> Hi everybody,
> I defined a field Date_End with default NULL, but am missing it.
>
> CREATE TABLE Regions
> ( Country CHAR(4) NOT NULL,
> Date_Start CHAR(4) NOT NULL,
> Date_End CHAR(4) default NULL, # this one ...
> Description CHAR(50)
> ) Engine = MyISAM;
>
> Regions table is filled with a tab-delimited input table like:
> 0001 1997 2500 France
> 1000 1976 WORLD
> 1010 1976 INTRA-EUR
> LOAD DATA INFILE '../Regions.txt' INTO TABLE Regions
> FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\r\n' IGNORE 0 LINES;
You have empty strings, '', not NULLs in column 3. NULLs are indicated by \N
for use with LOAD DATA INFILE. See the manual for all the details
<http://dev.mysql.com/doc/refman/5.0/en/load-data.html>.
> SELECT * FROM Regions WHERE Country >= 1000;
> +---------+------------+----------+-------------+
> | country | date_start | date_end | description |
> +---------+------------+----------+-------------+
> | 1000 | 1976 | | WORLD |
> | 1010 | 1976 | | INTRA-EUR |
> | 1011 | 1976 | | EXTRA-EUR |
> etc.
>
> Question: why is NULL not shown for date_end ?
Because date_end = '', not NULL, for those rows.
> DELETE FROM Regions WHERE Country >= 1000 AND Date_End < 2001;
> Query OK; 33 rows affected;
>
> SELECT * FROM Regions WHERE Country >= 1000;
> Empty Set
>
> Question: So NULL values are matching Date_End < 2001 in the DELETE ???
No, '' is interpreted as 0 in numeric context, hence it is less than 2001.
> I am using MySQL version 5.0.15-nt. Help will be appreciated.
> Cor Vegelin
Either use \N where you want NULLs in your Regions.txt file, or fix them after
importing. Fixing them is probably just a matter of
UPDATE Regions
SET date_end = NULL
WHERE Country >= 1000 AND Date_End = '';
Michael
