前言
在使用mysql過(guò)程中發(fā)現(xiàn)了一些非常有用的內(nèi)置函數(shù)，今天拿出來(lái)分享到大家，希望對(duì)你會(huì)有所幫助。
1. group_concat
平時(shí)使用mysql的時(shí)候使用group by 分組的場(chǎng)景還是比較多的。
比如想統(tǒng)計(jì)具體用戶名稱有哪些
mysql> select name from user group by name;
+-----------+
| name |
+-----------+
| 張三 |
| 111胡桃 |
| 1胡桃11 |
| 11胡桃1 |
| 胡桃111 |
| 胡桃 |
| 甘雨 |
| 鐘離 |
| 刻晴 |
| 七七 |
+-----------+
如果我們想讓相同用戶的地區(qū)拼接在一起的話就可以使用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函數(shù)，可以輕松的把分組后，name相同的數(shù)據(jù)拼接到一起，組成一個(gè)字符串，用逗號(hào)分隔。
當(dāng)然group_concat函數(shù)還有一些很巧妙的用法，比如我們現(xiàn)在想通過(guò)用戶名分組，然后找到其中年齡最大的那個(gè)用戶就可以這樣做。
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 |
+----+
通過(guò)group_concat我們指定id按照age倒序拼接，然后使用SUBSTRING_INDEX截取,分割后的第一個(gè)元素那么這個(gè)元素就是年齡最大的id。之后就可以通過(guò)子查詢查詢?cè)撚脩舻乃行畔⒘恕?/p>
2. replace
實(shí)際開發(fā)中經(jīng)常會(huì)有替換字符串中部分內(nèi)容的需求，比如：將字符串中的字符A替換成B。這種情況就可以使用replace函數(shù)。
update `user` set `name`=replace(`name`,' ','') where `name` like ' %';
update `user` set `name`=replace(`name`,' ','') where `name` like '% ';
這樣就能輕松實(shí)現(xiàn)字符串替換。
3. char_length
有時(shí)候我們需要獲取字符的長(zhǎng)度，然后根據(jù)字符的長(zhǎng)度進(jìn)行排序。
這時(shí)就可以使用char_length。
通過(guò)該函數(shù)就能獲取字符長(zhǎng)度。
獲取字符長(zhǎng)度并且排序的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
有時(shí)候我們?cè)诓檎夷硞€(gè)關(guān)鍵字，比如：胡桃，并且需要明確知道它在某個(gè)字符串中的位置時(shí)，就能使用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 |
+----+-----------+-----+--------+------------+
我們可以看到首先通過(guò)字符串長(zhǎng)度排序后，相同長(zhǎng)度的數(shù)據(jù)會(huì)根據(jù)關(guān)鍵字所在字符串中的位置排序，越靠左越靠前。
除此之外，我們還可以使用：instr和position函數(shù)，它們的功能跟locate函數(shù)類似。順帶一提instr等可以用來(lái)替代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的執(zhí)行計(jì)劃，它會(huì)顯示索引的使用情況。如：
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 |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
可以簡(jiǎn)單通過(guò)type、key、key_len這幾列判斷索引使用情況，具體執(zhí)行計(jì)劃包含列的含義如下圖所示：
當(dāng)然sql語(yǔ)句沒(méi)有走索引，排除沒(méi)有建索引之外，最大的可能性是索引失效了。
下面說(shuō)說(shuō)索引失效的常見(jiàn)原因：
如果不是這些原因那就需要進(jìn)一步排查了。
