摘要：MySQL中的常用字符串函数介绍及实测。
MySQL的字符串函数非常丰富，几乎涵盖了能想到的各种操作，下表就是5.7版本中支持的字符串函数和操作：
| Name | Description |
|---|---|
| ASCII() | Return numeric value of left-most character |
| BIN() | Return a string containing binary representation of a number |
| BIT_LENGTH() | Return length of argument in bits |
| CHAR() | Return the character for each integer passed |
| CHAR_LENGTH() | Return number of characters in argument |
| CHARACTER_LENGTH() | Synonym for CHAR_LENGTH() |
| CONCAT() | Return concatenated string |
| CONCAT_WS() | Return concatenate with separator |
| ELT() | Return string at index number |
| EXPORT_SET() | Return a string such that for every bit set in the value bits, you get an on string and for every unset bit, you get an off string |
| FIELD() | Index (position) of first argument in subsequent arguments |
| FIND_IN_SET() | Index (position) of first argument within second argument |
| FORMAT() | Return a number formatted to specified number of decimal places |
| FROM_BASE64() | Decode base64 encoded string and return result |
| HEX() | Hexadecimal representation of decimal or string value |
| INSERT() | Insert substring at specified position up to specified number of characters |
| INSTR() | Return the index of the first occurrence of substring |
| LCASE() | Synonym for LOWER() |
| LEFT() | Return the leftmost number of characters as specified |
| LENGTH() | Return the length of a string in bytes |
| LIKE | Simple pattern matching |
| LOAD_FILE() | Load the named file |
| LOCATE() | Return the position of the first occurrence of substring |
| LOWER() | Return the argument in lowercase |
| LPAD() | Return the string argument, left-padded with the specified string |
| LTRIM() | Remove leading spaces |
| MAKE_SET() | Return a set of comma-separated strings that have the corresponding bit in bits set |
| MATCH | Perform full-text search |
| MID() | Return a substring starting from the specified position |
| NOT LIKE | Negation of simple pattern matching |
| NOT REGEXP | Negation of REGEXP |
| OCT() | Return a string containing octal representation of a number |
| OCTET_LENGTH() | Synonym for LENGTH() |
| ORD() | Return character code for leftmost character of the argument |
| POSITION() | Synonym for LOCATE() |
| QUOTE() | Escape the argument for use in an SQL statement |
| REGEXP | Whether string matches regular expression |
| REPEAT() | Repeat a string the specified number of times |
| REPLACE() | Replace occurrences of a specified string |
| REVERSE() | Reverse the characters in a string |
| RIGHT() | Return the specified rightmost number of characters |
| RLIKE | Whether string matches regular expression |
| RPAD() | Append string the specified number of times |
| RTRIM() | Remove trailing spaces |
| SOUNDEX() | Return a soundex string |
| SOUNDS LIKE | Compare sounds |
| SPACE() | Return a string of the specified number of spaces |
| STRCMP() | Compare two strings |
| SUBSTR() | Return the substring as specified |
| SUBSTRING() | Return the substring as specified |
| SUBSTRING_INDEX() | Return a substring from a string before the specified number of occurrences of the delimiter |
| TO_BASE64() | Return the argument converted to a base-64 string |
| TRIM() | Remove leading and trailing spaces |
| UCASE() | Synonym for UPPER() |
| UNHEX() | Return a string containing hex representation of a number |
| UPPER() | Convert to uppercase |
| WEIGHT_STRING() | Return the weight string for a string |
我们通过实例来研究下常用函数的用法。
- CONCAT(str1,str2,…)，把传入的参数连接成为一个字符串
root@database-one 05:56: [gftest]> SELECT CONCAT('My', 'S', 'QL');
+-------------------------+
| CONCAT('My', 'S', 'QL') |
+-------------------------+
| MySQL |
+-------------------------+
1 row in set (0.01 sec)
root@database-one 05:57: [gftest]> SELECT CONCAT('My', NULL, 'QL');
+--------------------------+
| CONCAT('My', NULL, 'QL') |
+--------------------------+
| NULL |
+--------------------------+
1 row in set (0.01 sec)
root@database-one 05:57: [gftest]> SELECT CONCAT(14.3);
+--------------+
| CONCAT(14.3) |
+--------------+
| 14.3 |
+--------------+
1 row in set (0.01 sec)
上面可以看到，任何字符串与NULL连接的结构都是NULL。
- INSERT(str,pos,len,newstr)，将字符串str从第pos位置开始，len长的字串替换为字符串newstr
root@database-one 06:03: [gftest]> SELECT INSERT('Quadratic', 3, 4, 'What');
+-----------------------------------+
| INSERT('Quadratic', 3, 4, 'What') |
+-----------------------------------+
| QuWhattic |
+-----------------------------------+
1 row in set (0.02 sec)
root@database-one 06:03: [gftest]> SELECT INSERT('Quadratic', -1, 4, 'What');
+------------------------------------+
| INSERT('Quadratic', -1, 4, 'What') |
+------------------------------------+
| Quadratic |
+------------------------------------+
1 row in set (0.01 sec)
root@database-one 06:03: [gftest]> SELECT INSERT('Quadratic', 3, 100, 'What');
+-------------------------------------+
| INSERT('Quadratic', 3, 100, 'What') |
+-------------------------------------+
| QuWhat |
+-------------------------------------+
1 row in set (0.01 sec)
root@database-one 06:03: [gftest]> SELECT INSERT('Quadratic', 3, 100, null);
+-----------------------------------+
| INSERT('Quadratic', 3, 100, null) |
+-----------------------------------+
| NULL |
+-----------------------------------+
1 row in set (0.01 sec)
root@database-one 06:04: [gftest]> SELECT INSERT('Quadratic', 3, null, 'What');
+--------------------------------------+
| INSERT('Quadratic', 3, null, 'What') |
+--------------------------------------+
| NULL |
+--------------------------------------+
1 row in set (0.01 sec)
上面可以看到，任何参数为NULL返回结果都是NULL。
- LOWER(str)和UPPER(str)，分别把字符串转换成小写或大写
root@database-one 06:07: [gftest]> SELECT LOWER('QUADRATICALLY');
+------------------------+
| LOWER('QUADRATICALLY') |
+------------------------+
| quadratically |
+------------------------+
1 row in set (0.01 sec)
root@database-one 06:07: [gftest]> SELECT UPPER('Hej');
+--------------+
| UPPER('Hej') |
+--------------+
| HEJ |
+--------------+
1 row in set (0.02 sec)
- LEFT(str,len)和RIGHT(str,len)，分别返回字符串最左边len个字符和最右边len个字符
root@database-one 06:11: [gftest]> SELECT LEFT('foobarbar', 5);
+----------------------+
| LEFT('foobarbar', 5) |
+----------------------+
| fooba |
+----------------------+
1 row in set (0.02 sec)
root@database-one 06:11: [gftest]> SELECT LEFT(null, 5);
+---------------+
| LEFT(null, 5) |
+---------------+
| NULL |
+---------------+
1 row in set (0.00 sec)
root@database-one 06:11: [gftest]> SELECT RIGHT('foobarbar', 4);
+-----------------------+
| RIGHT('foobarbar', 4) |
+-----------------------+
| rbar |
+-----------------------+
1 row in set (0.01 sec)
root@database-one 06:12: [gftest]> SELECT RIGHT('foobarbar', null);
+--------------------------+
| RIGHT('foobarbar', null) |
+--------------------------+
| NULL |
+--------------------------+
1 row in set (0.01 sec)
同样的，任何参数为NULL返回结果都是NULL。
- LPAD(str,len,padstr)和RPAD(str,len,padstr)，分别用字符串padstr对str最左边和最右边进行填充，直到长度为n个字符长度
root@database-one 06:19: [gftest]> SELECT LPAD('hi',4,'**');
+-------------------+
| LPAD('hi',4,'**') |
+-------------------+
| **hi |
+-------------------+
1 row in set (0.00 sec)
root@database-one 06:20: [gftest]> SELECT LPAD('hi',1,'**');
+-------------------+
| LPAD('hi',1,'**') |
+-------------------+
| h |
+-------------------+
1 row in set (0.04 sec)
root@database-one 06:20: [gftest]> SELECT RPAD('hi',5,'*');
+------------------+
| RPAD('hi',5,'*') |
+------------------+
| hi*** |
+------------------+
1 row in set (0.01 sec)
root@database-one 06:20: [gftest]> SELECT RPAD('hi',1,'*');
+------------------+
| RPAD('hi',1,'*') |
+------------------+
| h |
+------------------+
1 row in set (0.02 sec)
上面可以看到，如果str比len长，返回值将会被截短到len长度。
- LTRIM(str)和RTRIM(str)，分别去掉字符串str左侧和右侧的空格
root@database-one 06:26: [gftest]> SELECT LTRIM(' barbar');
+------------------+
| LTRIM(' barbar') |
+------------------+
| barbar |
+------------------+
1 row in set (0.02 sec)
root@database-one 06:26: [gftest]> SELECT RTRIM('barbar ');
+------------------+
| RTRIM('barbar ') |
+------------------+
| barbar |
+------------------+
1 row in set (0.01 sec)
- REPEAT(str,count)，返回str重复count次的结果
root@database-one 06:30: [gftest]> SELECT REPEAT('MySQL', 3);
+--------------------+
| REPEAT('MySQL', 3) |
+--------------------+
| MySQLMySQLMySQL |
+--------------------+
1 row in set (0.00 sec)
root@database-one 06:30: [gftest]> SELECT REPEAT('MySQL', 0);
+--------------------+
| REPEAT('MySQL', 0) |
+--------------------+
| |
+--------------------+
1 row in set (0.01 sec)
root@database-one 06:31: [gftest]> SELECT REPEAT('MySQL', null);
+-----------------------+
| REPEAT('MySQL', null) |
+-----------------------+
| NULL |
+-----------------------+
1 row in set (0.00 sec)
上面可以看到，如果count小于1，将返回空字符串，任何参数为NULL返回结果都是NULL。
- REPLACE(str,from_str,to_str)，用字符串to_str替换字符串str中所有的字符串from_str
root@database-one 06:36: [gftest]> SELECT REPLACE('www.mysql.com', 'w', 'Ww');
+-------------------------------------+
| REPLACE('www.mysql.com', 'w', 'Ww') |
+-------------------------------------+
| WwWwWw.mysql.com |
+-------------------------------------+
1 row in set (0.01 sec)
- STRCMP(expr1,expr2)，比较字符串expr1和expr2的ASCII码值大小
如果expr1比expr2小，返回-1；如果expr1与expr2相等，返回0；如果expr1比expr2大，返回1。
root@database-one 06:41: [gftest]> SELECT STRCMP('text', 'text2'),STRCMP('text', 'text'),STRCMP('text2', 'text');
+-------------------------+------------------------+-------------------------+
| STRCMP('text', 'text2') | STRCMP('text', 'text') | STRCMP('text2', 'text') |
+-------------------------+------------------------+-------------------------+
| -1 | 0 | 1 |
+-------------------------+------------------------+-------------------------+
1 row in set (0.00 sec)
- SUBSTRING(str,pos), SUBSTRING(str FROM pos), SUBSTRING(str,pos,len),
SUBSTRING(str FROM pos FOR len)，返回字符串str中第pos位置开始len长度的字符串
root@database-one 06:45: [gftest]> SELECT SUBSTRING('Quadratically',5);
+------------------------------+
| SUBSTRING('Quadratically',5) |
+------------------------------+
| ratically |
+------------------------------+
1 row in set (0.00 sec)
root@database-one 06:45: [gftest]> SELECT SUBSTRING('foobarbar' FROM 4);
+-------------------------------+
| SUBSTRING('foobarbar' FROM 4) |
+-------------------------------+
| barbar |
+-------------------------------+
1 row in set (0.00 sec)
root@database-one 06:45: [gftest]> SELECT SUBSTRING('Quadratically',5,6);
+--------------------------------+
| SUBSTRING('Quadratically',5,6) |
+--------------------------------+
| ratica |
+--------------------------------+
1 row in set (0.00 sec)
root@database-one 06:45: [gftest]> SELECT SUBSTRING('Sakila', -3);
+-------------------------+
| SUBSTRING('Sakila', -3) |
+-------------------------+
| ila |
+-------------------------+
1 row in set (0.00 sec)
root@database-one 06:46: [gftest]> SELECT SUBSTRING('Sakila', -5, 3);
+----------------------------+
| SUBSTRING('Sakila', -5, 3) |
+----------------------------+
| aki |
+----------------------------+
1 row in set (0.00 sec)
root@database-one 06:46: [gftest]> SELECT SUBSTRING('Sakila' FROM -4 FOR 2);
+-----------------------------------+
| SUBSTRING('Sakila' FROM -4 FOR 2) |
+-----------------------------------+
| ki |
+-----------------------------------+
1 row in set (0.00 sec)
从上面的例子可以看到，当pos为正数时，将从str的开头往后数来确定pos的位置，当pos为负数时，将从str的结尾往前数来确定pos的位置。
另外，SUBSTR() 是 SUBSTRING()的同义词。如果len小于1，将返回空字符串。任何参数为NULL返回结果都是NULL。
root@database-one 06:49: [gftest]> SELECT SUBSTR('Quadratically',5,0);
+-----------------------------+
| SUBSTR('Quadratically',5,0) |
+-----------------------------+
| |
+-----------------------------+
1 row in set (0.00 sec)
root@database-one 06:49: [gftest]> SELECT SUBSTR('Quadratically',5,null);
+--------------------------------+
| SUBSTR('Quadratically',5,null) |
+--------------------------------+
| NULL |
+--------------------------------+
1 row in set (0.01 sec)
root@database-one 06:50: [gftest]> SELECT SUBSTR('Quadratically',null,1);
+--------------------------------+
| SUBSTR('Quadratically',null,1) |
+--------------------------------+
| NULL |
+--------------------------------+
1 row in set (0.02 sec)
© 2020, morinson. 版权所有. 欢迎转载，但请保留作者及出处。