MySQL Functions

This document introduces common MySQL functions and operators by category: numeric functions, string functions, datetime functions, aggregate functions, and flow-control functions. For more function usage, see the official MySQL documentation.

MySQL Numeric Functions

Function Name	Purpose
ABS	Returns the absolute value.
SQRT	Returns the square root.
MOD	Returns the remainder.
CEIL and CEILING	These two functions have the same behavior. They return the smallest integer not less than the argument, meaning round up.
FLOOR	Rounds down and converts the return value to a BIGINT.
RAND	Generates a random number between 0 and 1. When an integer parameter is passed, it is used to generate a repeatable sequence.
ROUND	Rounds the passed parameter.
SIGN	Returns the sign of the parameter.
POW and POWER	These two functions have the same behavior. They return the result of raising the parameter to a power.
SIN	Returns the sine value.
ASIN	Returns the arcsine value. It is the inverse of SIN.
COS	Returns the cosine value.
ACOS	Returns the arccosine value. It is the inverse of COS.
TAN	Returns the tangent value.
ATAN	Returns the arctangent value. It is the inverse of TAN.
COT	Returns the cotangent value.

MySQL String Functions

Function Name	Purpose
LENGTH	Calculates string length and returns the byte length of the string.
CONCAT	Concatenates strings. The returned result is the string produced by joining one or more parameters.
INSERT	Replaces a string.
LOWER	Converts letters in a string to lowercase.
UPPER	Converts letters in a string to uppercase.
LEFT	Extracts characters from the left side of a string and returns several characters from the left.
RIGHT	Extracts characters from the right side of a string and returns several characters from the right.
TRIM	Removes spaces from both sides of a string.
REPLACE	Replaces strings and returns the new string after replacement.
SUBSTRING	Extracts a string and returns characters of the specified length starting from the specified position.
REVERSE	Reverses a string and returns a string in the opposite order from the original string.

MySQL Date and Time Functions

Function Name	Purpose
CURDATE and CURRENT_DATE	These two functions have the same behavior and return the current system date.
CURTIME and CURRENT_TIME	These two functions have the same behavior and return the current system time.
NOW and SYSDATE	These two functions have the same behavior and return the current system date and time.
UNIX_TIMESTAMP	Gets the UNIX timestamp and returns an unsigned integer based on the UNIX timestamp.
FROM_UNIXTIME	Converts a UNIX timestamp to a time format. It is the inverse of UNIX_TIMESTAMP.
MONTH	Gets the month from a specified date.
MONTHNAME	Gets the English month name from a specified date.
DAYNAME	Gets the English weekday name corresponding to a specified date.
DAYOFWEEK	Gets the index position value of the specified date in a week.
WEEK	Gets which week of the year a specified date belongs to. The return value range may be 0-52 or 1-53.
DAYOFYEAR	Gets which day of the year a specified date is. The return value range is 1-366.
DAYOFMONTH	Gets which day of the month a specified date is. The return value range is 1-31.
YEAR	Gets the year. The return value range is 1970-2069.
TIME_TO_SEC	Converts a time parameter to seconds.
SEC_TO_TIME	Converts seconds to time. It is the inverse of TIME_TO_SEC.
DATE_ADD and ADDDATE	These two functions have the same behavior and add a specified time interval to a date.
DATE_SUB and SUBDATE	These two functions have the same behavior and subtract a specified time interval from a date.
ADDTIME	Time addition. Adds a specified time to the original time.
SUBTIME	Time subtraction. Subtracts a specified time from the original time.
DATEDIFF	Gets the interval between two dates and returns parameter 1 minus parameter 2.
DATE_FORMAT	Formats a specified date and returns a value in the specified format based on the parameter.
WEEKDAY	Gets the weekday index corresponding to a specified date.

MySQL Aggregate Functions

Function Name	Purpose
MAX	Queries the maximum value of a specified column.
MIN	Queries the minimum value of a specified column.
COUNT	Counts the number of rows in the query result.
SUM	Returns the sum of a specified column.
AVG	Returns the average value of data in a specified column.

MySQL Flow-Control Functions

Function Name	Purpose
IF	Judgment and flow control.
IFNULL	Checks whether a value is null.
CASE	Search statement.