NOW() Correct
NOW () Incorrect
If you start the server in ANSI mode with the --ansi option, it will allow spaces after function names, although a side-effect is that all function names become reserved words. You may also be able to select this behavior on a connection-specific basis, depending on the client program. For example, you can start mysql with the --ignore-space option; in C programs, you can call mysql_real_connect() with the CLIENT_IGNORE_SPACE option.
In most cases, multiple arguments to a function are separated by commas. Spaces are allowed around function arguments:
CONCAT('abc','def') This is okay
CONCAT( 'abc' , 'def' ) This is okay, too
There are a few exceptions to this syntax, such as TRIM() or EXTRACT():
TRIM(' ' FROM ' x ') 'x'
EXTRACT(YEAR FROM '2003-01-01') 2003
Each function entry describes its allowable syntax.
Comparison Functions
GREATEST(expr1,expr2,...)
Returns the largest argument, where "largest" is defined according to the following rules:
If the function is called in an integer context or all its arguments are integers, the arguments are compared as integers.
If the function is called in a floating-point context or all its arguments are floating-point values, the arguments are compared as floating-point values.
If neither of the preceding two rules applies, the arguments are compared as strings. The comparisons are not case sensitive unless some argument is a binary string.
GREATEST(2,3,1) 3
GREATEST(38.5,94.2,-1) 94.2
GREATEST('a','ab','abc') 'abc'
GREATEST(1,3,5) 5
GREATEST('A','b','C') 'C'
GREATEST(BINARY 'A','b','C') 'b'
GREATEST() was introduced in MySQL 3.22.5. In earlier versions, you can use MAX() instead.
IF(expr1,expr2,expr3)
If expr1 is true (not 0 or NULL), returns expr2; otherwise, it returns expr3. IF() returns a number or string according to the context in which it is used.
IF(1,'true','false') 'true'
IF(0,'true','false') 'false'
IF(NULL,'true','false') 'false'
IF(1.3,'non-zero','zero') 'non-zero'
IF(0.3,'non-zero','zero') 'zero'
IF(0.3 != 0,'non-zero','zero') 'non-zero'
expr1 is evaluated as an integer value, and the last three examples indicate how this behavior can catch you unaware if you're not careful. 1.3 converts to the integer value 1, which is true. But 0.3 converts to the integer value 0, which is false. The last example shows the proper way to use a floating-point number: Test the number using a comparison expression. The comparison treats the number correctly as a floating-point value and produces a true or false comparison result as an integer 1 or 0, as required by IF().
IFNULL(expr1,expr2)
Returns expr2 if the value of the expression expr1 is NULL; otherwise, it returns expr1. IFNULL() returns a number or string depending on the context in which it is used.
IFNULL(NULL,'null') 'null'
IFNULL('not null','null') 'not null'
INTERVAL(n,n1,n2,...)
Returns 0 if n < n1, 1 if n < n2, and so on or –1 if n is NULL. The values n1, n2, ... must be in strictly increasing order (n1 < n2 < ...) because a fast binary search is used. Otherwise, INTERVAL() will behave unpredictably.
INTERVAL(1.1,0,1,2) 2
INTERVAL(7,1,3,5,7,9) 4
ISNULL(expr)
Returns 1 if the value of the expression expr is NULL; otherwise, it returns 0.
ISNULL(NULL) 1
ISNULL(0) 0
ISNULL(1) 0
LEAST(expr1,expr2,...)
Returns the smallest argument, where "smallest" is defined using the same comparison rules as for the GREATEST() function.
LEAST(2,3,1) 1
LEAST(38.5,94.2,-1) -1.0
LEAST('a','ab','abc') 'a'
LEAST() was introduced in MySQL 3.22.5. In earlier versions, you can use MIN() instead.
NULLIF(expr1,expr2)
Returns expr1 if the two expression values differ, NULL if they are the same.
NULLIF(3,4) 3
NULLIF(3,3) NULL
NULLIF() was introduced in MySQL 3.23.19.
STRCMP(str1,str2)
This function returns 1, 0, or –1, depending on whether the first argument is lexically greater than, equal to, or less than the second argument. If either argument is NULL, the function returns NULL. As of MySQL 4.0.0, the comparison is not case sensitive unless either argument is a binary string:
STRCMP('a','a') 0
STRCMP('a','A') 0
STRCMP('A','a') 0
STRCMP(BINARY 'a','A') 1
STRCMP(BINARY 'A','a') -1
Prior to MySQL 4.0.0, the comparison is case sensitive:
STRCMP('a','a') 0
STRCMP('a','A') 1
STRCMP('A','a') -1
Cast Functions
These functions convert values from one type to another.
CAST(expr AS type)
Cast an expression value expr to a given type. The type value can be BINARY (binary string), DATE, DATETIME, TIME, SIGNED, SIGNED INTEGER, UNSIGNED, or UNSIGNED INTEGER.
CAST(304 AS BINARY) '304'
CAST(-1 AS UNSIGNED) 18446744073709551615
CAST() can be useful for forcing columns to have a particular type when creating a new table with CREATE TABLE ... SELECT.
mysql> CREATE TABLE t SELECT CAST(20020101 AS DATE) AS date_val;
mysql> SHOW COLUMNS FROM t;
+----------+------+------+-----+------------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+------+------+-----+------------+-------+
| date_val | date | | | 0000-00-00 | |
+----------+------+------+-----+------------+-------+
mysql> SELECT * FROM t;
+------------+
| date_val |
+------------+
| 2002-01-01 |
+------------+
CAST() was introduced in MySQL 4.0.2. It is similar to CONVERT(), but CAST() has ANSI SQL syntax, whereas CONVERT() has ODBC syntax.
CONVERT(expr,type)
CONVERT(expr USING charset)
The first form of CONVERT() serves the same purpose as CAST(), but has slightly different syntax. The expr and type arguments have the same meaning. The USING form converts the value to use a given character set.
CONVERT(304,BINARY) '304'
CONVERT(-1,UNSIGNED) 18446744073709551615
CONVERT('abc' USING utf8); 'abc'
CONVERT() was introduced in MySQL 4.0.2. The form that has USING syntax was introduced in MySQL 4.1.0.
Numeric Functions
Numeric functions return NULL if an error occurs. For example, if you pass arguments to the function that are out of range or otherwise invalid, the function will return NULL.
ABS(x)
Returns the absolute value of x.
ABS(13.5) 13.5
ABS(-13.5) 13.5
ACOS(x)
Returns the arccosine of x or NULL if x is not in the range from–1 to 1.
ACOS(1) 0.000000
ACOS(0) 1.570796
ACOS(-1) 3.141593
ASIN(x)
Returns the arcsine of x or NULL if x is not in the range from –1 to 1.
ASIN(1) 1.570796
ASIN(0) 0.000000
ASIN(-1) -1.570796
ATAN(x)
ATAN(y,x)
The one-argument form of ATAN() returns the arctangent of x. The two-argument form is a synonym for ATAN2().
ATAN(1) 0.785398
ATAN(0) 0.000000
ATAN(-1) -0.785398
ATAN2(y,x)
This is like ATAN(y/x), but it uses the signs of both arguments to determine the quadrant of the return value.
ATAN2(1,1) 0.785398
ATAN2(1,-1) 2.356194
ATAN2(-1,1) -0.785398
ATAN2(-1,-1) -2.356194
CEILING(x)
Returns the smallest integer not less than x. The return type is always a BIGINT value.
CEILING(3.8) 4
CEILING(-3.8) -3
COS(x)
Returns the cosine of x, where x is measured in radians.
COS(0) 1.000000
COS(PI()) -1.000000
COS(PI()/2) 0.000000
COT(x)
Returns the cotangent of x, where x is measured in radians.
COT(PI()/2) 0.00000000
COT(PI()/4) 1.00000000
CRC32(str)
Computes a cyclic redundancy check value from the argument, which is treated as a string. The return value is a 32-bit unsigned value in the range from 0 to 232–1 or NULL if the argument is NULL.
CRC32('xyz') 3951999591
CRC32('0') 4108050209
CRC32(0) 4108050209
CRC32(NULL) NULL
CRC32() was introduced in MySQL 4.1.0.
DEGREES(x)
Returns the value of x, converted from radians to degrees.
DEGREES(PI()) 180
DEGREES(PI()*2) 360
DEGREES(PI()/2) 90
DEGREES(-PI()) -180
EXP(x)
Returns ex, where e is the base of natural logarithms.
EXP(1) 2.718282
EXP(2) 7.389056
EXP(-1) 0.367879
1/EXP(1) 0.36787944
FLOOR(x)
Returns the largest integer not greater than x. The return type is always a BIGINT value.
FLOOR(3.8) 3
FLOOR(-3.8) -4
LN(x)
This is a synonym for LOG(); it was introduced in MySQL 4.0.3.
LOG(x)
LOG(b, x)
The one-argument form of LOG() returns the natural (base e) logarithm of x.
LOG(0) NULL
LOG(1) 0.000000
LOG(2) 0.693147
LOG(EXP(1)) 1.000000
The two-argument form returns the logarithm of x to the base b.
LOG(10,100) 2.000000
LOG(2,256) 8.000000
The two-argument form was introduced in MySQL 4.0.3. For earlier versions, you can compute the logarithm of x to the base b using LOG(x)/LOG(b).
LOG(100)/LOG(10) 2.00000000
LOG10(100) 2.000000
LOG10(x)
Returns the logarithm of x to the base 10.
LOG10(0) NULL
LOG10(10) 1.000000
LOG10(100) 2.000000
LOG2(x)
Returns the logarithm of x to the base 2.
LOG2(0) NULL
LOG2(255) 7.994353
LOG2(32767) 14.999956
LOG2() tells you the "width" of a value in bits. One use for this is to assess the amount of storage required for the value.
LOG2() was introduced in MySQL 4.0.3.
MOD(m,n )
MOD() performs a modulo operation. MOD(m,n ) is the same as m %n. See the "Arithmetic Operators" section earlier in this appendix.
PI()
Returns the value of p.
PI() 3.141593
POW(x,y )
Returns xy; that is, x raised to the power y.
POW(2,3) 8.000000
POW(2,-3) 0.125000
POW(4,.5) 2.000000
POW(16,.25) 2.000000
POWER(x,y )
This function is a synonym for POW().
RADIANS(x)
Returns the value of x, converted from degrees to radians.
RADIANS(0) 0
RADIANS(360) 6.2831853071796
RADIANS(-360) -6.2831853071796
RAND()
RAND(n)
RAND() returns a random floating-point value in the range from 0.0 to 1.0. RAND(n) does the same thing, using n as the seed value for the randomizer. All calls to RAND() with the same value of n return the same result. You can use this property when you need a repeatable sequence of numbers. (Call RAND() the first time with an argument of n, and then call it successively with no argument to get the next numbers in the sequence.)
RAND(10) 0.18109053110805
RAND(10) 0.18109053110805
RAND() 0.7502322306393
RAND() 0.20788959060599
RAND(10) 0.18109053110805
RAND() 0.7502322306393
RAND() 0.20788959060599
In the examples, notice how sequential calls to RAND() behave when you supply an argument compared to when you do not.
Seeding operations are client-specific. If one client invokes RAND(n) to seed the random number generator, that does not affect the numbers returned for other clients.
ROUND(x)
ROUND(x,d)
ROUND(x) returns the value of x, rounded to an integer.
ROUND(x,d) returns the value of x, rounded to a number with d decimal places. If d is 0, the result has no decimal point or fractional part.
ROUND(15.3) 15
ROUND(15.5) 16
ROUND(-33.27834,2) -33.28
ROUND(1,4) 1.0000
The precise behavior of ROUND() depends on the rounding behavior of your underlying math library. This means the results from ROUND() may vary from system to system.
SIGN(x)
Returns –1, 0, or 1, depending on whether the value of x is negative, zero, or positive.
SIGN(15.803) 1
SIGN(0) 0
SIGN(-99) -1
SIN(x)
Returns the sine of x, where x is measured in radians.
SIN(0) 0.000000
SIN(PI()) 0.000000
SIN(PI()/2) 1.000000
SQRT(x)
Returns the non-negative square root of x.
SQRT(625) 25.000000
SQRT(2.25) 1.500000
SQRT(-1) NULL
TAN(x)
Returns the tangent of x, where x is measured in radians.
TAN(0) 0.000000
TAN(PI()/4) 1.000000
TRUNCATE(x,d)
Returns the value x, with the fractional part truncated to d decimal places. If d is 0, the result has no decimal point or fractional part. If d is greater than the number of decimal places in x, the fractional part is right-padded with trailing zeros to the desired width.
TRUNCATE(1.23,1) 1.2
TRUNCATE(1.23,0) 1
TRUNCATE(1.23,4) 1.2300
String Functions
Most of the functions in this section return a string result. Some of them, such as LENGTH(), take strings as arguments and return a number. For functions that operate on strings based on string positions, the position of the first (leftmost) character is 1 (not 0).
Several string functions are multi-byte safe as of MySQL 3.23.7: INSERT(), INSTR(), LCASE(), LEFT(), LOCATE(), LOWER(), MID(),POSITION(), REPLACE(), REVERSE(), RIGHT(), RTRIM(),SUBSTRING(), SUBSTRING_INDEX(), TRIM(), UCASE(), and UPPER().
ASCII(str)
Returns the ASCII code of the leftmost character of the string str. It returns 0 if str is empty or NULL if str is NULL.
ASCII('abc') 97
ASCII('') 0
ASCII(NULL) NULL
BIN(n)
Returns the value of n in binary form as a string. The following two expressions are equivalent:
BIN(n)
CONV(n,10,2)
See the description of CONV() for more information.
BIN() was introduced in MySQL 3.22.4.
CHAR(n1,n2,...)
Interprets the arguments as ASCII codes and returns a string consisting of the concatenation of the corresponding character values. NULL arguments are ignored.
CHAR(65) 'A'
CHAR(97) 'a'
CHAR(89,105,107,101,115,33) 'Yikes!'
CHARACTER_LENGTH(str)
This function is a synonym for CHAR_LENGTH().
CHAR_LENGTH(str)
This function is similar to LENGTH(), except that as of MySQL 3.23.6, multi-byte characters are each counted as having a length of 1.
CHARSET(str)
Returns the name of the character set in which the string str is represented.
CHARSET('abc') 'latin1'
CHARSET(CONVERT('abc' USING utf8)) 'utf8'
CHARSET() was introduced in MySQL 4.1.0.
COALESCE(expr1,expr2,...)
Returns the first non-NULL element in the list or NULL if no argument is non-NULL.
COALESCE(NULL,1/0,2,'a',45+97) '2'
COALESCE(NULL,1/0) NULL
COALESCE() was introduced in MySQL 3.23.3.
CONCAT(str1,str2,...)
Returns a string consisting of the concatenation of all of its arguments. Returns NULL if any argument is NULL. CONCAT() can be called with a single argument.
CONCAT('abc','def') 'abcdef'
CONCAT('abc') 'abc'
CONCAT('abc',NULL) NULL
CONCAT('Hello',', ','goodbye') 'Hello, goodbye'
If the arguments to CONCAT() have different character sets (as is possible as of MySQL 4.1), the result has the character set of the first argument.
Another way to concatenate strings is to just specify them next to each other.
'three' 'blind' 'mice' 'threeblindmice'
'abc' 'def' = 'abcdef' 1
CONCAT_WS(delim,str1,str2,...)
Returns a string consisting of the concatenation of its second and following arguments, with the delim string used as the separator between strings. Returns NULL if delim is NULL, but ignores any NULL values or empty strings in the list of strings to be concatenated.
CONCAT_WS(',','a','b','c','d') 'a,b,c,d'
CONCAT_WS('*-*','lemon','','lime',NULL,'grape') 'lemon*-*lime*-*grape'
CONV(n,from_base,to_base)
Given a number n represented in base from_base returns a string representation of n in base to_base. The result is NULL if any argument is NULL. from_base and to_base should be integers in the range from 2 to 36. n is treated as a BIGINT value (64-bit integer), but can be specified as a string because numbers in bases higher than 10 can contain non-decimal digits. (This is also the reason that CONV() returns a string; the result may contain characters from A to Z for bases 11 to 36.) The result is 0 if n is not a legal number in base from_base. (For example, if from_base is 16 and n is 'abcdefg', the result is 0 because g is not a legal hexadecimal digit.)
Non-decimal characters in n can be specified in either uppercase or lowercase. Non-decimal characters in the result will be uppercase.
Convert 14 specified as a hexadecimal number to binary:
CONV('e',16,2) '1110'
Convert 255 specified in binary to octal:
CONV(11111111,2,8) '377'
CONV('11111111',2,8) '377'
n is treated as an unsigned number by default. If you specify to_base as a negative number, n is treated as a signed number.
CONV(-10,10,16) 'FFFFFFFFFFFFFFF6'
CONV(-10,10,-16) '-A'
CONV() was introduced in MySQL 3.22.4.
ELT(n,str1,str2,...)
Returns the n-th string from the list of strings str1,str2,.... Returns NULL if n is NULL, the nth string is NULL, or there is no nth string. The index of the first string is 1. ELT() is complementary to FIELD().
ELT(3,'a','b','c','d','e') 'c'
ELT(0,'a','b','c','d','e') NULL
ELT(6,'a','b','c','d','e') NULL
ELT(FIELD('b','a','b','c'),'a','b','c') 'b'
EXPORT_SET(n,on,off,[delim,[bit_count]])
Returns a string consisting of the strings on and off, separated by the delimiter string delim. on is used to represent each bit that is set in n, and off is used to represent each bit that is not set. bit_count indicates the maximum number of bits in n to examine. The default delimiter is a comma, and the default bit_count value is 64. Returns NULL if any argument is NULL.
EXPORT_SET(7,'+','-','',5) '+++--'
EXPORT_SET(0xa,'1','0','',6) '010100'
EXPORT_SET(97,'Y','N',',',8) 'Y,N,N,N,N,Y,Y,N'
EXPORT_SET() was introduced in MySQL 3.23.2.
FIELD(str,str1,str2,...)
Finds str in the list of strings str1,str2, ... and returns the index of the matching string. Returns 0 if there is no match or if str is NULL. The index of the first string is 1. FIELD() is complementary to ELT().
FIELD('b','a','b','c') 2
FIELD('d','a','b','c') 0
FIELD(NULL,'a','b','c') 0
FIELD(ELT(2,'a','b','c'),'a','b','c') 2
FIND_IN_SET(str,str_list)
str_list is a string consisting of substrings separated by commas (that is, it is like a SET value). FIND_IN_SET() returns the index of str within str_list. Returns 0 if str is not present in str_list or NULL if either argument is NULL. The index of the first substring is 1.
FIND_IN_SET('cow','moose,cow,pig') 2
FIND_IN_SET('dog','moose,cow,pig') 0
FORMAT(x,d)
Formats the number x to d decimals using a format like 'nn,nnn.nnn' and returns the result as a string. If d is 0, the result has no decimal point or fractional part.
FORMAT(1234.56789,3) '1,234.568'
FORMAT(999999.99,2) '999,999.99'
FORMAT(999999.99,0) '1,000,000'
Note the rounding behavior exhibited by the final example.
HEX(n)
HEX(str)
With a numeric argument n, HEX() returns the value of the argument in hexadecimal form, as a string. The following two expressions are equivalent:
HEX(n)
CONV(n,10,16)
See the description of CONV() for more information.
Prior to MySQL 4.0.1, HEX() always interprets its argument as a string.
HEX(255) 'FF'
HEX('255') 'FF'
As of MySQL 4.0.1, HEX() can accept a string argument and returns a string consisting of each character in the argument represented as two hex digits:
HEX('255') '323535'
HEX('abc') '616263'
HEX() was introduced in MySQL 3.22.4.
INSERT(str,pos,len,ins_str)
Returns the string str, with the substring beginning at position pos and len characters long replaced by the string ins_str. Returns the original string if pos is out of range or NULL if any argument is NULL.
INSERT('nighttime',6,4,'fall') 'nightfall'
INSERT('sunshine',1,3,'rain or ') 'rain or shine'
INSERT('sunshine',0,3,'rain or ') 'sunshine'
INSTR(str,substr)
INSTR() is like the two-argument form of LOCATE(), but with the arguments reversed. The following two expressions are equivalent:
INSTR(str,substr)
LOCATE(substr,str)
LCASE(str)
This function is a synonym for LOWER().
LEFT(str,len)
Returns the leftmost len characters from the string str or the entire string if there aren't that many characters. Returns NULL if str is NULL. Returns the empty string if len is NULL or less than 1.
LEFT('my left foot', 2) 'my'
LEFT(NULL,10) NULL
LEFT('abc',NULL) ''
LEFT('abc',0) ''
LENGTH(str)
Returns the length of the string str.
LENGTH('abc') 3
LENGTH('') 0
LENGTH(NULL) NULL
LOCATE(substr,str)
LOCATE(substr,str,pos)
The two-argument form of LOCATE() returns the position of the first occurrence of the string substr within the string str or 0 if substr does not occur within str. Returns NULL if any argument is NULL. If the position argument pos is given, LOCATE() starts looking for substr at that position. As of MySQL 4.0.1, the test is not case sensitive unless either argument is a binary string:
LOCATE('b','abc') 2
LOCATE('b','ABC') 2
LOCATE(BINARY 'b','ABC') 0
Prior to MySQL 4.0.1, the test is case sensitive:
LOCATE('b','abc') 2
LOCATE('b','ABC') 0
LOWER(str)
Returns the string str with all the characters converted to lowercase or NULL if str is NULL.
LOWER('New York, NY') 'new york, ny'
LOWER(NULL) NULL
LPAD(str,len,pad_str)
Returns a string consisting of the value of the string str, left-padded with the string pad_str to a length of len characters. Returns NULL if any argument is NULL.
LPAD('abc',12,'def') 'defdefdefabc'
LPAD('abc',10,'.') '.......abc'
As of MySQL 3.23.29, LPAD() shortens the result to len characters:
LPAD('abc',2,'.') 'ab'
Prior to MySQL 3.23.29, LPAD() returns str if str is already len characters long:
LPAD('abc',2,'.') 'abc'
LPAD() was introduced in MySQL 3.22.2.
LTRIM(str)
Returns the string str with leftmost (leading) spaces removed or NULL if str is NULL.
LTRIM(' abc ') 'abc '
MAKE_SET(n,bit0_str,bit1_str,...)
Constructs a SET value (a string consisting of substrings separated by commas) based on the value of the integer n and the strings bit0_str,bit1_str, ... For each bit that is set in the value of n, the corresponding string is included in the result. (If bit 0 is set, the result includes bit0_str, and so on.) If n is 0, the result is the empty string. If n is NULL, the result is NULL. If any string in the list is NULL, it is ignored when constructing the result string.
MAKE_SET(8,'a','b','c','d','e') 'd'
MAKE_SET(7,'a','b','c','d','e') 'a,b,c'
MAKE_SET(2+16,'a','b','c','d','e') 'b,e'
MAKE_SET(2|16,'a','b','c','d','e') 'b,e'
MAKE_SET(-1,'a','b','c','d','e') 'a,b,c,d,e'
The last example selects every string because the value –1 has all bits turned on.
MAKE_SET() was introduced in MySQL 3.22.2.
MATCH(column_list) AGAINST(str)
MATCH(column_list) AGAINST(str IN BOOLEAN MODE)
MATCH performs a search operation using a FULLTEXT index. The MATCH list consists of one or more column names separated by commas. These must be the columns that make up a FULLTEXT index on the table you are searching. The str argument to AGAINST() indicates the word or words to search for in the given columns. Words are sequences of characters made up of letters, digits, single quotes, or underscores. The parentheses are optional for MATCH, but not for AGAINST.
MATCH produces a relevance ranking for each row. Ranks are non-negative floating-point numbers, with a rank of zero indicating that the search words were not found. Positive values indicate that at least one search word was found. Words that are present in the more than half the rows of the table are considered to have zero relevance because they are so common. In addition, MySQL has an internal list of stop words (such as "the" and "but") that are never considered relevant.
If the search string is followed by IN BOOLEAN MODE, the search results are based purely on absence or presence of the search words without regard to how often they occur in the table. For boolean searches, words in the search string can be modified with the following operators to affect how the search is done:
+ or -
A leading + or - indicates that the word must be present or absent.
< or >
A leading < or > decreases or increases a word's contribution to the relevance value calculation.
~
A leading ~ negates a word's contribution to the relevance value calculation, but does not exclude rows containing the word entirely, as - would.
*
A trailing * acts as a wildcard operator. For example, act* matches act, acts, action, and so on.
"phrase"
A phrase search can be performed by surrounding the phrase within double quotes. Each word must be present in the order given in the phrase.
()
Parentheses group words into expressions.
Words with no modifiers are treated as optional in a boolean search, just as for non-boolean searches.
It's possible to perform a boolean-mode search in the absence of a FULLTEXT index, but this can be quite slow.
FULLTEXT searching was introduced in MySQL 3.23.23, boolean mode searches in MySQL 4.0.1 and phrase searching in MySQL 4.0.2.
More information on FULLTEXT searching can be found in Chapter 3, "MySQL SQL Syntax and Use."
MID(str,pos,len)
MID(str,pos)
The three-argument form returns a substring of the string str, beginning at position pos and len characters long. The two-argument form returns the substring beginning at pos to the end of the string. Returns NULL if any argument is NULL.
MID('what a dull example',8,4) 'dull'
MID('what a dull example',8) 'dull example'
MID() is actually a synonym for SUBSTRING() and can be used with any of the forms of syntax that SUBSTRING() allows.
OCT(n)
Returns the value of n in octal form, as a string. The following two expressions are equivalent:
OCT(n)
CONV(n,10,8)
See the description of CONV() for more information.
OCT() was introduced in MySQL 3.22.4.
OCTET_LENGTH(str)
This function is a synonym for LENGTH().
POSITION(substr IN str)
This is like the two-argument form of LOCATE(). The following expressions are equivalent:
POSITION(substr IN str)
LOCATE(substr,str)
ORD(str)
Returns the ordinal value of the first character of the string str or NULL if str is NULL. If the first character is not a multi-byte character, ORD() is the same as ASCII().
ORD('abc') 97
ASCII('abc') 97
For a multi-byte character, ORD() returns a value determined from the ASCII values of the character's individual bytes b1 through bn. The formula is as follows:
( ... ((ASCII(b1)*256 + ASCII(b2)))*256 + ...) + ASCII(bn)
ORD() was introduced in MySQL 3.23.6.
QUOTE(str)
Processes its argument to return a string that is properly quoted for use in an SQL statement. This is useful for writing queries that produce other queries as their result. For non-NULL values, the return value has each single quote, ASCII NUL, backslash, and Ctrl-Z character escaped with a leading backslash, and the result is surrounded by single quotes. For NULL values, the return value is the word "NULL" without surrounding single quotes.
QUOTE('X') 'X'
QUOTE("'") '\''
QUOTE(NULL) NULL
QUOTE() was introduced in MySQL 4.0.3.
REPEAT(str,n)
Returns a string consisting of n repetitions of the string str. Returns the empty string if n is non-positive or NULL if either argument is NULL.
REPEAT('x',10) 'xxxxxxxxxx'
REPEAT('abc',3) 'abcabcabc'
REPLACE(str, from_str,to_str)
Returns a string consisting of the string str with all occurrences of the string from_str replaced by the string to_str. If to_str is empty, the effect is to delete occurrences of from_str. If from_str is empty, REPLACE() returns str unchanged. Returns NULL if any argument is NULL.
REPLACE('abracadabra','a','oh') 'ohbrohcohdohbroh'
REPLACE('abracadabra','a','') 'brcdbr'
REPLACE('abracadabra','','x') 'abracadabra'
REVERSE(str)
Returns a string consisting of the string str with the characters reversed. Returns NULL if str is NULL.
REVERSE('abracadabra') 'arbadacarba'
REVERSE('tararA ta tar a raT') 'Tar a rat at Ararat'
RIGHT(str,len)
Returns the rightmost len characters from the string str or the entire string if there aren't that many characters. Returns NULL if str is NULL. Returns the empty string if len is NULL or less than 1.
RIGHT('rightmost',4) 'most'
RPAD(str,len,pad_str)
Returns a string consisting of the value of the string str, right-padded with the string pad_str to a length of len characters. Returns NULL if any argument is NULL.
RPAD('abc',12,'def') 'abcdefdefdef'
RPAD('abc',10,'.') 'abc.......'
As of MySQL 3.23.29, RPAD() shortens the result to len characters:
RPAD('abc',2,'.') 'ab'
Prior to MySQL 3.23.29, RPAD() returns str if str is already len characters long:
RPAD('abc',2,'.') 'abc'
RPAD() was introduced in MySQL 3.22.2.
RTRIM(str)
Returns the string str with rightmost (trailing) spaces removed or NULL if str is NULL.
RTRIM(' abc ') ' abc'
SOUNDEX(str)
Returns a soundex string calculated from the string str or NULL if str is NULL. Non-alphanumeric characters in str are ignored. International non-alphabetic characters outside the range from A to Z are treated as vowels.
SOUNDEX('Cow') 'C000'
SOUNDEX('Cowl') 'C400'
SOUNDEX('Howl') 'H400'
SOUNDEX('Hello') 'H400'
SPACE(n)
Returns a string consisting of n spaces, the empty set if n is non-positive, or NULL if n is NULL.
SPACE(6) ' '
SPACE(0) ''
SPACE(NULL) NULL
SUBSTRING(str,pos)
SUBSTRING(str,pos,len)
SUBSTRING(str FROM pos)
SUBSTRING(str FROM pos FOR len)
Returns a substring from the string str beginning at position pos, or NULL if any argument is NULL. If a len argument is given, returns a substring that many characters long; otherwise, it returns the entire rightmost part of str, beginning at position pos.
SUBSTRING('abcdef',3) 'cdef'
SUBSTRING('abcdef',3,2) 'cd'
The following expressions are equivalent:
SUBSTRING(str,pos,len)
SUBSTRING(str FROM pos FOR len)
MID(str,pos,len)
SUBSTRING_INDEX(str,delim,n)
Returns a substring from the string str. If n is positive, SUBSTRING_ INDEX() finds the nth occurrence of the delimiter string delim and then returns everything to the left of that delimiter. If n is negative, SUBSTRING_INDEX() finds the nth occurrence of delim, counting back from the right end of str, and then returns everything to the right of that delimiter. If delim is not found in str, the entire string is returned. Returns NULL if any argument is NULL.
SUBSTRING_INDEX('jar-jar','j',-2) 'ar-jar'
SUBSTRING_INDEX('sampadm@localhost','@',1) 'sampadm'
SUBSTRING_INDEX('sampadm@localhost','@',-1) 'localhost'
TRIM([[LEADING | TRAILING | BOTH] [trim_str] FROM] str)
Returns the string str with leading and/or trailing instances of the string trim_str trimmed off. If LEADING is specified, TRIM() strips leading occurrences of trim_str. If TRAILING is specified, TRIM() strips trailing occurrences of trim_str. If BOTH is specified, TRIM() strips leading and trailing occurrences of trim_str. The default is BOTH if none of LEADING, TRAILING, or BOTH is specified. TRIM() strips spaces if trim_str is not specified.
TRIM('^' FROM '^^^xyz^^') 'xyz'
TRIM(LEADING '^' FROM '^^^xyz^^') 'xyz^^'
TRIM(TRAILING '^' FROM '^^^xyz^^') '^^^xyz'
TRIM(BOTH '^' FROM '^^^xyz^^') 'xyz'
TRIM(BOTH FROM ' abc ') 'abc'
TRIM(' abc ') 'abc'
UCASE(str)
This function is a synonym for UPPER().
UPPER(str)
Returns the string str with all the characters converted to uppercase, or NULL if str is NULL.
UPPER('New York, NY') 'NEW YORK, NY'
UPPER(NULL) NULL
Date and Time Functions
The date and time functions take various types of arguments. In general, a function that expects a DATE argument will also accept a DATETIME or TIMESTAMP argument, and will ignore the time part of the value. Some functions that expect a TIME value accept DATETIME or TIMESTAMP arguments and ignore the date part.
Many of the functions in this section are able to interpret numeric arguments as temporal values:
MONTH('2004-07-25') 7
MONTH(20040725) 7
Similarly, many functions that normally return temporal values return numbers when used in numeric context:
CURDATE() '2002-05-14'
CURDATE() + 0 20020514
If you don't supply legal date or time values to date and time functions, you can't expect a reasonable result. Verify your arguments first.
ADDDATE(date,INTERVAL expr interval)
This function is a synonym for DATE_ADD().
CURDATE()
Returns the current date as a string in 'CCYY-MM-DD' format or as a number in CCYYMMDD format, depending on the context in which it is used.
CURDATE() '2002-05-14'
CURDATE() + 0 20020514
CURRENT_DATE()
This function is a synonym for CURDATE(); the parentheses are optional.
CURRENT_TIME()
This function is a synonym for CURTIME(); the parentheses are optional.
CURRENT_TIMESTAMP()
This function is a synonym for NOW(); the parentheses are optional.
CURTIME()
Returns the current time of day as a string in 'hh:mm:ss' format or as a number in hhmmss format, depending on the context in which it is used.
CURTIME() '09:51:36'
CURTIME() + 0 95136
DATE_ADD(date,INTERVAL expr interval)
Takes a date or date and time value date adds a time interval to it, and returns the result. expr specifies the time value to be added to date (or subtracted, if expr begins with '-'), and interval specifies how to interpret the interval. The result is a DATE value if date is a DATE value and no time-related values are involved in calculating the result. Otherwise, the result is a DATETIME value. The result is NULL if date is not a legal date.
DATE_ADD('2002-12-01',INTERVAL 1 YEAR) '2003-12-01'
DATE_ADD('2002-12-01',INTERVAL 60 DAY) '2003-01-30'
DATE_ADD('2002-12-01',INTERVAL -3 MONTH) '2002-09-01'
DATE_ADD('2002-12-01 08:30:00',INTERVAL 12 HOUR) '2002-12-01 20:30:00'
Table C.3 shows the allowable interval values, their meanings, and the format in which values for each interval type should be specified. The keyword INTERVAL and the interval specifiers can be given in any lettercase.
Table C.3. DATE_ADD() Interval Types Type Meaning Value Format
SECOND Seconds ss
MINUTE Minutes mm
HOUR Hours hh
DAY Days DD
MONTH Months MM
YEAR Years YY
MINUTE_SECOND Minutes and seconds 'mm:ss'
HOUR_MINUTE Hours and minutes 'hh:mm'
HOUR_SECOND Hours, minutes, and seconds 'hh:mm:ss'
DAY_HOUR Days and hours 'DD hh'
DAY_MINUTE Days, hours, and minutes 'DD hh:mm'
DAY_SECOND Days, hours, minutes, and seconds 'DD hh:mm:ss'
YEAR_MONTH Years and months 'YY-MM'
The expression expr that is added to the date can be specified as a number or as a string, unless it contains non-digit characters, in which case, it must be a string. The delimiter characters can be any-punctuation character:
DATE_ADD('2002-12-01',INTERVAL '2:3' YEAR_MONTH) '2005-03-01'
DATE_ADD('2002-12-01',INTERVAL '2-3' YEAR_MONTH) '2005-03-01'
The parts of the value of expr are matched from right to left against the parts to be expected based on the interval specifier. For example, the expected format for HOUR_SECOND is 'hh:mm:ss'. An expr value of '15:21' is interpreted as '00:15:21', not as '15:21:00'.
DATE_ADD('2002-12-01 12:00:00',INTERVAL '15:21' HOUR_SECOND)
'2002-12-01 12:15:21'
If interval is YEAR, MONTH, or YEAR_MONTH and the day part of the result is larger than the number of days in the result month, the day is set to the maximum number of days in that month.
DATE_ADD('2002-12-31',INTERVAL 2 MONTH) '2003-02-28'
DATE_ADD() was introduced in MySQL 3.22.4. In addition, an alternate syntax is supported as of MySQL 3.23.4:
'2002-12-31' + INTERVAL 2 MONTH '2003-02-28'
INTERVAL 2 MONTH + '2002-12-31' '2003-02-28'
DATE_FORMAT(date,format)
Formats a date or date and time value date depending on the formatting string format and returns the resulting string. DATE_FORMAT() can be used to reformat DATE or DATETIME values from the form MySQL uses to provide any format you want.
DATE_FORMAT('2002-12-01','%M %e, %Y') 'December 1, 2002'
DATE_FORMAT('2002-12-01','The %D of %M') 'The 1st of December'
Table C.4 shows the available specifiers that can be used in the formatting string.
The '%' character preceding each format code is required. (Prior to MySQL 3.23, the '%' is allowed but is optional.) Characters present in the formatting string that are not listed in the table are copied to the result string literally.
If you refer to time specifiers for a DATE value, the time part of the value is treated as '00:00:00'.
DATE_FORMAT('2002-12-01','%i') '00'
The %v, %V, %x, and %X format specifiers were introduced in MySQL 3.23.8.
Table C.4. DATE_FORMAT() Formatting Specifiers Specifier Meaning
%S, %s Second in two-digit form (00, 01, …, 59)
%i Minute in two-digit form (00, 01, …, 59)
%H Hour in two-digit form, 24-hour time (00, 01, …, 23)
%h, %I Hour in two-digit form, 12-hour time (01, 02, …, 12)
%k Hour in numeric form, 24-hour time (0, 1, …, 23)
%l Hour in numeric form, 12-hour time (1, 2, …, 12)
%T Time in 24-hour form (hh:mm:ss)
%r Time in 12-hour form (hh:mm:ss AM or hh:mm:ss PM)
%p AM or PM
%W Weekday name (Sunday, Monday, …, Saturday)
%a Weekday name in abbreviated form (Sun, Mon, …, Sat)
%d Day of the month in two-digit form (00, 01, …, 31)
%e Day of the month in numeric form (1, 2, …, 31)
%D Day of the month with English suffix (1st, 2nd, 3rd, …)
%w Day of the week in numeric form (0=Sunday, 1=Monday, …, 6=Saturday)
%j Day of the year in three-digit form (001, 002, …, 366)
%U Week (00, …, 53), where Sunday is the first day of the week
%u Week (00, …, 53), where Monday is the first day of the week
%V Week (01, …, 53), where Sunday is the first day of the week
%v Week (01, …, 53), where Monday is the first day of the week
%M Month name (January, February, …, December)
%b Month name in abbreviated form (Jan, Feb, …, Dec)
%m Month in two-digit form (01, 02, …, 12)
%c Month in numeric form (1, 2, …, 12)
%Y Year in 4-digit form
%y Year in 2-digit form
%X Year for the week in which Sunday is the first day, 4-digit form
%x Year for the week in which Monday is the first day, 4-digit form
%% A literal '%' character
DATE_SUB(date,INTERVAL expr interval)
Performs date arithmetic in the same manner as DATE_ADD() except that expr is subtracted from the date value date. See DATE_ADD() for more information.
DATE_SUB('2002-12-01',INTERVAL 1 MONTH) '2002-11-01'
DATE_SUB('2002-12-01',INTERVAL '13-2' YEAR_MONTH) '1989-10-01'
DATE_SUB('2002-12-01 04:53:12',INTERVAL '13-2' MINUTE_SECOND)
'2002-12-01 04:40:10'
DATE_SUB('2002-12-01 04:53:12',INTERVAL '13-2' HOUR_MINUTE)
'2002-11-30 15:51:12'
DATE_SUB() was introduced in MySQL 3.22.4. In addition, an alternate syntax is supported as of MySQL 3.23.4:
'2002-12-01' - INTERVAL 1 MONTH '2002-11-01'
DAYNAME(date)
Returns a string containing the weekday name for the date value date.
DAYNAME('2002-12-01') 'Sunday'
DAYNAME('1900-12-01') 'Saturday'
DAYOFMONTH(date)
Returns the numeric value of the day of the month for the date value date in the range from 1 to 31.
DAYOFMONTH('2002-12-01') 1
DAYOFMONTH('2002-12-25') 25
DAYOFWEEK(date)
Returns the numeric value of the weekday for the date value date. Weekday values are in the range from 1 for Sunday to 7 for Saturday, per the ODBC standard. See also the WEEKDAY() function.
DAYOFWEEK('2002-12-08') 1
DAYNAME('2002-12-08') 'Sunday'
DAYOFWEEK('2002-12-14') 7
DAYNAME('2002-12-14') 'Saturday'
DAYOFYEAR(date)
Returns the numeric value of the day of the year for the date value date in the range from 1 to 366.
DAYOFYEAR('2002-12-01') 335
DAYOFYEAR('2004-12-31') 366
EXTRACT(interval FROM datetime)
Returns the part of the date and time value datetime indicated by interval, which can be any of the interval specifiers that are allowed for DATE_ADD().
EXTRACT(YEAR FROM '2002-12-01 13:42:19') 2002
EXTRACT(MONTH FROM '2002-12-01 13:42:19') 12
EXTRACT(DAY FROM '2002-12-01 13:42:19') 1
EXTRACT(HOUR_MINUTE FROM '2002-12-01 13:42:19') 1342
EXTRACT(SECOND FROM '2002-12-01 13:42:19') 19
As of MySQL 3.23.39, EXTRACT() can be used with dates that have "missing" parts.
EXTRACT(YEAR FROM '2004-00-12') 2004
EXTRACT(MONTH FROM '2004-00-12') 0
EXTRACT(DAY FROM '2004-00-12') 12
EXTRACT() was introduced in MySQL 3.23.0.
FROM_DAYS(n)
Given a numeric value n representing the number of days since the year 0 (typically obtained by calling TO_DAYS()), returns the corresponding date.
TO_DAYS('2009-12-01') 734107
FROM_DAYS(734107 + 3) '2009-12-04'
FROM_DAYS() is intended only for dates covered by the Gregorian calendar (1582 on).
FROM_UNIXTIME(unix_timestamp)
FROM_UNIXTIME(unix_timestamp,format)
Given a UNIX timestamp value unix_timestamp such as is returned by UNIX_TIMESTAMP(), returns a date and time value as a string in 'CCYY-MM-DD hh:mm:ss' format or as a number in CCYYMMDDhhmmss format, depending on the context in which it is used. If the format argument is given, the return value is formatted as a string, just as it would be by the DATE_FORMAT() function.
UNIX_TIMESTAMP() 1021389416
FROM_UNIXTIME(1021389416) '2002-05-14 10:16:56'
FROM_UNIXTIME(1021389416,'%Y') '2002'
HOUR(time)
Returns the numeric value of the hour for the time value time in the range from 0 to 23.
HOUR('12:31:58') 12
HOUR(123158) 12
MINUTE(time)
Returns the numeric value of the minute for the time value time in the range from 0 to 59.
MINUTE('12:31:58') 31
MINUTE(123158) 31
MONTH(date)
Returns the numeric value of the month of the year for the date value date in the range from 1 to 12.
MONTH('2002-12-01') 12
MONTH(20021201) 12
MONTHNAME(date)
Returns a string containing the month name for the date value date.
MONTHNAME('2002-12-01') 'December'
MONTHNAME(20021201) 'December'
NOW()
Returns the current date and time as a string in 'CCYY-MM-DDhh:mm:ss' format or as a number in CCYYMMDDhhmmss format, depending on the context in which it is used.
NOW() '2002-05-14 10:19:20'
NOW() + 0 20020514101920
PERIOD_ADD(period,n)
Adds n months to the period value period and returns the result. The return value format is CCYYMM. The period argument format can be CCYYMM or YYMM (neither is a date value).
PERIOD_ADD(200202,12) 200302
PERIOD_ADD(0202,-3) 200111
PERIOD_DIFF(period1,period2)
Takes the difference of the period-valued arguments and returns the number of months between them. The arguments can be in the format CCYYMM or YYMM (neither is a date value).
PERIOD_DIFF(200302,200202) 12
PERIOD_DIFF(200111,0202) -3
QUARTER(date)
Returns the numeric value of the quarter of the year for the date value date in the range from 1 to 4.
QUARTER('2002-12-01') 4
QUARTER('2003-01-01') 1
SECOND(time)
Returns the numeric value of the second for the time value time in the range from 0 to 59.
SECOND('12:31:58') 58
SECOND(123158) 58
SEC_TO_TIME(seconds)
Given a number of seconds seconds, returns the corresponding time value as a string in 'hh:mm:ss' format or as a number in hhmmss format, depending on the context in which it is used.
SEC_TO_TIME(29834) '08:17:14'
SEC_TO_TIME(29834) + 0 81714
SUBDATE(date,INTERVAL expr interval)
This function is a synonym for DATE_SUB().
SYSDATE()
This function is a synonym for NOW().
TIME_FORMAT(time,format)
Formats the time value time according to the formatting string format and returns the resulting string. This function also accepts DATETIME or TIMESTAMP arguments. The formatting string is like that used by DATE_FORMAT(), but the only specifiers that can be used are those that are time-related. Other specifiers result in a NULL value or 0.
TIME_FORMAT('12:31:58','%H %i') '12 31'
TIME_FORMAT(123158,'%H %i') '12 31'
TIME_TO_SEC(time)
Given a value time representing elapsed time, returns a number representing the corresponding number of seconds. The return value can be passed to SEC_TO_TIME() to convert it back to a time.
TIME_TO_SEC('08:17:14') 29834
SEC_TO_TIME(29834) '08:17:14'
If given a DATETIME or TIMESTAMP value, TIME_TO_SEC() ignores the date part.
TIME_TO_SEC('2002-03-26 08:17:14') 29834
TO_DAYS(date)
Returns a numeric value representing the date value date converted to the number of days since the year 0. The return value can be passed to FROM_DAYS() to convert it back to a date.
TO_DAYS('2002-12-01') 731550
FROM_DAYS(731550 - 365) '2001-12-01'
If given a DATETIME or TIMESTAMP value, TO_DAYS() ignores the time part.
TO_DAYS('2002-12-01 12:14:37') 731550
TO_DAYS() is intended only for dates covered by the Gregorian calendar (1582 on).
UNIX_TIMESTAMP()
UNIX_TIMESTAMP(date)
When called with no arguments, returns the number of seconds since the UNIX epoch ('1970-01-01 00:00:00' GMT). When called with a date-valued argument date, returns the number of seconds between the epoch and that date. date can be specified several ways: as a DATE, DATETIME, or TIMESTAMP value; or as a number in the format CCYYMMDD or YYMMDD in local time.
UNIX_TIMESTAMP() 1021389578
UNIX_TIMESTAMP('2002-12-01') 1038722400
UNIX_TIMESTAMP(20021201) 1038722400
WEEK(date)
WEEK(date,first_day)
When called with a single argument, returns a number representing the week of the year for the date value date in the range from 0 to 53. The week is assumed to start on Sunday. When called with two arguments, WEEK() returns the same kind of value, but the first_day argument indicates the day on which the week starts. If first_day is 0, the week starts on Sunday. If first_day is 1, the week starts on Monday.
WEEK('2002-12-08') 50
WEEK('2002-12-08',0) 50
WEEK('2002-12-08',1) 49
A WEEK() value of 0 indicates that the date occurs prior to the first Sunday of the year (or prior to the first Monday, if the second argument is 1).
WEEK('2005-01-01') 0
DAYNAME('2005-01-01') 'Saturday'
WEEK('2006-01-01',1) 0
DAYNAME('2006-01-01') 'Sunday'
The two-argument form of WEEK() was introduced in MySQL 3.22.1.
WEEKDAY(date)
Returns the numeric value of the weekday for the date value date. Weekday values are in the range from 0 for Monday to 6 for Sunday; see also the DAYOFWEEK() function.
WEEKDAY('2002-12-08') 6
DAYNAME('2002-12-08') 'Sunday'
WEEKDAY('2002-12-16') 0
DAYNAME('2002-12-16') 'Monday'
YEAR(date)
Returns the numeric value of the year for the date value date in the range from 1000 to 9999.
YEAR('2002-12-01') 2002
YEAR(20021201) 2002
YEARWEEK(date)
YEARWEEK(date,first_day)
When called with a single argument, returns a number in the format CCYYWW representing the year and week of the year for the date value date. The week value ranges from 01 to 53. The week is assumed to start on Sunday. When called with two arguments, YEARWEEK() returns the same kind of value, but the first_day argument indicates the day on which the week starts. If first_day is 0, the week starts on Sunday. If first_day is 1, the week starts on Monday.
YEARWEEK('2006-01-01') 200601
YEARWEEK('2006-01-01',0) 200601
YEARWEEK('2006-01-01',1) 200552
Note that it is possible for the year part in the result to differ from the year in the date argument. This occurs in cases where WEEK() would return a week value of 0. YEARWEEK() never returns a week value of 0. Instead, it returns a value consisting of the previous year and the final week number from that year.
WEEK('2005-01-01') 0
YEARWEEK('2005-01-01') 200452
YEARWEEK() was introduced in MySQL 3.23.8.
Summary Functions
Summary functions are also known as aggregate functions. They calculate a single value based on a group of values. However, the resulting value is based only on non-NULL values from the selected rows (with the exception that COUNT(*) counts all rows). Summary functions can be used to summarize an entire set of values or to produce summaries for each subgroup of a set of values when the query includes a GROUP BY clause. See the "Generating Summaries" section in Chapter 1, "Getting Started with MySQL and SQL."
For the examples in this section, assume the existence of a table mytbl with an integer column mycol that contains eight rows with the values 1, 3, 5, 5, 7, 9, 9, and NULL.
mysql> SELECT mycol FROM mytbl;
+-------+
| mycol |
+-------+
| 1 |
| 3 |
| 5 |
| 5 |
| 7 |
| 9 |
| 9 |
| NULL |
+-------+
AVG(expr)
Returns the average value of expr for all non-NULL values in the selected rows.
SELECT AVG(mycol) FROM mytbl 5.5714
SELECT AVG(mycol)*2 FROM mytbl 11.1429
SELECT AVG(mycol*2) FROM mytbl 11.1429
BIT_AND(expr)
Returns the bitwise AND value of expr for all non-NULL values in the selected rows.
SELECT BIT_AND(mycol) FROM mytbl 1
BIT_OR(expr)
Returns the bitwise OR value of expr for all non-NULL values in the selected rows.
SELECT BIT_OR(mycol) FROM mytbl 15
COUNT(expr)
COUNT(*)
COUNT(DISTINCT expr1, expr2,...)
With an expression argument, returns a count of the number of non-NULL values in the result set. With an argument of *, returns a count of all rows in the result set, regardless of their contents.
SELECT COUNT(mycol) FROM mytbl 7
SELECT COUNT(*) FROM mytbl 8
For ISAM and MyISAM tables, COUNT(*) with no WHERE clause is optimized to return the number of records in the table named in the FROM clause very quickly. When more than one table is named, COUNT(*) returns the product of the number of rows in the individual tables:
SELECT COUNT(*) FROM mytbl AS m1, mytbl AS m2 64
As of MySQL 3.23.2, COUNT(DISTINCT) can be used to count the number of distinct non-NULL values.
SELECT COUNT(DISTINCT mycol) FROM mytbl 5
SELECT COUNT(DISTINCT MOD(mycol,3)) FROM mytbl 3
If multiple expressions are given, COUNT(DISTINCT) counts the number of distinct combinations of non-NULL values.
MAX(expr)
Returns the maximum value of expr for all non-NULL values in the selected rows. MAX() can also be used with strings or temporal values, in which case it returns the lexically or temporally greatest value.
SELECT MAX(mycol) FROM mytbl 9
MIN(expr)
Returns the minimum value of expr for all non-NULL values in the selected rows. MIN() can also be used with strings or temporal values, in which case it returns the lexically or temporally least value.
SELECT MIN(mycol) FROM mytbl 1
STD(expr)
Returns the standard deviation of expr for all non-NULL values in the selected rows.
SELECT STD(mycol) FROM mytbl 2.7701
STDDEV(expr)
This function is a synonym for STD().
SUM(expr)
Returns the sum of expr for all non-NULL values in the selected rows.
SELECT SUM(mycol) FROM mytbl 39
Security-Related Functions
These functions perform various security-related operations, such as encrypting or decrypting strings. Several of these functions come in pairs, with one function producing an encrypted value and the other performing decryption. Such pairs of functions typically use a string as a key or password value. You must decrypt a value with the same key used to encrypt it if you want to get back the original value. Otherwise, the decrypted result will be meaningless.
When using encryption functions that return a binary string, if you want to save the result in a database, it's conventional to use a column that is one of the BLOB types.
AES_DECRYPT(str,key_str)
Given an encrypted string str obtained as a result of a call to AES_ENCRYPT(), decrypts it using the key string key_str and returns the resulting string. Returns NULL if either argument is NULL.
AES_DECRYPT(AES_ENCRYPT('secret','scramble'),'scramble')
'secret'
AES_DECRYPT() was introduced in MySQL 4.0.2.
AES_ENCRYPT(str,key_str)
Encrypts the string str with the key string key_str using the Advanced Encryption Standard (AES) and a 128-bit key length. Returns the result as a binary string or NULL if either argument is NULL. The string can be decoded with AES_DECRYPT() using the same key string.
AES_ENCRYPT() was introduced in MySQL 4.0.2.
DECODE(str,key_str)
Given an encrypted string str obtained as a result of a call to ENCODE() decrypts it using the key string key_str., Returns the resulting string or NULL if str is NULL.
DECODE(ENCODE('secret','scramble'),'scramble') 'secret'
DES_DECRYPT(str)
DES_DECRYPT(str,key_str)
Decrypts a string str which should be an encrypted value produced by DES_ENCRYPT()., If SSL support has not been enabled or decryption fails, DES_DECRYPT() returns NULL.
If a key_str argument is given, it is used as the decryption key. If no key_str argument is given, DES_DECRYPT() uses a key from the server's DES key file to decrypt the string. The key number is determined from bits 0–6 of the first byte of the encrypted string. The location of the key file is specified at server startup time by means of the --des-key-file option. If different keys are used to encrypt and decrypt the string, the result will not be meaningful.
If str does not look like an encrypted string, DES_DECRYPT() returns the string unchanged. (This will occur, for example, if the first byte does not have bit 7 set.)
Use of the single-argument form of DES_DECRYPT() requires the SUPER privilege.
DES_DECRYPT() was introduced in MySQL 4.0.1.
DES_ENCRYPT(str)
DES_ENCRYPT(str,key_num)
DES_ENCRYPT(str,key_str)
Performs DES encryption on the string str and returns the encrypted result as a binary string. The encrypted string can be decrypted with DES_DECRYPT(). If SSL support has not been enabled or encryption fails, DES_ENCRYPT() returns NULL.
If a key_str argument is given, it is used as the encryption key. If a key_num argument is given, it should be a value from 0 to 9, indicating the key number of an entry in the server's DES key file. In this case, the encryption key is taken from that entry. If no key_str or key_num argument is given, the first key from the DES key file is used to perform encryption. (This is not necessarily the same as specifying a key_num value of 0.)
The first byte of the resulting string indicates how the string was encrypted. This byte will have bit 7 set, and bits 0–6 indicate the key number. The number is 0 to 9 to specify which key in the DES key file was used to encrypt the string, or 127 if a key_str argument was used. For example, if you encrypt a string using key 3, the first byte of the result will be 131 (that is, 128+3). If you encrypt a string with a key_str value, the first byte will be 255 (that is, 128+127).
For encryption performed on the basis of a key number, the server reads the DES key file to find the corresponding key string. The location of the key file is specified at server startup time by means of the --des-key-file option. The key file contains lines of the following format:
key_num key_str
Each key_num value should be a number from 0 to 9, and the key_str value is the corresponding encryption key. key_num and key_str should be separated by at least one whitespace character. Lines in the key file can be arranged in any order.
Unlike DES_DECRYPT(), DES_ENCRYPT() does not require the SUPER privilege to use keys from the DES key file. (Anyone is allowed to encrypt information based on the key file; only privileged users are allowed to decrypt it.)
DES_ENCRYPT() was introduced in MySQL 4.0.1.
ENCODE(str,key_str)
Encrypts the string str using the key string key_str and returns the result as a binary string. The string can be decoded with DECODE() using the same key string.
ENCRYPT(str)
ENCRYPT(str,salt )
Encrypts the string str and returns the resulting string or NULL if either argument is NULL. This is a non-reversible encryption. The salt argument, if given, should be a string with two characters. (As of MySQL 3.22.16, salt can be longer than two characters.) By specifying a salt value, the encrypted result for str will be the same each time. With no salt argument, identical calls to ENCRYPT() yield different results over time.
ENCRYPT('secret','AB') 'ABS5SGh1EL6bk'
ENCRYPT('secret','AB') 'ABS5SGh1EL6bk'
ENCRYPT('secret') '9u0hlzMKCx9N2'
ENCRYPT('secret') 'avGJcOP2vakBE'
ENCRYPT() uses the UNIX crypt() system call, so if crypt() is unavailable on your system, ENCRYPT() always returns NULL. ENCRYPT() is subject to the way crypt() operates for those systems on which it is present. In particular, on some systems, crypt() looks only at the first eight characters of the string to be encrypted.
MD5(str)
Calculates a 128-bit checksum from the string str based on the RSA Data Security, Inc. MD5 Message-Digest algorithm. The return value is a string consisting of 32 hexadecimal digits or NULL if the argument is NULL.
MD5('secret') '5ebe2294ecd0e0f08eab7690d2a6ee69'
MD5() was introduced in MySQL 3.23.2. See also the SHA1() function.
PASSWORD(str)
Given a string str, calculates and returns an encrypted password string of the form used in the MySQL grant tables. This is a non-reversible encryption.
PASSWORD('secret') '428567f408994404'
Note that PASSWORD() does not use the same algorithm as the one used on UNIX to encrypt user account passwords. For that type of encryption, use ENCRYPT().
SHA(str)
This function is a synonym for SHA1().
SHA1(str)
Calculates a 160-bit checksum from the string str using the Secure Hash Algorithm. The return value is a string consisting of 40 hexadecimal digits or NULL if the argument is NULL.
SHA1('secret') 'e5e9fa1ba31ecd1ae84f75caaa474f3a663f05f4'
SHA1() was introduced in MySQL 4.0.2. See also the MD5() function.
Miscellaneous Functions
The functions in this section do not fall into any of the other categories.
BENCHMARK(n,expr)
Evaluates the expression expr repetitively n times. BENCHMARK() is something of an unusual function in that it is intended for use within the mysql client program. Its return value is always 0, and thus is of no use. The value of interest is the elapsed time that mysql prints after displaying the result of the query:
mysql> SELECT BENCHMARK(1000000,PASSWORD('secret'));
+---------------------------------------+
| BENCHMARK(1000000,PASSWORD('secret')) |
+---------------------------------------+
| 0 |
+---------------------------------------+
1 row in set (2.35 sec)
The time is only an approximate indicator of how quickly the server evaluates the expression because it represents wall-clock time on the client, not CPU time on the server. The time can be influenced by factors such as the load on the server, whether the server is in a runnable state or swapped out when the query arrives, and so forth. You may want to execute it several times to see what a representative value is.
BENCHMARK() was introduced in MySQL 3.22.15.
BIT_COUNT(n)
Returns the number of bits that are set in the argument, which is treated as a BIGINT value (a 64-bit integer).
BIT_COUNT(0) 0
BIT_COUNT(1) 1
BIT_COUNT(2) 1
BIT_COUNT(7) 3
BIT_COUNT(-1) 64
BIT_COUNT(NULL) NULL
BIT_LENGTH(str)
Returns the length of the string str in bits or NULL if the argument is NULL.
BIT_LENGTH('abc') 24
BIT_LENGTH('a long string') 104
BIT_LENGTH() was introduced in MySQL 4.0.2 for ODBC compatibility.
CONNECTION_ID()
Returns the connection identifier for the current connection. This is the thread identifier that the server associates with the client connection.
CONNECTION_ID() 10146
CONNECTION_ID() was introduced in MySQL 3.23.14.
DATABASE()
Returns a string containing the current database name or the empty string if there is no current database.
DATABASE() 'sampdb'
FOUND_ROWS()
Returns the number of rows that a preceding SELECT statement would have returned without a LIMIT clause. For example, the following statement would return a maximum of 10 rows:
mysql> SELECT * FROM mytbl LIMIT 10;
To determine how many rows the statement would have returned without the LIMIT clause, do the following:
mysql> SELECT SQL_CALC_FOUND_ROWS * FROM mytbl LIMIT 10;
mysql> SELECT FOUND_ROWS();
FOUND_ROWS() was introduced in MySQL 4.0.0.
GET_LOCK(str,timeout)
GET_LOCK() is used in conjunction with RELEASE_LOCK() and IS_FREE_LOCK() to perform advisory (cooperative) locking. You can use the two functions to write applications that cooperate based on the status of an agreed-upon lock name.
GET_LOCK() is called with a lock name indicated by the string str and a timeout value of timeout seconds. It returns 1 if the lock was obtained successfully within the timeout period, 0 if the lock attempt failed due to timing out, or NULL if an error occurred. The timeout value determines how long to wait while attempting to obtain the lock, not the duration of the lock. After it is obtained, the lock remains in force until released.
The following call acquires a lock named 'Nellie' waiting up to 10 seconds for it:
GET_LOCK('Nellie',10)
The lock applies only to the string name itself. It does not lock a database, a table, or any rows or columns within a table. In other words, the lock does not prevent any other client from doing anything to database tables, which is why GET_LOCK() locking is advisory only—it simply allows other cooperating clients to determine whether or not the lock is in force.
A client that has a lock on a name blocks attempts by other clients to lock the name (or attempts by other threads within a multi-threaded client that maintains multiple connections to the server). Suppose client 1 locks the string 'Nellie'., If client 2 attempts to lock the same string, it will block until client 1 releases the lock or until the timeout period expires. In the former case, client 2 will acquire the lock successfully; in the latter case, it will fail.
Because two clients cannot lock a given string at the same time, applications that agree on a name can use the lock status of that name as an indicator of when it is safe to perform operations related to the name. For example, you can construct a lock name based on a unique key value for a row in a table to allow cooperative locking of that row.
To release a lock explicitly, call RELEASE_LOCK() with the lock name:
RELEASE_LOCK('Nellie')
RELEASE_LOCK() returns 1 if the lock was released successfully, 0 if the lock was held by another connection (you can only release your own locks), or NULL if no such lock exists.
Any lock held by a client is automatically released if the client issues another GET_LOCK() call because only one string at a time can be locked per client connection. In this case, the lock being held is released before the new lock is obtained, even if the lock name is the same. A lock also is released when the client's connection to the server terminates. Note that if you have a very long-running client and its connection times out due to inactivity, any lock held by the client is released.
GETLOCK(str,0) can be used as a simple poll to determine without waiting whether or not a lock on str is in force. (Of course, this will lock the string if it is not currently locked, so remember to call RELEASE_LOCK() as appropriate.)
To test the status of a lock name, invoke IS_FREE_LOCK(str), which returns 1 if the name is available (not currently being used as a lock), 0 if the name is in use, or NULL if an error occurred.
All three functions return NULL if the lock name argument is NULL.
INET_ATON(str)
Given an IP address represented as a string in dotted-quad notation, returns the integer representation of the address or NULL if the argument is not a valid IP address.
INET_ATON('64.28.67.70') 1075594054
INET_ATON('255.255.255.255') 4294967295
INET_ATON('256.255.255.255') NULL
INET_ATON('www.mysql.com') NULL
INET_ATON() was introduced in MySQL 3.23.15.
INET_NTOA(n)
Given the integer representation of an IP address, returns the corresponding dotted-quad representation as a string or NULL if the value is illegal.
INET_NTOA(1075594054) '64.28.67.70'
INET_NTOA(2130706433) '127.0.0.1'
INET_NTOA() was introduced in MySQL 3.23.15.
IS_FREE_LOCK(str)
Checks the status of the advisory lock named by str. IS_FREE_LOCK() is used in conjunction with GET_LOCK().. See the description of GET_LOCK() for details.
IS_FREE_LOCK() was introduced in MySQL 4.0.2.
LAST_INSERT_ID()
LAST_INSERT_ID(expr)
With no argument, returns the AUTO_INCREMENT value that was most recently generated during the current server session or 0 if no such value has been generated. With an argument, LAST_INSERT_ID() is intended to be used in an UPDATE statement. The result is treated the same way as an automatically generated value, which is useful for generating sequences.
More details can be found in Chapter 2. For both forms of LAST_INSERT_ID(), the value is maintained by the server on a per-connection basis and cannot be changed by other clients, even by those that cause new automatically generated values to be created.
The form of LAST_INSERT_ID() that takes an argument was introduced in MySQL 3.22.9.
LOAD_FILE(file_name)
Reads the file file_name and returns its contents as a string. The file must be located on the server, must be specified as an absolute (full) pathname, and must be world-readable to ensure that you're not trying to read a protected file. Because the file must be on the server, you must have the FILE privilege. If any of these conditions fail, LOAD_FILE() returns NULL.
LOAD_FILE() was introduced in MySQL 3.23.0.
MASTER_POS_WAIT(log_file,pos)
This function is used when testing master replication servers. It causes the master to block until the slave server reaches the given position in the log file. If the slave has already reached that position, the function returns immediately. If the slave isn't running, the master blocks until the slave is started and reaches the given position.
MASTER_POS_WAIT() returns the number of log file events it had to wait for until the slave reached the position, or NULL if an error occurred or the master server information has not been initialized.
MASTER_POS_WAIT() was introduced in MySQL 3.23.32.
RELEASE_LOCK(str)
Releases the advisory lock named by str. RELEASE_LOCK() is used in conjunction with GET_LOCK(). See the description of GET_LOCK() for details.
SESSION_USER()
This function is a synonym for USER().
SYSTEM_USER()
This function is a synonym for USER().
USER()
Returns a string representing the current client user, as a string of the form 'user@host', where user is the user name and host is the name of the host from which the client connection was established.
USER() 'sampadm@localhost'
SUBSTRING_INDEX(USER(),'@',1) 'sampadm'
SUBSTRING_INDEX(USER(),'@',-1) 'localhost'
Prior to MySQL 3.22.1, the return value from USER() consists only of the user name.
VERSION()
Returns a string describing the server version.
VERSION() '4.0.3-beta-log'
The value consists of a version number, possibly followed by one or more suffixes. The suffixes may include the following:
-alpha, -beta, or -gamma indicate the stability of the MySQL release.
-debug means that the server is running in debug mode.
-demo indicates that the server is running in demo mode (used in MySQL 3.23.30 and earlier only).
-embedded indicates the embedded server, libmysqld.
-log means logging is enabled.
-max indicates a server compiled with additional features.
-nt indicates a server built for Windows NT-based systems.
No comments:
Post a Comment