MySQL使用LOAD DATA INFILE导入数据前批量移除指定关键词方案问询

有两种可落地的方案，可根据你的数据量、禁词表规模选择：

方案1：导入环节逐行实时清洗（适合禁词数量<1000的场景） #

核心是利用MySQL自定义函数+LOAD DATA的SET语法，在导入时直接处理字段内容：

1. 首先创建批量替换禁词的自定义函数：

DELIMITER //
CREATE FUNCTION strip_banned_words(input_str TEXT) 
RETURNS TEXT 
DETERMINISTIC
BEGIN
  DECLARE done INT DEFAULT FALSE;
  DECLARE bad_word TEXT;
  -- 遍历stripoutwords表的所有禁词
  DECLARE cur CURSOR FOR SELECT name FROM stripoutwords;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

  OPEN cur;
  read_loop: LOOP
    FETCH cur INTO bad_word;
    IF done THEN
      LEAVE read_loop;
    END IF;
    -- 替换掉当前禁词
    SET input_str = REPLACE(input_str, bad_word, '');
  END LOOP;
  CLOSE cur;
  RETURN input_str;
END //
DELIMITER ;

2. 修改原有LOAD DATA语句，加入变量接收和字段处理逻辑：

LOAD DATA LOCAL INFILE 'Base.txt' 
INTO TABLE mydata 
CHARACTER SET latin1 
FIELDS TERMINATED BY '\t' 
ENCLOSED BY '"' 
LINES TERMINATED BY '\n' 
IGNORE 1 LINES 
-- 原始内容先赋值给临时变量
(@raw_keywords, fixedprice, quantity, @raw_description, condition1, image1url, merchantcategory, leadtime, Manufacturer, Model_Number, AZ_Code, Volts, Watts, ColorTemp, Shape, Life, Base)
-- 清洗后赋值给正式字段
SET keywords = strip_banned_words(@raw_keywords),
    description = strip_banned_words(@raw_description);

方案2：先导入临时表再批量清洗（适合大文件、禁词数量多的场景） #

该方案对导入速度影响极小，适合GB级以上的大文件导入：

1. 先创建和正式表结构一致的临时表：

CREATE TEMPORARY TABLE temp_mydata LIKE mydata;

2. 把原始数据完整导入临时表，不需要做任何处理：

LOAD DATA LOCAL INFILE 'Base.txt' 
INTO TABLE temp_mydata 
CHARACTER SET latin1 
FIELDS TERMINATED BY '\t' 
ENCLOSED BY '"' 
LINES TERMINATED BY '\n' 
IGNORE 1 LINES;

3. 批量清洗临时表中的两个字段，批量更新比逐行处理效率高很多：

-- 生成批量更新语句一次性执行
SELECT GROUP_CONCAT('UPDATE temp_mydata SET keywords = REPLACE(keywords, ''', name, ''' , ''''), description = REPLACE(description, ''', name, ''' , '''');' SEPARATOR ' ') 
INTO @update_sql 
FROM stripoutwords;

PREPARE stmt FROM @update_sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

4. 把清洗完成的数据写入正式表：

INSERT INTO mydata SELECT * FROM temp_mydata;
-- 完成后删除临时表
DROP TEMPORARY TABLE IF EXISTS temp_mydata;

注意事项 #

• 注意字符集兼容：stripoutwords表的字符集是latin1，如果你的禁词包含中文等特殊字符，建议把该表的字符集改为utf8mb4，避免替换时出现乱码
• 如果禁词需要全词匹配而不是子串匹配，可以把REPLACE函数换成正则替换函数REGEXP_REPLACE（MySQL 8.0+支持），按单词边界匹配即可
• 大文件导入时推荐选择方案2，整体耗时比方案1短30%以上

内容的提问来源于stack exchange，提问作者TSCAmerica.com