NameDescription
+Addition operator
/Division operator
*Multiplication operator
%Modulo operator. Returns the remainder of N divided by M
-Subtraction operator
!=Not equals
<Less than
<=Less than or equal
<=>NULL-safe equal
=Equal
>Greater than
>=Greater than or equal
&Bitwise AND
<<Shift left
>>Shift right
^Bitwise XOR
!Logical NOT
&&Logical AND
XORLogical XOR
||Logical OR
|Bitwise OR
:=Assignment operator
=Assignment and comparison operator
~Bitwise NOT
ABSReturns an absolute value
ACOSReturns an arc cosine
ADDDATEAdd days or another interval to a date
ADDTIMEAdds a time to a time or datetime
AES_DECRYPTDecryption data encrypted with AES_ENCRYPT
AES_ENCRYPTEncrypts a string with the AES algorithm
AREASynonym for ST_AREA
AsBinarySynonym for ST_AsBinary
ASCIINumeric ASCII value of leftmost character
ASINReturns the arc sine
AsTextSynonym for ST_AsText
AsWKBSynonym for ST_AsBinary
AsWKTSynonym for ST_AsText
ATANReturns the arc tangent
ATAN2Returns the arc tangent of two variables
AVGReturns the average value
BENCHMARKExecutes an expression repeatedly
BETWEEN ANDTrue if expression between two values
BINReturns binary value
BINARY OPERATORCasts to a binary string
BINLOG_GTID_POSReturns a string representation of the corresponding GTID position
BIT_ANDBitwise AND
BIT_COUNTReturns the number of set bits
BIT_LENGTHReturns the length of a string in bits
BIT_ORBitwise OR
BIT_XORBitwise XOR
BOUNDARYSynonym for ST_BOUNDARY
BUFFERSynonym for ST_BUFFER
CASEReturns the result where value=compare_value or for the first condition that is true
CASTCasts a value of one type to another type
CEILSynonym for CEILING()
CEILINGReturns the smallest integer not less than X
CENTROIDSynonym for ST_CENTROID
CHAR FunctionReturns string based on the integer values for the individual characters
CHARACTER_LENGTHSynonym for CHAR_LENGTH()
CHAR_LENGTHLength of the string in characters
CHARSETReturns the character set
COALESCEReturns the first non-NULL parameter
COERCIBILITYReturns the collation coercibility value
COLLATIONCollation of the string argument
COLUMN_ADDAdds or updates dynamic columns
COLUMN_CHECKChecks if a dynamic column blob is valid
COLUMN_CREATEReturns a dynamic columns blob
COLUMN_DELETEDeletes a dynamic column
COLUMN_EXISTSChecks is a column exists
COLUMN_GETGets a dynamic column value by name
COLUMN_JSONReturns a JSON representation of dynamic column blob data
COLUMN_LISTReturns comma-separated list
COMPRESSReturns a binary, compressed string
CONCATReturns concatenated string
CONCAT_WSConcatenate with separator
CONNECTION_IDConnection thread ID
CONTAINSWhether one geometry contains another
CONVERTConvert a value from one type to another type
CONVConverts numbers between different number bases
CONVERT_TZConverts a datetime from on time zone to another
CONVEXHULLSynonym for ST_CONVEXHULL
COSReturns the cosine
COTReturns the cotangent
COUNTReturns count of non-null values
COUNT DISTINCTReturns count of number of different non-NULL values
CRC32Computes a cyclic redundancy check value
CROSSESWhether two geometries spatially cross
CUME_DISTWindow function that returns the cumulative distribution of a given row
CURDATEReturns the current date
CURRENT_DATESynonym for CURDATE()
CURRENT_ROLECurrent role name
CURRENT_TIMESynonym for CURTIME()
CURRENT_TIMESTAMPSynonym for NOW()
CURRENT_USERUsername/host that authenicated the current client
CURTIMEReturns the current time
DATABASECurrent default database
DATE FUNCTIONExtracts the date portion of a datetime
DATEDIFFDifference in days between two date/time values
DATE_ADDDate arithmetic - addition
DATE_FORMATFormats the date value according to the format string
DATE_SUBDate arithmetic - subtraction
DAYSynonym for DAYOFMONTH()
DAYNAMEReturn the name of the weekday
DAYOFMONTHReturns the day of the month
DAYOFWEEKReturns the day of the week index
DAYOFYEARReturns the day of the year
DECODEDecrypts a string encoded with ENCODE()
DECODE_HISTOGRAMReturns comma separated numerics corresponding to a probability distribution represented by a histogram
DEFAULTReturns column default
DEGREESConverts from radians to degrees
DENSE_RANKRank of a given row with identical values receiving the same result, no skipping
DES_DECRYPTDecrypts a string encrypted with DES_ENCRYPT()
DES_ENCRYPTEncrypts a string using the Triple-DES algorithm
DIMENSIONSynonym for ST_DIMENSION
DISJOINTWhether the two elements do not intersect
DIVInteger division
ELTReturns the N'th element from a set of strings
ENCODEEncrypts a string
ENCRYPTEncrypts a string with Unix crypt()
ENDPOINTSynonym for ST_ENDPOINT
ENVELOPESynonym for ST_ENVELOPE
EQUALSIndicates whether two geometries are spatially equal
EXPe raised to the power of the argument
EXPORT_SETReturns an on string for every bit set, an off string for every bit not set
ExteriorRingSynonym for ST_ExteriorRing
EXTRACTExtracts a portion of the date
EXTRACTVALUEReturns the text of the first text node matched by the XPath expression
FIELDReturns the index position of a string in a list
FIND_IN_SETReturns the position of a string in a set of strings
FLOORLargest integer value not greater than the argument
FORMATFormats a number
FOUND_ROWSNumber of (potentially) returned rows
FROM_BASE64Given a base-64 encoded string, returns the decoded result as a binary string
FROM_DAYSReturns a date given a day
FROM_UNIXTIMEReturns a datetime from a Unix timestamp
GeomCollFromTextSynonym for ST_GeomCollFromText
GeomCollFromWKBSynonym for ST_GeomCollFromWKB
GeometryCollectionFromTextSynonym for ST_GeomCollFromText
GeometryCollectionFromWKBSynonym for ST_GeomCollFromWKB
GeometryFromTextSynonym for ST_GeomFromText
GeometryFromWKBSynonym for ST_GeomFromWKB
GeomFromTextSynonym for ST_GeomFromText
GeomFromWKBSynonym for ST_GeomFromWKB
GeometryNSynonym for ST_GeometryN
GEOMETRYCOLLECTIONConstructs a WKB GeometryCollection
GeometryTypeSynonym for ST_GeometryType
GET_FORMATReturns a format string
GET_LOCKObtain LOCK
GLENGTHLength of a LineString value
GREATESTReturns the largest argument
GROUP_CONCATReturns string with concatenated values from a group
HEXReturns hexadecimal value
HOURReturns the hour
IFIf expr1 is TRUE, returns expr2; otherwise returns expr3
IFNULLCheck whether an expression is NULL
INTrue if expression equals any of the values in the list
INTERVALIndex of the argument that is less than the first argument
INET6_ATONGiven an IPv6 or IPv4 network address, returns a VARBINARY numeric value
INET6_NTOAGiven an IPv6 or IPv4 network address, returns the address as a nonbinary string
INET_ATONReturns numeric value of IPv4 address
INET_NTOAReturns dotted-quad representation of IPv4 address
INSERT FunctionReplaces a part of a string with another string
INSTRReturns the position of a string withing a string
InteriorRingNSynonym for ST_InteriorRingN
INTERSECTSIndicates whether two geometries spatially intersect
ISTests whether a boolean is TRUE, FALSE, or UNKNOWN
IsClosedSynonym for ST_IsClosed
IsEmptySynonym for ST_IsEmpty
IS_FREE_LOCKChecks whether lock is free to use
IS_IPV4Whether or not an expression is a valid IPv4 address
IS_IPV4_COMPATWhether or not an IPv6 address is IPv4-compatible
IS_IPV4_MAPPEDWhether an IPv6 address is a valid IPv4-mapped address
IS_IPV6Whether or not an expression is a valid IPv6 address
IS NOTTests whether a boolean value is not TRUE, FALSE, or UNKNOWN
IS NOT NULLTests whether a value is not NULL
IS NULLTests whether a value is NULL
ISNULLChecks if an expression is NULL
IsRingSynonym for ST_IsRing
IsSimpleSynonym for ST_IsSimple
IS_USED_LOCKCheck if lock is in use
JSON_ARRAYReturns a JSON array containing the listed values
JSON_ARRAY_APPENDAppends values to the end of the given arrays within a JSON document
JSON_ARRAY_INSERTInserts a value into a JSON document
JSON_COMPACTRemoves all unnecessary spaces so the json document is as short as possible
JSON_CONTAINSWhether a value is found in a given JSON document or at a specified path within the document
JSON_CONTAINS_PATHIndicates whether the given JSON document contains data at the specified path or paths
JSON_DEPTHMaximum depth of a JSON document
JSON_DETAILEDRepresents JSON in the most understandable way emphasizing nested structures
JSON_EXISTSDetermines whether a specified JSON value exists in the given data
JSON_EXTRACTExtracts data from a JSON document
JSON_INSERTInserts data into a JSON document
JSON_KEYSReturns keys from top-level value of a JSON object or top-level keys from the path
JSON_LENGTHReturns the length of a JSON document, or the length of a value within the document
JSON_LOOSEAdds spaces to a JSON document to make it look more readable
JSON_MERGEMerges the given JSON documents
JSON_OBJECTReturns a JSON object containing the given key/value pairs
JSON_QUERYGiven a JSON document, returns an object or array specified by the path
JSON_QUOTEQuotes a string as a JSON value
JSON_REMOVERemoves data from a JSON document
JSON_REPLACEReplaces existing values in a JSON document
JSON_SEARCHReturns the path to the given string within a JSON document
JSON_SETUpdates or inserts data into a JSON document
JSON_TYPEReturns the type of a JSON value
JSON_UNQUOTEUnquotes a JSON value, returning a string
JSON_VALIDWhether a value is a valid JSON document or not
JSON_VALUEGiven a JSON document, returns the specified scalar
LAST_DAYReturns the last day of the month
LAST_INSERT_IDLast inserted autoinc value
LAST_VALUEReturns the last value in a list
LASTVALGet last value generated from a sequence
LCASESynonym for [LOWER()
LEASTReturns the smallest argument
LEFTReturns the leftmost characters from a string
LENGTHLength of the string in bytes
LIKEWhether expression matches a pattern
LineFromTextSynonym for ST_LineFromText
LineFromWKBSynonym for ST_LineFromWKB
LINESTRINGConstructs a WKB LineString value from a number of WKB Point arguments
LineStringFromTextSynonym for ST_LineFromText
LineStringFromWKBSynonym for ST_LineFromWKB
LNReturns natural logarithm
LOAD_FILEReturns file contents as a string
LOCALTIMESynonym for NOW()
LOCALTIMESTAMPSynonym for NOW()
LOCATEReturns the position of a substring in a string
LOGReturns the natural logarithm
LOG10Returns the base-10 logarithm
LOG2Returns the base-2 logarithm
LOWERReturns a string with all characters changed to lowercase
LPADReturns the string left-padded with another string to a given length
LTRIMReturns the string with leading space characters removed
MAKE_SETMake a set of strings that matches a bitmask
MAKEDATEReturns a date given a year and day
MAKETIMEReturns a time
MASTER_GTID_WAITWait until slave reaches the GTID position
MASTER_POS_WAITBlocks until the slave has applied all specified updates
MATCH AGAINSTPerform a fulltext search on a fulltext index
MAXReturns the maximum value
MBRContainsIndicates one Minimum Bounding Rectangle contains another
MBRDisjointIndicates whether the Minimum Bounding Rectangles of two geometries are disjoint
MBREqualWhether the Minimum Bounding Rectangles of two geometries are the same.
MBRIntersectsIndicates whether the Minimum Bounding Rectangles of the two geometries intersect
MBROverlapsWhether the Minimum Bounding Rectangles of two geometries overlap.
MBRTouchesWhether the Minimum Bounding Rectangles of two geometries touch.
MBRWithinIndicates whether one Minimum Bounding Rectangle is within another
MD5MD5 checksum
MICROSECONDReturns microseconds from a date or datetime
MIDSynonym for SUBSTRING(str,pos,len)
MINReturns the minimum value
MINUTEReturns a minute from 0 to 59
MLineFromTextConstructs MULTILINESTRING using its WKT representation and SRID
MLineFromWKBConstructs a MULTILINESTRING
MODModulo operation. Remainder of N divided by M
MONTHReturns a month from 1 to 12
MONTHNAMEReturns the full name of the month
MPointFromTextConstructs a MULTIPOINT value using its WKT and SRID
MPointFromWKBConstructs a MULTIPOINT value using its WKB representation and SRID
MPolyFromTextConstructs a MULTIPOLYGON value
MPolyFromWKBConstructs a MULTIPOLYGON value using its WKB representation and SRID
MultiLineStringFromTextSynonym for MLineFromText
MultiLineStringFromWKBA synonym for MLineFromWKB
MULTIPOINTConstructs a WKB MultiPoint value
MultiPointFromTextSynonym for MPointFromText
MultiPointFromWKBSynonym for MPointFromWKB
MULTIPOLYGONConstructs a WKB MultiPolygon
MultiPolygonFromTextSynonym for MPolyFromText
MultiPolygonFromWKBSynonym for MPolyFromWKB
MULTILINESTRINGConstructs a MultiLineString value
NAME_CONSTReturns the given value
NOT LIKESame as NOT(expr LIKE pat [ESCAPE 'escape_char'])
NOT REGEXPSame as NOT (expr REGEXP pat)
NULLIFReturns NULL if expr1 = expr2
NEXTVALGenerate next value for sequence
NOT BETWEENSame as NOT (expr BETWEEN min AND max)
NOT INSame as NOT (expr IN (value,...))
NOWReturns the current date and time
NTILEReturns an integer indicating which group a given row falls into
NumGeometriesSynonym for ST_NumGeometries
NumInteriorRingsSynonym for NumInteriorRings
NumPointsSynonym for ST_NumPoints
OCTReturns octal value
OCTET_LENGTHSynonym for LENGTH()
OLD_PASSWORDPre MySQL 4.1 password implementation
ORDReturn ASCII or character code
OVERLAPSIndicates whether two elements spatially overlap
PASSWORDCalculates a password string
PERCENT_RANKWindow function that returns the relative percent rank of a given row
PERIOD_ADDAdd months to a period
PERIOD_DIFFNumber of months between two periods
PIReturns the value of π (pi)
POINTConstructs a WKB Point
PointFromTextSynonym for ST_PointFromText
PointFromWKBSynonym for PointFromWKB
PointNSynonym for PointN
PointOnSurfaceSynonym for ST_PointOnSurface
POLYGONConstructs a WKB Polygon value from a number of WKB LineString arguments
PolyFromTextSynonym for ST_PolyFromText
PolyFromWKBSynonym for ST_PolyFromWKB
PolygonFromTextSynonym for ST_PolyFromText
PolygonFromWKBSynonym for ST_PolyFromWKB
POSITIONReturns the position of a substring in a string
POWReturns X raised to the power of Y
POWERSynonym for POW()
QUARTERReturns year quarter from 1 to 4
QUOTEReturns quoted, properly escaped string
RADIANSConverts from degrees to radians
RANDRandom floating-point value
RANKRank of a given row with identical values receiving the same result
REGEXPPerforms pattern matching
REGEXP_INSTRPosition of the first appearance of a regex
REGEXP_REPLACEReplaces all occurrences of a pattern
REGEXP_SUBSTRReturns the matching part of a string
RELEASE_LOCKReleases lock obtained with GET_LOCK()
REPEAT FunctionReturns a string repeated a number of times
REPLACE FunctionReplace occurrences of a string
REVERSEReverses the order of a string
RIGHTReturns the rightmost N characters from a string
RLIKESynonym for REGEXP()
RPADReturns the string right-padded with another string to a given length
ROUNDRounds a number
ROW_COUNTNumber of rows affected by previous statement
ROW_NUMBERRow number of a given row with identical values receiving a different result
RTRIMReturns the string with trailing space characters removed
SCHEMASynonym for DATABASE()
SECONDReturns the second of a time
SEC_TO_TIMEConverts a second to a time
SETVALSet the next value to be returned by a sequence
SESSION_USERSynonym for USER()
SHASynonym for SHA1()
SHA1Calculates an SHA-1 checksum
SHA2Calculates an SHA-2 checksum
SIGNReturns 1, 0 or -1
SINReturns the sine
SLEEPPauses for the given number of seconds
SOUNDEXReturns a string based on how the string sounds
SOUNDS LIKESOUNDEX(expr1) = SOUNDEX(expr2)
SPACEReturns a string of space characters
SPIDER_BG_DIRECT_SQLBackground SQL execution
SPIDER_COPY_TABLESCopy table data
SPIDER_DIRECT_SQLExecute SQL on the remote server
SPIDER_FLUSH_TABLE_MON_CACHERefreshing Spider monitoring server information
SQRTSquare root
SRIDSynonym for ST_SRID
ST_AREAArea of a Polygon
ST_AsBinaryConverts a value to its WKB representation
ST_AsTextConverts a value to its WKT-Definition
ST_AsWKBSynonym for ST_AsBinary
ST_ASWKTSynonym for ST_ASTEXT()
ST_BOUNDARYReturns a geometry that is the closure of a combinatorial boundary
ST_BUFFERA new geometry with a buffer added to the original geometry
ST_CENTROIDThe mathematical centroid (geometric center) for a MultiPolygon
ST_CONTAINSWhether one geometry is contained by another
ST_CONVEXHULLThe minimum convex geometry enclosing all geometries within the set
ST_CROSSESWhether two geometries spatially cross
ST_DIFFERENCEPoint set difference
ST_DIMENSIONInherent dimension of a geometry value
ST_DISJOINTWhether one geometry is spatially disjoint from another
ST_DISTANCEThe distance between two geometries
ST_ENDPOINTReturns the endpoint of a LineString
ST_ENVELOPEReturns the Minimum Bounding Rectangle for a geometry value
ST_EQUALSWhether two geometries are spatoially equal
ST_ExteriorRingReturns the exterior ring of a Polygon as a LineString
ST_GeomCollFromTextConstructs a GEOMETRYCOLLECTION value
ST_GeomCollFromWKBConstructs a GEOMETRYCOLLECTION value from a WKB
ST_GeometryCollectionFromTextSynonym for ST_GeomCollFromText
ST_GeometryCollectionFromWKBSynonym for ST_GeomCollFromWKB
ST_GeometryFromTextSynonym for ST_GeomFromText
ST_GeometryFromWKBSynonym for ST_GeomFromWKB
ST_GEOMETRYNReturns the N-th geometry in a GeometryCollection
ST_GEOMETRYTYPEReturns name of the geometry type of which a given geometry instance is a member
ST_GeomFromTextConstructs a geometry value using its WKT and SRID
ST_GeomFromWKBConstructs a geometry value using its WKB representation and SRID
ST_InteriorRingNReturns the N-th interior ring for a Polygon
ST_INTERSECTIONThe intersection, or shared portion, of two geometries
ST_INTERSECTSWhether two geometries spatially intersect
ST_ISCLOSEDReturns true if a given LINESTRING's start and end points are the same
ST_ISEMPTYIndicated validity of geometry value
ST_IsRingReturns true if a given LINESTRING is both ST_IsClosed and ST_IsSimple
ST_IsSimpleReturns true if the given Geometry has no anomalous geometric points
ST_LENGTHLength of a LineString value
ST_LineFromTextCreates a linestring value
ST_LineFromWKBConstructs a LINESTRING using its WKB and SRID
ST_LineStringFromTextSynonym for ST_LineFromText
ST_LineStringFromWKBSynonym for ST_LineFromWKB
ST_NUMGEOMETRIESNumber of geometries in a GeometryCollection
ST_NumInteriorRingsNumber of interior rings in a Polygon
ST_NUMPOINTSReturns the number of Point objects in a LineString
ST_OVERLAPSWhether two geometries overlap
ST_PointFromTextConstructs a POINT value
ST_PointFromWKBConstructs POINT using its WKB and SRID
ST_POINTNReturns the N-th Point in the LineString
ST_POINTONSURFACEReturns a POINT guaranteed to intersect a surface
ST_PolyFromTextConstructs a POLYGON value
ST_PolyFromWKBConstructs POLYGON value using its WKB representation and SRID
ST_PolygonFromTextSynonym for ST_PolyFromText
ST_PolygonFromWKBSynonym for ST_PolyFromWKB
ST_RELATEReturns true if two geometries are related
ST_SRIDReturns a Spatial Reference System ID
ST_STARTPOINTReturns the start point of a LineString
ST_SYMDIFFERENCEPortions of two geometries that don't intersect
ST_TOUCHESWhether one geometry g1 spatially touches another
ST_UNIONUnion of two geometries
ST_WITHINWhether one geometry is within another
ST_XX-coordinate value for a point
ST_YY-coordinate for a point
STARTPOINTSynonym for ST_StartPoint
STDPopulation standard deviation
STDDEVPopulation standard deviation
STDDEV_POPReturns the population standard deviation
STDDEV_SAMPStandard deviation
STR_TO_DATEConverts a string to date
STRCMPCompares two strings in sort order
SUBDATESubtract a date unit or number of days
SUBSTRReturns a substring from string starting at a given position
SUBSTRINGReturns a substring from string starting at a given position
SUBSTRING_INDEXReturns the substring from string before count occurrences of a delimiter
SUBTIMESubtracts a time from a date/time
SUMSum total
SYSDATEReturns the current date and time
SYSTEM_USERSynonym for USER()
TANReturns the tangent
TIME functionExtracts the time
TIMEDIFFReturns the difference between two date/times
TIMESTAMP FUNCTIONReturn the datetime, or add a time to a date/time
TIMESTAMPADDAdd interval to a date or datetime
TIMESTAMPDIFFDifference between two datetimes
TIME_FORMATFormats the time value according to the format string
TIME_TO_SECReturns the time argument, converted to seconds
TO_BASE64Converts a string to its base-64 encoded form
TO_DAYSNumber of days since year 0
TO_SECONDSNumber of seconds since year 0
TOUCHESWhether two geometries spatially touch
TRIMReturns a string with all given prefixes or suffixes removed
TRUNCATETruncates X to D decimal places
UCASESynonym for UPPER]]()
UNHEXInterprets pairs of hex digits as a number and converts to the character represented by the number
UNCOMPRESSUncompresses string compressed with COMPRESS()
UNCOMPRESSED_LENGTHReturns length of a string before being compressed with COMPRESS()
UNIX_TIMESTAMPReturns a Unix timestamp
UPDATEXMLReplace XML
UPPERChanges string to uppercase
USERCurrent user/host
UTC_DATEReturns the current UTC date
UTC_TIMEReturns the current UTC time
UTC_TIMESTAMPReturns the current UTC date and time
UUIDReturns a Universal Unique Identifier
UUID_SHORTReturn short universal identifier
VALUESRefer to columns in INSERT ... ON DUPLICATE KEY UPDATE
VAR_POPPopulation standard variance
VAR_SAMPReturns the sample variance
VARIANCEPopulation standard variance
VERSIONMariaDB server version
WEEKReturns the week number
WEEKDAYReturns the weekday index
WEEKOFYEARReturns the calendar week of the date as a number in the range from 1 to 53
WEIGHT_STRINGWeight of the input string
WITHINIndicate whether a geographic element is spacially within another
XSynonym for ST_X
YSynonym for ST_Y
YEARReturns the year for the given date
YEARWEEKReturns year and week for a date

Comments

Comments loading...