前言
在使用mysql过程中发现了一些非常有用的内置函数，今天拿出来分享到大家，希望对你会有所帮助。
1. group_concat
平时使用mysql的时候使用group by 分组的场景还是比较多的。
比如想统计具体用户名称有哪些
mysql> select name from user group by name;
+-----------+
| name |
+-----------+
| 张三 |
| 111胡桃 |
| 1胡桃11 |
| 11胡桃1 |
| 胡桃111 |
| 胡桃 |
| 甘雨 |
| 钟离 |
| 刻晴 |
| 七七 |
+-----------+
如果我们想让相同用户的地区拼接在一起的话就可以使用group_concat
mysql> select name, group_concat(area) from user group by name;
+-----------+-----------------------------+
| name | group_concat(area) |
+-----------+-----------------------------+
| 111胡桃 | 重庆 |
| 11胡桃1 | 北京 |
| 1胡桃11 | 成都 |
| 七七 | 成都,北京,上海,重庆 |
| 刻晴 | 成都,重庆,上海,北京 |
| 张三 | 成都 |
| 甘雨 | 重庆,上海,北京,成都 |
| 胡桃 | 北京,上海,重庆 |
| 胡桃111 | 上海 |
| 钟离 | 重庆,上海,北京 |
+-----------+-----------------------------+
使用group_concat函数，可以轻松的把分组后，name相同的数据拼接到一起，组成一个字符串，用逗号分隔。
当然group_concat函数还有一些很巧妙的用法，比如我们现在想通过用户名分组，然后找到其中年龄最大的那个用户就可以这样做。
mysql> SELECT SUBSTRING_INDEX(group_concat(id ORDER BY `age` DESC), ',', 1) as id FROM `user` GROUP BY `name`;
+----+
| id |
+----+
| 2 |
| 4 |
| 3 |
| 21 |
| 22 |
| 1 |
| 11 |
| 7 |
| 5 |
| 10 |
+----+
通过group_concat我们指定id按照age倒序拼接，然后使用SUBSTRING_INDEX截取,分割后的第一个元素那么这个元素就是年龄最大的id。之后就可以通过子查询查询该用户的所有信息了。
2. replace
实际开发中经常会有替换字符串中部分内容的需求，比如：将字符串中的字符A替换成B。这种情况就可以使用replace函数。
update `user` set `name`=replace(`name`,' ','') where `name` like ' %';
update `user` set `name`=replace(`name`,' ','') where `name` like '% ';
这样就能轻松实现字符串替换。
3. char_length
有时候我们需要获取字符的长度，然后根据字符的长度进行排序。
这时就可以使用char_length。
通过该函数就能获取字符长度。
获取字符长度并且排序的sql如下：
select * from `user` where `name` like '%胡桃%' order by char_length(`name`) desc limit 3;
+----+-----------+-----+--------+------------+
| id | name | age | area | birthday |
+----+-----------+-----+--------+------------+
| 2 | 111胡桃 | 32 | 重庆 | 1990-10-29 |
| 3 | 1胡桃11 | 44 | 成都 | 1978-05-22 |
| 4 | 11胡桃1 | 10 | 北京 | 2011-12-30 |
+----+-----------+-----+--------+------------+
4. locate
有时候我们在查找某个关键字，比如：胡桃，并且需要明确知道它在某个字符串中的位置时，就能使用locate。
select * from `user` where `name` like '%胡桃%' order by char_length(`name`) asc , locate('胡桃',`name`) asc ;
+----+-----------+-----+--------+------------+
| id | name | age | area | birthday |
+----+-----------+-----+--------+------------+
| 6 | 胡桃 | 28 | 重庆 | 1994-11-11 |
| 7 | 胡桃 | 32 | 上海 | 1990-03-02 |
| 8 | 胡桃 | 18 | 北京 | 2004-07-01 |
| 5 | 胡桃111 | 25 | 上海 | 2001-08-15 |
| 3 | 1胡桃11 | 44 | 成都 | 1978-05-22 |
| 4 | 11胡桃1 | 10 | 北京 | 2011-12-30 |
| 2 | 111胡桃 | 32 | 重庆 | 1990-10-29 |
+----+-----------+-----+--------+------------+
我们可以看到首先通过字符串长度排序后，相同长度的数据会根据关键字所在字符串中的位置排序，越靠左越靠前。
除此之外，我们还可以使用：instr和position函数，它们的功能跟locate函数类似。顺带一提instr等可以用来替代like查询。
select * from `user` where instr(`name`,'胡桃') > 0;
+----+-----------+-----+--------+------------+
| id | name | age | area | birthday |
+----+-----------+-----+--------+------------+
| 2 | 111胡桃 | 32 | 重庆 | 1990-10-29 |
| 3 | 1胡桃11 | 44 | 成都 | 1978-05-22 |
| 4 | 11胡桃1 | 10 | 北京 | 2011-12-30 |
| 5 | 胡桃111 | 25 | 上海 | 2001-08-15 |
| 6 | 胡桃 | 28 | 重庆 | 1994-11-11 |
| 7 | 胡桃 | 32 | 上海 | 1990-03-02 |
| 8 | 胡桃 | 18 | 北京 | 2004-07-01 |
+----+-----------+-----+--------+------------+
5. explain
使用explain命令，查看mysql的执行计划，它会显示索引的使用情况。如：
explain select * from `user` where id = 2;
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| 1 | SIMPLE | user | NULL | const | PRIMARY | PRIMARY | 8 | const | 1 | 100.00 | NULL |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
可以简单通过type、key、key_len这几列判断索引使用情况，具体执行计划包含列的含义如下图所示：
QQ20221229-113605
当然sql语句没有走索引，排除没有建索引之外，最大的可能性是索引失效了。
下面说说索引失效的常见原因：
QQ20221229-113720
如果不是这些原因那就需要进一步排查了。
网友评论