快捷導(dǎo)航

MySQL字符串截取的核心要點(diǎn)和注意事項(xiàng)

更新時(shí)間：2025年08月19日 09:24:30 作者：步行cgn

這篇文章主要介紹了MySQL字符串截取的核心要點(diǎn)和注意事項(xiàng),substr函數(shù)在數(shù)據(jù)處理中有著廣泛的應(yīng)用,從日志分析、數(shù)據(jù)報(bào)告生成到復(fù)雜的數(shù)據(jù)清洗和處理流程中,substr都能大顯身手,需要的朋友可以參考下

我將全面講解 MySQL 中字符串截取的核心要點(diǎn)和注意事項(xiàng)：

核心截取函數(shù)

1.SUBSTRING()/SUBSTR()

-- 基本語法
SELECT SUBSTRING('MySQL Tutorial', 7, 5); -- 'Tutor'

-- 負(fù)起始位置（從右向左）
SELECT SUBSTRING('Database', -4, 4); -- 'base'

2.LEFT()&RIGHT()

SELECT LEFT('Hello World', 5); -- 'Hello'
SELECT RIGHT('Hello World', 5); -- 'World'

3.SUBSTRING_INDEX()

-- 按分隔符截取
SELECT SUBSTRING_INDEX('www.mysql.com', '.', 2); -- 'www.mysql'

八大關(guān)鍵注意事項(xiàng)

1.索引從1開始（不是0）

SELECT SUBSTRING('ABCDE', 1, 1); -- 'A' ?
SELECT SUBSTRING('ABCDE', 0, 1); -- '' ?

2.多字節(jié)字符問題（中文/emoji）

-- UTF8中文字符（每個(gè)占3字節(jié)）
SELECT SUBSTRING('中國(guó)', 2, 1); -- 亂碼（'?'）

-- 解決方案：按字符截取
SELECT SUBSTRING('中國(guó)' FROM 2 FOR 1); -- '國(guó)' ?

3.長(zhǎng)度超限自動(dòng)修正

-- 實(shí)際截取長(zhǎng)度 = min(指定長(zhǎng)度, 剩余長(zhǎng)度)
SELECT SUBSTRING('Short', 2, 10); -- 'hort'

4.NULL處理規(guī)則

SELECT SUBSTRING(NULL, 1, 3); -- NULL
SELECT SUBSTRING('Text', NULL, 3); -- NULL

5.負(fù)起始位置計(jì)算

-- 公式：實(shí)際位置 = 字符串長(zhǎng)度 + 負(fù)起始值 + 1
SELECT SUBSTRING('Database', -3, 3); -- 'ase'（位置6開始）

6.性能陷阱（大數(shù)據(jù)量）

-- 避免在WHERE中直接截?。o法使用索引）
SELECT * FROM users WHERE SUBSTRING(email, 5, 10) = 'example';

-- 優(yōu)化方案：使用前綴索引
ALTER TABLE users ADD INDEX idx_email_prefix (email(15));
SELECT * FROM users WHERE email LIKE 'exam%';

7.與CHAR_LENGTH的區(qū)別

SELECT 
  LENGTH('中國(guó)'), -- 6字節(jié)（UTF8）
  CHAR_LENGTH('中國(guó)'), -- 2字符
  SUBSTRING('中國(guó)', 2, 1); -- 截取第2字節(jié)（亂碼）

8.日期/數(shù)字轉(zhuǎn)換問題

-- 數(shù)字需先轉(zhuǎn)為字符串
SELECT SUBSTRING(20230815, 5, 2); -- 錯(cuò)誤（數(shù)字截取）
SELECT SUBSTRING(CAST(20230815 AS CHAR), 5, 2); -- '08' ?

實(shí)用截取技巧

1. 提取文件名

SELECT 
  SUBSTRING_INDEX('/path/to/file.txt', '/', -1); -- 'file.txt'

2. 郵箱域名提取

SELECT 
  SUBSTRING_INDEX('user@example.com', '@', -1); -- 'example.com'

3. 安全截取URL參數(shù)

SET @url = 'https://example.com/page?param=value#section';
SELECT
  SUBSTRING_INDEX(SUBSTRING_INDEX(@url, '?', -1), '#', 1); -- 'param=value'

4. 中文字符安全截取

-- 創(chuàng)建多字節(jié)安全函數(shù)
DELIMITER $$
CREATE FUNCTION mb_substr(str VARCHAR(255), start INT, len INT)
RETURNS VARCHAR(255) DETERMINISTIC
BEGIN
  RETURN CONVERT(SUBSTRING(str FROM start FOR len) USING utf8mb4);
END$$
DELIMITER ;

SELECT mb_substr('中國(guó)加油', 3, 2); -- '加油' ?

性能優(yōu)化方案

1.索引使用原則

-- 前綴索引創(chuàng)建
ALTER TABLE products ADD INDEX idx_name_prefix (product_name(10));

-- 有效查詢
SELECT * FROM products WHERE product_name LIKE 'Apple%';

-- 無效查詢（索引失效）
SELECT * FROM products WHERE SUBSTRING(product_name, 1, 5) = 'Apple';

2.避免全表掃描

-- 優(yōu)化前（全表掃描）
SELECT * FROM logs WHERE SUBSTRING(message, 10, 5) = 'ERROR';

-- 優(yōu)化后（使用虛擬列+索引）
ALTER TABLE logs ADD COLUMN error_code VARCHAR(5) 
  AS (SUBSTRING(message, 10, 5)) VIRTUAL;
ALTER TABLE logs ADD INDEX idx_error_code (error_code);

3.內(nèi)存使用監(jiān)控

-- 監(jiān)控臨時(shí)表大小
SHOW VARIABLES LIKE 'tmp_table_size'; -- 默認(rèn)16MB
SET tmp_table_size = 64*1024*1024; -- 增大臨時(shí)表內(nèi)存

函數(shù)對(duì)比表

函數(shù)	特點(diǎn)	適用場(chǎng)景
`SUBSTRING()`	靈活定位	任意位置截取
`LEFT()/RIGHT()`	高效簡(jiǎn)單	首尾固定長(zhǎng)度截取
`SUBSTRING_INDEX()`	分隔符處理	路徑/域名解析
`MID()`	`SUBSTRING`別名	兼容舊代碼

最佳實(shí)踐總結(jié)

始終驗(yàn)證字符編碼：處理多語言數(shù)據(jù)前執(zhí)行 SHOW VARIABLES LIKE 'character_set%'
避免負(fù)位置與超長(zhǎng)參數(shù)：明確業(yè)務(wù)需求邊界
大文本處理優(yōu)先考慮應(yīng)用層：減少數(shù)據(jù)庫壓力
創(chuàng)建計(jì)算列+索引：對(duì)頻繁截取字段優(yōu)化
重要數(shù)據(jù)先備份：執(zhí)行UPDATE前備份原字段

-- 安全更新示例
CREATE TABLE users_backup AS SELECT * FROM users;
UPDATE users 
SET phone_area = SUBSTRING(phone, 1, 3);

總結(jié)

到此這篇關(guān)于MySQL字符串截取的核心要點(diǎn)和注意事項(xiàng)的文章就介紹到這了,更多相關(guān)MySQL字符串截取內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

您可能感興趣的文章:

欧美bbbwbbbw肥妇,免费乱码人妻系列日韩,一级黄片