萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MySQL 教學

MySQL 刪除 NULL、空字串與空白字元資料完整指南

核心概念區分

  • NULL:缺失值,佔 0 位元組(含 NULL bitmap),= NULL 永遠為假,必須用 IS NULL。
  • 空字串 '':長度 0 的字串,佔 1 位元組(長度前綴),='' 為真。
  • 空白字元:' '、'\t'、'\n' 等,長度 > 0,肉眼不可見。

刪除 NULL 與空字串

-- 刪除 col 為 NULL 或 '' 的行
DELETE FROM t WHERE col IS NULL OR col = '';

若要同步處理:WHERE IFNULL(col, '') = '' 或 WHERE COALESCE(col, '') = ''。

清洗欄位內的空白字元

1. REPLACE() 移除所有指定字元(如空格)

UPDATE news SET content = REPLACE(content, ' ', '');
-- 僅移除半形空格,全形空格、Tab、換行需分別 REPLACE

若需一次移除多種空白,MySQL 8.0 可用 REGEXP_REPLACE:

UPDATE news SET content = REGEXP_REPLACE(content, '[[:space:]]', '');

2. TRIM() 移除首尾空白

-- 預設移除首尾空格
SELECT TRIM('  phpernote  ');        -- 'phpernote'

-- 指定字元
SELECT TRIM(LEADING 'x' FROM 'xxxabcxxx');   -- 'abcxxx'
SELECT TRIM(BOTH 'x' FROM 'xxxabcxxx');      -- 'abc'
SELECT TRIM(TRAILING 'xyz' FROM 'abcxyz');   -- 'abc'

注意: TRIM 不支援正則,無法移除中間空白。

3. 組合拳:先 TRIM 再 REPLACE

UPDATE t SET col = REPLACE(TRIM(col), ' ', '');

批次更新大表策略

-- 分批提交,避免長事務
SET @batch = 5000;
REPEAT
  UPDATE t SET col = REPLACE(TRIM(col), ' ', '')
  WHERE id > @last_id
  ORDER BY id LIMIT @batch;
  SET @last_id = @last_id + @batch;
  SET @row_count = ROW_COUNT();
UNTIL @row_count = 0 END REPEAT;

或使用 pt-archiver、gh-ost 線上無鎖變更。

防患未然:資料庫層面約束

-- 禁止 NULL
ALTER TABLE t MODIFY col VARCHAR(255) NOT NULL DEFAULT '';

-- 禁止空字串(需觸發器或 CHECK CONSTRAINT,MySQL 8.0.16+)
ALTER TABLE t ADD CONSTRAINT chk_col_not_empty CHECK (col <> '');
copyright © 萬盛學電腦網 all rights reserved