Code: Select all
[root@iceheart-rhel4es ~]# cat /home/mysql/pet.txt
Fluffy Harold cat f 1993-02-04 /N
Ringo Kreshna dog m 1995-08-07 2002-12-10
Goatse Josh goat /N 2000-03-11 /N
Fang Benny dog m 1979-08-31 /N
Browser Firefox dog m 1979-08-31 1995-07-29
Chirpy Gewn bird f 1998-09-11 /N
Whistler Gwen bird /N 1997-12-09 /N
Slim Benny snake m 1996-04-29 /N
The file is tab-separated. However, the number of tabs being used varies between columns. For example, there are two tabs between "Fluffy" and "Harold", but there's only one tabe between "cat" and "f".
Then I loaded the text file into a MySQL table named pet:
Code: Select all
mysql> desc pet
-> ;
+---------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| name | varchar(20) | YES | | NULL | |
| owner | varchar(20) | YES | | NULL | |
| species | varchar(20) | YES | | NULL | |
| sex | char(1) | YES | | NULL | |
| birth | date | YES | | NULL | |
| death | date | YES | | NULL | |
+---------+-------------+------+-----+---------+-------+
6 rows in set (0.00 sec)
mysql> use menagerie
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> load data local infile '/home/mysql/pet.txt' into table pet;
Query OK, 8 rows affected, 24 warnings (0.01 sec)
Records: 8 Deleted: 0 Skipped: 0 Warnings: 24
Judging from above, it seems the loading was successfull.
But when I selected the table, the result is as follows:
Code: Select all
mysql> select * from pet;
+----------+-------+---------+------+------------+------------+
| name | owner | species | sex | birth | death |
+----------+-------+---------+------+------------+------------+
| Fluffy | | Harold | | 0000-00-00 | 0000-00-00 |
| Ringo | | Kreshna | | 0000-00-00 | 0000-00-00 |
| Goatse | | Josh | | 0000-00-00 | 0000-00-00 |
| Fang | | Benny | | 0000-00-00 | 0000-00-00 |
| Browser | | Firefox | | 0000-00-00 | 0000-00-00 |
| Chirpy | | Gewn | | 0000-00-00 | 0000-00-00 |
| Whistler | Gwen | | b | 0000-00-00 | 1997-12-09 |
| Slim | | Benny | | 0000-00-00 | 0000-00-00 |
+----------+-------+---------+------+------------+------------+
8 rows in set (0.00 sec)
Well I wonder why. The operating system, by the way, is RHEL 4 ES, not Windows, so I didn't use LINES TERMINATED BY '\r\n'; option, since IIRC it's for Windows text files.
What happens? Why, for instance, the '1997-12-09' became the death date for Whistler, while in the text file it is the birth date?
I suspect this somehow related to the number of tabs I'm using. For instance, I was using two tabs between pet's name and owner's name (for instance, between 'Fluffy' and 'Harold'). Thus, 'Harold' was placed in species column instead of owner.
However, if that's really the case, then I wonder what to do with a column's content that is longer than tab's length? For example, if the owner's name is 'Harold Graham Kennedy', then I will require more than a single tab for the column, am I correct?