## 12.1 Function and Operator Reference

Table 12.1 Functions/Operators

NameDescription
`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
`Area()` Return Polygon or MultiPolygon area
`AsBinary()`, `AsWKB()` Convert from internal geometry format to WKB
`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
`AsText()`, `AsWKT()` Convert from internal geometry format to WKT
`ASYMMETRIC_DECRYPT()` Decrypt ciphertext using private or public key
`ASYMMETRIC_DERIVE()` Derive symmetric key from asymmetric keys
`ASYMMETRIC_ENCRYPT()` Encrypt cleartext using private or public key
`ASYMMETRIC_SIGN()` Generate signature from digest
`ASYMMETRIC_VERIFY()` Verify that signature matches digest
`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
`~` Bitwise inversion
`|` Bitwise OR
`^` Bitwise XOR
`Buffer()` Return geometry of points within given distance from geometry
`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
`Centroid()` Return centroid as a point
`CHAR_LENGTH()` Return number of characters in argument
`CHAR()` Return the character for each integer passed
`CHARACTER_LENGTH()` 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
`Contains()` Whether MBR of one geometry contains MBR of another
`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
`CREATE_ASYMMETRIC_PRIV_KEY()` Create private key
`CREATE_ASYMMETRIC_PUB_KEY()` Create public key
`CREATE_DH_PARAMETERS()` Generate shared DH secret
`CREATE_DIGEST()` Generate digest from string
`Crosses()` Whether one geometry crosses another
`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
`Dimension()` Dimension of geometry
`Disjoint()` Whether MBRs of two geometries are disjoint
`DIV` Integer division
`/` Division operator
`ELT()` Return string at index number
`ENCODE()` Encode a string
`ENCRYPT()` Encrypt a string
`EndPoint()` End Point of LineString
`Envelope()` Return MBR of geometry
`<=>` NULL-safe equal to operator
`=` Equal operator
`Equals()` Whether MBRs of two geometries are equal
`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
`ExteriorRing()` Return exterior ring of Polygon
`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
`GeomCollFromText()`, `GeometryCollectionFromText()` Return geometry collection from WKT
`GeomCollFromWKB()`, `GeometryCollectionFromWKB()` Return geometry collection from WKB
`GeometryCollection()` Construct geometry collection from geometries
`GeometryN()` Return N-th geometry from geometry collection
`GeometryType()` Return name of geometry type
`GeomFromText()`, `GeometryFromText()` Return geometry from WKT
`GeomFromWKB()`, `GeometryFromWKB()` Return geometry from WKB
`GET_FORMAT()` Return a date format string
`GET_LOCK()` Get a named lock
`GLength()` Return length of LineString
`>=` 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
`InteriorRingN()` Return N-th interior ring of Polygon
`Intersects()` Whether MBRs of two geometries intersect
`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
`IsClosed()` Whether a geometry is closed and simple
`IsEmpty()` Placeholder function
`ISNULL()` Test whether the argument is NULL
`IsSimple()` Whether a geometry is simple
`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
`LineFromText()`, `LineStringFromText()` Construct LineString from WKT
`LineFromWKB()`, `LineStringFromWKB()` Construct LineString from WKB
`LineString()` Construct LineString from Point values
`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()` Create time from hour, minute, second
`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
`MBRContains()` Whether MBR of one geometry contains MBR of another
`MBRDisjoint()` Whether MBRs of two geometries are disjoint
`MBREqual()` Whether MBRs of two geometries are equal
`MBRIntersects()` Whether MBRs of two geometries intersect
`MBROverlaps()` Whether MBRs of two geometries overlap
`MBRTouches()` Whether MBRs of two geometries touch
`MBRWithin()` Whether MBR of one geometry is within MBR of another
`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
`MLineFromText()`, `MultiLineStringFromText()` Construct MultiLineString from WKT
`MLineFromWKB()`, `MultiLineStringFromWKB()` Construct MultiLineString from WKB
`MOD()` Return the remainder
`%`, `MOD` Modulo operator
`MONTH()` Return the month from the date passed
`MONTHNAME()` Return the name of the month
`MPointFromText()`, `MultiPointFromText()` Construct MultiPoint from WKT
`MPointFromWKB()`, `MultiPointFromWKB()` Construct MultiPoint from WKB
`MPolyFromText()`, `MultiPolygonFromText()` Construct MultiPolygon from WKT
`MPolyFromWKB()`, `MultiPolygonFromWKB()` Construct MultiPolygon from WKB
`MultiLineString()` Contruct MultiLineString from LineString values
`MultiPoint()` Construct MultiPoint from Point values
`MultiPolygon()` Construct MultiPolygon from Polygon values
`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
`NumGeometries()` Return number of geometries in geometry collection
`NumInteriorRings()` Return number of interior rings in Polygon
`NumPoints()` Return number of points in LineString
`OCT()` Return a string containing octal representation of a number
`OCTET_LENGTH()` Synonym for LENGTH()
`OLD_PASSWORD()` (deprecated 5.6.5) Return the value of the pre-4.1 implementation of PASSWORD
`||`, `OR` Logical OR
`ORD()` Return character code for leftmost character of the argument
`Overlaps()` Whether MBRs of two geometries overlap
`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
`Point()` Construct Point from coordinates
`PointFromText()` Construct Point from WKT
`PointFromWKB()` Construct Point from WKB
`PointN()` Return N-th point from LineString
`PolyFromText()`, `PolygonFromText()` Construct Polygon from WKT
`PolyFromWKB()`, `PolygonFromWKB()` Construct Polygon from WKB
`Polygon()` Construct Polygon from LineString arguments
`POSITION()` 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
`RANDOM_BYTES()` Return a random byte vector
`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()` 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
`SRID()` Return spatial reference system ID for geometry
`ST_Area()` Return Polygon or MultiPolygon area
`ST_AsBinary()`, `ST_AsWKB()` Convert from internal geometry format to WKB
`ST_AsText()`, `ST_AsWKT()` Convert from internal geometry format to WKT
`ST_Buffer()` Return geometry of points within given distance from geometry
`ST_Centroid()` Return centroid as a point
`ST_Contains()` Whether one geometry contains another
`ST_Crosses()` Whether one geometry crosses another
`ST_Difference()` Return point set difference of two geometries
`ST_Dimension()` Dimension of geometry
`ST_Disjoint()` Whether one geometry is disjoint from another
`ST_Distance()` The distance of one geometry from another
`ST_EndPoint()` End Point of LineString
`ST_Envelope()` Return MBR of geometry
`ST_Equals()` Whether one geometry is equal to another
`ST_ExteriorRing()` Return exterior ring of Polygon
`ST_GeomCollFromText()`, `ST_GeometryCollectionFromText()`, `ST_GeomCollFromTxt()` Return geometry collection from WKT
`ST_GeomCollFromWKB()`, `ST_GeometryCollectionFromWKB()` Return geometry collection from WKB
`ST_GeometryN()` Return N-th geometry from geometry collection
`ST_GeometryType()` Return name of geometry type
`ST_GeomFromText()`, `ST_GeometryFromText()` Return geometry from WKT
`ST_GeomFromWKB()`, `ST_GeometryFromWKB()` Return geometry from WKB
`ST_InteriorRingN()` Return N-th interior ring of Polygon
`ST_Intersection()` Return point set intersection of two geometries
`ST_Intersects()` Whether one geometry intersects another
`ST_IsClosed()` Whether a geometry is closed and simple
`ST_IsEmpty()` Placeholder function
`ST_IsSimple()` Whether a geometry is simple
`ST_LineFromText()` Construct LineString from WKT
`ST_LineFromWKB()`, `ST_LineStringFromWKB()` Construct LineString from WKB
`ST_NumGeometries()` Return number of geometries in geometry collection
`ST_NumInteriorRing()`, `ST_NumInteriorRings()` Return number of interior rings in Polygon
`ST_NumPoints()` Return number of points in LineString
`ST_Overlaps()` Whether one geometry overlaps another
`ST_PointFromText()` Construct Point from WKT
`ST_PointFromWKB()` Construct Point from WKB
`ST_PointN()` Return N-th point from LineString
`ST_PolyFromText()`, `ST_PolygonFromText()` Construct Polygon from WKT
`ST_PolyFromWKB()`, `ST_PolygonFromWKB()` Construct Polygon from WKB
`ST_SRID()` Return spatial reference system ID for geometry
`ST_StartPoint()` Start Point of LineString
`ST_SymDifference()` Return point set symmetric difference of two geometries
`ST_Touches()` Whether one geometry touches another
`ST_Union()` Return point set union of two geometries
`ST_Within()` Whether one geometry is within another
`ST_X()` Return X coordinate of Point
`ST_Y()` Return Y coordinate of Point
`StartPoint()` Start Point of LineString
`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()` 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
`Touches()` Whether one geometry touches another
`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()` Return a string that indicates the MySQL server version
`WAIT_UNTIL_SQL_THREAD_AFTER_GTIDS()` Wait until the given GTIDs have executed on slave.
`WEEK()` Return the week number
`WEEKDAY()` Return the weekday index
`WEEKOFYEAR()` Return the calendar week of the date (1-53)
`WEIGHT_STRING()` Return the weight string for a string
`Within()` Whether MBR of one geometry is within MBR of another
`X()` Return X coordinate of Point
`XOR` Logical XOR
`Y()` Return Y coordinate of Point
`YEAR()` Return the year
`YEARWEEK()` Return the year and week