一：MySQL 字符串截取相关函数
首先来看一下 MySQL 中跟字符串截取相关的函数有：
left(), right(), substring(), substring_index()、 mid(), substr()。其中 mid() 和 substr() 都是 substring() 的同义词
二：MySQL 字符串截取函数使用说明
left()
Name: 'LEFT'Description:Syntax:LEFT(str,len)Returns the leftmost len characters from the string str, or NULL if anyargument is NULL.URL: https://dev.mysql.com/doc/refman/5.7/en/string-functions.htmlExamples:mysql> SELECT LEFT('foobarbar', 5); -> 'fooba'##该函数从最左侧开始向右截取字符串，截取 len 指定的字符后停止，例如上面的例子中 len 为5，所以从最左侧开始向右截取5个字符right()
Name: 'RIGHT'Description:Syntax:RIGHT(str,len)Returns the rightmost len characters from the string str, or NULL ifany argument is NULL.URL: https://dev.mysql.com/doc/refman/5.7/en/string-functions.htmlExamples:mysql> SELECT RIGHT('foobarbar', 4); -> 'rbar'##该函数从最右侧开始往左截取字符串，截取 len 指定的字符后停止，例如上面的例子中 len 为4，所以从最右侧开始向左截取4个字符substring()
Name: 'SUBSTRING'Description:Syntax:SUBSTRING(str,pos), SUBSTRING(str FROM pos), SUBSTRING(str,pos,len),SUBSTRING(str FROM pos FOR len)The forms without a len argument return a substring from string strstarting at position pos. The forms with a len argument return asubstring len characters long from string str, starting at positionpos. The forms that use FROM are standard SQL syntax. It is alsopossible to use a negative value for pos. In this case, the beginningof the substring is pos characters from the end of the string, ratherthan the beginning. A negative value may be used for pos in any of theforms of this function. A value of 0 for pos returns an empty string.For all forms of SUBSTRING(), the position of the first character inthe string from which the substring is to be extracted is reckoned as1.URL: https://dev.mysql.com/doc/refman/5.7/en/string-functions.htmlExamples:mysql> SELECT SUBSTRING('Quadratically',5); -> 'ratically'mysql> SELECT SUBSTRING('foobarbar' FROM 4); -> 'barbar'mysql> SELECT SUBSTRING('Quadratically',5,6); -> 'ratica'mysql> SELECT SUBSTRING('Sakila', -3); -> 'ila'mysql> SELECT SUBSTRING('Sakila', -5, 3); -> 'aki'mysql> SELECT SUBSTRING('Sakila' FROM -4 FOR 2); -> 'ki'##该函数从pos参数指定位置开始向右截取 len 参数指定的字符个数(len 如果不指定，则截取到最后一个字符)。如果 pos 指定的是正数，则位置从左往右计数。如果pos指定的负数，则位置从由往左数。from 和 for 关键字可以用逗号代替substring_index()
Name: 'SUBSTRING_INDEX'Description:Syntax:SUBSTRING_INDEX(str,delim,count)Returns the substring from string str before count occurrences of thedelimiter delim. If count is positive, everything to the left of thefinal delimiter (counting from the left) is returned. If count isnegative, everything to the right of the final delimiter (counting fromthe right) is returned. SUBSTRING_INDEX() performs a case-sensitivematch when searching for delim.URL: https://dev.mysql.com/doc/refman/5.7/en/string-functions.htmlExamples:mysql> SELECT SUBSTRING_INDEX('www.mysql.com', '.', 2); -> 'www.mysql'mysql> SELECT SUBSTRING_INDEX('www.mysql.com', '.', -2); -> 'mysql.com'##该函数截取指定的第几个分隔符之前的字符串，第二个参数用来指定分隔符，第三个参数指定第几个分隔符。如果count 为正数，从左往右计算分隔符个数，截取指定分隔符之前的所有字符。如果count 为负数，则从右往走计算分隔符个数，截取指定分隔符及之后的所有字符三：MySQL 常用字符串截取操作示例
1)从第三个字符开始截取直到结束[root@127.0.0.1][information_schema][04:42:28]> select substring('www.mysql.com',3);+------------------------------+| substring('www.mysql.com',3) |+------------------------------+| w.mysql.com |+------------------------------+1 row in set (0.00 sec)2) 从第三个字符开始截取，一共截取6个字符[root@127.0.0.1][information_schema][04:42:38]> select substring('www.mysql.com',3,6);+--------------------------------+| substring('www.mysql.com',3,6) |+--------------------------------+| w.mysq |+--------------------------------+1 row in set (0.00 sec)3) 从倒数第三个字符开始截取，直到结束(截取的时候从左往右)[root@127.0.0.1][information_schema][04:42:43]> select substring('www.mysql.com',-3);+-------------------------------+| substring('www.mysql.com',-3) |+-------------------------------+| com |+-------------------------------+1 row in set (0.00 sec)4) 截取第一个指定字符/字符串之前所有字符[root@127.0.0.1][information_schema][04:43:01]> select substring_index('www.mysql.com','.',1);+----------------------------------------+| substring_index('www.mysql.com','.',1) |+----------------------------------------+| www |+----------------------------------------+1 row in set (0.00 sec)5) 截取倒数第二个字符串之后所有的字符[root@127.0.0.1][information_schema][04:43:30]> select substring_index('www.mysql.com','.',-2);+-----------------------------------------+| substring_index('www.mysql.com','.',-2) |+-----------------------------------------+| mysql.com |+-----------------------------------------+1 row in set (0.00 sec)6) 如果指定的分隔符不存在，则输出整个字符串[root@127.0.0.1][information_schema][04:44:00]> select substring_index('www.mysql.com','/',-2);+-----------------------------------------+| substring_index('www.mysql.com','/',-2) |+-----------------------------------------+| www.mysql.com |+-----------------------------------------+1 row in set (0.00 sec)7)截取最后一个指定字符之前所有的字符[root@127.0.0.1][information_schema][04:44:15]> select REVERSE(SUBSTR(REVERSE("/data/mysql/mysql3306/log/3306-bin") , INSTR(REVERSE("/data/mysql/mysql3306/log/3306-bin"),'/')+1));+-----------------------------------------------------------------------------------------------------------------------------+| REVERSE(SUBSTR(REVERSE("/data/mysql/mysql3306/log/3306-bin") , INSTR(REVERSE("/data/mysql/mysql3306/log/3306-bin"),'/')+1)) |+-----------------------------------------------------------------------------------------------------------------------------+| /data/mysql/mysql3306/log |+-----------------------------------------------------------------------------------------------------------------------------+1 row in set (0.00 sec)来源地址：https://blog.csdn.net/shaochenshuo/article/details/127555152
