Kettle/PDI中如何将Excel导入MySQL的300列里的'-'替换为null或空值

基础说明 #

完全不用逐列手动配置300个字段的替换规则，所有实现方式都靠全局处理能力完成，操作300列和3列的成本没有区别，下面是几个可直接上手的方案： #

方案1：MySQL原生能力实现，无额外工具依赖 #

正常把Excel数据导入目标表后，靠MySQL自带的INFORMATION_SCHEMA元数据表自动生成覆盖全字段的更新语句，哪怕上千列都不用手动列字段名：

• 如果是要把**单元格值完全等于-**的内容替换为NULL，直接执行下面的查询，自动生成所有需要跑的UPDATE语句：

  SELECT GROUP_CONCAT(
    CONCAT('UPDATE `你的目标表名` SET `', COLUMN_NAME, '` = NULL WHERE `', COLUMN_NAME, '` = \'-\'')
  SEPARATOR ';\n') AS 批量更新SQL
  FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_NAME = '你的目标表名' AND TABLE_SCHEMA = '你的数据库名';
  

  把查询出来的SQL复制出来执行一遍，就能遍历全表300个字段完成替换。
• 如果要替换为空字符串，只要把上面语句里的= NULL改成= ''即可。
• 如果是需要替换字段值内部嵌套的-（比如把订单-001改成订单001），就把生成SQL的逻辑改成带REPLACE的版本，同样自动遍历所有字段：

  SELECT GROUP_CONCAT(
    CONCAT('UPDATE `你的目标表名` SET `', COLUMN_NAME, '` = REPLACE(`', COLUMN_NAME, '`, \'-\', \'\') WHERE `', COLUMN_NAME, '` LIKE \'%-%\'')
  SEPARATOR ';\n') AS 批量嵌套替换SQL
  FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_NAME = '你的目标表名' AND TABLE_SCHEMA = '你的数据库名';
  

方案2：导入环节直接处理，减少二次数据读写 #

适合百万行以上的大数据量场景，不用导完再跑一遍全表扫描更新，节省IO时间：

• 用Navicat、DBeaver这类图形化导入工具时，找配置页的全局值映射选项，直接加一条规则：匹配单元格值为-的，统一替换为NULL或者空字符串，规则会自动应用到所有选中的导入列，不需要逐列做转换配置。
• 用Python脚本做自动化导入的话，核心逻辑只用一行全局替换，和列数完全无关：

  import pandas as pd
  from sqlalchemy import create_engine
  # 读取Excel，全局把所有单元格值为'-'的内容替换为None（对应MySQL的NULL）
  df = pd.read_excel("你的Excel文件路径.xlsx").replace('-', None)
  # 直接写入MySQL
  engine = create_engine("mysql+pymysql://你的账号:你的密码@数据库地址:端口/库名")
  df.to_sql(name="你的目标表名", con=engine, if_exists="append", index=False)
  

实操注意事项 #

• 除非确定所有字段都不会合法包含-（比如订单编号、手机号、证件号这类本身可能带短横线的字段），否则不要做全值嵌套替换，只处理单元格完全等于-的无效值，避免误改合法数据。
• 正式执行批量更新前，先在测试环境跑一遍，统计下待处理的行数，确认范围和预期一致再操作生产库。

内容的提问来源于stack exchange，提问作者Elsa