MySQL Select 列搞砸了

Question

问题来了。

首先，我在MySQL中创建了一个数据库

CREATE TABLE Users (User TEXT, Pass TEXT, ID TEXT, RegDate TEXT, Email TEXT, Rank TEXT);

我现在有这个数据库

+-----------+------------------+------------+----------+-------------------+------------+
| User      | Pass             | ID         | RegDate  | Email             | Rank       |
+-----------+------------------+------------+----------+-------------------+------------+

我导入了一个 csv 文件 (Users.csv) (UTF-8, PlainText)

load data local infile 'Users.csv' into table Users fields terminated by ',' enclosed by '"' lines terminated by '\n';

该文件的内容是

arinerron,asdfasdf,USR0000001,92082015,arinesaugmail,Owner
looy,asdfasdf,USR0000002,92082015,tjesaugmail,User
mooncat39,marsisawesome,USR0000003,92082015,harveyfamilygmail,Owner
Goldy,foxythepirate777,USR0000004,92082015,goldyftwgmail,User
Reid,reidyreid,USR0000005,92082015,reidgmail,Moderator
Bo,bobobobo,USR0000006,92082015,bobogmail,Moderator

它的 react 是

Query OK, 6 rows affected (0.00 sec)
Records: 6  Deleted: 0  Skipped: 0  Warnings: 0

我运行了命令

SELECT * from Users

我得到了这个回复

+-----------+------------------+------------+----------+-------------------+------------+
| User      | Pass             | ID         | RegDate  | Email             | Rank       |
+-----------+------------------+------------+----------+-------------------+------------+
     |erron | asdfasdf         | USR0000001 | 92082015 | arinesaugmail     | Owner
      |     | asdfasdf         | USR0000002 | 92082015 | tjesaugmail       | User
     |cat39 | marsisawesome    | USR0000003 | 92082015 | harveyfamilygmail | Owner
      |     | foxythepirate777 | USR0000004 | 92082015 | goldyftwgmail     | User
 |Reid      | reidyreid        | USR0000005 | 92082015 | reidgmail         | Moderator
| Bo        | bobobobo         | USR0000006 | 92082015 | bobogmail         | Moderator  |
+-----------+------------------+------------+----------+-------------------+------------+

它有什么问题？为什么格式不正确？排名列也乱七八糟

我一遍又一遍地重试这些步骤，但没有成功。我真的需要尽快解决这个问题。非常感谢!

Answer 1

您的文件有 Windows CR-LF 行结尾，而不是 Unix LF 行结尾。所以 Rank字段的末尾都有一个 CR 字符。所以发生的事情是最后一个字段被打印为:

Owner<cr><spaces>|

<cr>返回到同一行的开头，然后打印几个空格和 | .这会覆盖 User 的开头字段。

使用

LINES TERMINATED BY '\r\n'

或使用 dos2unix在加载文件之前从文件中删除所有 CR 字符。