MySQL Functions (a comprehensive list)
MySQL String Functions
Function | Description |
ABS | Returns the absolute value of a number |
ACOS | Returns the arc cosine of a number |
ASIN | Returns the arc sine of a number |
ATAN | Returns the arc tangent of one or two numbers |
ATAN2 | Returns the arc tangent of two numbers |
AVG | Returns the average value of an expression |
CEIL | Returns the smallest integer value that is >= to a number |
CEILING | Returns the smallest integer value that is >= to a number |
COS | Returns the cosine of a number |
COT | Returns the cotangent of a number |
COUNT | Returns the number of records returned by a select query |
DEGREES | Converts a value in radians to degrees |
DIV | Used for integer division |
EXP | Returns e raised to the power of a specified number |
FLOOR | Returns the largest integer value that is <= to a number |
GREATEST | Returns the greatest value of the list of arguments |
LEAST | Returns the smallest value of the list of arguments |
LN | Returns the natural logarithm of a number |
LOG | Returns the natural logarithm of a number, or the logarithm of a number to a specified base |
LOG10 | Returns the natural logarithm of a number to base 10 |
LOG2 | Returns the natural logarithm of a number to base 2 |
MAX | Returns the maximum value in a set of values |
MIN | Returns the minimum value in a set of values |
MOD | Returns the remainder of a number divided by another number |
PI | Returns the value of PI |
POW | Returns the value of a number raised to the power of another number |
POWER | Returns the value of a number raised to the power of another number |
RADIANS | Converts a degree value into radians |
RAND | Returns a random number |
ROUND | Rounds a number to a specified number of decimal places |
SIGN | Returns the sign of a number |
SIN | Returns the sine of a number |
SQRT | Returns the square root of a number |
SUM | Calculates the sum of a set of values |
TAN | Returns the tangent of a number |
TRUNCATE | Truncates a number to the specified number of decimal places |
MySQL Date Functions | |
Function | Description |
ADDDATE | Adds a time/date interval to a date and then returns the date |
ADDTIME | Adds a time interval to a time/datetime and then returns the time/datetime |
CURDATE | Returns the current date |
CURRENT_DATE | Returns the current date |
CURRENT_TIME | Returns the current time |
CURRENT_TIMESTAMP | Returns the current date and time |
CURTIME | Returns the current time |
DATE | Extracts the date part from a datetime expression |
DATEDIFF | Returns the number of days between two date values |
DATE_ADD | Adds a time/date interval to a date and then returns the date |
DATE_FORMAT | Formats a date |
DATE_SUB | Subtracts a time/date interval from a date and then returns the date |
DAY | Returns the day of the month for a given date |
DAYNAME | Returns the weekday name for a given date |
DAYOFMONTH | Returns the day of the month for a given date |
DAYOFWEEK | Returns the weekday index for a given date |
DAYOFYEAR | Returns the day of the year for a given date |
EXTRACT | Extracts a part from a given date |
FROM_DAYS | Returns a date from a numeric datevalue |
HOUR | Returns the hour part for a given date |
LAST_DAY | Extracts the last day of the month for a given date |
LOCALTIME | Returns the current date and time |
LOCALTIMESTAMP | Returns the current date and time |
MAKEDATE | Creates and returns a date based on a year and a number of days value |
MAKETIME | Creates and returns a time based on an hour, minute, and second value |
MICROSECOND | Returns the microsecond part of a time/datetime |
MINUTE | Returns the minute part of a time/datetime |
MONTH | Returns the month part for a given date |
MONTHNAME | Returns the name of the month for a given date |
NOW | Returns the current date and time |
PERIOD_ADD | Adds a specified number of months to a period |
PERIOD_DIFF | Returns the difference between two periods |
QUARTER | Returns the quarter of the year for a given date value |
SECOND | Returns the seconds part of a time/datetime |
SEC_TO_TIME | Returns a time value based on the specified seconds |
STR_TO_DATE | Returns a date based on a string and a format |
SUBDATE | Subtracts a time/date interval from a date and then returns the date |
SUBTIME | Subtracts a time interval from a datetime and then returns the time/datetime |
SYSDATE | Returns the current date and time |
TIME | Extracts the time part from a given time/datetime |
TIME_FORMAT | Formats a time by a specified format |
TIME_TO_SEC | Converts a time value into seconds |
TIMEDIFF | Returns the difference between two time/datetime expressions |
TIMESTAMP | Returns a datetime value based on a date or datetime value |
TO_DAYS | Returns the number of days between a date and date “0000-00-00” |
WEEK | Returns the week number for a given date |
WEEKDAY | Returns the weekday number for a given date |
WEEKOFYEAR | Returns the week number for a given date |
YEAR | Returns the year part for a given date |
YEARWEEK | Returns the year and week number for a given date |
MySQL Advanced Functions | |
Function | Description |
BIN | Returns a binary representation of a number |
BINARY | Converts a value to a binary string |
CASE | Goes through conditions and return a value when the first condition is met |
CAST | Converts a value (of any type) into a specified datatype |
COALESCE | Returns the first non-null value in a list |
CONNECTION_ID | Returns the unique connection ID for the current connection |
CONV | Converts a number from one numeric base system to another |
CONVERT | Converts a value into the specified datatype or character set |
CURRENT_USER | Returns the user name and host name for the MySQL account that the server used to authenticate the current client |
DATABASE | Returns the name of the current database |
IF | Returns a value if a condition is TRUE, or another value if a condition is FALSE |
IFNULL | Return a specified value if the expression is NULL, otherwise return the expression |
ISNULL | Returns 1 or 0 depending on whether an expression is NULL |
LAST_INSERT_ID | Returns the AUTO_INCREMENT id of the last row that has been inserted or updated in a table |
NULLIF | Compares two expressions and returns NULL if they are equal. Otherwise, the first expression is returned |
SESSION_USER | Returns the current MySQL user name and host name |
SYSTEM_USER | Returns the current MySQL user name and host name |
USER | Returns the current MySQL user name and host name |
VERSION | Returns the current version of the MySQL database |