如果结果的长度大于 max_allowed_packet 系统变量的值，则字符串值函数将返回 NULL。看 Configuring the Server
常用字符串函数
| 名称 | 描述 | 备注 |
|---|
LEFT() | 从左边截取指定的字符串 | |
MID() | 从指定的位置截取字符串 | |
RIGHT() | 从右边截取指定的字符串 | |
TRIM() | 移除字符串两端的空格 | |
SUBSTR() | 截取指定的字符串 | |
SUBSTRING() | 截取指定的字符串 | |
SUBSTRING_INDEX() | 截取指定的定界符出现的次数之前的字符串 | |
字符串函数用法
LEFT 和 RIGHT 函数
1
2
3
4
5
6
| SELECT LEFT('hello world, hello mysql', 5) AS url;
SELECT RIGHT('hello world, hello mysql', 5) AS url;
// 结果
hello
mysql
|
TRIM 函数
1
2
3
4
5
6
7
8
9
10
| SELECT TRIM(' http://www.mysql.com ') AS url;
SELECT TRIM(LEADING 'xyz' FROM 'xyzxyzxyzhello world') AS url;
SELECT TRIM(BOTH 'z' FROM 'zzzzzhello worldzzzzz') AS url;
SELECT TRIM(TRAILING 'test' FROM 'hello worldtest') AS url;
// 结果
http://www.mysql.com
hello world
hello world
hello world
|
SUBSTR 和 SUBSTRING 和 MID 函数
1
2
3
4
5
6
7
8
9
| SET @url = 'http://www.mysql.com/';
SELECT MID(@url, 8, 13) AS url;
SELECT SUBSTR(@url, 8, 13) AS url;
SELECT SUBSTRING(@url , -14, 13) AS url;
// 结果
www.mysql.com
www.mysql.com
www.mysql.com
|
SUBSTRING_INDEX 函数
1
2
3
4
5
6
| SELECT SUBSTRING_INDEX('www.mysql.com.cn', '.', 3) AS url;
SELECT SUBSTRING_INDEX('hello world, hello mysql', 'mysql ', 1) AS url;
// 结果
www.mysql.com
hello world, hello mysql
|
参考