|
|
< Day Day Up > |
|
A.2 SQL FunctionsA.2.1 ABS()
ABS(num)
Returns the absolute value of num. This function is safe to use with BIGINT values. A.2.2 AES_DECRYPT()
AES_DECRYPT(string,key_string)
This function allows decryption of data using the official AES (Advanced Encryption Standard) algorithm, previously known as Rijndael. Encoding with a 128-bit key length is used, but you can extend it up to 256 bits by modifying the source. We chose 128 bits because it is much faster and it is usually secure enough. The input arguments may be any length. If either argument is NULL, the result of this function is also NULL. Because AES is a block-level algorithm, padding is used to encode uneven length strings and so the result string length may be calculated as 16*(trunc(string_length/16)+1). If AES_DECRYPT() detects invalid data or incorrect padding, it returns NULL. However, it is possible for AES_DECRYPT() to return a non-NULL value (possibly garbage) if the input data or the key is invalid. AES_DECRYPT() was added in version 4.0.2, and can be considered the most cryptographically secure decryption function currently available in MySQL. A.2.3 AES_ENCRYPT()
AES_ENCRYPT(string,key_string)
This function allows encryption of data using the official AES (Advanced Encryption Standard) algorithm, previously known as Rijndael. Encoding with a 128-bit key length is used, but you can extend it up to 256 bits by modifying the source. We chose 128 bits because it is much faster and it is usually secure enough. The input arguments may be any length. If either argument is NULL, the result of this function is also NULL. Because AES is a block-level algorithm, padding is used to encode uneven length strings and so the result string length may be calculated as 16*(trunc(string_length/16)+1). You can use AES_ENCRYPT to store data in an encrypted form by modifying your queries, for example:
INSERT INTO t VALUES (1,AES_ENCRYPT('text','password'));
You can get even more security by not transferring the key over the connection for each query, which can be accomplished by storing it in a server-side variable at connection time. For example:
SELECT @password:='my password';
INSERT INTO t VALUES (1,AES_ENCRYPT('text',@password));
AES_ENCRYPT() was added in version 4.0.2, and can be considered the most cryptographically secure encryption function currently available in MySQL. A.2.4 BIN()
BIN(num)
Returns a string representation of the binary value of num, where num is a longlong (BIGINT) number. This is equivalent to CONV(num,10,2). Returns NULL if num is NULL. A.2.5 CEILING()
CEILING(num)
Returns the smallest integer value not less than num. A synonym is CEIL(). A.2.6 CHAR()CHAR(num, [, num [, ...]]) Interprets the arguments as integers and returns a string consisting of the characters given by the ASCII code values of those integers. NULL values are skipped. A.2.7 CHAR_LENGTH()
CHAR_LENGTH(str)
Returns the length of the string str, measured in characters. A.2.8 CHARACTER_LENGTH()
CHARACTER_LENGTH(str)
Returns the length of the string str, measured in characters. A.2.9 CONCAT()CONCAT(str [, str [, ...]]) Returns the string that results from concatenating the arguments. Returns NULL if any argument is NULL. May have more than two arguments. A numeric argument is converted to its equivalent string form. A.2.10 CONCAT_WS()CONCAT_WS(separator, str [, str [, ...]]) CONCAT_WS() stands for CONCAT With Separator and is a special form of CONCAT(). The first argument is the separator for the rest of the arguments. The separator is added between the strings to be concatenated. The separator can be a string as can the rest of the arguments. If the separator is NULL, the result is NULL. The function skips any NULL values after the separator argument. A.2.11 CONV()
CONV(N,from_base,to_base)
Converts numbers between different number bases. Returns a string representation of the number N, converted from base from_base to base to_base. Returns NULL if any argument is NULL. The argument N is interpreted as an integer, but may be specified as an integer or a string. The minimum base is 2 and the maximum base is 36. If to_base is a negative number, N is regarded as a signed number. Otherwise, N is treated as unsigned. (CONV) works with 64-bit precision. A.2.12 CURRENT_DATE()CURRENT_DATE() Returns the current date as a value in 'YYYY-MM-DD' or YYYYMMDD format, depending on whether the function is used in a string or numeric context. CURRENT_DATE and CURDATE() are synonyms. A.2.13 CURRENT_TIME()CURRENT_TIME() Returns the current time as a value in 'HH:MM:SS' or HHMMSS format, depending on whether the function is used in a string or numeric context. CURRENT_TIME and CURTIME() are synonyms. A.2.14 DATE_ADD()DATE_ADD(date,INTERVAL expr type) Performs date arithmetiA. INTERVAL expr type also can be used in arithmetic expressions. INTERVAL expr type is allowed on either side of the + operator if the expression on the other side is a date or datetime value. For the - operator, INTERVAL expr type is allowed only on the right side because it makes no sense to subtract a date or datetime value from an interval. date is a DATETIME or DATE value specifying the starting date. expr is an expression specifying the interval value to be added or subtracted from the starting date. expr is a string; it may start with a - for negative intervals. type is a keyword indicating how the expression should be interpreted. A.2.15 DATE_FORMAT()
DATE_FORMAT(date,format)
Formats the date value according to the format string. A large number of specifiers may be used in the format string. See the MySQL Reference Manual for details. A.2.16 DATE_SUB()DATE_SUB(date,INTERVAL expr type) Performs date arithmetiA. INTERVAL expr type also can be used in arithmetic expressions. INTERVAL expr type is allowed on either side of the + operator if the expression on the other side is a date or datetime value. For the - operator, INTERVAL expr type is allowed only on the right side because it makes no sense to subtract a date or datetime value from an interval. date is a DATETIME or DATE value specifying the starting date. expr is an expression specifying the interval value to be added or subtracted from the starting date. expr is a string; it may start with a - for negative intervals. type is a keyword indicating how the expression should be interpreted. A.2.17 DAYNAME()
DAYNAME(date)
Returns the name of the weekday for date. A.2.18 DAYOFMONTH()
DAYOFMONTH(date)
Returns the day of the month for date, in the range 1 to 31. A.2.19 DAYOFWEEK()
DAYOFWEEK(date)
Returns the weekday index for date (1 = Sunday, 2 = Monday, … 7 = Saturday). These index values correspond to the ODBC standard. A.2.20 DAYOFYEAR()
DAYOFYEAR(date)
Returns the day of the year for date, in the range 1 to 366. A.2.21 DECODE()
DECODE(crypt_str, pass_str)
Decrypts the encrypted string crypt_str using pass_str as the password. crypt_str should be a string returned from ENCODE() (see section A.2.25, "ENCODE()"). A.2.22 DES_DECRYPT()DES_DECRYPT(string_to_decrypt [, key_string]) Decrypts a string encrypted with DES_ENCRYPT() (see section A.2.23, "DES_ENCRYPT()"). Note that this function works only if MySQL has been configured with SSL support. If no key_string argument is given, DES_DECRYPT() examines the first byte of the encrypted string to determine the DES key number that was used to encrypt the original string, and then reads the key from the des-key-file to decrypt the message. For this to work, the user must have the SUPER privilege. If you pass this function a key_string argument, that string is used as the key for decrypting the message. If the string_to_decrypt doesn't look like an encrypted string, MySQL will return the given string_to_decrypt. On error, this function returns NULL. A.2.23 DES_ENCRYPT()DES_ENCRYPT(string_to_encrypt [, (key_number | key_string) ] ) Encrypts the string with the given key using the Triple-DES algorithm. Note that this function works only if MySQL has been configured with SSL support. The encryption key to use is chosen the following way:
The return string will be a binary string where the first character will be CHAR(128 | key_number). The 128 is added to make it easier to recognize an encrypted key. If you use a string key, key_number will be 127. On error, this function returns NULL. The string length for the result will be new_length = orig_length + (8-(orig_length % 8))+1. A.2.24 ELT()ELT(num, str1 [, str2 [, ...]]) Returns str1 if num = 1, str2 if num = 2, and so on. Returns NULL if num is less than 1 or greater than the number of arguments. ELT() is the complement of FIELD() (see section A.2.27, "FIELD()"). A.2.25 ENCODE()
ENCODE(str, pass_str)
Encrypts str using pass_str as the password. To decrypt the result, use DECODE() (see section A.2.21, "DECODE()"). The result is a binary string of the same length as str. If you want to save it in a column, use a BLOB column type. A.2.26 EXPORT_SET()EXPORT_SET(bits, on, off, [separator, [number_of_bits]]) Returns a string in which for every bit set in bit, you get an on string and for every reset bit, you get an off string. Each string is separated by separator (default ',') and only number_of_bits (default 64) of bits is used. A.2.27 FIELD()FIELD(str, str1 [, str2 [, ...]]) Returns the index of str in the str1, str2, str3, … list. Returns 0 if str is not found. FIELD() is the complement of ELT() (see section A.2.24, "ELT()"). A.2.28 FIND_IN_SET()
FIND_IN_SET(str, strlist)
Returns a value 1 to N if the string str is in the list strlist consisting of N substrings. A string list is a string composed of substrings separated by ',' characters. If the first argument is a constant string and the second is a column of type SET, the FIND_IN_SET() function is optimized to use bit arithmetiA. Returns 0 if str is not in strlist or if strlist is the empty string. Returns NULL if either argument is NULL. This function will not work properly if the first argument contains a comma (,). A.2.29 FLOOR()
FLOOR(num)
Returns the largest integer value not greater than num. A.2.30 FROM_DAYS()
FROM_DAYS(num)
Given a day number num, the function returns a DATE value. A.2.31 FROM_UNIXTIME()FROM_UNIXTIME(unix_timestamp [, format]) Returns a representation of the unix_timestamp argument as a value in 'YYYY-MM-DD HH:MM:SS' or YYYYMMDDHHMMSS format, depending on whether the function is used in a string or numeric context. If format is given, the result is formatted according to the format string. format may contain the same specifiers as those listed in the entry for the DATE_FORMAT() function. A.2.32 GREATEST()GREATEST(num1 [, num2 [, ...]]) Returns the largest (maximum-valued) argument. The arguments are compared using the same rules as for LEAST. See section A.2.38, "LEAST()." A.2.33 HEX()
HEX(N_or_S)
If N_OR_S is a number, returns a string representation of the hexadecimal value of N, where N is a longlong (BIGINT) number. This is equivalent to CONV(N,10,16). See section A.2.11, "CONV()." If N_OR_S is a string, returns a hexadecimal string of N_OR_S where each character in N_OR_S is converted to two hexadecimal digits. A.2.34 HOUR()
HOUR(time)
Returns the hour for time. The range of the return value will be 0 to 23 for time-of-day values. However, the range of TIME values actually is much larger, so HOUR can return values greater than 23. A.2.35 INSERT()
INSERT(str, xpos, len, newstr)
Returns the string str, with the substring beginning at position pos and len characters long replaced by the string newstr. This function is multi-byte safe. A.2.36 INSTR()
INSTR(str, substr)
Returns the position of the first occurrence of substring substr in string str. This is the same as the two-argument form of LOCATE(), except that the arguments are swapped. This function is multi-byte safe. This function is case sensitive if either argument is a binary string. Otherwise, it is not case sensitive. A.2.37 LCASE()
LCASE(str)
Synonym for LOWER(). See section A.2.43, "LOWER()." A.2.38 LEAST()LEAST(num1 [, num2 [, ...]]) With two or more arguments, returns the smallest (minimum-valued) argument. The arguments are compared using the following rules:
A.2.39 LEFT()
LEFT(str, len)
Returns the leftmost len characters from the string str. This function is multi-byte safe. A.2.40 LENGTH()
LENGTH(str)
Returns the length of the string str, measured in bytes. A.2.41 LOAD_FILE()
LOAD_FILE(filename)
Reads the file and returns the file contents as a string. The file must exist on the server host, you must specify the full pathname to the file, and you must have the FILE privilege. The file must be world-readable and be smaller than max_allowed_packet bytes (see section A.4, "Server System Variables"). If any of these conditions are not satisfied, the function returns NULL. A.2.42 LOCATE()
LOCATE(substr, str)
A synonym for POSITION(), although with a slightly different syntax. See section A.2.57, "POSITION()." A.2.43 LOWER()
LOWER(str)
Returns the string str with all characters changed to lowercase according to the current character set mapping. A synonym is LCASE() (see section A.2.37, "LCASE()"). A.2.44 LPAD()
LPAD(str, len, padstr)
Returns the string str, left-padded with the string padstr to a length of len characters. If str is longer than len, the return value is shortened to len characters. A.2.45 LTRIM()
LTRIM(str)
Returns the string str with leading space characters removed. A.2.46 MAKE_SET()MAKE_SET(bits, str1 [, str2 [,...]]) Returns a set (a string containing substrings separated by, characters) consisting of the strings that have the corresponding bit in bits set. str1 corresponds to bit 0, str2 to bit 1, and so on. NULL values in str1, str2, … are not appended to the result. A.2.47 MID()
MID(str, pos, len)
Returns a substring len characters long from string str, starting at position pos. A synonym is SUBSTRING() (see section A.2.72, "SUBSTRING()"). A.2.48 MINUTE()
MINUTE(time)
Returns the minute for time, in the range 0 to 59. A.2.49 MOD()
MOD(N, M)
Modulo (like the % operator in C). Returns the remainder of N divided by M. A.2.50 MONTH()
MONTH(date)
Returns the month for date, in the range 1 to 12. A.2.51 MONTHNAME()
MONTHNAME(date)
Returns the name of the month for date. A.2.52 NOW()NOW() Returns the current date and time as a value in 'YYYY-MM-DD HH:MM:SS' or YYYYMMDDHHMMSS format, depending on whether the function is used in a string or numeric context. Synonyms are SYSDATE(), CURRENT_TIMESTAMP, CURRENT_TIMESTAMP(), LOCALTIME, LOCALTIME(), LOCALTIMESTAMP, and LOCALTIMESTAMP(). A.2.53 OCT()
OCT(num)
Returns a string representation of the octal value of num, where num is a longlong number. This is equivalent to CONV(num,10,8). See section A.2.11, "CONV()." Returns NULL if num is NULL. A.2.54 PASSWORD()
PASSWORD(str)
Calculates a password string from the plaintext password str. This function is used for encrypting MySQL passwords for storage in the Password column of the user grant table. The encryption that this function produces is irreversible. Note that it does not perform password encryption in the same way that Unix passwords are encrypted. This function is for use by the authentication system in the MySQL Server. You should not use it in your own applications. Use MD5() or SHA1() instead. Also see RFC-2195 for more information about handling passwords and authentication securely in your application. A.2.55 PERIOD_ADD()
PERIOD_ADD(P, N)
Adds N months to period P (in the format YYMM or YYYYMM). Returns a value in the format YYYYMM. Note that the period argument P is not a date value. A.2.56 PERIOD_DIFF()
PERIOD_DIFF(P1, P2)
Returns the number of months between periods P1 and P2. P1 and P2 should be in the format YYMM or YYYYMM. Note that the period arguments P1 and P2 are not date values. A.2.57 POSITION()POSITION(substr IN str) Returns the position of the first occurrence of substring substr in string str. Returns 0 if substr is not in str: This function is multi-byte safe. This function is case sensitive if either argument is a binary string. Otherwise, it is not case sensitive. A synonym, although with a slightly different syntax, is LOCATE (see section A.2.42, "LOCATE()"). A.2.58 POW()
POW(X, Y)
A synonym for POWER() (see section A.2.59, "POWER()"). A.2.59 POWER()
POWER(X, Y)
Returns the value of X raised to the power of Y. A synonym is POW() (see section A.2.58, "POW()"). A.2.60 QUARTER()
QUARTER(date)
Returns the quarter of the year for date, in the range 1 to 4. A.2.61 QUOTE()
QUOTE(str)
Quotes a string to produce a result that can be used as a properly escaped data value in an SQL statement. The string is returned surrounded by single quotes and with each instance of single quote ('), backslash (\), ASCII NUL, and Control-Z preceded by a backslash. If the argument is NULL, the return value is the word "NULL" without surrounding quotes. A.2.62 RAND()
RAND() | RAND(num)
Returns a random floating-point value in the range 0 to 1.0. If an integer argument num is specified, it is used as the seed value (producing a repeatable sequence). A.2.63 REPLACE()
REPLACE(str, from_str, to_str)
Returns the string str with all occurrences of the string from_str replaced by the string to_str. This function is multi-byte safe. A.2.64 REVERSE()
REVERSE(str)
Returns the string str with the order of the characters reversed. This function is multi-byte safe. A.2.65 RIGHT()
RIGHT(str, len)
Returns the rightmost len characters from the string str. This function is multi-byte safe. A.2.66 ROUND()ROUND(X) | ROUND(X, D) Returns the argument X, rounded to the nearest integer. With two arguments, rounded to a number to D decimals. Note that the behavior of ROUND() when the argument is halfway between two integers depends on the C library implementation. Some round to the nearest even number, always up, always down, or always toward zero. If you need one kind of rounding, you should use a well-defined function such as TRUNCATE() or FLOOR() instead. A.2.67 RPAD()
RPAD(str, len, padstr)
Returns the string str, right-padded with the string padstr to a length of len characters. If str is longer than len, the return value is shortened to len characters. A.2.68 RTRIM()
RTRIM(str)
Returns the string str with trailing space characters removed. This function is multi-byte safe. A.2.69 SEC_TO_TIME()
SEC_TO_TIME(seconds)
Returns the seconds argument, converted to hours, minutes, and seconds, as a value in 'HH:MM:SS' or HHMMSS format, depending on whether the function is used in a string or numeric context. A.2.70 SECOND()
SECOND(time)
Returns the second for time, in the range 0 to 59. A.2.71 SIGN()
SIGN(num)
Returns the sign of the argument as -1, 0, or 1, depending on whether num is negative, zero, or positive. A.2.72 SUBSTRING()SUBSTRING(str, pos, len) | SUBSTRING(str FROM pos FOR len) Returns a substring len characters long from string str, starting at position pos. The variant form that uses FROM is SQL-92 syntax. A synonym is MID() (see section A.2.47, "MID()"). A.2.73 SUBSTRING_INDEX()
SUBSTRING_INDEX(str, delim, count)
Returns the substring from string str before count occurrences of the delimiter delim. If count is positive, everything to the left of the final delimiter (counting from the left) is returned. If count is negative, everything to the right of the final delimiter (counting from the right) is returned. This function is multi-byte safe. A.2.74 TIME_FORMAT()
TIME_FORMAT(time, format)
This is used like the DATE_FORMAT() function (see section A.2.15, "DATE_FORMAT()"), but the format string may contain only those format specifiers that handle hours, minutes, and seconds. Other specifiers produce a NULL value or 0. If the time value contains an hour part that is greater than 23, the %H and %k hour format specifiers produce a value larger than the usual range of 0..23. The other hour format specifiers produce the hour value modulo 12. A.2.75 TIME_TO_SEC()
TIME_TO_SEC(time)
Returns the time argument, converted to seconds. A.2.76 TO_DAYS()
TO_DAYS(date)
Given a date date, returns a daynumber (the number of days since year 0). TO_DAYS() is not intended for use with values that precede the advent of the Gregorian calendar (1582) because it doesn't take into account the days that were lost when the calendar was changed. A.2.77 TRIM()TRIM([[BOTH | LEADING | TRAILING] [remstr] FROM] str) Returns the string str with all remstr prefixes and/or suffixes removed. If none of the specifiers BOTH, LEADING or TRAILING is given, BOTH is assumed. If remstr is not specified, spaces are removed. This function is multi-byte safe. A.2.78 TRUNCATE()
TRUNCATE(X, D)
Returns the number X, truncated to D decimals. If D is 0, the result will have no decimal point or fractional part. All numbers are rounded toward zero. If D is negative, the whole part of the number is zeroed out. Note that decimal numbers are normally not stored as exact numbers in computers, but as double-precision values. A.2.79 UCASE()
UCASE(str)
Synonym for UPPER(). See section A.2.81, "UPPER()." A.2.80 UNIX_TIMESTAMP()
UNIX_TIMESTAMP([date])
If called with no argument, returns a Unix timestamp (seconds since '1970-01-01 00:00:00' GMT) as an unsigned integer. If UNIX_TIMESTAMP() is called with a date argument, it returns the value of the argument as seconds since '1970-01-01 00:00:00' GMT. date may be a DATE string, a DATETIME string, a TIMESTAMP, or a number in the format YYMMDD or YYYYMMDD in local time. When UNIX_TIMESTAMP is used on a TIMESTAMP column, the function returns the internal timestamp value directly, with no implicit "string-to-Unix-timestamp" conversion. If you pass an out-of-range date to UNIX_TIMESTAMP(), it returns 0, but please note that only basic checking is performed (year 1970-2037, month 01-12, day 01-31). If you want to subtract UNIX_TIMESTAMP() columns, you might want to cast the result to a signed integer. A.2.81 UPPER()
UPPER(str)
Returns the string str with all characters changed to uppercase according to the current character set mapping. This function is multi-byte safe. A synonym is UCASE() (see section A.2.79, "UCASE()"). A.2.82 WEEK()WEEK(date [, start]) With a single argument, returns the week for date, in the range 0 to 53 (yes, there could be the beginnings of a week 53), for locations where Sunday is the first day of the week. The two-argument form of WEEK() enables you to specify whether the week starts on Sunday or Monday and whether the return value should be in the range 0-53 or 1-52. A.2.83 WEEKDAY()
WEEKDAY(date)
Returns the weekday index for date (0 = Monday, 1 = Tuesday, … 6 = Sunday). A.2.84 YEAR()
YEAR(date)
Returns the year for date, in the range 1000 to 9999. A.2.85 YEARWEEK()YEARWEEK(date [, start]) Returns the year and week for a date. The start argument works exactly like the start argument to WEEK() (see section A.2.82, "WEEK()"). Note that the year in the result may be different from the year in the date argument for the first and the last week of the year. Note that the week number is different from what the WEEK() function would return (0) for optional argument 0 or 1 because WEEK() returns the week in the context of the given year. |
|
|
< Day Day Up > |
|