MySQL中字符串函數(shù)詳解 字符串處理在數(shù)據(jù)清洗中的應(yīng)用

mysql中處理字符串的關(guān)鍵函數(shù)有trim、replace、substring和concat。1. trim用于清除字段兩端空格，如update users set name = trim(name)，還可指定清除左或右空格；2. replace用于替換錯(cuò)誤內(nèi)容，如替換電話(huà)號(hào)碼中的分隔符或去掉html標(biāo)簽；3. substring截取特定位置內(nèi)容，left/right提取前幾位或后幾位，適用于身份證號(hào)、url參數(shù)等信息提取；4. concat用于簡(jiǎn)單拼接，concat_ws可加分隔符合并多個(gè)字段，自動(dòng)跳過(guò)NULL值。這些函數(shù)在數(shù)據(jù)清洗中非常實(shí)用，組合使用能解決大部分文本問(wèn)題。

在mysql中處理字符串，尤其是做數(shù)據(jù)清洗時(shí)，掌握幾個(gè)關(guān)鍵的字符串函數(shù)能讓你事半功倍。這些函數(shù)不僅能幫你提取、替換、截取內(nèi)容，還能有效清理臟數(shù)據(jù)，比如空格、非法字符或格式不統(tǒng)一的問(wèn)題。

下面我挑幾個(gè)最常用的字符串函數(shù)，并結(jié)合實(shí)際清洗場(chǎng)景說(shuō)明它們?cè)趺从谩?/p>

1. TRIM：去掉多余空格，清理字段前后“隱形垃圾”

很多文本字段導(dǎo)入數(shù)據(jù)庫(kù)后，前或后會(huì)帶一些看不見(jiàn)的空格，影響查詢(xún)和匹配。這時(shí)候用 TRIM() 函數(shù)就特別合適。

• 基本用法：

  SELECT TRIM('  abc  '); -- 返回 'abc'

• 清洗應(yīng)用：

  • 清除字段兩端空格：

    UPDATE users SET name = TRIM(name);
  • 還可以只清除左邊或右邊：

    SELECT TRIM(LEADING ' ' FROM '  abc');  -- 去掉左邊空格 SELECT TRIM(TRaiLING ' ' FROM 'abc  '); -- 去掉右邊空格

這類(lèi)操作對(duì)用戶(hù)輸入類(lèi)的數(shù)據(jù)特別有用，比如注冊(cè)表單中的用戶(hù)名、郵箱等字段。

2. REPLACE：替換指定字符串，批量修復(fù)錯(cuò)誤

當(dāng)你發(fā)現(xiàn)某個(gè)字段里有錯(cuò)誤內(nèi)容，比如錯(cuò)別字、特殊符號(hào)或者多余的HTML標(biāo)簽，可以用 REPLACE() 批量替換。

• 基本用法：

  SELECT REPLACE('hello world', 'world', 'MySQL'); -- 返回 'hello MySQL'

• 實(shí)際清洗例子：

  • 替換電話(huà)號(hào)碼中的分隔符：

    SELECT REPLACE(phone, '-', '') FROM contacts;
  • 去掉HTML標(biāo)簽（假設(shè)字段存了帶標(biāo)簽的內(nèi)容）：

    UPDATE articles SET content = REPLACE(content, '<br>', '');

注意：這個(gè)函數(shù)是全字段替換，不能正則匹配。如果需要更復(fù)雜的替換，得配合其他工具或函數(shù)。

3. SUBSTRING 和 LEFT/RIGHT：截取部分內(nèi)容，提取關(guān)鍵信息

有時(shí)候我們需要從一段字符串中提取特定位置的內(nèi)容，比如身份證號(hào)里的出生年份、URL中的參數(shù)等。

• 截取中間部分用 SUBSTRING()：

  SELECT SUBSTRING('abcdefg', 3, 2); -- 從第3位開(kāi)始取2個(gè)字符，返回 'cd'

• 截取前幾位或后幾位可以用 LEFT() 或 RIGHT()：

  SELECT LEFT('abcdefg', 3);  -- 返回 'abc' SELECT RIGHT('abcdefg', 3); -- 返回 'efg'

• 實(shí)際應(yīng)用：

  • 提取日期字段中的年份（假設(shè)格式固定）：

    SELECT SUBSTRING(birthday, 1, 4) AS year FROM users;
  • 處理日志URL提取來(lái)源：

    SELECT SUBSTRING(url, 8) FROM logs; -- 去掉開(kāi)頭 http://

使用時(shí)要注意字段長(zhǎng)度是否一致，避免截?cái)喑鲥e(cuò)。

4. CONCAT 和 CONCAT_WS：拼接字符串，整理字段組合

有些時(shí)候我們需要把多個(gè)字段合并成一個(gè)，比如地址字段拆成了省市區(qū)，想拼成完整地址。

• 簡(jiǎn)單拼接用 CONCAT()：

  SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM users;

• 加分隔符更方便的是 CONCAT_WS()：

  SELECT CONCAT_WS(', ', 'Beijing', 'Shanghai', 'Guangzhou'); -- 返回 'Beijing, Shanghai, Guangzhou'

• 數(shù)據(jù)清洗用途：

  • 拼接地址字段：

    SELECT CONCAT_WS(' ', province, city, district, street) AS full_address FROM addresses;

遇到空值也不怕，它會(huì)自動(dòng)跳過(guò)NULL字段，不會(huì)影響整體拼接結(jié)果。

這些字符串函數(shù)在日常數(shù)據(jù)清洗中非常實(shí)用，很多時(shí)候只要簡(jiǎn)單組合一下，就能解決大部分文本問(wèn)題。像 TRIM 去空格、REPLACE 替換錯(cuò)誤、SUBSTRING 提取信息、CONCAT 合并字段，都是經(jīng)常需要用到的操作。

基本上就這些，不復(fù)雜但容易忽略細(xì)節(jié)的地方還是得多練多試。