mysql 函数

函数名称 描述  

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_LENGTH()	Return number of characters in argument
CHAR()	Return the character for each integer passed
CHARACTER_LENGTH()	Synonym for CHAR_LENGTH()
CONCAT_WS()	Return concatenate with separator
CONCAT()	Return concatenated string
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()	Return the index (position) of the first argument in the subsequent arguments
FIND_IN_SET()	Return the index position of the first argument within the second argument
FORMAT()	Return a number formatted to specified number of decimal places
HEX()	Return a hexadecimal representation of a decimal or string value
INSERT()	Insert a substring at the specified position up to the 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	Pattern matching using regular expressions
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	Synonym for REGEXP
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_INDEX()	Return a substring from a string before the specified number of occurrences of the delimiter
SUBSTRING()	Return the substring as specified
TRIM()	Remove leading and trailing spaces
UCASE()	Synonym for UPPER()
UNHEX()	Return a string containing hex representation of a number
UPPER()	Convert to uppercase

1 函数 ASCII() 返回字符的ASCII值

mysql> select ascii('a');
+------------+
| ascii('a') |
+------------+
|         97 |
+------------+
1 row in set

mysql> select ascii('b');
+------------+
| ascii('b') |
+------------+
|         98 |
+------------+
1 row in set

 2 bin()函数获得某数字的二进制

mysql> select bin(1);
+--------+
| bin(1) |
+--------+
| 1      |
+--------+
1 row in set

mysql> select bin(3);
+--------+
| bin(3) |
+--------+
| 11     |
+--------+
1 row in set

 

3 BIT_LENGTH() 获得某参数的比特位数

 

mysql> select bit_length('a');
+-----------------+
| bit_length('a') |
+-----------------+
|               8 |
+-----------------+
1 row in set
mysql> select bit_length('ab');
+------------------+
| bit_length('ab') |
+------------------+
|               16 |
+------------------+
1 row in set

 4 CHAR_LENGTH() 获得字符长度

 

 mysql> select char_length('a');
+------------------+
| char_length('a') |
+------------------+
|                1 |
+------------------+
1 row in set
mysql> select char_length(12);
+-----------------+
| char_length(12) |
+-----------------+
|               2 |
+-----------------+
1 row in set

  5 CONCAT_WS() 拼接字符串


 

mysql> select concat_ws('-','a','b');
+------------------------+
| concat_ws('-','a','b') |
+------------------------+
| a-b                    |
+------------------------+
1 row in set
mysql> select concat_ws(',','aa','bb','cc');
+-------------------------------+
| concat_ws(',','aa','bb','cc') |
+-------------------------------+
| aa,bb,cc                      |
+-------------------------------+
1 row in set

 6 CONCAT() 直接拼接字符串

  

mysql> select concat('aa','bb');
+-------------------+
| concat('aa','bb') |
+-------------------+
| aabb              |
+-------------------+
1 row in set

mysql> select concat('a','c','b');
+---------------------+
| concat('a','c','b') |
+---------------------+
| acb                 |
+---------------------+
1 row in set

 7 CHAR() 将参数解释为整数并且返回由这些整数的ascii代码字符组成的一个字符串。null值被跳过。

 

mysql> select char(77);
+----------+
| char(77) |
+----------+
| M        |
+----------+
1 row in set

 8 field(str,str1,str2,str3,...) 

 

 

mysql> select field('ee','eaa','eee','ee');
+------------------------------+
| field('ee','eaa','eee','ee') |
+------------------------------+
|                            3 |
+------------------------------+
1 row in set

 9 find_in_set(str,strlist) 

 

如果字符串str在由n子串组成的表strlist之中，返回一个1到n的值。一个字符串表是被“,”分隔的子串组成的一个字符串。如果第一个参数是一个常数字符串并且第二个参数是一种类型为set的列，find_in_set()函数被优化而使用位运算！如果str不是在strlist里面或如果strlist是空字符串，返回0。如果任何一个参数是null，返回null。如果第一个参数包含一个“,”，该函数将工作不正常。

mysql> select find_in_set('bb','a,b,c,d,e,bb,ee');
+-------------------------------------+
| find_in_set('bb','a,b,c,d,e,bb,ee') |
+-------------------------------------+
|                                   6 |
+-------------------------------------+
1 row in set

 10 format()函数在mysql中是数据内容格式化的

mysql> select format(23.5666,3);
+-------------------+
| format(23.5666,3) |
+-------------------+
| 23.567            |
+-------------------+
1 row in set

 11 LCASE 转换成小写

mysql> select LCASE('AA');
+-------------+
| LCASE('AA') |
+-------------+
| aa          |
+-------------+
1 row in set

 12 LEFT(str,len) 返回字符串str的最左面len个字符。

mysql> select left('adfdfeedf',3);
+---------------------+
| left('adfdfeedf',3) |
+---------------------+
| adf                 |
+---------------------+
1 row in set

 13  类似subString，用于截取字符串

mysql> select mid('abcdefg',2,4);
+--------------------+
| mid('abcdefg',2,4) |
+--------------------+
| bcde               |
+--------------------+
1 row in set

 13 REPEAT 返回由重复countTimes次的字符串str组成的一个字符串。如果count <= 0，返回一个空字符串。如果str或count是NULL，返回NULL。

mysql> select repeat('ab',5);
+----------------+
| repeat('ab',5) |
+----------------+
| ababababab     |
+----------------+
1 row in set