MySQL文本操作函数

2019-03-25 本文已影响0人竹鼠不要中暑

为了演示以下的文本操作函数，首先创建一个names表格。

mysql> SELECT * FROM names;
+------+
| name |
+------+
| 李四 |
| Amy  |
| 123  |
+------+

LENGTH

返回字符串的长度。
注意：

utf8编码下,一个汉字算三个字符,一个数字或字母算一个字符。
其他编码下,一个汉字算两个字符, 一个数字或字母算一个字符。

utf8编码示例：

mysql> SELECT LENGTH(name) FROM names;
+--------------+
| LENGTH(name) |
+--------------+
|            6 |
|            3 |
|            3 |
+--------------+

其他编码示例(我的数据库的character_set_connection字符集为gbk)：

mysql> SELECT LENGTH('李四');
+----------------+
| LENGTH('李四') |
+----------------+
|              4 |
+----------------+
1 row in set (0.00 sec)

mysql> SELECT LENGTH('Amy');
+---------------+
| LENGTH('Amy') |
+---------------+
|             3 |
+---------------+
1 row in set (0.00 sec)

mysql> SELECT LENGTH(123);
+-------------+
| LENGTH(123) |
+-------------+
|           3 |
+-------------+
1 row in set (0.00 sec)

CHAR_LENGTH

如果我们想得到一个字符串它的“位数”长度，就可以使用CHAR_LENGTH函数，无论是一个汉字，一个英文还是一个数字，都看作一个字符，这可能也更符合很多时候我们队字符串长度的获取需求。
示例：

mysql> SELECT CHAR_LENGTH(name) FROM names;
+-------------------+
| CHAR_LENGTH(name) |
+-------------------+
|                 2 |
|                 3 |
|                 3 |
+-------------------+
3 rows in set (0.00 sec)

mysql> SELECT CHAR_LENGTH('李四');
+---------------------+
| CHAR_LENGTH('李四') |
+---------------------+
|                   2 |
+---------------------+
1 row in set (0.00 sec)

LEFT, RIGHT

LEFT(str, length)从字符串左边开始取指定长度的子字符串并返回；
RIGHT(str, length)从字符串右边开始取指定长度的子字符串并返回。
示例：

mysql> SELECT LEFT(name,1) FROM names;
+--------------+
| LEFT(name,1) |
+--------------+
| 李           |
| A            |
| 1            |
+--------------+
3 rows in set (0.02 sec)

mysql> SELECT RIGHT(name,1) FROM names;
+---------------+
| RIGHT(name,1) |
+---------------+
| 四            |
| y             |
| 3             |
+---------------+
3 rows in set (0.00 sec)

LOWER, UPPER

LOWER(str)将字符串转为小写并返回；
UPPER(str)将字符串转为大写并返回。
示例（中文和数字不变）：

mysql> SELECT LOWER(name) FROM names;
+-------------+
| LOWER(name) |
+-------------+
| 李四        |
| amy         |
| 123         |
+-------------+
3 rows in set (0.03 sec)

mysql> SELECT UPPER(name) FROM names;
+-------------+
| UPPER(name) |
+-------------+
| 李四        |
| AMY         |
| 123         |
+-------------+
3 rows in set (0.03 sec)

LTRIM, RTRIM, TRIM

LTRIM(str)将字符串左边空格去除后返回；
RTRIM(str)将字符串右边空格去除后返回；
TRIM(str)将字符串左右两边空格去除后返回。
示例：

mysql> SELECT LTRIM('  really good day  ');
+------------------------------+
| LTRIM('  really good day  ') |
+------------------------------+
| really good day              |
+------------------------------+
1 row in set (0.00 sec)

mysql> SELECT RTRIM('  really good day  ');
+------------------------------+
| RTRIM('  really good day  ') |
+------------------------------+
|   really good day            |
+------------------------------+
1 row in set (0.02 sec)

mysql> SELECT TRIM('  really good day  ');
+-----------------------------+
| TRIM('  really good day  ') |
+-----------------------------+
| really good day             |
+-----------------------------+
1 row in set (0.00 sec)

SUBSTRING

SUBSTRING(str,m,n)从字符串第m位开始截取n位字符

字符位数从1开始
若n缺省，则截取至字符串末位

示例：

mysql> SELECT SUBSTRING('really good day',0,1);
+----------------------------------+
| SUBSTRING('really good day',0,1) |
+----------------------------------+
|                                  |
+----------------------------------+
1 row in set (0.00 sec)

mysql> SELECT SUBSTRING('really good day',1,1);
+----------------------------------+
| SUBSTRING('really good day',1,1) |
+----------------------------------+
| r                                |
+----------------------------------+
1 row in set (0.00 sec)

mysql> SELECT SUBSTRING('really good day',8,4);
+----------------------------------+
| SUBSTRING('really good day',8,4) |
+----------------------------------+
| good                             |
+----------------------------------+
1 row in set (0.00 sec)

mysql> SELECT SUBSTRING('really good day',8);
+--------------------------------+
| SUBSTRING('really good day',8) |
+--------------------------------+
| good day                       |
+--------------------------------+
1 row in set (0.00 sec)

CONCAT, CONCAT_WS

CONCAT(str1,str2,...)连接多个字符串

若有字符串为NULL，则返回NULL

CONCAT_WS(separator,str1,str2,…)以分隔符连接多个字符串

若separator为NULL,则结果为NULL；
字符串中为NULL的，连接时会被忽略。

示例：

CONCAT

mysql> SELECT CONCAT('really','Good','Day');
+-------------------------------+
| CONCAT('really','Good','Day') |
+-------------------------------+
| reallyGoodDay                 |
+-------------------------------+
1 row in set (0.00 sec)

mysql> SELECT CONCAT('really','Good',NULL,'Day');
+------------------------------------+
| CONCAT('really','Good',NULL,'Day') |
+------------------------------------+
| NULL                               |
+------------------------------------+
1 row in set (0.00 sec)

CONCAT_WS

mysql> SELECT CONCAT_WS('~','really','good','day');
+--------------------------------------+
| CONCAT_WS('~','really','good','day') |
+--------------------------------------+
| really~good~day                      |
+--------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT CONCAT_WS('~','really','good',NULL,'day');
+-------------------------------------------+
| CONCAT_WS('~','really','good',NULL,'day') |
+-------------------------------------------+
| really~good~day                           |
+-------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT CONCAT_WS(NULL,'really','good',NULL,'day');
+--------------------------------------------+
| CONCAT_WS(NULL,'really','good',NULL,'day') |
+--------------------------------------------+
| NULL                                       |
+--------------------------------------------+
1 row in set (0.00 sec)

INSTR, LOCATE

INSERT(str,substr)和LOCATE(substr,str)均返回str字符串中substr首次出现的位置，找不到则返回0。注意二者的参数列表是反的。

mysql> SELECT LOCATE('L','HELLO');
+---------------------+
| LOCATE('L','HELLO') |
+---------------------+
|                   3 |
+---------------------+
1 row in set (0.05 sec)

mysql> SELECT INSTR('HELLO','L');
+--------------------+
| INSTR('HELLO','L') |
+--------------------+
|                  3 |
+--------------------+

mysql> SELECT LOCATE('A','HELLO');
+---------------------+
| LOCATE('A','HELLO') |
+---------------------+
|                   0 |
+---------------------+
1 row in set (0.00 sec)

mysql> SELECT INSTR('HELLO','A');
+--------------------+
| INSTR('HELLO','A') |
+--------------------+
|                  0 |
+--------------------+
1 row in set (0.00 sec)

另外，LOCATE(substr,str,pos)可以从pos处开始查找substr,并返回找到的第一个substr的位置。

mysql> SELECT LOCATE('L','HELLO',4);
+-----------------------+
| LOCATE('L','HELLO',4) |
+-----------------------+
|                     4 |
+-----------------------+
1 row in set (0.00 sec)