MySQL字符串截取

在开发中有时会遇到只需要数据库某字段的一部分，这种场景有时直接通过数据库来操作比通过代码实现要更方便快捷些，MySQL有很多字符串函数可以用来处理这些需求，如left、right、substring、substring_index

1. 从左开始截取字符串left(str, length)

str：被截取字符串
length：截取长度，若为负数则什么也不返回

(1) length为正数

mysql> SELECT LEFT("www.baidu.com", 3);
+--------------------------+
| LEFT("www.baidu.com", 3) |
+--------------------------+
| www                      |
+--------------------------+
1 row in set (0.00 sec)

(2) length为负数

mysql> SELECT LEFT("www.baidu.com", -3);
+---------------------------+
| LEFT("www.baidu.com", -3) |
+---------------------------+
|                           |
+---------------------------+
1 row in set (0.00 sec)

2. 从右开始截取字符串right(str, length)

str：被截取字符串
length：截取长度，若为负数则什么也不返回

(1) length为正数

mysql> SELECT RIGHT("www.baidu.com", 3);
+---------------------------+
| RIGHT("www.baidu.com", 3) |
+---------------------------+
| com                       |
+---------------------------+
1 row in set (0.00 sec)

(2) length为负数

mysql> SELECT RIGHT("www.baidu.com", -3);
+----------------------------+
| RIGHT("www.baidu.com", -3) |
+----------------------------+
|                            |
+----------------------------+
1 row in set (0.00 sec)

3. 截取特定长度字符串substring(str, pos, length)

str：被截取字符串
pos：从第几位开始截取，若为负数则从倒数第几位字符开始从左往右截取
length：截取长度，若为负数则什么也不返回

(1) 从第5个字符开始向右边截取直到结尾

mysql> SELECT SUBSTRING("www.baidu.com", 5);
+-------------------------------+
| SUBSTRING("www.baidu.com", 5) |
+-------------------------------+
| baidu.com                     |
+-------------------------------+
1 row in set (0.00 sec)

(2) 从倒数第5个字符开始向右边截取直到结尾

mysql> SELECT SUBSTRING("www.baidu.com", -5);
+--------------------------------+
| SUBSTRING("www.baidu.com", -5) |
+--------------------------------+
| u.com                          |
+--------------------------------+
1 row in set (0.00 sec)

(3) 从第5个字符开始向右边截取5个字符

mysql> SELECT SUBSTRING("www.baidu.com", 5, 5);
+----------------------------------+
| SUBSTRING("www.baidu.com", 5, 5) |
+----------------------------------+
| baidu                            |
+----------------------------------+
1 row in set (0.00 sec)

(4) 从倒数第5个字符开始向右边截取5个字符

mysql> SELECT SUBSTRING("www.baidu.com", -5, 5);
+-----------------------------------+
| SUBSTRING("www.baidu.com", -5, 5) |
+-----------------------------------+
| u.com                             |
+-----------------------------------+
1 row in set (0.00 sec)

(5) length为负数

mysql> SELECT SUBSTRING("www.baidu.com", 5, -5);
+-----------------------------------+
| SUBSTRING("www.baidu.com", 5, -5) |
+-----------------------------------+
|                                   |
+-----------------------------------+
1 row in set (0.00 sec)

mysql> SELECT SUBSTRING("www.baidu.com", -5, -5);
+------------------------------------+
| SUBSTRING("www.baidu.com", -5, -5) |
+------------------------------------+
|                                    |
+------------------------------------+
1 row in set (0.00 sec)

4. 按关键字截取字符串 substring_index(str, delim, count)

str：被截取字符串
delim：关键字，从第count个delim分割str
count：关键字出现的次数，若为正数则从左往右数第count个delim分割str并返回左边内容，若为负数则从右往左数第count个delim分割str并返回右边内容

(1) 截取第一个"."之前的所有字符

mysql> SELECT SUBSTRING_INDEX('www.baidu.com', '.', 1);
+------------------------------------------+
| SUBSTRING_INDEX('www.baidu.com', '.', 1) |
+------------------------------------------+
| www                                      |
+------------------------------------------+
1 row in set (0.00 sec)

(2) 截取第二个"."之前的所有字符

mysql> SELECT SUBSTRING_INDEX('www.baidu.com', '.', 2);
+------------------------------------------+
| SUBSTRING_INDEX('www.baidu.com', '.', 2) |
+------------------------------------------+
| www.baidu                                |
+------------------------------------------+
1 row in set (0.00 sec)

(3) 截取倒数第一个"."之后的所有字符

mysql> SELECT SUBSTRING_INDEX('www.baidu.com', '.', -1);
+-------------------------------------------+
| SUBSTRING_INDEX('www.baidu.com', '.', -1) |
+-------------------------------------------+
| com                                       |
+-------------------------------------------+
1 row in set (0.00 sec)

(4) 截取倒数第二个"."之后的所有字符

mysql> SELECT SUBSTRING_INDEX('www.baidu.com', '.', -2);
+-------------------------------------------+
| SUBSTRING_INDEX('www.baidu.com', '.', -2) |
+-------------------------------------------+
| baidu.com                                 |
+-------------------------------------------+
1 row in set (0.00 sec)

(5) 如果关键字不存在，则返回整个字符串

mysql> SELECT SUBSTRING_INDEX('www.baidu.com', '*', 1);
+------------------------------------------+
| SUBSTRING_INDEX('www.baidu.com', '*', 1) |
+------------------------------------------+
| www.baidu.com                            |
+------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT SUBSTRING_INDEX('www.baidu.com', '*', -1);
+-------------------------------------------+
| SUBSTRING_INDEX('www.baidu.com', '*', -1) |
+-------------------------------------------+
| www.baidu.com                             |
+-------------------------------------------+
1 row in set (0.00 sec)

(6) 截取出"baidu"

mysql> SELECT SUBSTRING_INDEX(SUBSTRING_INDEX('www.baidu.com', '.', -2), '.', 1);
+--------------------------------------------------------------------+
| SUBSTRING_INDEX(SUBSTRING_INDEX('www.baidu.com', '.', -2), '.', 1) |
+--------------------------------------------------------------------+
| baidu                                                              |
+--------------------------------------------------------------------+
1 row in set (0.00 sec)