Geek Answers Handbook

MySQL 5.6 - load data trapping data (no zeros)

I ran into this problem while working on a project, but here is a simple example that replicates an “error” ...

First create a table with the last column being a kind of varchar:

Create table testtable(
ProductName varchar(100),
Color varchar(50),
CategoryID varchar(5)
);

Create a tab delimited text file with data:

Shirt   Red 1111
Pants   Blue    2222
Shoes   Green   3333

Loading data into a table:

load data infile 'testtable.txt' into table testtable;

I do not receive any errors or warnings or anything else. But, when I try to request data, it looks rather strange:

select * from testtable;
    ProductName Color   CategoryID
                Red     1111
                Blue    2222
        Shoes   Green   3333

Requests that will connect to ProductName will not work.

Then create a table with int as the last column:

Create table fixedtable(
ProductName varchar(100),
Color varchar(50),
CategoryID varchar(5),
Inventory int
);

Create a tab delimited text file with data:

Shirt   Red 1111    10
Pants   Blue    2222    15
Shoes   Green   3333    20

Loading data into a table:

load data infile 'fixedtable.txt' into table fixedtable;

And now the table looks fine:

    select * from fixedtable;
ProductName Color   CategoryID  Inventory
    Shirt   Red     1111        10
    Pants   Blue    2222        15
    Shoes   Green   3333        20

I have not tried repeating this using "Insert Into" or something like that; For this project, I specifically must use the "load data infile" statement.

, ... , - ? Windows 7.

- ? , ?

+4
1

. , , MySQL "" , , .

, - , , \r\n , \n. LOAD DATA INFILE , , \n. - http://dev.mysql.com/doc/refman/5.6/en/load-data.html " FIELDS LINES".

, LINES TERMINATED BY "\ r\n". :

mysql> Create table testtable(
-> ProductName varchar(100),
-> Color varchar(50),
-> CategoryID varchar(5)
-> );
Query OK, 0 rows affected (0.12 sec)

mysql> load data infile 'testtable.txt' into table testtable;
Query OK, 3 rows affected (0.01 sec)
Records: 3  Deleted: 0  Skipped: 0  Warnings: 0

mysql> SELECT * FROM testtable;
+-------------+-------+------------+
| ProductName | Color | CategoryID |
+-------------+-------+------------+
  |       | Red   | 1111
  |       | Blue  | 2222
| Shoes       | Green | 3333       |
+-------------+-------+------------+
3 rows in set (0.00 sec)

mysql> DELETE FROM testtable;
Query OK, 3 rows affected (0.02 sec)

mysql> load data infile 'testtable.txt' into table testtable
    -> LINES TERMINATED BY '\r\n';
Query OK, 3 rows affected (0.01 sec)
Records: 3  Deleted: 0  Skipped: 0  Warnings: 0

mysql> SELECT * FROM testtable;
+-------------+-------+------------+
| ProductName | Color | CategoryID |
+-------------+-------+------------+
| Shirt       | Red   | 1111       |
| Pants       | Blue  | 2222       |
| Shoes       | Green | 3333       |
+-------------+-------+------------+
3 rows in set (0.00 sec)

, SQL:

load data infile 'testtable.txt' into table testtable LINES TERMINATED BY '\r\n';
+1

More articles:


All Articles