MySQL字符函数

CONCAT()和CONCAT_WS()字符连接

mysql> SELECT CONCAT('mysql','5.6');+-----------------------+| CONCAT('mysql','5.6') |+-----------------------+| mysql5.6              |+-----------------------+1 row in set (0.09 sec)mysql> SELECT CONCAT('mysql','-','5.6');+---------------------------+| CONCAT('mysql','-','5.6') |+---------------------------+| mysql-5.6                 |+---------------------------+1 row in set (0.00 sec)

例如：将用户的first_name和last_name连接起来成一个字符串

mysql> SELECT * FROM tdb_test;+----+------------+-----------+| id | first_name | last_name |+----+------------+-----------+|  1 | A          | B         ||  2 | Jack       | Bob       ||  3 | tom%       | 123       |+----+------------+-----------+3 rows in set (0.00 sec)mysql> SELECT CONCAT(first_name,last_name) AS fullname FROM tdb_test;+----------+| fullname |+----------+| AB       || JackBob  || tom%123  |+----------+3 rows in set (0.00 sec)

CONCAT_WS()的第一个参数为分隔符，后面的为要连接的字符

mysql> SELECT CONCAT_WS('|','A','B','C');+----------------------------+| CONCAT_WS('|','A','B','C') |+----------------------------+| A|B|C                      |+----------------------------+1 row in set (0.00 sec)mysql> SELECT CONCAT_WS('-','mysql','5.6');+------------------------------+| CONCAT_WS('-','mysql','5.6') |+------------------------------+| mysql-5.6                    |+------------------------------+1 row in set (0.00 sec)

FORMAT()数字格式化

mysql> SELECT FORMAT(12560.75,1);+--------------------+| FORMAT(12560.75,1) |+--------------------+| 12,560.8           |+--------------------+1 row in set (0.01 sec)LOWER()和 UPPER()mysql> SELECT LOWER('MySql');+----------------+| LOWER('MySql') |+----------------+| mysql          |+----------------+1 row in set (0.00 sec)mysql> SELECT UPPER('MySql');+----------------+| UPPER('MySql') |+----------------+| MYSQL          |+----------------+1 row in set (0.00 sec)

LEFT()和RIGHT()

分别有两个参数，第一个为所用的字符串，第二个为取几位

例如获取MySQL的前两位

mysql> SELECT LEFT('MySQL',2);+-----------------+| LEFT('MySQL',2) |+-----------------+| My              |+-----------------+1 row in set (0.00 sec)

例如获取MySQL的后两位

mysql> SELECT RIGHT('MySQL',2);+------------------+| RIGHT('MySQL',2) |+------------------+| QL               |+------------------+1 row in set (0.00 sec)

LENGTH获取字符串长度

mysql> SELECT LENGTH('MySQL');+-----------------+| LENGTH('MySQL') |+-----------------+|               5 |+-----------------+1 row in set (0.02 sec)

LTRIM,RTRIM,TRIM

比如直接用SELECT TRIM(' mysql ');

将会删除mysql的前和后的空格，LTRIM只删除左侧的空格，RTRIM只删除右侧的空格。

其他用法：利用TRIM()删除左右两侧的特定字符。

例如：

mysql> SELECT TRIM(LEADING '?' FROM '??MySQL???');+-------------------------------------+| TRIM(LEADING '?' FROM '??MySQL???') |+-------------------------------------+| MySQL???                            |+-------------------------------------+1 row in set (0.02 sec)mysql> SELECT TRIM(TRAILING '?' FROM '??MySQL???');+--------------------------------------+| TRIM(TRAILING '?' FROM '??MySQL???') |+--------------------------------------+| ??MySQL                              |+--------------------------------------+1 row in set (0.00 sec)mysql> SELECT TRIM(BOTH '?' FROM '??MySQL???');+----------------------------------+| TRIM(BOTH '?' FROM '??MySQL???') |+----------------------------------+| MySQL                            |+----------------------------------+1 row in set (0.01 sec)

注：TRIM()只能删除前导和后续的，不能删除字符中间的，比如TRIM('My SQL');这个空格就删不掉。

REPLACE()字符串替换

例如将??My??SQL???中的？替换成空

mysql> SELECT REPLACE('??My??SQL???','?','');+--------------------------------+| REPLACE('??My??SQL???','?','') |+--------------------------------+| MySQL                          |+--------------------------------+1 row in set (0.00 sec)

SUBSTRING()字符串截取

所含参数，从第几位截，截取几位。

mysql> SELECT SUBSTRING('MySQL',1,2);+------------------------+| SUBSTRING('MySQL',1,2) |+------------------------+| My                     |+------------------------+1 row in set (0.00 sec)

需要注意的是，和编程语言不同的是，第一位是1，不是0。

如果只有从第几位起，没有截取几个，就会，一直截到最后。

mysql> SELECT SUBSTRING('MySQL',3);+----------------------+| SUBSTRING('MySQL',3) |+----------------------+| SQL                  |+----------------------+1 row in set (0.00 sec)

位置也可以为负值（倒着数）

mysql> SELECT SUBSTRING('MySQL',-1);+-----------------------+| SUBSTRING('MySQL',-1) |+-----------------------+| L                     |+-----------------------+1 row in set (0.04 sec)

注，只能是起始位置可以有负值，但是截取长度不能有负值。

LIKE模式匹配（常用于查询中）

mysql> SELECT 'MySQL' LIKE 'M%';+-------------------+| 'MySQL' LIKE 'M%' |+-------------------+|                 1 |+-------------------+1 row in set (0.00 sec)

%是指任意一位或者多位

mysql> SELECT * FROM tdb_test;+----+------------+-----------+| id | first_name | last_name |+----+------------+-----------+|  1 | A          | B         ||  2 | Jack       | Bob       ||  3 | tom%       | 123       |+----+------------+-----------+3 rows in set (0.00 sec)

例如查询，姓名中包含o的用户

mysql> SELECT * FROM tdb_test WHERE first_name LIKE '%o%';+----+------------+-----------+| id | first_name | last_name |+----+------------+-----------+|  3 | tom%       | 123       |+----+------------+-----------+1 row in set (0.00 sec)

例如查询，姓名中包含%的用户

mysql> SELECT * FROM tdb_test WHERE first_name LIKE '%%%';+----+------------+-----------+| id | first_name | last_name |+----+------------+-----------+|  1 | A          | B         ||  2 | Jack       | Bob       ||  3 | tom%       | 123       |+----+------------+-----------+3 rows in set (0.00 sec)

发现显示的是所有查询结果。因为mysql会认为上述的%都为通配符。正确操作如下

mysql> SELECT * FROM tdb_test WHERE first_name LIKE '%1%%' ESCAPE '1';+----+------------+-----------+| id | first_name | last_name |+----+------------+-----------+|  3 | tom%       | 123       |+----+------------+-----------+1 row in set (0.00 sec)

注：%：任意个字符.

_:任意一个字符。