MySQL 字符串函数的高阶用法

1、right函数

格式化编号时使用

比如我们要生成卡号共5位,固定符为A,后面为4位数字

select concat('A',right(concat('0000',54),4)),concat('A',right(concat('0000',154),4)),concat('A',right(concat('0000',1154),4))


with cte as
(
select 'A' as id union
select 'B'
)

select concat(a.id, right(concat('000',b.help_topic_id),3)) newid from cte a,
mysql.help_topic b where b.help_topic_id 0
order by newid


2、substring_index函数

拆分字符串使用

我们有如下数据

拆分变成

with cte as
(
select 1 as id,'a,b,c' as str union all
select 2 ,'张三,李四,王五,赵六'
)
select id,
substring_index(substring_index(a.str,',',help_topic_id + 1),',',-1)
from cte a,mysql.help_topic b
where help_topic_id < LENGTH(a.str) - length(replace(a.str,',','')) + 1


3、field函数

特殊排序要求时使用

老师要求，重点关注的李四与王一要排在最前面，其它同学按成绩从高到低排名

with cte as
(
select 1 as id,'张三' as name,50 as score union all
select 2,'李四',100 union all
select 3,'王五',89 union all
select 4,'赵六',5 union all
select 5,'王一',4
)
select *,field(name,'王一','李四') from cte order by field(name,'王一','李四') desc,score desc


这个功能用case when 也能实现，但对比起来 field方式 更清爽，简洁些。
下面代码为case when实现方式

with cte as
(
select 1 as id,'张三' as name,50 as score union all
select 2,'李四',100 union all
select 3,'王五',89 union all
select 4,'赵六',5 union all
select 5,'王一',4
)
select * from cte
order by case when name = '李四' then 1 when name = '王一' then 2 else 100 end,score desc