将数据加载到mysql加载数据INFILE困难

Question

我正在执行以下查询，以便将数据加载到MySQL中。

LOAD DATA LOCAL INFILE '/Users/ramjiseetharaman/Desktop/UTA/CSE\ 5330\ -\ Database\ Systems/Projects/Project\ 1/DEPARTMENT.txt'
INTO TABLE DEPARTMENT
FIELDS TERMINATED BY ','
ENCLOSED BY "'"
LINES TERMINATED BY "\n";

我收到了以下警告

| Level   | Code | Message                                                               |
+---------+------+-----------------------------------------------------------------------+
| Warning | 1366 | Incorrect integer value: ' '333445555'' for column 'Mgr_ssn' at row 1 |
| Warning | 1366 | Incorrect integer value: ' '987654321'' for column 'Mgr_ssn' at row 2 |
| Warning | 1366 | Incorrect integer value: ' '888665555'' for column 'Mgr_ssn' at row 3 |
| Warning | 1366 | Incorrect integer value: ' '111111100'' for column 'Mgr_ssn' at row 4 |
| Warning | 1366 | Incorrect integer value: ' '444444400'' for column 'Mgr_ssn' at row 5 |
| Warning | 1366 | Incorrect integer value: ' '555555500'' for column 'Mgr_ssn' at row 6 |
| Warning | 1366 | Incorrect integer value: ' '112244668'' for column 'Mgr_ssn' at row 7 |
| Warning | 1366 | Incorrect integer value: ' '110110110'' for column 'Mgr_ssn' at row 8 |
| Warning | 1366 | Incorrect integer value: ' '913323708'' for column 'Mgr_ssn' at row 9 |
+---------+------+-----------------------------------------------------------------------+

我想加载以下数据：

'Research', 5, '333445555', '22-MAY-1978'
'Administration', 4, '987654321', '01-JAN-1985'
'Headquarters', 1, '888665555', '19-JUN-1971'
'Software', 6, '111111100', '15-MAY-1999'
'Hardware', 7, '444444400', '15-MAY-1998'
'Sales', 8, '555555500', '01-JAN-1997'
'HR', 9, '112244668', '01-FEB-1989'
'Networking', 3, '110110110', '15-MAY-2009'
'QA', 11, '913323708', '2-FEB-2010'

我在查询中遗漏了什么？为什么Manager_Ssn不加载？任何帮助都是非常感谢的。

Answer 1

更改：

• FIELDS TERMINATED BY ','，FIELDS TERMINATED BY ', '.
• 格式化Mgr_start_date列。

尝试：

文件：/path/to/file/DEPARTMENT.txt

'Research', 5, '333445555', '22-MAY-1978'
'Administration', 4, '987654321', '01-JAN-1985'
'Headquarters', 1, '888665555', '19-JUN-1971'
'Software', 6, '111111100', '15-MAY-1999'
'Hardware', 7, '444444400', '15-MAY-1998'
'Sales', 8, '555555500', '01-JAN-1997'
'HR', 9, '112244668', '01-FEB-1989'
'Networking', 3, '110110110', '15-MAY-2009'
'QA', 11, '913323708', '2-FEB-2010'

MySQL命令行：

mysql> DROP TABLE IF EXISTS `DEPARTMENT`;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE IF NOT EXISTS `DEPARTMENT` (
    ->   `Dname` VARCHAR(255),
    ->   `Dnumber` TINYINT(4),
    ->   `Mgr_ssn` BIGINT(20),
    ->   `Mgr_start_date` DATE
    -> );
Query OK, 0 rows affected (0.00 sec)

mysql> LOAD DATA LOCAL INFILE '/path/to/file/DEPARTMENT.txt'
    ->   INTO TABLE DEPARTMENT
    ->   FIELDS TERMINATED BY ', '
    ->   ENCLOSED BY "'"
    ->   LINES TERMINATED BY "\n"
    ->   (`Dname`, `Dnumber`, `Mgr_ssn`, @`Mgr_start_date`)
    ->   SET `Mgr_start_date` = STR_TO_DATE(@`Mgr_start_date`, '%d-%M-%Y');
Query OK, 9 rows affected (0.00 sec)
Records: 9  Deleted: 0  Skipped: 0  Warnings: 0

mysql> SELECT
    ->   `Dname`,
    ->   `Dnumber`,
    ->   `Mgr_ssn`,
    ->   `Mgr_start_date`
    -> FROM
    ->   `DEPARTMENT`;
+----------------+---------+-----------+----------------+
| Dname          | Dnumber | Mgr_ssn   | Mgr_start_date |
+----------------+---------+-----------+----------------+
| Research       |       5 | 333445555 | 1978-05-22     |
| Administration |       4 | 987654321 | 1985-01-01     |
| Headquarters   |       1 | 888665555 | 1971-06-19     |
| Software       |       6 | 111111100 | 1999-05-15     |
| Hardware       |       7 | 444444400 | 1998-05-15     |
| Sales          |       8 | 555555500 | 1997-01-01     |
| HR             |       9 | 112244668 | 1989-02-01     |
| Networking     |       3 | 110110110 | 2009-05-15     |
| QA             |      11 | 913323708 | 2010-02-02     |
+----------------+---------+-----------+----------------+
9 rows in set (0.00 sec)