Skip to main content

MySQL Functions

This document introduces some commonly used MySQL functions and operators according to numeric functions, string functions, date and time functions, aggregate functions, and flow control functions. For more function usage, please refer to: MySQL Official Documentation

MySQL Numeric Functions

Function NamePurpose
ABSCalculate absolute value
SQRTCalculate square root
MODCalculate remainder
CEIL and CEILINGBoth functions have the same functionality, returning the smallest integer not less than the parameter, i.e., rounding up
FLOORRound down, return value converted to a BIGINT
RANDGenerate a random number between 0~1, when an integer parameter is passed, used to generate repeated sequences
ROUNDRound the passed parameter
SIGNReturn the sign of the parameter
POW and POWERBoth functions have the same functionality, returning the result value of the specified power of the passed parameter
SINCalculate sine value
ASINCalculate arcsine value, inverse function of SIN
COSCalculate cosine value
ACOSCalculate arccosine value, inverse function of COS
TANCalculate tangent value
ATANCalculate arctangent value, inverse function of TAN
COTCalculate cotangent value

MySQL String Functions

Function NamePurpose
LENGTHCalculate string length function, returns the byte length of the string
CONCATMerge string function, returns the string produced by connecting parameters, parameters can be one or more
INSERTReplace string function
LOWERConvert letters in string to lowercase
UPPERConvert letters in string to uppercase
LEFTExtract string from left side, returns several characters on the left side of the string
RIGHTExtract string from right side, returns several characters on the right side of the string
TRIMDelete spaces on both sides of the string
REPLACEString replacement function, returns the new string after replacement
SUBSTRINGExtract string, returns characters of specified length starting from specified position
REVERSEString reverse (inverse order) function, returns string with opposite order to original string

MySQL Date and Time Functions

Function NamePurpose
CURDATE and CURRENT_DATEBoth functions have the same effect, returning the current system date value
CURTIME and CURRENT_TIMEBoth functions have the same effect, returning the current system time value
NOW and SYSDATEBoth functions have the same effect, returning the current system date and time value
UNIX_TIMESTAMPGet UNIX timestamp function, returns an unsigned integer based on UNIX timestamp
FROM_UNIXTIMEConvert UNIX timestamp to time format, inverse function of UNIX_TIMESTAMP
MONTHGet the month in the specified date
MONTHNAMEGet the English name of the month in the specified date
DAYNAMEGet the English name of the day of the week corresponding to the specified date
DAYOFWEEKGet the index position value of the week corresponding to the specified date
WEEKGet which week of the year the specified date is, whether the return value range is 052 or 153
DAYOFYEARGet which day of the year the specified date is, return value range is 1~366
DAYOFMONTHGet which day of the month the specified date is, return value range is 1~31
YEARGet year, return value range is 1970~2069
TIME_TO_SECConvert time parameter to seconds
SEC_TO_TIMEConvert seconds to time, inverse function of TIME_TO_SEC
DATE_ADD and ADDDATEBoth functions have the same functionality, both add specified time interval to date
DATE_SUB and SUBDATEBoth functions have the same functionality, both subtract specified time interval from date
ADDTIMETime addition operation, add specified time to original time
SUBTIMETime subtraction operation, subtract specified time from original time
DATEDIFFGet the interval between two dates, returns the value of parameter 1 minus parameter 2
DATE_FORMATFormat the specified date, returns value in specified format according to parameters
WEEKDAYGet the corresponding workday index of the specified date within a week

MySQL Aggregate Functions

Function NamePurpose
MAXQuery the maximum value of the specified column
MINQuery the minimum value of the specified column
COUNTCount the number of rows in the query result
SUMSum, returns the sum of the specified column
AVGCalculate average, returns the average value of the specified column data

MySQL Flow Control Functions

Function NamePurpose
IFJudgment, flow control
IFNULLJudge whether it is empty
CASESearch statement