mysql - 在 MySQL 中使用 LOAD DATA INFILE 过滤 null 或空输入

Question

我有一些非常大的文件(数百万条记录)需要加载到数据库中。它们的形式是:

word1\tblahblahblah
word2\tblahblah
word3\tblahblah
word4
word5\tblahblah
...

我的问题是，我想忽略没有第二条记录的行('blahblah's')，例如 word4。

我目前正在使用以下查询来加载文件:

LOAD DATA LOCAL INFILE 'file' 
IGNORE INTO TABLE tablename 
COLUMNS TERMINATED BY '\t' 
LINES TERMINATED BY '\n'
(col1, col2);

这具有我想要的功能，除了它仍然接受空值。有没有办法跳过 word4 类型行而不将它们添加到数据库中？

凭直觉，我认为使用 WHEN 或 WHERE 子句是完美的，但通过在线查看文档和其他信息，我找不到任何可行的示例。我是必须事先过滤掉这些记录，还是可以在此查询中过滤掉这些记录？

非常感谢任何帮助!

Answer 1

我会通过使用 grep 或 awk 过滤文件然后将其通过管道传输到 MySQL(通过 /dev/stdin)来完成此操作。是这样的:

cat your_file.txt |
  awk '/\t.+/' |
    mysql -u your_username -pyour_password \
      -e "LOAD DATA LOCAL INFILE '/dev/stdin' \
          IGNORE INTO TABLE tablename         \
          COLUMNS TERMINATED BY '\t'          \
          LINES TERMINATED BY '\n'            \
          (col1, col2);" \
      your_database_name

第二行提供给 awk 的正则表达式只匹配任何具有制表符后跟一个或多个任意字符的行。您可能希望对其进行调整以满足您的需求。

编辑:我想到了另一种可能性。您可以使用 SET 在空白列上设置一些魔法值，并在表上放置一个 BEFORE INSERT 触发器，当它看到该值时将在一行上退出。我对触发器没有太多经验，但我认为这样的事情应该有效:

CREATE TRIGGER skip_magic_rows
  BEFORE INSERT ON tablename
  FOR EACH ROW
  BEGIN
    IF NEW.col2 = 'IDSPISPOPD4815162342' THEN  # Some unlikely magic string
      # Trigger an error, which will cause the INSERT to fail†

      # If you have MySQL < 5.5 this is kludgy -- see Note 1
      DROP TABLE `Skipped row`

      # OR

      # In MySQL >= 5.5 you can send a signal--'45000' is a generic error
      SIGNAL SQLSTATE '45000' SET message_text = 'Skipped row';  # See Note 2

    END IF
  END
;

†: Per the docs :

An error during either a BEFORE or AFTER trigger results in failure of the entire statement that caused trigger invocation.

然后...

LOAD DATA LOCAL INFILE 'file' 
  IGNORE INTO TABLE tablename 
  COLUMNS TERMINATED BY '\t' 
  LINES TERMINATED BY '\n'
  (col1, @var2)
  SET col2 = IF(@var2 IN (NULL, ''), 'IDSPISPOPD4815162342', @var2)
;

希望对您有所帮助!

注1: Relevant blog post and comments
注2: Relevant SO thread