mysql - 如何使用可选字段加载数据？

Question

是否可以LOAD DATA将csv加载到mysql中，而无需为不存在的空值添加最后的列？

我的所有可选列都在架构末尾排序:

CREATE TABLE `person` (
    id int(20) NOT NULL AUTO_INCREMENT,
    firstname varchar(30) NOT NULL,
    lastname varchar(30) NOT NULL,
    optional1 varchar DEFAULT NULL,
    optional... varchar DEFAULT NULL,
    optional50 varchar DEFAULT NULL,
    PRIMARY KEY (`id`)
) engine=innodb AUTO_INCREMENT=0;

样本.csv:

1;john;doe
2;jabe;doe;;;opt val3;;;;;;opt val9;;;;;;...

重要提示:我不想显式列出 LOAD DATA INFILE sql 语句中的所有列(我知道这可以通过使用 IFNULL 的组合来实现)和@var)。

但是我不能直接加载到表中，告诉 mysql 忽略每行末尾的任何缺失字段吗？

Answer 1

MySQL LOAD DATA syntax 的文档提供以下信息:

By default, when no column list is provided at the end of the LOAD DATA statement, input lines are expected to contain a field for each table column. If you want to load only some of a table's columns, specify a column list.

[...]

If an input line has too few fields, the table columns for which input fields are missing are set to their default values. For numeric types, the column is set to 0.

[...]

An empty field value is interpreted different from a missing field: for string types, the column is set to the empty string.

鉴于您的示例数据:

1;john;doe
2;jabe;doe;;;opt val3;;;;;;opt val9;;;;;;...

id为1的记录会将所有可选列设置为NULL(即默认值)。对于 id 2，可选字符串列将设置为空字符串。 .

我无法判断这是否适合您的用例。如果您确实希望可选列中的值保持一致，可用选项为:

• 输入预处理:使用SET将包含空字符串的列设置为NULL

    LOAD DATA INFILE 'file.txt' INTO TABLE t1
    SET 
        optional1 = NULLIF(optional1, ''), 
        optional2 = NULLIF(optional1, ''), 
        ...

• 在表上设置一个 BEFORE INSERT 触发器，将其设置为 NULL 空值

• 填充表后对表运行更新

    UPDATE t1 SET optional1 = NULLIF(option1, ''), optional2 = NULLIF(optional1, '')
    WHERE '' IN (optional1, optional2, ...)