当前位置: 代码网 > it编程>数据库>Mysql > MySQL字符串截取的核心要点和注意事项

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

2025年08月19日 Mysql 我要评论
我将全面讲解 mysql 中字符串截取的核心要点和注意事项:核心截取函数1.substring()/substr()-- 基本语法select substring('mysql tutorial',

我将全面讲解 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; -- 增大临时表内存

函数对比表

函数特点适用场景
substring()灵活定位任意位置截取
left()/right()高效简单首尾固定长度截取
substring_index()分隔符处理路径/域名解析
mid()substring别名兼容旧代码

最佳实践总结

  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字符串截取内容请搜索代码网以前的文章或继续浏览下面的相关文章希望大家以后多多支持代码网!

(0)

相关文章:

版权声明:本文内容由互联网用户贡献,该文观点仅代表作者本人。本站仅提供信息存储服务,不拥有所有权,不承担相关法律责任。 如发现本站有涉嫌抄袭侵权/违法违规的内容, 请发送邮件至 2386932994@qq.com 举报,一经查实将立刻删除。

发表评论

验证码:
Copyright © 2017-2025  代码网 保留所有权利. 粤ICP备2024248653号
站长QQ:2386932994 | 联系邮箱:2386932994@qq.com