Functions
Functions are called to perform a calculation and return a value. By default, functions must be invoked with no space between the function name and the parenthesis following it:
mysql> SELECT NOW();
+---------------------+
| NOW() |
+---------------------+
| 2005-01-07 17:06:57 |
+---------------------+
mysql> SELECT NOW ();
ERROR 1064 (42000): You have an error in your SQL syntax
If the IGNORE_SPACE SQL mode is enabled, the server allows spaces after function names, although a side effect is that all function names become reserved words. You also may 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. Both of the following lines are legal:
CONCAT('abc','def')
CONCAT( 'abc' , 'def' )
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 apply, the arguments are compared as strings. String comparison rules are as described at the beginning of the section "Comparison Operators."
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'
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 may 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(). Note that the IF() function differs from the IF statement described in "Stored Routine Syntax," in Appendix E. IFNULL(expr1,expr2) Returns expr2 if the value of the expression expr1 is NULL; otherwise, it returns expr1. IFNULL() returns a number or string according to 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. That is, INTERVAL() finds the position of the first argument within the intervals defined by the remaining arguments. The values n1, n2, … must be in strictly increasing order (n1 < n2 < …) because a fast binary search is used. INTERVAL() behaves unpredictably otherwise.
INTERVAL(1.1,0,1,2) 2
INTERVAL(7,1,3,5,7,9) 4
INTERVAL(6.5,1.0,3.5,7.5) 2
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'
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
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. The comparison is not case sensitive unless either argument is a binary string or a non-binary string with a binary or case-sensitive collation:
STRCMP('a','a') 0
STRCMP('a','A') 0
STRCMP('A','a') 0
STRCMP(BINARY 'a','A') 1
STRCMP(BINARY 'A','a') -1
STRCMP('A' COLLATE latin1_bin,'a') -1
STRCMP('A' COLLATE latin1_general_cs,'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 may 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(20050101 AS DATE) AS date_val;
mysql> SHOW COLUMNS FROM t;
+----------+------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+------+------+-----+---------+-------+
| date_val | date | YES | | NULL | |
+----------+------+------+-----+---------+-------+
mysql> SELECT * FROM t;
+------------+
| date_val |
+------------+
| 2005-01-01 |
+------------+
CAST() is similar to CONVERT(), but CAST() has standard 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'
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 returns 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)
CEIL(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 2321, or NULL if the argument is NULL.
CRC32('xyz') 3951999591
CRC32('0') 4108050209
CRC32(0) 4108050209
CRC32(NULL) NULL
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().
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
You also 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. MOD(m,n) MOD() performs a modulo operation. MOD(m,n ) is the same as m % n. See "Arithmetic Operators." 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
If d is negative, ROUND() "rounds" digits to the left of the decimal point.
ROUND(123456,-2) 123500
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
If d is negative, trUNCATE() TRims any fractional part and zeros ABS(d) digits to the left of the decimal point.
TRUNCATE(123456.789,-3) 123000
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: CHAR_LENGTH(), INSERT(), INSTR(), LCASE(), LEFT(), LOCATE(), LOWER(), LTRIM(), MID(), POSITION(), REPLACE(), REVERSE(), RIGHT(), RPAD(), RTRIM(), SUBSTRING(), SUBSTRING_INDEX(), trIM(), UCASE(), and UPPER().
ASCII(str) Returns the integer value of the leftmost byte of the string str, in the range from 0 to 255. It returns 0 if str is empty or NULL if str is NULL.
ASCII('abc') 97
ASCII('') 0
ASCII(NULL) NULL
BIN(n) Returns a string containing the binary-digit representation of the argument n. The following two expressions are equivalent:
BIN(65) '1000001'
CONV(65,10,2) '1000001'
See the description of CONV() for more information. CHAR(n1,n2,...) Interprets the arguments as numeric character 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 it counts the length of its argument in characters, not bytes. (Multi-byte characters are each counted as having a length of 1.) CHARSET(str) Returns the name of the character set of the given string, or NULL if the argument is NULL.
CHARSET('abc') 'latin1'
CHARSET(CONVERT('abc' USING utf8)) 'utf8'
CHARSET(123) 'binary'
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
COERCIBILITY(str) Returns the collation coercibility of a string. The return values range from 0 to 3 indicating lesser to greater coercibility, or NULL if the argument is illegal. Coercibility | Meaning |
|---|
0 | Collation is explicit, cannot be coerced | 1 | No collation specified | 2 | Collation is implicit | 3 | Collation is coercible |
COERCIBILITY(_utf8 'abc' COLLATE utf8_bin) 0
COERCIBILITY('abc') 3
COERCIBILITY() was introduced in MySQL 4.1.1. COLLATION(str) Returns the name of the collation of the given string, or NULL if the argument is illegal.
COLLATION(_latin2 'abc') 'latin2_general_ci'
COLLATION(CONVERT('abc' USING utf8) COLLATE utf8_bin)
'utf8_bin'
CONCAT(str1,str2,...) Returns a string consisting of the concatenation of all of its arguments. The result is a non-binary string if each argument is a non-binary string. The result is a binary string if any argument is a binary string. Returns NULL if any argument is NULL. CONCAT() may be called with a single argument.
CONCAT('abc','def') 'abcdef'
CONCAT('abc') 'abc'
CONCAT('abc',NULL) NULL
CONCAT('Hello',', ','goodbye') 'Hello, goodbye'
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 in the list of strings to be concatenated.
CONCAT_WS(',','a','b','','d') 'a,b,,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 may be specified as a string because numbers in bases higher than 10 may contain non-decimal digits. (This also is 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 may 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'
ELT(n,str1,str2,...) Returns the n-th string from the list of strings str1, str2, … Returns NULL if n is NULL, the n-th string is NULL, or there is no n-th 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. The default delimiter is a comma. on is used to represent each bit that is set in the value n, and off is used to represent each bit that is not set. The leftmost string in the result corresponds to the low-order bit in n. bit_count indicates the maximum number of bits in n to examine. The default bit_count value is 64, which also is its maximum value. 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'
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 hexadecimal-digit representation of the argument, as a string. The following two expressions are equivalent:
HEX(65) '41'
CONV(65,10,16) '41'
See the description of CONV() for more information. HEX() also can accept a string argument; in this case, it returns a string consisting of each character in the argument represented as two hex digits:
HEX('255') '323535'
HEX('abc') '616263'
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, in bytes. (Multi-byte characters are each counted as having a length greater than 1.) To measure the length in characters, use CHAR_LENGTH().
LENGTH('abc') 3
LENGTH(CONVERT('abc' USING ucs2)) 6
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. The test is not case sensitive unless either argument is a binary string or a non-binary string with a binary or case-sensitive collation:
LOCATE('b','abc') 2
LOCATE('b','ABC') 2
LOCATE(BINARY 'b','ABC') 0
LOCATE('b' COLLATE latin1_bin,'ABC') 0
LOCATE('b' COLLATE latin1_general_cs,'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
Lettercase conversion is based on the collation of the argument's character set. If the argument is a binary string, there is no character set or collation and LOWER() returns the argument unchanged:
LOWER(BINARY 'New York, NY') 'New York, NY'
LOWER(0x414243) 'ABC'
To deal with this, convert or cast the argument to a non-binary string that has an appropriate collation:
LOWER(CONVERT(BINARY 'New York, NY' USING latin1))
'new york, ny'
LOWER(_latin1 0x414243) 'abc'
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'
LPAD() shortens the result to len characters if str has a length greater than len:
LPAD('abc',2,'.') 'ab'
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.
MATCH(column_list) AGAINST(str)
MATCH(column_list) AGAINST(str IN BOOLEAN MODE)
MATCH(column_list) AGAINST(str WITH QUERY EXPANSION)
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, apostrophes, or underscores. The parentheses are optional for MATCH, but not for AGAINST. With neither IN BOOLEAN MODE nor WITH QUERY EXPANSION, MATCH performs a natural language search. This 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 stopwords (like "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 forth. "phrase" A phrase search may be performed by surrounding the phrase within double quotes. Each word must be present, in the order given in the phrase, including any punctuation in the phrase. () Parentheses group words into expressions.
Words with no modifiers are treated as optional in a boolean search, just as for natural language searches. It's possible to perform a boolean-mode search in the absence of a FULLTEXT index, but this can be quite slow. If the search string is followed by WITH QUERY EXPANSION, a natural language search is done once using the search string, and then again using the search string and the information from the first few most highly relevant matches from the original search. This allows records with content related to the original search string to be found. WITH QUERY EXPANSION was introduced in MySQL 4.1.1. More information on FULLTEXT searching can be found in Chapter 2, "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 a string containing the octal-digit representation of the argument n. The following two expressions are equivalent:
OCT(65) '101'
CONV(65,10,8) '101'
See the description of CONV() for more information. 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 numeric values of the character's individual bytes b1 through bn, The formula is as follows, where b1 is the highest-order byte:
( ... ((ASCII(b1)*256 + ASCII(b2)))*256 + ...) + ASCII(bn)
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, NUL (zero-valued byte), 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 any surrounding single quotes.
QUOTE('X') 'X'
QUOTE("'") '\''
QUOTE(NULL) NULL
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.......'
RPAD() shortens the result to len characters if str has a length greater than len:
RPAD('abc',2,'.') 'ab'
RTRIM(str) Returns the string str with rightmost (trailing) spaces removed, or NULL if str is NULL.
RTRIM(' abc ') ' abc'
SOUNDEX(str)
expr1 SOUNDS LIKE expr2
SOUNDEX() 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 n-th occurrence of the delimiter string delim, and then returns everything to the left of that delimiter. If n is negative, SUBSTRING_INDEX() finds the n-th occurrence of delim, counting back from the right end of str, and then returns everything to the right of that delimiter. If SUBSTRING_INDEX() does not find delim in str, it returns the entire string. 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(trim_str FROM str)
TRIM([|LEADING | TRAILING | BOTH} [trim_str] FROM] str)
The first form returns the string str with leading and trailing instances of the string TRim_str trimmed off. In the second form, 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, and spaces are trimmed 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(). UNHEX(expr) The argument is interpreted as a string containing pairs of hexadecimal digits. Each pair of digits is converted to a character and the return value is a binary string consisting of these characters. UNHEX() is the inverse of HEX().
UNHEX('414243') 'ABC'
HEX(UNHEX('414243')) '414243'
UNHEX(HEX('ABC')) 'ABC'
UNHEX(414243) 'ABC'
CHARSET(UNHEX('414243')) 'binary'
UNHEX() was introduced in MySQL 4.1.2. 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
See the description of the LOWER() function for notes regarding lettercase conversion of binary strings.
Date and Time Functions
The date and time functions take various types of arguments. In general, a function that expects a DATE argument also will 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() 2004-08-01
CURDATE() + 0 20040801
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)
ADDDATE(date,expr)
For the first syntax, ADDDATE() takes a date or date and time value date, adds a temporal 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.
ADDDATE('2004-12-01',INTERVAL 1 YEAR) '2005-12-01'
ADDDATE('2004-12-01',INTERVAL 60 DAY) '2005-01-30'
ADDDATE('2004-12-01',INTERVAL -3 MONTH) '2004-09-01'
ADDDATE('2004-12-01 08:30:00',INTERVAL 12 HOUR) '2004-12-01 20:30:00'
For the second syntax, ADDDATE() takes a date or date and time value date, adds a temporal value representing number of days to it, and returns the result. The second syntax can be rewritten in terms of the first syntax like this:
ADDDATE(date,expr) = ADDDATE(date,INTERVAL expr DAY)
Examples:
ADDDATE('2004-12-01',365) '2005-12-01'
ADDDATE('2004-12-01',60) '2005-01-30'
ADDDATE('2004-12-01',-91) '2004-09-01'
The second syntax was introduced in MySQL 4.1.1. 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 may be given in any lettercase. Table C.3. ADDDATE() Interval TypesType | Meaning | Value Format |
|---|
MICROSECOND | Microseconds | uuuuuu | SECOND | Seconds | ss | SECOND_MICROSECOND | Seconds and microseconds | 'ss.uuuuuu' | MINUTE | Minutes | mm | MINUTE_SECOND | Minutes and seconds | 'mm:ss' | MINUTE_MICROSECOND | Minutes and microseconds | 'mm.uuuuuu' | HOUR | Hours | hh | HOUR_MINUTE | Hours and minutes | 'hh:mm' | HOUR_SECOND | Hours, minutes, and seconds | 'hh:mm:ss' | HOUR_MICROSECOND | Hours and microseconds | 'hh.uuuuuu' | DAY | Days | DD | 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' | DAY_MICROSECOND | Days and microseconds | 'DD.uuuuuu' | WEEK | Weeks | WW | MONTH | Months | MM | QUARTER | Quarters | QQ | YEAR | Years | YY | YEAR_MONTH | Years and months | 'YY-MM' |
The DAY_MICROSECOND, HOUR_MICROSECOND, MINUTE_MICROSECOND, SECOND_MICROSECOND, MICROSECOND specifiers were introduced in MySQL 4.1.1. WEEK and QUARTER were introduced in MySQL 5.0.0. The expression expr that is added to the date may 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 may be any punctuation character:
ADDDATE('2005-12-01',INTERVAL '2:3' YEAR_MONTH) '2008-03-01'
ADDDATE('2005-12-01',INTERVAL '2-3' YEAR_MONTH) '2008-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'.
ADDDATE('2003-12-01 12:00:00',INTERVAL '15:21' HOUR_SECOND)
'2003-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.
ADDDATE('2003-12-31',INTERVAL 2 MONTH) '2004-02-29'
An alternative syntax can be used for date addition:
'2003-12-31' + INTERVAL 2 MONTH '2004-02-29'
INTERVAL 2 MONTH + '2003-12-31' '2004-02-29'
ADDTIME(expr1,expr2) Adds the two expressions and returns the result. expr1 should be a time or date and time value, and expr2 should be time value. The values can contain a microseconds part.
ADDTIME('06:30:00.5','12:30:00.5') '19:00:01.000000'
ADDTIME('2004-01-01 00:00:00','12:30:00') '2004-01-01 12:30:00'
ADDTIME() was introduced in MySQL 4.1.1. CONVERT_TZ(date,from_zone,to_zone) Given the date or date and time value date, CONVERT_TZ() treats it as a value in the time zone from_zone, converts it to a value in the time zone to_zone, and returns the result. Returns NULL if any argument is invalid. Time zones can be specified as described in "Configuring Time Zone Support," in Chapter 11, "General MySQL Administration." For CONVERT_TZ() to work properly, the resulting value must lie within the range of the TIMESTAMP data type.
CONVERT_TZ('2005-02-11 00:00:00','US/Central','US/Eastern')
2005-02-11 01:00:00
CONVERT_TZ('2005-02-11','+00:00','-03:00') 2005-02-10 21:00:00
CONVERT_TZ() was introduced in MySQL 4.1.3. CURDATE() Returns the current date as a string in 'CCYY-MM-DD' format, or as a number in CCYYMMDD format, according to the context in which it is used.
CURDATE() 2004-08-01
CURDATE() + 0 20040801
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, according to the context in which it is used.
CURTIME() '09:51:36'
CURTIME() + 0 95136
DATE(expr) Returns the date part of expr, which should be a date or date and time expression.
DATE('2005-03-12') 2005-03-12
DATE('2005-03-12 16:15:00') 2005-03-12
DATE() was introduced in MySQL 4.1.1. DATE_ADD(date,INTERVAL expr interval) This is a synonym for the ADDDATE() function. DATEDIFF(expr1,expr2) Returns the difference in number of days between the two expressions, which should be date or date and time values. The result is positive if the first argument is later than the second. Any time part in the values is ignored.
DATEDIFF('2004-01-01','2004-01-08') -7
DATEDIFF('2004-01-08','2004-01-01') 7
DATEDIFF('2004-01-01 12:00:00','2004-01-08') -7
DATEDIFF('2004-01-08','2004-01-01 12:00:00') 7
DATEDIFF() was introduced in MySQL 4.1.1. DATE_FORMAT(date,format) Formats a date or date and time value date according to 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('2004-12-01','%M %e, %Y') 'December 1, 2004'
DATE_FORMAT('2004-12-01','The %D of %M') 'The 1st of December'
Table C.4 shows the available specifiers that may be used in the formatting string. The ranges shown for the numeric month and day specifiers begin with zero. Zero values may be produced for incomplete date values such as '2004-00-13' or '1998-12-00' to be used. Table C.4. DATE_FORMAT() Formatting SpecifiersSpecifier | Meaning |
|---|
%S, %s | Second in two-digit form (00, 01, ..., 59) | %f | Microseconds in six-digit form (000000, 000001, ...) | %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 (0, 1, ..., 31) | %D | Day of the month with English suffix (0th, 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 (00, 01, ..., 12) | %c | Month in numeric form (0, 1, ..., 12) | %Y | Year in four-digit form | %y | Year in two-digit form | %X | Year for the week in which Sunday is the first day, four-digit form | %x | Year for the week in which Monday is the first day, four-digit form | %% | A literal '%' character |
The '%' character preceding each format code is required. 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('2004-12-01','%i') '00'
The %f specifier was introduced in MySQL 4.1.1. DATE_SUB(date,INTERVAL expr interval) This function is a synonym for SUBDATE(). DAY(date) This function is a synonym for DAYOFMONTH(). It was introduced in MySQL 4.1.1. DAYNAME(date) Returns a string containing the weekday name for the date value date.
DAYNAME('2004-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('2004-12-05') 1
DAYNAME('2004-12-05') 'Sunday'
DAYOFWEEK('2004-12-18') 7
DAYNAME('2004-12-18') '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 may be any of the interval specifiers that are allowed for ADDDATE().
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
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
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, according to 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'
GET_FORMAT(val_type,format_type) Returns a format string of the type that can be used with the DATE_FORMAT(), TIME_FORMAT(), and STR_TO_DATE() functions. The val_type argument indicates a data type and can be DATE, TIME, or DATETIME. TIMESTAMP also can be used as of MySQL 4.1.4 and produces the same result as DATETIME. The format_type argument indicates which style of format string to return and can be 'EUR' (European), 'INTERNAL' (internal representation), 'ISO' (ISO 9075), 'JIS' (Japanese Industrial Standards), or 'USA' (United States). GET_FORMAT() returns format strings for each combination of val_type and format_type as shown in the following table: val_type | format_type | Format String |
|---|
DATE | 'EUR' | '%d.%m.%Y' | DATE | 'INTERNAL' | '%Y%m%d' | DATE | 'ISO' | '%Y-%m-%d' | DATE | 'JIS' | '%Y-%m-%d' | DATE | 'USA' | '%m.%d.%Y' | TIME | 'EUR' | '%H.%i.%s' | TIME | 'INTERNAL' | '%H%i%s' | TIME | 'ISO' | '%H:%i:%s' | TIME | 'JIS' | '%H:%i:%s' | TIME | 'USA' | '%h:%i:%s %p' | DATETIME | 'EUR' | '%Y-%m-%d %H.%i.%s' | DATETIME | 'INTERNAL' | '%Y%m%d%H%i%s' | DATETIME | 'ISO' | '%Y-%m-%d %H:%i:%s' | DATETIME | 'JIS' | '%Y-%m-%d %H:%i:%s' | DATETIME | 'USA' | '%Y-%m-%d %H.%i.%s' |
Note that the date part of the 'EUR' and 'USA' format strings for DATETIME is not the same as the 'EUR' and 'USA' format strings for DATE. GET_FORMAT() was introduced in MySQL 4.1.1. 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
LAST_DAY(date) Returns the last day of the month in which the argument falls. date should be a date or date and time value.
LAST_DAY('2003-07-01') 2003-07-31
LAST_DAY('2003-07-01 12:30:00') 2003-07-31
LAST_DAY() was introduced in MySQL 4.1.1.
LOCALTIME()
LOCALTIMESTAMP()
These functions are synonyms for NOW(). The parentheses are optional. MAKEDATE(year,day_of_year) Given a year and a day of the year, returns a date value. The result is NULL if day_of_year is 0 or less.
MAKEDATE(2010,365) 2010-12-31
MAKEDATE(2010,367) 2011-01-02
MAKEDATE(2010,0) NULL
MAKEDATE() was introduced in MySQL 4.1.1. MAKETIME(hour,minute,second) Returns a time value constructed from the given hour, minute, and second. The minute and second values should be in the range from 0 to 59. The hour can be outside that range. If the hour is negative, the result is negative.
MAKETIME(0,0,0) 00:00:00
MAKETIME(12,59,59) 12:59:59
MAKETIME(12,59,60) NULL
MAKETIME(-12,59,59) -12:59:59
MAKETIME() was introduced in MySQL 4.1.1. MICROSECOND(expr) Returns the microsecond part of the given time or date and time value. The return value has a range of 0 to 999999.
MICROSECOND('00:00:00.000001'); 1
MICROSECOND('2004-06-30: 23:59:59.5'); 500000
MICROSECOND() was introduced in MySQL 4.1.1. 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-DD hh:mm:ss' format, or as a number in CCYYMMDDhhmmss format, according to 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 may 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 may 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, according to the context in which it is used.
SEC_TO_TIME(29834) '08:17:14'
SEC_TO_TIME(29834) + 0 81714
STR_TO_DATE(str,format_str) Interprets the string argument str using the date formatting argument format_str and returns a DATETIME value. You can use this function to interpret temporal values in non-ISO format. STR_TO_DATE() performs the inverse operation of DATE_FORMAT(), and the format specifiers listed in the description of DATE_FORMAT() also are legal for STR_TO_DATE(). If str is illegal or cannot be interpreted using the given format string, the result is NULL.
STR_TO_DATE('3/16/1960','%m/%d/%Y') 1960-03-16
STR_TO_DATE('3/16/1960','%m-%d-%Y') NULL
STR_TO_DATE('3/16/1960 12:00:00','%m/%d/%Y %H:%i:%s')
1960-03-16 12:00:00
STR_TO_DATE() was introduced in MySQL 4.1.1.
SUBDATE(date,INTERVAL expr interval)
SUBDATE(date,expr)
SUBDATE() performs date arithmetic in the same manner as ADDDATE(), except that expr is subtracted from the date value date. See ADDDATE() for more information. The second SUBDATE() syntax was introduced in MySQL 4.1.1.
SUBDATE('2004-12-01',INTERVAL 1 MONTH) '2004-11-01'
SUBDATE('2004-12-01',INTERVAL '13-2' YEAR_MONTH) '1991-10-01'
SUBDATE('2004-12-01 04:53:12',INTERVAL '13-2' MINUTE_SECOND)
'2004-12-01 04:40:10'
SUBDATE('2004-12-01 04:53:12',INTERVAL '13-2' HOUR_MINUTE)
'2004-11-30 15:51:12'
SUBDATE('2004-12-01',30) '2004-11-01'
An alternative syntax is supported for date subtraction:
'2004-12-01' - INTERVAL 1 MONTH '2004-11-01'
Using this syntax, the INTERVAL clause must be on the right side of the subtraction operator, because you cannot subtract a date from an interval. SUBTIME(expr1,expr2) Subtracts the second expression from the first and returns the result. expr1 should be a time or date and time value, and expr2 should be time value. The values can contain a microseconds part.
SUBTIME('06:30:00.5','12:30:00.5') '-06:00:00.000000'
SUBTIME('2004-01-01 00:00:00','12:30:00') '2003-12-31 11:30:00'
SUBTIME() was introduced in MySQL 4.1.1. SYSDATE() This function is a synonym for NOW(). TIME(expr) Returns the time part of expr, which should be a time or date and time expression.
TIME('16:15:00') 16:15:00
TIME('2005-03-12 16:15:00') 16:15:00
TIME() was introduced in MySQL 4.1.1. 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 may 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 may 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
TIMEDIFF(expr1,expr2) Returns the time difference between the two expressions. The first and second expressions are the start and end times, respectively. They should both be time or date and time values; you cannot mix a time value and a date and time value.
TIMEDIFF('00:00:00','09:30:45') -09:30:45
TIMEDIFF('09:30:45','00:00:00') 09:30:45
TIMEDIFF() was introduced in MySQL 4.1.1. TIMESTAMP(expr1[,expr2]) The single-argument form takes a date or date and time value and returns a DATETIME value. The two-argument form adds the time value expr2 to the date or date and time value expr and returns the result as a DATETIME value.
TIMESTAMP('1985-12-14'); 1985-12-14 00:00:00
TIMESTAMP('1985-12-14 09:00:00'); 1985-12-14 09:00:00
TIMESTAMP('1985-12-14','18:00:00'); '1985-12-14 18:00:00'
TIMESTAMP('1985-12-14 09:00:00','18:00:00'); '1985-12-15 03:00:00'
TIMESTAMP('1985-12-14 09:00:00','-18:00:00'); '1985-12-13 15:00:00'
TIMESTAMP() was introduced in MySQL 4.1.1. TIMESTAMPADD(interval,expr1,expr2) Interprets expr1 as an integer number of units given by the interval argument, adds it to the date or date and time value expr2, and returns the result. The allowable interval values are FRAC_SECOND, SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, and YEAR. Any of these values also may be given with a prefix of SQL_TSI_.
TIMESTAMPADD(DAY,12,'1995-07-01') '1995-07-13'
TIMESTAMPADD(MONTH,12,'1995-07-01') '1996-07-01'
TIMESTAMPADD(SQL_TSI_MONTH,12,'1995-07-01') '1996-07-01'
TIMESTAMPADD() was introduced in MySQL 5.0.0. TIMESTAMPDIFF(interval,expr1,expr2) Calculates the difference between the date or date and time expressions expr1 and expr2, and returns the result in the units given by the interval argument. Allowable interval values are the same as those given in the description for TIMESTAMPADD().
TIMESTAMPDIFF(DAY,'1995-07-01','1995-08-01') 31
TIMESTAMPDIFF(MONTH,'1995-07-01','1995-08-01') 1
TIMESTAMPDIFF() was introduced in MySQL 5.0.0. 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 may 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' UTC). When called with a date-valued argument date, returns the number of seconds between the epoch and that date. date may 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
UTC_DATE() Returns the current UTC date as a string in 'CCYY:MM:DD' format, or as a number in CCYYMMDD format, according to the context in which it is used. The parentheses are optional.
UTC_DATE() 2004-08-10
UTC_DATE() + 0 20040810
UTC_DATE() was introduced in MySQL 4.1.1. UTC_TIME() Returns the current UTC time as a string in 'hh:mm:ss' format, or as a number in hhmmss format, according to the context in which it is used. The parentheses are optional.
UTC_TIME() 16:30:53
UTC_TIME() + 0 163054
UTC_TIME() was introduced in MySQL 4.1.1. UTC_TIMESTAMP() Returns the current UTC date and time as a string in 'CCYY-MM-DD hh:mm:ss' format, or as a number in CCYYMMDDhhmmss format, according to the context in which it is used. The parentheses are optional.
UTC_TIMESTAMP() 2004-08-10 16:31:06
UTC_TIMESTAMP() + 0 20040810163106
UTC_TIMESTAMP() was introduced in MySQL 4.1.1. WEEK(date[,mode]) 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 mode argument indicates the day on which the week starts and whether to return a value in the range from 0 to 53 or 1 to 53. The following table indicates the meaning of the possible mode values. Mode | Starting Day | Return Range | Meaning |
|---|
0 | Sunday | 0..53 | Week 1 is first week that starts this year | 1 | Monday | 0..53 | Week 1 is first week with more than three days | 2 | Sunday | 1..53 | Week 1 is first week that starts this year | 3 | Monday | 1..53 | Week 1 is first week with more than three days | 4 | Sunday | 0..53 | Week 1 is first week with more than three days | 5 | Monday | 0..53 | Week 1 is first week that starts this year | 6 | Sunday | 1..53 | Week 1 is first week with more than three days | 7 | Monday | 1..53 | Week 1 is first week that starts this year |
If mode is missing, the value of the default_week_format system variable is used.
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, for mode values that treat Monday as the first day of the year).
WEEK('2005-01-01') 0
DAYNAME('2005-01-01') 'Saturday'
WEEK('2006-01-01',1) 0
DAYNAME('2006-01-01') 'Sunday'
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'
WEEKOFYEAR(date) Returns the calendar week of the given date as a value in the range from 1 to 53.
WEEKOFYEAR('2004-01-01') 1
WEEKOFYEAR('2003-12-01') 49
WEEKOFYEAR() was introduced in MySQL 4.1.1. 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[,mode]) 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 mode argument indicates the day on which the week starts and whether to return a value in the range from 0 to 52 or 1 to 53. The allowable mode values are the same as listed in the description of the WEEK() function. If mode is 0, the week starts on Sunday. If mode 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 "Generating Summaries," 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. Returns NULL if there are no non-NULL values.
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. Returns ~0 if there are no non-NULL values.
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. Returns 0 if there are no non-NULL values.
SELECT BIT_OR(mycol) FROM mytbl 15
BIT_XOR(expr) Returns the bitwise exclusive-OR value of expr for all non-NULL values in the selected rows. Returns 0 if there are no non-NULL values.
SELECT BIT_XOR(mycol) FROM mytbl 5
BIT_XOR() was introduced in MySQL 4.1.1.
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. Returns 0 if there are no non-NULL values. 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 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
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. GROUP_CONCAT([DISTINCT] var_list [ORDER BY ...] [SEPARATOR str]) This function concatenates the non-NULL values in a group of strings and returns the result. Returns NULL if there are no non-NULL values. You can use DISTINCT to remove duplicates, ORDER BY to sort the results, and SEPARATOR to specify the delimiter between strings. By default, GROUP_CONCAT() does not perform duplicate removal or sorting, and separates values with commas. Values returned by GROUP_CONCAT() are limited in length to the value of the group_concat_max_len system variable. You can change the value of this variable to allow longer values.
mysql> CREATE TABLE t (name CHAR(10));
mysql> INSERT INTO t VALUES('dog'),('cat'),('rat'),('dog'),('rat');
mysql> SELECT GROUP_CONCAT(name) FROM t;
+---------------------+
| GROUP_CONCAT(name) |
+---------------------+
| dog,cat,rat,dog,rat |
+---------------------+
mysql> SELECT GROUP_CONCAT(name SEPARATOR ':') FROM t;
+----------------------------------+
| GROUP_CONCAT(name SEPARATOR ':') |
+----------------------------------+
| dog:cat:rat:dog:rat |
+----------------------------------+
mysql> SELECT GROUP_CONCAT(name ORDER BY name DESC) FROM t;
+---------------------------------------+
| GROUP_CONCAT(name ORDER BY name DESC) |
+---------------------------------------+
| rat,rat,dog,dog,cat |
+---------------------------------------+
mysql> SELECT GROUP_CONCAT(DISTINCT name ORDER BY name) FROM t;
+-------------------------------------------+
| GROUP_CONCAT(DISTINCT name ORDER BY name) |
+-------------------------------------------+
| cat,dog,rat |
+-------------------------------------------+
MAX(expr) Returns the maximum value of expr for all non-NULL values in the selected rows. Returns NULL if there are no non-NULL values. MAX() also can 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. Returns NULL if there are no non-NULL values. MIN() also may 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. Returns NULL if there are no non-NULL values. This is a population standard deviation, not a sample standard deviation. (For n values, the divisor is n rather than n1.)
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. Returns NULL if there are no non-NULL values.
SELECT SUM(mycol) FROM mytbl 39
VARIANCE(expr) Returns the variance of expr for all non-NULL values in the selected rows. Returns NULL if there are no non-NULL values. This is a population variance, not a sample variance. (For n values, the divisor is n rather than n1.)
SELECT VARIANCE(mycol) FROM mytbl 7.6735
Security and Compression Functions
These functions perform various security-related operations such as encrypting or compressing 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. The decrypted result will be meaningless otherwise.
If you want to save the result in a database when using encryption functions that return a binary string, 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_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 may be decoded with AES_DECRYPT(), using the same key string. COMPRESS(str) Returns a compressed version of the argument string, or NULL if the server was not compiled with a compression library. COMPRESS() was introduced in MySQL 4.1.1. 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 06 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_ENCRYPT(str)
DES_DECRYPT(str,key_num)
DES_DECRYPT(str,key_str)
Performs DES encryption on the string str and returns the encrypted result as a binary string. The encrypted string may 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 06 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 may 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.) ENCODE(str,key_str) Encrypts the string str using the key string key_str and returns the result as a binary string. The string may 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 or more characters. By specifying a salt value, the encrypted result for str will be the same each time. With no salt argument, MySQL uses a random value, so 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 at only 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 binary string consisting of 32 hexadecimal digits, or NULL if the argument is NULL.
MD5('secret') '5ebe2294ecd0e0f08eab7690d2a6ee69'
See also the SHA1() function. OLD_PASSWORD(str) This function returns the encrypted password value that PASSWORD() returned before MySQL 4.1.1. OLD_PASSWORD() was introduced in MySQL 4.1.1. 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(). If the old_passwords system variable is non-zero, PASSWORD() returns the password encrypted using the same hashing algorithm that was used before MySQL 4.1.1. In this case, PASSWORD() and OLD_PASSWORD() return the same value. old_passwords can be enabled by using a SET old_passwords = 1 statement or by starting the server with the --old-passwords option. 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 binary string consisting of 40 hexadecimal digits, or NULL if the argument is NULL.
SHA1('secret') 'e5e9fa1ba31ecd1ae84f75caaa474f3a663f05f4'
See also the MD5() function. UNCOMPRESS(str) Given a string that was compressed with the COMPRESS() function, UNCOMPRESS() returns the original string. Returns NULL if the argument is not a compressed string or if the server was not compiled with a compression library. UNCOMPRESS() was introduced in MySQL 4.1.1. UNCOMPRESSED_LENGTH(str) Given a string that was compressed with the COMPRESS() function, returns the length of the original uncompressed string. Returns NULL if the server was not compiled with a compression library. UNCOMPRESSED_LENGTH() was introduced in MySQL 4.1.1.
Advisory Locking Functions
The functions in this section are used for advisory (cooperative) locking. You can use them to write applications that cooperate based on the status of an agreed-upon lock name. The primary functions for this purpose are GET_LOCK() and RELEASE_LOCK(), which are used to acquire and release locks. Two other functions, IS_FREE_LOCK() and IS_USED_LOCK() can be used to query the status of a lock or determine which client holds a lock.
The basic for advisory locking is that you lock a name, which is nothing more than a string. An advisory lock is private in the sense that only the client that holds a lock on a name can release it, and global in the sense that any client can query the status of a lock name.
To acquire a lock, call GET_LOCK(str,timeout), where str indicates the lock name and timeout is a timeout value in seconds. GET_LOCK() 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 onlyit 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 that 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. If client 1 releases the lock within the timeout period, client 2 will acquire the lock successfully. Otherwise, client 2 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 release only your own locks), or NULL if no such lock exists.
Any lock held by a client is automatically released if the same 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.
To test the status of a lock name, you have two options:
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. Invoke IS_USED_LOCK(str), which returns NULL if there is no lock or the connection ID of the client that holds it if there is one.
You also can use GETLOCK(str,0) as a simple poll to determine without waiting whether a lock on str is in force. However, this has the side effect of locking the string if it is not currently locked, so you must remember to call RELEASE_LOCK() as appropriate.
All advisory locking functions return NULL if the lock name argument is NULL.
GET_LOCK(str,timeout) Attempt to acquire an advisory lock with the name indicated by the string str within a timeout value of timeout seconds. GET_LOCK() 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. IS_FREE_LOCK(str) Checks the status of the advisory lock named by str. 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. IS_USED_LOCK(str) If there is a lock with the name given by str, IS_USED_LOCK() returns the connection ID of the client that created the lock. Returns NULL if there is no such lock. RELEASE_LOCK(str) Releases the advisory lock named by str. Returns 1 if the lock was released successfully, 0 if the lock was held by another connection, or NULL if no such lock exists.
Spatial Functions
The functions in this section operate on spatial values, also known here as "geometries." Spatial values can be represented in three formats:
Functions that take spatial arguments expect them to be in the correct format. If you pass a spatial value in a different format or pass a non-spatial value, the result is NULL. There are functions for converting between spatial values in different formats.
Most functions that take spatial arguments expect them to be in internal format, and MySQL uses only internal format when storing spatial values in columns that have a spatial data type. You can store values in WKT or WKB formats by using columns with other data types, such as BLOB.
Spatial values can be associated with a spatial reference ID (SRID). Many of the spatial functions take SRID as optional arguments.
Note:
Because these spatial functions are implemented against the OpenGIS specification, the following sections also point out when functions in the specification are not implemented, or are implemented in a different way than described in the specification.
Spatial Value Format-Conversion Functions
The following functions accept a geometry value in Well-Known Binary format and return a geometry value in internal format. wkb_expr represents a WKB value for a geometry object of the type accepted by a given function. srid is an optional spatial reference identifier.
GEOMCOLLFROMWKB(wkb_expr[,srid])
GEOMETRYCOLLECTIONFROMWKB(wkb_expr[,srid])
Produces a GEOMETRYCOLLECTION value from its WKB value.
GEOMFROMWKB(wkb_expr[,srid])
GEOMETRYFROMWKB(wkb_expr[,srid])
Produces a GEOMETRY value from its WKB value. This function can accept a WKB value for any spatial type.
LINEFROMWKB(wkb_expr[,srid])
LINESTRINGFROMWKB(wkb_expr[,srid])
Produces a LINESTRING value from its WKB value.
MLINEFROMWKB(wkb_expr[,srid])
MULTILINESTRINGFROMWKB(wkb_expr[,srid])
Produces a MULTILINESTRING value from its WKB value.
MPOINTFROMWKB(wkb_expr[,srid])
MULTIPOINTFROMWKB(wkb_expr[,srid])
Produces a MULTIPOINT value from its WKB value.
MPOLYFROMWKB(wkb_expr[,srid])
MULTIPOLYGONFROMWKB(wkb_expr[,srid])
Produces a MULTIPOLYGON value from its WKB value. POINTFROMWKB(wkb_expr[,srid]) Produces a POINT value from its WKB value.
POLYFROMWKB(wkb_expr[,srid])
POLYGONFROMWKB(wkb_expr[,srid])
Produces a POLYGON value from its WKB value.
Unimplemented functions.
The OpenGIS specification describes optional functions BDPOLYFROMWKB() and BDMPOLYFROMWKB() for creating geometries from WKB values. MySQL does not implement these functions.
The following functions accept a geometry value in Well-Known Text format and return a geometry value in internal format. wkt_expr represents a WKT value for a geometry object of the type accepted by a given function. srid is an optional spatial reference identifier.
GEOMCOLLFROMTEXT(wkt_expr[,srid])
GEOMETRYCOLLECTIONFROMTEXT(wkt_expr[,srid])
Produces a GEOMETRYCOLLECTION value from its WKT value.
GEOMFROMTEXT(wkt_expr[,srid])
GEOMETRYFROMTEXT(wkt_expr[,srid])
Produces a GEOMETRY value from its WKT value. This function can accept a WKT value for any spatial type.
LINEFROMTEXT(wkt_expr[,srid])
LINESTRINGFROMTEXT(wkt_expr[,srid])
Produces a LINESTRING value from its WKT value.
MLINEFROMTEXT(wkt_expr[,srid])
MULTILINESTRINGFROMTEXT(wkt_expr[,srid])
Produces a MULTILINESTRING value from its WKT value.
MPOINTFROMTEXT(wkt_expr[,srid])
MULTIPOINTFROMTEXT(wkt_expr[,srid])
Produces a MULTIPOINT value from its WKT value.
MPOLYFROMTEXT(wkt_expr[,srid])
MULTIPOLYGONFROMTEXT(wkt_expr[,srid])
Produces a MULTIPOLYGON value from its WKT value. POINTFROMTEXT(wkt_expr[,srid]) Produces a POINT value from its WKT value.
POLYFROMTEXT(wkt_expr[,srid])
POLYGONFROMTEXT(wkt_expr[,srid])
Produces a POLYGON value from its WKT value.
Unimplemented functions.
The OpenGIS specification describes optional functions BDPOLYFROMTEXT() and BDMPOLYFROMTEXT() for creating geometries from WKT values. MySQL does not implement these functions.
The following functions convert spatial values from internal format to either Well-Known Binary or Well-Known Text format:
ASBINARY(geom) ASWKB(geom)
The following functions take a geometry in internal format and return the corresponding value in Well-Known Binary format:
Spatial Property Functions
The following functions take a spatial value geom of any type in internal format and return a property of the value.
DIMENSION(geom) Returns the dimension of the geometry. The dimension values have the following meanings: Dimension | Meaning |
|---|
1 | Empty geometry | 0 | Geometry with no length or area | 1 | Geometry with a non-zero length and zero area | 2 | Geometry with a non-zero area |
Examples:
A POINT has a dimension of 0, a LINESTRING has a dimension of 1, and a POLYGON has a dimension of 2. ENVELOPE(geom) Returns a POLYGON representing the minimum bounding rectangle of the geometry. GEOMETRYTYPE(geom) Returns the spatial type of the geometry as a string.
GEOMETRYTYPE(GEOMFROMTEXT('LINESTRING(1 1,2 2)')) 'LINESTRING'
SRID(geom) Returns the spatial reference ID of the geometry as an integer.
Unimplemented functions.
The OpenGIS specification also defines the following general spatial property functions, but MySQL does not implement them: BOUNDARY(), ISEMPTY(), and ISSIMPLE().
The following functions take a spatial POINT value pt in internal format and return a property of the value.
The following functions take a spatial LINESTRING value ls in internal format and return a property of the value.
ENDPOINT(ls) Returns the end point (final point) of ls as a POINT value. GLENGTH(ls) Returns the length of ls as a double-precision value. ISCLOSED(ls) Returns 1 if ls is closed, 0 if it is not, and 1 if it is NULL. A closed LINESTRING is one for which the starting point and end point are the same. NUMPOINTS(ls) Returns the number of points in ls. POINTN(ls,n) Returns the n-th point in ls as a POINT value. Points are numbered beginning with 1. STARTPOINT(ls) Returns the starting point (first point) of ls as a POINT value.
Unimplemented function.
The OpenGIS specification also defines the following LINESTRING property function, but MySQL does not implement it: ISRING().
The following functions take a spatial MULTILINESTRING value mls in internal format and return a property of the value.
GLENGTH(mls) Returns the length of mls as a double-precision value. The length of a MULTILINESTRING value is the sum of its constituent LINESTRING values. ISCLOSED(mls) Returns 1 if ls is closed, 0 if it is not, and 1 if it is NULL. A closed MULTILINESTRING is one for which the starting point and end point are the same for each of its constituent LINESTRING values.
The following functions take a spatial POLYGON value poly in internal format and return a property of the value.
AREA(poly) Returns the area of poly as a double-precision number. EXTERIORRING(poly) Returns the exterior ring of poly as a LINESTRING value. INTERIORRINGN(poly,n) Returns the n-th interior ring of poly as a LINESTRING value. Rings are numbered beginning with 1. NUMINTERIORRINGS(poly) Returns the number of interior rings in poly.
The following function takes a spatial MULTIPOLYGON value mpoly in internal format and returns a property of the value.
Unimplemented functions.
The OpenGIS specification also defines the following MULTIPOLYGON property functions, but MySQL does not implement them: CENTROID(), POINTONSURFACE().
The following functions take a spatial GEOMETRYCOLLECTION value gc in internal format and return a property of the value.
Spatial Relationship Functions
MySQL implements the following functions for testing spatial relationships between two geometries geom1 and geom2 in internal spatial format. These functions are based on the minimum bounding rectangle (MBR) of each geometry value.
MBRCONTAINS(geom1,geom2) Returns 1 if the minimum bounding rectangle of geom1 contains the minimum bounding rectangle of geom2, 0 if it does not. MBRDISJOINT(geom1,geom2) Returns 1 if the minimum bounding rectangles of geom1 and geom2 are disjoint, 0 if they are not. Geometries are disjoint if they do not intersect. MBREQUAL(geom1,geom2) Returns 1 if the minimum bounding rectangles of geom1 and geom2 are equal, 0 if they are not. MBRINTERSECTS(geom1,geom2) Returns 1 if the minimum bounding rectangles of geom1 and geom2 intersect, 0 if they do not. MBROVERLAPS(geom1,geom2) Returns 1 if the minimum bounding rectangles of geom1 and geom2 overlap, 0 if they do not. MBRTOUCHES(geom1,geom2) Returns 1 if the minimum bounding rectangles of geom1 and geom2 touch, 0 if they do not. MBRWITHIN(geom1,geom2) Returns 1 if the minimum bounding rectangle of geom1 is within the minimum bounding rectangle of geom2, 0 if it does not.
The OpenGIS specification defines the following functions for testing spatial relationships. MySQL currently implements them the same way as the corresponding MBR-based functions.
CONTAINS(geom1,geom2) DISJOINT(geom1,geom2) EQUALS(geom1,geom2) INTERSECTS(geom1,geom2) OVERLAPS(geom1,geom2) TOUCHES(geom1,geom2) WITHIN(geom1,geom2)
Unimplemented functions.
The OpenGIS specification also defines these spatial-relationship functions, which are not implemented: CROSSES(), DISTANCE(), and RELATED().
Miscellaneous Functions
The functions in this section do not fall into any of the preceding 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 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 since 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. 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
CONNECTION_ID() Returns the connection identifier for the current connection. This is the thread identifier that the server associates with the client connection. Every client has an identifier that is unique among the set of currently connected clients.
CONNECTION_ID() 10146
CURRENT_USER() When you connect to the MySQL server, your connection is associated with a given account row in the mysql.user table. The CURRENT_USER() function returns the values from the User and Host columns of that row, as a string in 'user_name@host_name' format. The value is a utf8 string; take this into account when passing the value to functions that take multiple string arguments to avoid triggering a collation mismatch error. The parentheses are optional.
CURRENT_USER() 'sampadm@localhost'
SUBSTRING_INDEX(CURRENT_USER(),_utf8'@',1) 'sampadm'
You can use CURRENT_USER() to determine who the server believes you to be. This might be different from the user that you specified when connecting if the server authenticates you as some other account. In particular, if the server authenticates you as an anonymous user, the username part of the return value is empty, whereas the username part of the value returned by USER() contains the username you specified when making the connection. DATABASE() Returns a utf8 string containing the default database name. If there is no default database, DATABASE() returns NULL.
DATABASE() 'sampdb'
Prior to MySQL 4.1.1, DATABASE() returns the empty string if there is no default database. FOUND_ROWS() Returns the number of rows that a preceding SELECT statement would have returned without a LIMIT clause. For example, this 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 this:
mysql> SELECT SQL_CALC_FOUND_ROWS * FROM mytbl LIMIT 10;
mysql> SELECT FOUND_ROWS();
DEFAULT(col_name) The INSERT statement allows you to specify the keyword DEFAULT to indicate explicitly that you want to insert a column's default value into a new record. However, this keyword is not allowable in arbitrary expressions or in other contexts. For example, you cannot use it if you want to reset a column to its default value in an UPDATE statement. The DEFAULT() function can be used for this. Given a column name, it returns the column's default value.
UPDATE counts SET counter = DEFAULT(counter)
WHERE max_time > expire_time;
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_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'
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 3. 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 their own new automatically generated values to be created. 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. MASTER_POS_WAIT(log_file,pos[,timeout]) This function is used when testing replication servers. When executed on a slave server, it blocks until the slave has read and processed events from the master server up to the given replication coordinates specified by the log_file and pos arguments. The optional timeout value can be given to tell MASTER_POS_WAIT() to place a limit on the number of seconds the function should wait. A value of 0 or less is equivalent to no timeout. MASTER_POS_WAIT() returns the number of log file events it had to wait for to get to the given replication coordinates. If the slave had already reached the coordinates, the function returns immediately with a value of 0. A return value of 1 indicates that the function timed out, an error occurred, or the master server information has not been initialized. A return value of NULL indicates that the slave SQL thread was not running or was stopped while the function was waiting. ROW_COUNT() This function operates as an SQL-level version of the mysql_affected_rows() C API function. It returns the number of rows affected by the previous statement. This is the number of rows inserted, deleted, or updated. A value of 1 indicates that the previous statement was a SELECT statement (or some other statement that returned a result set), or resulted in an error. ROW_COUNT() was introduced in MySQL 5.0.1. SESSION_USER() This function is a synonym for USER(). SYSTEM_USER() This function is a synonym for USER(). USER() Returns a string representing the username that you specified when connecting to the MySQL server, and the host from which you connected. The return value is a string in 'user_name@host_name' format. The value is a utf8 string; take this into account when passing the value to functions that take multiple string arguments to avoid triggering a collation mismatch error.
USER() 'paul@localhost'
SUBSTRING_INDEX(USER(),_utf8'@',1) 'paul'
SUBSTRING_INDEX(USER(),_utf8'@',-1) 'localhost'
UUID() Returns a "universal unique identifier." The intent is that the return value from one call to UUID() should be different than the value from any other call.
UUID() '4550868e-3c1f-1027-9cc8-78fa7f8d46b6'
UUID() 'cbb9ad76-3d10-1027-8c06-349c71608da3'
The return value is a five-part string of hexadecimal digits generated from a 128-bit number. The first four parts should be temporally unique, and the last part should be spatially unique. The first three parts of the value are derived from a timestamp. The fourth part ensures uniqueness for situations in which the sequence of timestamp might not be monotonic, as happens when time changes for daylight saving time. The fifth part is an IEEE 802 node number. This might be generated from a value assumed to unique to your server host, such as a network interface address. A 48-bit random number is used instead if no such unique value can be obtained. Note that uniqueness of the return value is not absolutely guaranteed, but duplicated values should be very unlikely. UUID() was introduced in MySQL 4.1.2. VERSION() Returns a utf8 string describing the server version.
VERSION() '5.0.3-alpha-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. -classic, -pro, or -gpl indicate distribution build types of Classic, Pro, and GPL. -debug means that the server is running in debug mode. -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.
|
