mysql中那些冷门确很有用的知识

前言

在使用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 |
+----+-----------+-----+--------+------------+

我们可以看到首先通过字符串长度排序后,相同长度的数据会根据关键字所在字符串中的位置排序,越靠左越靠前。
除此之外,我们还可以使用:instrposition函数,它们的功能跟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  |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+

可以简单通过typekeykey_len这几列判断索引使用情况,具体执行计划包含列的含义如下图所示:
QQ20221229-113605

当然sql语句没有走索引,排除没有建索引之外,最大的可能性是索引失效了。
下面说说索引失效的常见原因:
QQ20221229-113720
如果不是这些原因那就需要进一步排查了。

# mysql 

评论

Your browser is out-of-date!

Update your browser to view this website correctly. Update my browser now

×