一：MySQL 字符串截取相關(guān)函數(shù)
首先來看一下 MySQL 中跟字符串截取相關(guān)的函數(shù)有：
left(), right(), substring(), substring_index()、 mid(), substr()。其中 mid() 和 substr() 都是 substring() 的同義詞

二：MySQL 字符串截取函數(shù)使用說明

1. left()

Name: 'LEFT' Description: Syntax: LEFT(str,len)Returns the leftmost len characters from the string str, or NULL if any argument is NULL.URL: https://dev.mysql.com/doc/refman/5.7/en/string-functions.htmlExamples: mysql> SELECT LEFT('foobarbar', 5);-> 'fooba'##該函數(shù)從最左側(cè)開始向右截取字符串，截取 len 指定的字符后停止，例如上面的例子中 len 為5，所以從最左側(cè)開始向右截取5個(gè)字符

2.right()

Name: 'RIGHT' Description: Syntax: RIGHT(str,len)Returns the rightmost len characters from the string str, or NULL if any argument is NULL.URL: https://dev.mysql.com/doc/refman/5.7/en/string-functions.htmlExamples: mysql> SELECT RIGHT('foobarbar', 4);-> 'rbar'##該函數(shù)從最右側(cè)開始往左截取字符串，截取 len 指定的字符后停止，例如上面的例子中 len 為4，所以從最右側(cè)開始向左截取4個(gè)字符

3.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 str starting at position pos. The forms with a len argument return a substring len characters long from string str, starting at position pos. The forms that use FROM are standard SQL syntax. It is also possible to use a negative value for pos. In this case, the beginning of the substring is pos characters from the end of the string, rather than the beginning. A negative value may be used for pos in any of the forms of this function. A value of 0 for pos returns an empty string.For all forms of SUBSTRING(), the position of the first character in the string from which the substring is to be extracted is reckoned as 1.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'##該函數(shù)從pos參數(shù)指定位置開始向右截取 len 參數(shù)指定的字符個(gè)數(shù)(len 如果不指定，則截取到最后一個(gè)字符)。如果 pos 指定的是正數(shù)，則位置從左往右計(jì)數(shù)。如果pos指定的負(fù)數(shù)，則位置從由往左數(shù)。from 和 for 關(guān)鍵字可以用逗號(hào)代替

4.substring_index()

Name: 'SUBSTRING_INDEX' Description: Syntax: SUBSTRING_INDEX(str,delim,count)Returns the substring from string str before count occurrences of the delimiter delim. If count is positive, everything to the left of the final delimiter (counting from the left) is returned. If count is negative, everything to the right of the final delimiter (counting from the right) is returned. SUBSTRING_INDEX() performs a case-sensitive match 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'##該函數(shù)截取指定的第幾個(gè)分隔符之前的字符串，第二個(gè)參數(shù)用來指定分隔符，第三個(gè)參數(shù)指定第幾個(gè)分隔符。如果count 為正數(shù)，從左往右計(jì)算分隔符個(gè)數(shù)，截取指定分隔符之前的所有字符。如果count 為負(fù)數(shù)，則從右往走計(jì)算分隔符個(gè)數(shù)，截取指定分隔符及之后的所有字符

三：MySQL 常用字符串截取操作示例

1)從第三個(gè)字符開始截取直到結(jié)束 [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) 從第三個(gè)字符開始截取，一共截取6個(gè)字符 [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) 從倒數(shù)第三個(gè)字符開始截取，直到結(jié)束(截取的時(shí)候從左往右) [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) 截取第一個(gè)指定字符/字符串之前所有字符 [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) 截取倒數(shù)第二個(gè)字符串之后所有的字符 [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) 如果指定的分隔符不存在，則輸出整個(gè)字符串 [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)截取最后一個(gè)指定字符之前所有的字符 [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)

總結(jié)

以上是生活随笔為你收集整理的MySQL 字符串截取操作的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。