12.1. Function and Operator Reference

Table 12.1. Functions/Operators

Name Description
`ABS()` Return the absolute value
`ACOS()` Return the arc cosine
`ADDDATE()` Add time values (intervals) to a date value
`ADDTIME()` Add time
`AES_DECRYPT()` Decrypt using AES
`AES_ENCRYPT()` Encrypt using AES
`AND`, `&&` Logical AND
`ASCII()` Return numeric value of left-most character
`ASIN()` Return the arc sine
`=` Assign a value (as part of a `SET` statement, or as part of the `SET` clause in an `UPDATE` statement)
`:=` Assign a value
`ATAN2()`, `ATAN()` Return the arc tangent of the two arguments
`ATAN()` Return the arc tangent
`AVG()` Return the average value of the argument
`BENCHMARK()` Repeatedly execute an expression
```BETWEEN ... AND ...``` Check whether a value is within a range of values
`BIN()` Return a string containing binary representation of a number
`BINARY` Cast a string to a binary string
`BIT_AND()` Return bitwise and
`BIT_COUNT()` Return the number of bits that are set
`BIT_LENGTH()` Return length of argument in bits
`BIT_OR()` Return bitwise or
`BIT_XOR()` Return bitwise xor
``` &``` Bitwise AND
`~` Invert bits
`|` Bitwise OR
`^` Bitwise XOR
`CASE` Case operator
`CAST()` Cast a value as a certain type
`CEIL()` Return the smallest integer value not less than the argument
`CEILING()` Return the smallest integer value not less than the argument
`CHAR_LENGTH()` Return number of characters in argument
`CHAR()` Return the character for each integer passed
`CHARACTER_LENGTH()` A synonym for CHAR_LENGTH()
`CHARSET()` Return the character set of the argument
`COALESCE()` Return the first non-NULL argument
`COERCIBILITY()` Return the collation coercibility value of the string argument
`COLLATION()` Return the collation of the string argument
`COMPRESS()` Return result as a binary string
`CONCAT_WS()` Return concatenate with separator
`CONCAT()` Return concatenated string
`CONNECTION_ID()` Return the connection ID (thread ID) for the connection
`CONV()` Convert numbers between different number bases
`CONVERT_TZ()` Convert from one timezone to another
`CONVERT()` Cast a value as a certain type
`COS()` Return the cosine
`COT()` Return the cotangent
`COUNT(DISTINCT)` Return the count of a number of different values
`COUNT()` Return a count of the number of rows returned
`CRC32()` Compute a cyclic redundancy check value
`CURDATE()` Return the current date
`CURRENT_DATE()`, `CURRENT_DATE` Synonyms for CURDATE()
`CURRENT_TIME()`, `CURRENT_TIME` Synonyms for CURTIME()
`CURRENT_TIMESTAMP()`, `CURRENT_TIMESTAMP` Synonyms for NOW()
`CURRENT_USER()`, `CURRENT_USER` The authenticated user name and host name
`CURTIME()` Return the current time
`DATABASE()` Return the default (current) database name
`DATE_ADD()` Add time values (intervals) to a date value
`DATE_FORMAT()` Format date as specified
`DATE_SUB()` Subtract a time value (interval) from a date
`DATE()` Extract the date part of a date or datetime expression
`DATEDIFF()` Subtract two dates
`DAY()` Synonym for DAYOFMONTH()
`DAYNAME()` Return the name of the weekday
`DAYOFMONTH()` Return the day of the month (0-31)
`DAYOFWEEK()` Return the weekday index of the argument
`DAYOFYEAR()` Return the day of the year (1-366)
`DECODE()` Decodes a string encrypted using ENCODE()
`DEFAULT()` Return the default value for a table column
`DEGREES()` Convert radians to degrees
`DES_DECRYPT()` Decrypt a string
`DES_ENCRYPT()` Encrypt a string
`DIV` Integer division
`/` Division operator
`ELT()` Return string at index number
`ENCODE()` Encode a string
`ENCRYPT()` Encrypt a string
`<=>` NULL-safe equal to operator
`=` Equal operator
`EXP()` Raise to the power of
`EXPORT_SET()` Return a string such that for every bit set in the value bits, you get an on string and for every unset bit, you get an off string
`EXTRACT()` Extract part of a date
`ExtractValue()` Extracts a value from an XML string using XPath notation
`FIELD()` Return the index (position) of the first argument in the subsequent arguments
`FIND_IN_SET()` Return the index position of the first argument within the second argument
`FLOOR()` Return the largest integer value not greater than the argument
`FORMAT()` Return a number formatted to specified number of decimal places
`FOUND_ROWS()` For a SELECT with a LIMIT clause, the number of rows that would be returned were there no LIMIT clause
`FROM_BASE64()` Decode to a base-64 string and return result
`FROM_DAYS()` Convert a day number to a date
`FROM_UNIXTIME()` Format UNIX timestamp as a date
`GET_FORMAT()` Return a date format string
`GET_LOCK()` Get a named lock
`>=` Greater than or equal operator
``` >``` Greater than operator
`GREATEST()` Return the largest argument
`GROUP_CONCAT()` Return a concatenated string
`GTID_SUBSET()` Return true if all GTIDs in subset are also in set; otherwise false.
`GTID_SUBTRACT()` Return all GTIDs in set that are not in subset.
`HEX()` Return a hexadecimal representation of a decimal or string value
`HOUR()` Extract the hour
`IF()` If/else construct
`IFNULL()` Null if/else construct
`IN()` Check whether a value is within a set of values
`INET_ATON()` Return the numeric value of an IP address
`INET_NTOA()` Return the IP address from a numeric value
`INET6_ATON()` Return the numeric value of an IPv6 address
`INET6_NTOA()` Return the IPv6 address from a numeric value
`INSERT()` Insert a substring at the specified position up to the specified number of characters
`INSTR()` Return the index of the first occurrence of substring
`INTERVAL()` Return the index of the argument that is less than the first argument
`IS_FREE_LOCK()` Checks whether the named lock is free
`IS_IPV4_COMPAT()` Return true if argument is an IPv4-compatible address
`IS_IPV4_MAPPED()` Return true if argument is an IPv4-mapped address
`IS_IPV4()` Return true if argument is an IPv4 address
`IS_IPV6()` Return true if argument is an IPv6 address
```IS NOT NULL``` NOT NULL value test
```IS NOT``` Test a value against a boolean
```IS NULL``` NULL value test
`IS_USED_LOCK()` Checks whether the named lock is in use. Return connection identifier if true.
`IS` Test a value against a boolean
`ISNULL()` Test whether the argument is NULL
`LAST_DAY` Return the last day of the month for the argument
`LAST_INSERT_ID()` Value of the AUTOINCREMENT column for the last INSERT
`LCASE()` Synonym for LOWER()
`LEAST()` Return the smallest argument
`<<` Left shift
`LEFT()` Return the leftmost number of characters as specified
`LENGTH()` Return the length of a string in bytes
`<=` Less than or equal operator
``` <``` Less than operator
`LIKE` Simple pattern matching
`LN()` Return the natural logarithm of the argument
`LOAD_FILE()` Load the named file
`LOCALTIME()`, `LOCALTIME` Synonym for NOW()
`LOCALTIMESTAMP`, `LOCALTIMESTAMP()` Synonym for NOW()
`LOCATE()` Return the position of the first occurrence of substring
`LOG10()` Return the base-10 logarithm of the argument
`LOG2()` Return the base-2 logarithm of the argument
`LOG()` Return the natural logarithm of the first argument
`LOWER()` Return the argument in lowercase
`LPAD()` Return the string argument, left-padded with the specified string
`LTRIM()` Remove leading spaces
`MAKE_SET()` Return a set of comma-separated strings that have the corresponding bit in bits set
`MAKEDATE()` Create a date from the year and day of year
`MAKETIME` MAKETIME()
`MASTER_POS_WAIT()` Block until the slave has read and applied all updates up to the specified position
`MATCH` Perform full-text search
`MAX()` Return the maximum value
`MD5()` Calculate MD5 checksum
`MICROSECOND()` Return the microseconds from argument
`MID()` Return a substring starting from the specified position
`MIN()` Return the minimum value
`-` Minus operator
`MINUTE()` Return the minute from the argument
`MOD()` Return the remainder
```% or MOD``` Modulo operator
`MONTH()` Return the month from the date passed
`MONTHNAME()` Return the name of the month
`NAME_CONST()` Causes the column to have the given name
```NOT BETWEEN ... AND ...``` Check whether a value is not within a range of values
`!=`, `<>` Not equal operator
```NOT IN()``` Check whether a value is not within a set of values
```NOT LIKE``` Negation of simple pattern matching
```NOT REGEXP``` Negation of REGEXP
`NOT`, `!` Negates value
`NOW()` Return the current date and time
`NULLIF()` Return NULL if expr1 = expr2
`OCT()` Return a string containing octal representation of a number
`OCTET_LENGTH()` A synonym for LENGTH()
`OLD_PASSWORD()` Return the value of the pre-4.1 implementation of PASSWORD
`||`, `OR` Logical OR
`ORD()` Return character code for leftmost character of the argument
`PASSWORD()` Calculate and return a password string
`PERIOD_ADD()` Add a period to a year-month
`PERIOD_DIFF()` Return the number of months between periods
`PI()` Return the value of pi
`+` Addition operator
`POSITION()` A synonym for LOCATE()
`POW()` Return the argument raised to the specified power
`POWER()` Return the argument raised to the specified power
```PROCEDURE ANALYSE()``` Analyze the results of a query
`QUARTER()` Return the quarter from a date argument
`QUOTE()` Escape the argument for use in an SQL statement
`RADIANS()` Return argument converted to radians
`RAND()` Return a random floating-point value
`REGEXP` Pattern matching using regular expressions
`RELEASE_LOCK()` Releases the named lock
`REPEAT()` Repeat a string the specified number of times
`REPLACE()` Replace occurrences of a specified string
`REVERSE()` Reverse the characters in a string
``` >>``` Right shift
`RIGHT()` Return the specified rightmost number of characters
`RLIKE` Synonym for REGEXP
`ROUND()` Round the argument
`ROW_COUNT()` The number of rows updated
`RPAD()` Append string the specified number of times
`RTRIM()` Remove trailing spaces
`SCHEMA()` A synonym for DATABASE()
`SEC_TO_TIME()` Converts seconds to 'HH:MM:SS' format
`SECOND()` Return the second (0-59)
`SESSION_USER()` Synonym for USER()
`SHA1()`, `SHA()` Calculate an SHA-1 160-bit checksum
`SHA2()` Calculate an SHA-2 checksum
`SIGN()` Return the sign of the argument
`SIN()` Return the sine of the argument
`SLEEP()` Sleep for a number of seconds
`SOUNDEX()` Return a soundex string
```SOUNDS LIKE``` Compare sounds
`SPACE()` Return a string of the specified number of spaces
`SQRT()` Return the square root of the argument
`STD()` Return the population standard deviation
`STDDEV_POP()` Return the population standard deviation
`STDDEV_SAMP()` Return the sample standard deviation
`STDDEV()` Return the population standard deviation
`STR_TO_DATE()` Convert a string to a date
`STRCMP()` Compare two strings
`SUBDATE()` A synonym for DATE_SUB() when invoked with three arguments
`SUBSTR()` Return the substring as specified
`SUBSTRING_INDEX()` Return a substring from a string before the specified number of occurrences of the delimiter
`SUBSTRING()` Return the substring as specified
`SUBTIME()` Subtract times
`SUM()` Return the sum
`SYSDATE()` Return the time at which the function executes
`SYSTEM_USER()` Synonym for USER()
`TAN()` Return the tangent of the argument
`TIME_FORMAT()` Format as time
`TIME_TO_SEC()` Return the argument converted to seconds
`TIME()` Extract the time portion of the expression passed
`TIMEDIFF()` Subtract time
`*` Multiplication operator
`TIMESTAMP()` With a single argument, this function returns the date or datetime expression; with two arguments, the sum of the arguments
`TIMESTAMPADD()` Add an interval to a datetime expression
`TIMESTAMPDIFF()` Subtract an interval from a datetime expression
`TO_BASE64()` Return the argument converted to a base-64 string
`TO_DAYS()` Return the date argument converted to days
`TO_SECONDS()` Return the date or datetime argument converted to seconds since Year 0
`TRIM()` Remove leading and trailing spaces
`TRUNCATE()` Truncate to specified number of decimal places
`UCASE()` Synonym for UPPER()
`-` Change the sign of the argument
`UNCOMPRESS()` Uncompress a string compressed
`UNCOMPRESSED_LENGTH()` Return the length of a string before compression
`UNHEX()` Return a string containing hex representation of a number
`UNIX_TIMESTAMP()` Return a UNIX timestamp
`UpdateXML()` Return replaced XML fragment
`UPPER()` Convert to uppercase
`USER()` The user name and host name provided by the client
`UTC_DATE()` Return the current UTC date
`UTC_TIME()` Return the current UTC time
`UTC_TIMESTAMP()` Return the current UTC date and time
`UUID_SHORT()` Return an integer-valued universal identifier
`UUID()` Return a Universal Unique Identifier (UUID)
`VALIDATE_PASSWORD_STRENGTH()` Determine strength of password
`VALUES()` Defines the values to be used during an INSERT
`VAR_POP()` Return the population standard variance
`VAR_SAMP()` Return the sample variance
`VARIANCE()` Return the population standard variance
`VERSION()` Returns a string that indicates the MySQL server version
`WAIT_UNTIL_SQL_THREAD_AFTER_GTIDS()` Wait until the slave SQL thread has executed all the given GTIDs. Returns: the number of events that were executed (or NULL, if GTID mode is not enabled).
`WEEK()` Return the week number
`WEEKDAY()` Return the weekday index
`WEEKOFYEAR()` Return the calendar week of the date (0-53)
`WEIGHT_STRING()` Return the weight string for a string
`XOR` Logical XOR
`YEAR()` Return the year
`YEARWEEK()` Return the year and week

Spec-Zone.ru - all specs in one place