MySQL 字符串截取相关函数

MySQL 字符串截取相关函数

在工作中，可能需要将某些字段按某个分割符组成一个字符串作为字段值存取到数据库表中，比如某个任务对应三个结果，分别存储在不同的数据表中，这时可以将这三个不同表的主键按照约定的顺序进行组合（主键a:主键b:主键c）。当需要分别去查任务对应类别的详情信息时，可以截取特定位置的字符串（主键b） join 表b进行操作。正好最近也遇到这块操作，特意将 MySQL 字符串截取的相关函数做一个梳理，以便今后回顾。

一、left(str, len)

返回字符串 str 自左数的 len 个字符。如果任一参数为 NULL，则返回 NULL。

mysql> select left('shinejaie', 5);
+---------------------------------------------------------+
| left('shinejaie', 5) |
+---------------------------------------------------------+
| shine |
+---------------------------------------------------------+
1 row in set (0.00 sec)

二、right(str, len)

返回 str 右边末 len 位的字符。如果有的参数是 NULL 值，则返回 NULL。

mysql> select right('shinejaie', 4);
+---------------------------------------------------------+
| right('shinejaie', 4) |
+---------------------------------------------------------+
| jaie |
+---------------------------------------------------------+
1 row in set (0.00 sec)

三、substring_index(str, delim, count)

返回 str 中第 count 次出现的分隔符 delim 之前的子字符串。如果 count 为正数，将最后一个分隔符左边（因为是从左数分隔符）的所有内容作为子字符串返回；如果 count 为负值，返回最后一个分隔符右边（因为是从右数分隔符）的所有内容作为子字符串返回。在寻找分隔符时，函数对大小写是敏感的。如果在字符串 str 中找不到 delim 参数指定的值，就返回整个字符串。


mysql> select substring_index('home.cnblogs.com', '.', 2);
+---------------------------------------------------------+
| substring_index('home.cnblogs.com', '.', 2) |
+---------------------------------------------------------+
| home.cnblogs |
+---------------------------------------------------------+
1 row in set (0.00 sec)


mysql> select substring_index('home.cnblogs.com', '/', 2);
+---------------------------------------------------------+
| substring_index('home.cnblogs.com', '/', 2) |
+---------------------------------------------------------+
| home.cnblogs.com |
+---------------------------------------------------------+
1 row in set (0.00 sec)

四、substring() 与 substr() ---> substring(str, pos)、substring(str from pos)、substring(str, pos, len)、substring(str from pos for len)

在以上4种函数变种形式中，没有 len 参数的函数形式会返回自 str 中位置 pos 处之后的子字符串；有 len 参数的函数形式会返回自 str 中位置 pos 处之后，长度为 len 的子字符串。使用 FROM 的函数形式则是采用的标准的 SQL 语法。pos 参数也可能取负值，在这种情况下，取字符串的方式是从字符串 str 的末尾向前（而非从前往后），从这种逆向顺序的 pos 处开始取字符串。另外，负值的 pos 参数可用于任何形式的 substring() 函数中。


mysql> select substring('shinejaie', 6);
+---------------------------------------------------------+
| substring('shinejaie',6) |
+---------------------------------------------------------+
| jaie |
+---------------------------------------------------------+
1 row in set (0.00 sec)mysql> select substr('shinejaie' from 6);
+---------------------------------------------------------+
| substr('shinejaie' from 6) |
+---------------------------------------------------------+
| jaie |
+---------------------------------------------------------+
1 row in set (0.00 sec)mysql> select substring('shinejaie', -9, 5);
+---------------------------------------------------------+
| substring('shinejaie', -9, 5) |
+---------------------------------------------------------+
| shine |
+---------------------------------------------------------+
1 row in set (0.00 sec)

五、trim([{both | leading | trailing} [remstr] form] str)

将字符串 str去除 remstr 所指定的前缀或后缀，返回结果字符串。如果没有指定标识符both、leading，或trailing，则默认采用 both，即将前后缀都删除。remstr 其实是个可选参数，如果没有指定它，则删除的是空格。


mysql> select trim(' shinejaie ');
+---------------------------------------------------------+
| trim(' shinejaie ') |
+---------------------------------------------------------+
| shinejaie |
+---------------------------------------------------------+
1 row in set (0.00 sec)mysql> select trim(leading 'cn_' from 'cn_shinejaiecn_');
+---------------------------------------------------------+
| trim(leading 'cn_' from 'cn_shinejaiecn_') |
+---------------------------------------------------------+
| shinejaiecn_ |
+---------------------------------------------------------+
1 row in set (0.00 sec)mysql> select trim(both 'cn_' from 'cn_shinejaiecn_');
+---------------------------------------------------------+
| trim(both 'cn_' from 'cn_shinejaiecn_') |
+---------------------------------------------------------+
| shinejaie |
+---------------------------------------------------------+
1 row in set (0.00 sec)mysql> select trim(trailing 'cn_' from 'cn_shinejaiecn_');
+---------------------------------------------------------+
| trim(trailing 'cn_' from 'cn_shinejaiecn_') |
+---------------------------------------------------------+
| cn_shinejaie |
+---------------------------------------------------------+
1 row in set (0.00 sec)