MySQL LOAD DATA INFILE存储行号 [英] MySQL LOAD DATA INFILE store line number

问题描述

我正在使用MySQL LOAD DATA INFILE将CSV文件数据移动到数据库表中. 我正在寻找一种在导入的记录中引用原始文件行号(行)的方法.

I'm using MySQL LOAD DATA INFILE to move CSV file data into a database table. I'm looking for a way to reference the original file line number (row) in the imported record.

这样一个表:

CREATE TABLE tableName (
  uniqueId INT UNSIGNED NOT NULL PRIMARY_KEY,
  import_file VARCHAR(100),
  import_line INT,
  import_date = DATETIME,
  fieldA VARCHAR(100),
  fieldB VARCHAR(100),
  fieldC VARCHAR(100)
);

其中import_file，import_line和import数据是与特定文件导入相关的元数据. fieldA，fieldB和fieldC代表文件中的实际数据.

Where import_file,import_line and import data are meta data relevant to the specific file import. fieldA, fieldB and fieldC represent the actual data in the file.

将通过这样的查询进行更新:

Would be updated by a query like this:

LOAD DATA INFILE '$file' 
REPLACE
INTO TABLE '$tableName' 
FIELDS TERMINATED BY ',' ENCLOSED BY '\"'
LINES TERMINATES BY '\n'
IGNORE 1 LINES # first row is column headers
(fieldA,fieldB,fieldC)
SET import_date = now(), 
import_file = '" . addslashes($file) . "', 
import_line = '???';

是否可以将'import_line'设置为变量?

谢谢

-M

推荐答案

您可以先设置用户变量，然后在SET子句中将该变量递增，即

You can do that by setting a user variable first, and increment this variable in your SET clause, i.e.

SET @a:=0;                                       -- initialize the line count
LOAD DATA INFILE 'c:/tools/import.csv'           -- my test import 
REPLACE
INTO TABLE tableName 
FIELDS TERMINATED BY ',' ENCLOSED BY '\"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES # first row is column headers
(fieldA,fieldB,fieldC)
SET import_date = now(), 
import_file = 'import.csv',               
import_line = @a:=@a+1;                          -- save the incremented line count

这篇关于MySQL LOAD DATA INFILE存储行号的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！