mysql - 如何从 mysql 中的文件插入到我的表中？

Question

我有一个 file.dat 如下:

"Evan","E","Wallis","222222200","1958-01-16","134 Pelham, Milwaukee, WI","M","92000.00",null,"7"
"Jared","D","James","111111100","1966-10-10","123 Peachtree, Atlanta, GA","M","85000.00",null,"6"

我想将这些值插入到我的表中。

我尝试了以下方法，但没有成功。

LOAD DATA LOCAL INFILE "C:/file.dat" 
INTO TABLE table
;

没有错误，但有警告，并且表未填充数据。

LOAD DATA LOCAL INFILE "C:/file.dat" INTO TABLE table
0 row(s) affected, 64 warning(s):
1265 Data truncated for column 'fname' at row 1
1261 Row 1 doesn't contain data for all columns... 

以下是表的创建方式。

CREATE TABLE table(
  fname    varchar(15) not null, 
  minit    varchar(1),
  lname    varchar(15) not null,
  ssn      char(9),
  bdate    date,
  address  varchar(50),
  sex      char,
  salary   decimal(10,2),
  superssn char(9),
  dno      integer(4),
  primary key (ssn),
  foreign key (superssn) references employee(ssn)
);

Answer 1

默认情况下，MySQL 正在寻找制表符分隔的文件。请尝试以下操作:

LOAD DATA LOCAL INFILE "C:/file.dat" 
INTO TABLE table
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
;