mySQL 触发器 : Setting empty to NULL, 在最后一列上不起作用

Question

在您的帮助下，我已经成功设置了一个触发器，如果​​本地加载文件中的 4 个主键列中包含空字符串，则该触发器会将其中的 3 个设置为 NULL。

但是问题是它似乎忽略了第四个，并且没有用 NULL 替换空字符串，这允许将空的“性别”列输入到数据库中。

我能看到的唯一区别是“性别”列恰好是 csv 加载文件中的最后一列。有什么想法如何让这个工作吗？也尝试过 elseif，只是通过执行多个 if 语句来更改它。似乎没有任何作用。

触发代码:

BEGIN
  if new.year = '' then
     set new.year = null;
     end if;
  if new.event_id = '' then
     set new.event_id = null;
     end if;
  if new.round = '' then
     set new.round = null;
     end if;
  if new.sex = '' then
     set new.sex = null;
     end if;
END

Answer 1

尝试:

文件:/path/to/file/test_file.csv

2000|1|1|
2001|2|1|M
2002|3|1|F
2003|4|2|

DROP TRIGGER IF EXISTS `bi_trg_test`;
DROP TABLE IF EXISTS `test_table`;

CREATE TABLE `test_table` (
    `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    `year` VARCHAR(4) DEFAULT NULL,
    `event_id` VARCHAR(7) DEFAULT NULL,
    `round` VARCHAR(7) DEFAULT NULL,
    `sex` CHAR(1) DEFAULT NULL,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB;

DELIMITER //

CREATE TRIGGER `bi_trg_test` BEFORE INSERT ON `test_table`
FOR EACH ROW
BEGIN
    IF NEW.`year` = '' THEN
        SET NEW.`year` := NULL;
    END IF;
    IF NEW.`event_id` = '' THEN
        SET NEW.`event_id` := NULL;
    END IF;
    IF NEW.`round` = '' THEN
        SET NEW.`round` := NULL;
    END IF;
    IF NEW.`sex` = '' THEN
        SET NEW.`sex` := NULL;
    END IF;
END//

DELIMITER ;

mysql> LOAD DATA INFILE '/path/to/file/test_file.csv'
    -> INTO TABLE `test_table`
    -> FIELDS TERMINATED BY '|'
    -> (`year`, `event_id`, `round`, `sex`);
Query OK, 4 rows affected (0.00 sec)
Records: 4  Deleted: 0  Skipped: 0  Warnings: 0

mysql> SELECT
    -> `id`,
    -> `year`,
    -> `event_id`,
    -> `round`,
    -> `sex`
    -> FROM
    -> `test_table`;
+----+------+----------+-------+------+
| id | year | event_id | round | sex  |
+----+------+----------+-------+------+
|  1 | 2000 | 1        | 1     | NULL |
|  2 | 2001 | 2        | 1     | M    |
|  3 | 2002 | 3        | 1     | F    |
|  4 | 2003 | 4        | 2     | NULL |
+----+------+----------+-------+------+
4 rows in set (0.00 sec)

不使用触发器的另一个选项:

DROP TRIGGER IF EXISTS `bi_trg_test`;
TRUNCATE TABLE `test_table`;

LOAD DATA INFILE '/path/to/file/test_file.csv'
    INTO TABLE `test_table`
    FIELDS TERMINATED BY '|'
    (@`year`, @`event_id`, @`round`, @`sex`)
    SET 
        `year` = IF(CHAR_LENGTH(TRIM(@`year`)) = 0, NULL, @`year`),
        `event_id` = IF(CHAR_LENGTH(TRIM(@`event_id`)) = 0, NULL, @`event_id`),       
        `round` = IF(CHAR_LENGTH(TRIM(@`round`)) = 0, NULL, @`round`),       
        `sex` = IF(CHAR_LENGTH(TRIM(@`sex`)) = 0, NULL, @`sex`);
Query OK, 4 rows affected (0.00 sec)
Records: 4  Deleted: 0  Skipped: 0  Warnings: 0

mysql> SELECT
    -> `id`,
    -> `year`,
    -> `event_id`,
    -> `round`,
    -> `sex`
    -> FROM
    -> `test_table`;
+----+------+----------+-------+------+
| id | year | event_id | round | sex  |
+----+------+----------+-------+------+
|  1 | 2000 | 1        | 1     | NULL |
|  2 | 2001 | 2        | 1     | M    |
|  3 | 2002 | 3        | 1     | F    |
|  4 | 2003 | 4        | 2     | NULL |
+----+------+----------+-------+------+
4 rows in set (0.00 sec)