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
`SQL_THREAD_WAIT_AFTER_GTIDS()`(deprecated 5.6.9)	OBSOLETE: Replaced by WAIT_UNTIL_SQL_THREAD_AFTER_GTIDS()
`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