mysql 中文字符 函数_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)

注：%：任意个字符.

_:任意一个字符。

以上就是MySQL字符函数的详细介绍的内容，更多相关内容请关注PHP中文网(www.php.cn)！

本文原创发布php中文网，转载请注明出处，感谢您的尊重！