MySQL 字符串函数的高阶用法

下面代码均用公共表达式生成的测试数据，可直接将代码COPY执行。执行完成后也不需要销毁什么数据。

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 <= 100 and 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

mysql.help_topic是我常用到的一个系统辅助表，help_topic是一个有序的序列，直接拿来即使用，如果你的应用不允许使用系统表，那么你就用公共表达式生成一个序列也是可以的

这里关键用法其实就两段
第一段是 substring_index(substring_index(a.str,’,’,help_topic_id + 1),’,’,-1)
先按序号从左到右取得字符串， 再重左到右取第一个，即实现了按序分开
substring_index(‘张三,李四,王五,赵六’,’,’,1) = 张三
substring_index(‘张三,李四,王五,赵六’,’,’,2) = 张三,李四
substring_index(‘张三,李四,王五,赵六’,’,’,3) = 张三,李四,王五
…以此类推

然后再从右到左到第一串，即实现了
张三
李四
王五

第二段 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