MySQL字符串截取的核心要点和注意事项

这篇文章主要介绍了MySQL字符串截取的核心要点和注意事项,substr函数在数据处理中有着广泛的应用,从日志分析、数据报告生成到复杂的数据清洗和处理流程中,substr都能大显身手,需要的朋友可以参考下

目录

• 核心截取函数
• 1.SUBSTRING()/SUBSTR()
• 2.LEFT()&RIGHT()
• 3.SUBSTRING_INDEX()
• 八大关键注意事项
• 1.索引从1开始（不是0）
• 2.多字节字符问题（中文/emoji）
• 3.长度超限自动修正
• 4.NULL处理规则
• 5.负起始位置计算
• 6.性能陷阱（大数据量）
• 7.与CHAR_LENGTH的区别
• 8.日期/数字转换问题
• 实用截取技巧
• 1. 提取文件名
• 2. 邮箱域名提取
• 3. 安全截取URL参数
• 4. 中文字符安全截取
• 性能优化方案
• 1.索引使用原则
• 2.避免全表扫描
• 3.内存使用监控
• 函数对比表
• 最佳实践总结
• 总结 

我将全面讲解 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); -- NULL
SELECT SUBSTRING('Text', NULL, 3); -- NULL

5.负起始位置计算

-- 公式：实际位置 = 字符串长度 + 负起始值 + 1
SELECT 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) DETERMINISTIC
BEGIN
  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'; -- 默认16MB
SET 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);

总结 

到此这篇关于MySQL字符串截取的核心要点和注意事项的文章就介绍到这了,更多相关MySQL字符串截取内容请搜索骃骐网以前的文章或继续浏览下面的相关文章希望大家以后多多支持骃骐网！

您可能感兴趣的文章:

• MySQL截取和拆分字符串函数用法示例
• Mysql字符串截取及获取指定字符串中的数据
• MySQL 字符串拆分操作(含分隔符的字符串截取)
• MySQL实现字符串截取的图文教程
• mysql中截取字符串的6个函数讲解
• MySQL 字符串截取相关函数小结
• MySQL中常用的字段截取和字符串截取方法
• mysql中字符串截取与拆分的实现示例
• mysql sql字符串截取函数详解
• mysql函数之截取字符串的实现