MySQL字符函数

深碍√TFBOYSˉ_ 2022-07-18 05:57 215阅读 0赞

![Center][]

![Center 1][]

**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()数字格式化**

**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)

%是指任意一位或者多位

例如查询，姓名中包含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)

注：%：任意个字符.

\_:任意一个字符。

[Center]: /images/20220717/b75b119ab2cd4ea1970dafcbdada01df.png
[Center 1]: /images/20220717/8a4d3b51309c4681b76e56f25ca8d6e8.png