MySQL 全角轉半角完整指南：從數據清洗到通用存儲函數

MySQL 全角轉半角完整指南：從數據清洗到通用存儲函數

用戶註冊時誤輸入全角字符（如全角手機號），導致短信發送失敗。本文提供從問題排查、數據清洗到通用轉換函數的完整解決方案。

場景還原與數據清洗步驟

1. 查找混雜數據：使用嵌套 REPLACE 將全角數字轉半角，分組統計重複記錄。
2. 刪除衝突記錄：先刪除已存在半角版本的全角記錄，避免轉換後主鍵/唯一鍵衝突。
3. 批量轉換更新：對 MOBILE 與 USER_NAME 字段同步執行 10 層 REPLACE 替換全角數字 ０-９ 為 0-9。

通用轉換存儲函數

針對上述硬編碼方式不靈活，封裝通用函數 func_convert(str, flag)：

• flag=0：全角→半角（Unicode -65248，匹配範圍 ！-～）。
• flag=1：半角→全角（Unicode +65248，匹配範圍 !-~）。
• 特殊處理全角空格 　（U+3000）↔ 半角空格 （U+0020）。
• 循環遍歷字符串，逐字符轉換，返回結果。

DELIMITER $$
CREATE FUNCTION `func_convert`(p_str VARCHAR(200), flag INT)
RETURNS VARCHAR(200)
BEGIN
  DECLARE pat VARCHAR(8);
  DECLARE step INT;
  DECLARE i INT;
  DECLARE str VARCHAR(200);
  SET str = p_str;
  IF flag = 0 THEN
    SET pat = N'%[！-～]%';
    SET step = -65248;
    SET str = REPLACE(str, N'　', N' ');
  ELSE
    SET pat = N'%[!-~]%';
    SET step = 65248;
    SET str = REPLACE(str, N' ', N'　');
  END IF;
  SET i = LOCATE(pat, str);
  WHILE i > 0 DO
    SET str = REPLACE(str, SUBSTRING(str, i, 1), CHAR(UNICODE(SUBSTRING(str, i, 1)) + step));
    SET i = LOCATE(pat, str);
  END WHILE;
  RETURN str;
END $$
DELIMITER ;

SQL Server 版本參考

文中亦提供 T-SQL 版 u_convert，使用 PATINDEX + COLLATE LATIN1_GENERAL_BIN 實現二進制匹配，邏輯一致。

最佳實踐建議

• 業務層輸入驗證優先，攔截全角輸入。
• ETL/數據入庫階段統一調用轉換函數。
• 定期掃描核心業務表，防止臟數據積累。

掌握 Unicode 碼位偏移規律（基本拉丁字母與全角形式相差 65248），即可靈活擴展至其他字符集轉換場景。