MySQL优化：大且频繁更新表中空字符串转NULL的高效方法

针对大型MySQL表的空字符串转NULL需求，以下几个方案比逐个列写CASE WHEN更高效简洁：

1. 使用NULLIF()函数简化UPDATE语句 #

MySQL内置的NULLIF()函数可以直接将指定值转为NULL，语法是NULLIF(列名, 要替换的值)，比CASE WHEN写法更简洁，执行效率也相当。
示例语句：

UPDATE your_table
SET
  col1 = NULLIF(col1, ''),
  col2 = NULLIF(col2, ''),
  col3 = NULLIF(col3, '')
-- 只处理新增记录，避免全表更新（需提前定义记录创建时间列）
WHERE create_time >= '2024-01-01';

如果需要批量处理大量列，也可以通过脚本（比如Python、Shell）自动生成SET子句，不用手动逐个列编写。

2. 插入阶段直接处理，避免事后UPDATE #

如果数据是从外部加载到表中的，建议在插入环节就完成转换，省去后续的UPDATE操作（大表UPDATE会占用大量IO和锁资源）。
示例插入语句：

INSERT INTO your_table (col1, col2, col3)
SELECT
  NULLIF(source_col1, ''),
  NULLIF(source_col2, ''),
  NULLIF(source_col3, '')
FROM source_table;

3. 用触发器自动处理新增/修改记录 #

创建BEFORE INSERT和BEFORE UPDATE触发器，让数据库自动将空字符串转为NULL，后续新增或修改记录时无需手动处理：

-- 创建INSERT触发器
DELIMITER //
CREATE TRIGGER trg_before_insert_your_table
BEFORE INSERT ON your_table
FOR EACH ROW
BEGIN
  SET NEW.col1 = NULLIF(NEW.col1, '');
  SET NEW.col2 = NULLIF(NEW.col2, '');
  SET NEW.col3 = NULLIF(NEW.col3, '');
END //
DELIMITER ;

-- 创建UPDATE触发器
DELIMITER //
CREATE TRIGGER trg_before_update_your_table
BEFORE UPDATE ON your_table
FOR EACH ROW
BEGIN
  SET NEW.col1 = NULLIF(NEW.col1, '');
  SET NEW.col2 = NULLIF(NEW.col2, '');
  SET NEW.col3 = NULLIF(NEW.col3, '');
END //
DELIMITER ;

注意：批量插入大量数据时，触发器会带来一定性能开销，需要根据实际数据量评估是否适用。

4. 虚拟生成列（查询时自动转换） #

如果不需要修改原表数据，只是查询时需要将空字符串显示为NULL，可以用MySQL 5.7+支持的虚拟生成列：

ALTER TABLE your_table
ADD COLUMN col1_clean VARCHAR(255) GENERATED ALWAYS AS (NULLIF(col1, '')) VIRTUAL,
ADD COLUMN col2_clean VARCHAR(255) GENERATED ALWAYS AS (NULLIF(col2, '')) VIRTUAL;

虚拟列不会占用实际存储，查询时自动计算转换结果，适合只需要在查询层面处理的场景。

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