Hi all,
This is a bit OT but I am trying to fix up some MySQL data on a Linux
system and having just one problem
Numeric types that have no entry end up as 0.00000 rather than NULL when
imported into the tables.
Here is a short test table that shows the problem.
mysql> CREATE TABLE TEST (
-> map_id VARCHAR(10) PRIMARY KEY,
-> sheet_name VARCHAR(30),
-> map_scale DECIMAL(8,2),
-> map_latitude DECIMAL(11,7)
-> );
Query OK, 0 rows affected (0.01 sec)
Here is the data in file short.csv (its all quoted values)
"AUASF00001","Cave Regions","1",""
"AUASF00002","Willi Caves","2",""
"AUASF00003","Caving Areas","3",""
Here I do the insert...
mysql> LOAD DATA LOCAL INFILE 'short.csv' REPLACE INTO TABLE TEST
-> FIELDS TERMINATED BY ',' ENCLOSED BY '"';
Query OK, 3 rows affected (0.00 sec)
Records: 3 Deleted: 0 Skipped: 0 Warnings: 3
The three warnings relate (I guess) to the three values in the last
column which are empty.
And one can see that they have been converted to 0.0000
mysql> select * from TEST;
+------------+--------------+-----------+--------------+
| map_id | sheet_name | map_scale | map_latitude |
+------------+--------------+-----------+--------------+
| AUASF00001 | Cave Regions | 1.00 | 0.0000000 |
| AUASF00002 | Willi Caves | 2.00 | 0.0000000 |
| AUASF00003 | Caving Areas | 3.00 | 0.0000000 |
+------------+--------------+-----------+--------------+
3 rows in set (0.00 sec)
Reading the MySQL docs it clearly says that:
" If neither NULL nor NOT NULL is specified, the column is treated as
though NULL had been specified."
" If no DEFAULT value is specified for a column, MySQL automatically
assigns one. If the column may take NULL as a value, the default
value is NULL. If the column is declared as NOT NULL, the default
value depends on the column type: For numeric types other than
those declared with the AUTO_INCREMENT attribute, the default is 0."
mysql> desc TEST;
+--------------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+---------------+------+-----+---------+-------+
| map_id | varchar(10) | | PRI | | |
| sheet_name | varchar(30) | YES | | NULL | |
| map_scale | decimal(8,2) | YES | | NULL | |
| map_latitude | decimal(11,7) | YES | | NULL | |
+--------------+---------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
You can see from the above that the last field can contain a NULL and
its default will be NULL
but that aint occuring !
I have tried adding NULL to the end of the DECIMAL(11,7) but I still get
same problem. I dont think I should need to add a DEFAULT modifier
either as the default is NULL for a numeric.
I can solve the problem by changing "" in the data to "\N" but I dont
want to do that to the raw data as it will be interchanged with other
databases. I want the csv data fields that dont contain data to be ""
and when I export from the table I want "" for integer fields that are
NULL.
Help much appreciated.
Mike
--
--------------------------------------------------------------------
Michael Lake
Active caver, Linux enthusiast and interested in anything technical.
--------------------------------------------------------------------
--
SLUG - Sydney Linux User's Group - http://slug.org.au/
More Info: http://lists.slug.org.au/listinfo/slug
