sql - MySQL : how to load data with fixed-row format into user variables

Question

我正在尝试加载一个文件，其中所有行都使用相同的规则。 (假设 HEADER 是单行)

HEADER1
HEADER2
.......

但不幸的是，当我尝试使用 LOAD DATA INFILE 语句时出现此错误:错误代码:1409无法将具有固定大小行的文件中的值加载到变量。

这是我写的代码:

USE test;
DROP TABLE IF EXISTS EXAMPLE_H;
CREATE TABLE EXAMPLE_H(
    ID CHAR(20),
    SP CHAR(3),
    IVA CHAR(11) PRIMARY KEY,
    NLP CHAR(6),
    DLP DATE,
    DUVI DATE,
    DELP CHAR(30),
    FILLER CHAR(39),
    VTLP CHAR(3),
    FILL CHAR(49)
);

LOAD DATA INFILE 'BTILSP.TXT' 
    INTO TABLE test.EXAMPLE_H
    FIELDS TERMINATED BY ''
    LINES TERMINATED BY '\n'
    (ID, SP, IVA, NLP, @var_date_one, @var_date_two, DELP, FILLER, VTLP, FILL)
    SET DLP = str_to_date(@var_date_one, '%Y%m%d',
        DUVI = str_to_date(@var_date_two, '%Y%m%d');

我在阅读 this page 的底部时有了这个想法。 (Ramam Pullella 的评论)，我在一些网站上发现了同样的解释，但我不明白为什么我会收到这个错误。

如果我不使用@var_date_one 和@var_date_two 变量以及 STR_TO_DATE 函数，则日期不会按照 MySql 的需要呈现 - 文件中的日期类似于“20100701” - 那么该字段将包含全为零或与我预期的日期不同。如果我将 DLP 和 DUVI 更改为由 CHAR(8) 表示，那么它可以工作，但我不会使用 SQL DATE 比较和类似工具。

你能帮帮我吗？ :)非常感谢。

编辑:

问题似乎是由 LINE TERMINATED BY '' 给出的，因为这种行是“固定行(未定界)”。也许由于未知原因无法将其分配给变量，但它就是这样工作的。文档说:

User variables cannot be used when loading data with fixed-row format because user variables do not have a display width.

有什么建议吗？

重新编辑:我已阅读该页面底部 Ryan Neve 的评论。他给出了一个将固定行读入变量的技巧:

LOAD DATA LOCAL INFILE '<file name>' INTO TABLE <table>
(@var1)
SET Date=str_to_date(SUBSTR(@var1,3,10),'%m/%d/%Y'),
Time=SUBSTR(@var1,14,8),
WindVelocity=SUBSTR(@var1,26,5),
WindDirection=SUBSTR(@var1,33,3),
WindCompass=SUBSTR(@var1,38,3),
WindNorth=SUBSTR(@var1,43,6),
WindEast=SUBSTR(@var1,51,6),
WindSamples=SUBSTR(@var1,61,4);

你觉得这样做好吗？ :)

Answer 1

我不是专家，但在我看来，如果字段以空字符串结尾，那么它们必须是固定大小的；必须有一些方法来确定字段之间的边界，如果没有终止符，那么它们几乎必须是固定大小。

我观察到 MySQL 5.5手册说:

• User variables cannot be used when loading data with fixed-row format because user variables do not have a display width.

它还(在页面的较早部分)说:

• If the FIELDS TERMINATED BY and FIELDS ENCLOSED BY values are both empty (''), a fixed-row (nondelimited) format is used. With fixed-row format, no delimiters are used between fields (but you can still have a line terminator). Instead, column values are read and written using a field width wide enough to hold all values in the field. For TINYINT, SMALLINT, MEDIUMINT, INT, and BIGINT, the field widths are 4, 6, 8, 11, and 20, respectively, no matter what the declared display width is.

由于您的语句没有“FIELDS ENCLOSED BY”和空的“FIELDS ENCLOSED BY”，这就是您具有固定格式的原因。因此你不能随心所欲。

---

有时，在 DBMS 之外处理数据更容易 - 修复数据表示可能就是这样一种操作。我确实有一个我称为 DBLDFMT 的程序，我已经好几年没用过了，但它可以执行各种操作，例如转换带有隐式小数点的十进制数(大型机技巧；价格字段可能是 0023199，代表值(value) 231.99 英镑)。它也可以处理日期操作(不一定使用特别用户友好的表示法，但它能够处理我将数据从大型机获取到 Unix DBMS - 而不是 MySQL 时遇到的问题；当我写这篇文章时它不存在代码。如果有任何兴趣，请与我联系 - 查看我的个人资料。