Importing and Exporting Data - MySQL - page 379

i INT ,
c CHAR ( 3 ),
d DATE
);
And suppose that a datafile data.txt looks like this:
1 1 1
abc abc abc
2010-10-10 2010-10-10 2010-10-10
Loading the file into the table causes a number, a string, and a date to be loaded into
each of the three columns. Doing so results in several data conversions and warnings,
which you can see using SHOW WARNINGS immediately following LOAD DATA :
mysql> LOAD DATA LOCAL INFILE 'data.txt' INTO TABLE t;
Query OK, 3 rows affected, 5 warnings (0.01 sec)
Records: 3 Deleted: 0 Skipped: 0 Warnings: 5
mysql> SHOW WARNINGS;
+---------+------+--------------------------------------------------------+
| Level | Code | Message |
+---------+------+--------------------------------------------------------+
| Warning | 1265 | Data truncated for column 'd' at row 1 |
| Warning | 1366 | Incorrect integer value: 'abc' for column 'i' at row 2 |
| Warning | 1265 | Data truncated for column 'd' at row 2 |
| Warning | 1265 | Data truncated for column 'i' at row 3 |
| Warning | 1265 | Data truncated for column 'c' at row 3 |
+---------+------+--------------------------------------------------------+
5 rows in set (0.00 sec)
The SHOW WARNINGS output helps you determine which values were converted and why.
The resulting table looks like this:
mysql> SELECT * FROM t;
+------+------+------------+
| i | c | d |
+------+------+------------+
| 1 | 1 | 0000-00-00 |
| 0 | abc | 0000-00-00 |
| 2010 | 201 | 2010-10-10 |
+------+------+------------+
Skipping datafile lines
To skip the first n lines of a datafile, add an IGNORE n LINES clause to the LOAD DATA
statement. For example, a file might include an initial line of column labels. You can
skip it like this:
mysql> LOAD DATA LOCAL INFILE 'mytbl.txt' INTO TABLE mytbl
-> IGNORE 1 LINES;
mysqlimport supports an --ignore-lines= n option that corresponds to IGNORE n
LINES .