MySQL 字符串截取深度解析_mysql substr

MySQL 字符串截取深度解析

我将全面讲解 MySQL 中字符串截取的核心要点和注意事项：

📌 核心截取函数

1. SUBSTRING() / SUBSTR()

-- 基本语法SELECT SUBSTRING(\'MySQL Tutorial\', 7, 5); -- \'Tutor\'-- 负起始位置（从右向左）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\'

⚠️ 八大关键注意事项

1. 索引从1开始（不是0）

SELECT SUBSTRING(\'ABCDE\', 1, 1); -- \'A\' ✅SELECT SUBSTRING(\'ABCDE\', 0, 1); -- \'\' ❌

2. 多字节字符问题（中文/emoji）

-- UTF8中文字符（每个占3字节）SELECT SUBSTRING(\'中国\', 2, 1); -- 乱码（\'�\'）-- 解决方案：按字符截取SELECT SUBSTRING(\'中国\' FROM 2 FOR 1); -- \'国\' ✅

3. 长度超限自动修正

-- 实际截取长度 = min(指定长度, 剩余长度)SELECT SUBSTRING(\'Short\', 2, 10); -- \'hort\'

4. NULL处理规则

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

5. 负起始位置计算

-- 公式：实际位置 = 字符串长度 + 负起始值 + 1SELECT SUBSTRING(\'Database\', -3, 3); -- \'ase\'（位置6开始）

6. 性能陷阱（大数据量）

-- 避免在WHERE中直接截取（无法使用索引）SELECT * FROM users WHERE SUBSTRING(email, 5, 10) = \'example\';-- 优化方案：使用前缀索引ALTER TABLE users ADD INDEX idx_email_prefix (email(15));SELECT * FROM users WHERE email LIKE \'exam%\';

7. 与CHAR_LENGTH的区别

SELECT LENGTH(\'中国\'), -- 6字节（UTF8） CHAR_LENGTH(\'中国\'), -- 2字符 SUBSTRING(\'中国\', 2, 1); -- 截取第2字节（乱码）

8. 日期/数字转换问题

-- 数字需先转为字符串SELECT SUBSTRING(20230815, 5, 2); -- 错误（数字截取）SELECT SUBSTRING(CAST(20230815 AS CHAR), 5, 2); -- \'08\' ✅

🔧 实用截取技巧

1. 提取文件名

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

2. 邮箱域名提取

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

3. 安全截取URL参数

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

4. 中文字符安全截取

-- 创建多字节安全函数DELIMITER $$CREATE FUNCTION mb_substr(str VARCHAR(255), start INT, len INT)RETURNS VARCHAR(255) DETERMINISTICBEGIN RETURN CONVERT(SUBSTRING(str FROM start FOR len) USING utf8mb4);END$$DELIMITER ;SELECT mb_substr(\'中国加油\', 3, 2); -- \'加油\' ✅

🚀 性能优化方案

1. 索引使用原则

-- 前缀索引创建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. 避免全表扫描

-- 优化前（全表扫描）SELECT * FROM logs WHERE SUBSTRING(message, 10, 5) = \'ERROR\';-- 优化后（使用虚拟列+索引）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. 内存使用监控

-- 监控临时表大小SHOW VARIABLES LIKE \'tmp_table_size\'; -- 默认16MBSET tmp_table_size = 64*1024*1024; -- 增大临时表内存

📊 函数对比表

💡 最佳实践总结

1. 始终验证字符编码：处理多语言数据前执行 SHOW VARIABLES LIKE \'character_set%\'
2. 避免负位置与超长参数：明确业务需求边界
3. 大文本处理优先考虑应用层：减少数据库压力
4. 创建计算列+索引：对频繁截取字段优化
5. 重要数据先备份：执行UPDATE前备份原字段

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