用Mysql LOAD DATA导入数据时，是否要求源文本字段数量和表中列字段的一样多？

数据库表字段定义：
CREATE TABLE `TABLE_NAME` (
  `f1`         varchar(25)  default NULL,
  `f2`         varchar(20)  default NULL,
  `f3`         double(15,0) default -1,
  `f4`         varchar(20)  default NULL,
  `f5`         double(15,3) default NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

源文件：
example_data1.txt数据内容:
abc;cdef;;  

[注：这里数据只有4个字段，而表中是有5个字段的]

执行如下LOAD DATA命令导入数据：

load data infile 'D:\\my_program\\Tek_sig\\example_data1.txt'
into table TABLE_NAME
FIELDS TERMINATED BY ';'

系统报错：
Data truncated for column 'f3' at row 1
可能是由于字段定义为double型，而文本文件中是空值，将文本文件更新为：
abc;cdef;2;

再次导入，报错如下：
Row 1 doesn't contain data for all columns

分析原因可能是由于表中字段一共是5个，而在文本数据源中有了4个字段，少1个字段。
我要问的问题是：
1.LOAD DATA 要求源文件中的字段与数据库表中的字段数量必须完全一样多吗？否则肯定不能导入吗？
2.在数据库里，表的定义中允许有空值的字段，例如`f3`  double(15,0) default -1,而在数据源文件中此字段没有数值，如“abc;cdef;;”，即f3是空的，用两个分号分隔，系统为何报错：

Data truncated for column 'f3' at row 1 ，

我理解系统应当按空值处理，即在数据库中这个字段default值为“-1”， 但实际上不是这样的，根本无法导入！这一点我觉得MYSQL没有微软的ACCESS的导入功能强大，ACCESS可以允许数据源中缺少最后几个连续的字段，即ACCESS允许被导入的源文件比目标数据库表中的字段数量少，而且在导入数据的过程中ACCESS会用DEFAULT空值来处理那些缺少的字段，即全部用空值或DEFAULT值来填充数据库中表的内容,很方便的。 但MYSQL在导入数据方面，要求的相当严格，字段数量、数据格式要求的相当严格，完全匹配。不知道我的理解是否正确？

再有，我要强调的是：

我设计的数据分析算法决定了必须要在源数据中多加入一些辅助字段，在后期处理时程序会更新这些辅助字段，所以字段数量不一致的问题是客观存在的，以前我用ACCESS处理数据没有发现这个问题，但ACCESS处理的数据量有限，最大2G，所以我现在想改用MYSQL处理数据，但是遇到了海量数据无法顺利导入的问题。
我不想用代码一条一条的INSERT数据，因为我要处理的数据量太大了，这样会相当慢的！还是想用工具一次导入，如果LOAD DATA 不能支持，那有没有其它相关的工具或手段？

多谢各位的指教。

5 个解决方案

#1

引用

用Mysql LOAD DATA导入数据时，是否要求源文本字段数量和表中列字段的一样多

没有细看你的内容，仅回答这个标题上的问题如下。

不需要一样多。

引用

默认情况下，如果在LOAD DATA INFILE语句的末尾处没有设列清单时，则输入行预计会包含一个字段，用于表中的每个列。如果您只想载入一个表的部分列，则应指定一个列清单：

mysql> LOAD DATA INFILE 'persondata.txt'
    ->           INTO TABLE persondata (col1,col2,...);
如果输入文件中各字段的顺序与表中各列的顺序不同，您也必须指定一个列清单。否则，MySQL不能把输入字段和表中的列匹配起来。

列清单可以包含列名称或用户变量。支持SET子句。这使您可以把输入值赋予用户变量，然后在把结果赋予列之前，对这些值进行变换。

SET子句中的用户变量可以采用多种方式使用。以下例子使用数据文件中的第一列，直接用于t1.column1的值。在用户变量被用于t2.column2值之前，把第二列赋予用户变量。该变量从属于一个分割运行。

LOAD DATA INFILE 'file.txt'
  INTO TABLE t1
  (column1, @var1)
  SET column2 = @var1/100;
SET子句可以被用于提供不是来源于输入文件的值。以下语句把column3设置为当前的日期和时间：

LOAD DATA INFILE 'file.txt'
  INTO TABLE t1
  (column1, column2)
  SET column3 = CURRENT_TIMESTAMP;
您也可以通过把输入值赋予一个用户变量，同时不把变量赋予表中的列，来丢弃此输入值：

LOAD DATA INFILE 'file.txt'
  INTO TABLE t1
  (column1, @dummy, column2, @dummy, column3);
列/变量清单和SET子句的使用受到以下限定：

·         在SET子句中的赋值应只含有位于赋值操作符的左侧的列名称。

·         您可以在SET赋值的右侧使用子查询。如果子查询可以返回一个值，并且此值将被赋予到一个列中，则此子查询只能是标量子查询。另外，您不能使用子查询从一个正在被载入的表中选择。

·         对于于列/变量清单或SET子句，被IGNORE子句忽略的行不被处理。

·         当载入采用固定行格式的数据时，不能使用用户变量，因为用户变量没有显示宽度。

当处理一个输入行时，LOAD DATA会依据列/变量清单和SET子句，把行拆分成字段，并使用值。然后，得到的行被插入表中。如果有用于表的BEFORE INSERT或AFTER INSERT触发器，则在插入行之前和插入行之后分别启动触发器。

如果一个输入行含有过多的字段，则多余的字段被忽略，并且警告的数量增加。

如果一个输入行含有的字段过少，则输入字段缺失的表中的列被设置为默认值。默认值赋值在13.1.5节，“CREATE TABLE语法”中进行了说明。

如果字段值缺失，则对一个空字段值会被按不同方式理解：

·         对于字符串类型，列被设置为空字符串。

·         对于数字类型，列被设置为0。

·         对于日期和时间类型，列被设置为该类型相应的“zero”。请参见11.3节，“日期和时间类型”。

如果您明确地把一个空字符串赋予一个INSERT或UPDATE语句中的字符串类型、数字类型或日期或时间类型，则产生的这些值相同。

只有在两种情况下TIMESTAMP列被设置为当前日期和时间。一种情况时当列有一个NULL值（也就是\N）时；另一种情况是（仅对于第一个TIMESTAMP列），当一个字段清单被指定时，TIMESTAMP列会从字段清单中被略去。

#2

按上面的回复内容，我试了一下，带列字段还是不能解决此问题：

数据库表字段定义：
CREATE TABLE `TABLE_NAME` (
  `f1`         varchar(25)  default NULL,
  `f2`         varchar(20)  default NULL,
  `f3`         double(15,0) default -1,
  `f4`         varchar(20)  default NULL,
  `f5`         double(15,3) default NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

源文件内容：【文件名：example_data1.txt】
cat;red;2.0;tree

导入命令：

load data infile 'D:\\my_program\\Tek_sig\\example_data1.txt'
into table TABLE_NAME (f1,f2,f3,f4);

报错：
Row 1 doesn't contain data for all columns

数据还是导不进去。

#3

cat ;red;2.0;tree
你的文件的字段分隔符是；不是默认的逗号，
你需要注明 

#4

我又仔细研究了一下LOAD DATA的说明文档，最终搞明白了，您说的是对的。

正确的命令是：
load data infile 'D:\\my_program\\Tek_sig\\example_data1.txt'
into table TABLE_NAME 
FIELDS TERMINATED BY ';'
LINES TERMINATED BY '\n'
(f1,f2,f3,f4)

结果OK了：
Query OK, 1 rows affected (210 ms)

非常感谢！！

我的数据文件各字段之间是用分号分隔的。
附LOAD DATA语法说明：
7.16 LOAD DATA INFILE句法
LOAD DATA [LOW_PRIORITY] [LOCAL] INFILE 'file_name.txt' [REPLACE | IGNORE]
    INTO TABLE tbl_name
    [FIELDS
        [TERMINATED BY '\t']
        [OPTIONALLY] ENCLOSED BY '']
        [ESCAPED BY '\\' ]]
    [LINES TERMINATED BY '\n']
    [IGNORE number LINES]
    [(col_name,...)]

#5

字段数不一致导入问题解决了，但是又发现一个新的问题：

数据库表字段定义：
CREATE TABLE `TABLE_NAME` (
  `f1`         varchar(25)  default NULL,
  `f2`         varchar(20)  default NULL,
  `f3`         double(15,0) default -1,
  `f4`         varchar(20)  default NULL,
  `f5`         double(15,3) default NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

源文件内容：【文件名：example_data1.txt】
cat;red;2.0;tree

可以成功导入，没有任何报错：
load data infile 'D:\\my_program\\Tek_sig\\example_data1.txt'
into table TABLE_NAME  
FIELDS TERMINATED BY ';'
LINES TERMINATED BY '\n'
(f1,f2,f3,f4)

结果OK：
Query OK, 1 rows affected (210 ms)


但是将源文件内容改为：

cat;red;;tree   【即将‘2.0’数值删除，即f3字段为空了】

则系统报错：
Data truncated for column 'f3' at row 1

这是为什么？我在数据库表的字段“f3”的定义中不是可以为空吗（`f3` double(15,0) default -1,）？而且DEFAULT值为-1,难道不起作用吗？我想实现的效果是，如果在源文件中的f3字段没有值，则导入数据后f3字段的内空应为“-1”。

谁能帮我分析一下，能否实现我的这一想法？

再次感谢！