MySQL Reference
MySQL Functions
Aggregates
See the official docs for more information. The Docs link for each function points to that function's reference in the official docs. The Tutorial link for each function, if there is one, points to that function's reference on the MySQL tutorial site.
| Aggregate function | Docs | Tutorial | Description |
|---|---|---|---|
AVG() | Docs | Tutorial | Return the average value of the argument |
COUNT() | Docs | Tutorial | Return a count of the number of rows returned |
GROUP_CONCAT() | Docs | Tutorial | Return a concatenated string |
MAX() | Docs | Tutorial | Return the maximum value |
MIN() | Docs | Tutorial | Return the minimum value |
STDDEV() | Docs | Tutorial | Return the population standard deviation |
SUM() | Docs | Tutorial | Return the sum |
BIT_AND() | Docs | Return bitwise AND | |
BIT_OR | Docs | Return bitwise OR | |
BIT_XOR | Docs | Return bitwise XOR | |
COUNT(DISTINCT) | Docs | Return the count of a number of different values | |
JSON_ARRAYAGG() | Docs | Return result set as a single JSON array | |
JSON_OBJECTAGG() | Docs | Return result set as a single JSON object | |
STD() | Docs | Return the population standard deviation | |
STDDEV_POP() | Docs | Return the population standard deviation | |
STDDEV_SAMP() | Docs | Return the sample standard deviation | |
VAR_POP() | Docs | Return the population standard variance | |
VAR_SAMP() | Docs | Return the sample variance | |
VARIANCE() | Docs | Return the population standard variance |
Strings
See the official docs for more information. The Docs link for each function points to that function's reference in the official docs. The Tutorial link for each function, if there is one, points to that function's reference on the MySQL tutorial site.
| Aggregate function | Docs | Tutorial | Description |
|---|---|---|---|
CONCAT() | Docs | Tutorial | Return concatenated string |
FIND_IN_SET() | Docs | Tutorial | Index (position) of first argument within second argument |
FORMAT() | Docs | Tutorial | Return a number formatted to specified number of decimal places |
INSTR() | Docs | Tutorial | Return the index of the first occurrence of substring |
LEFT() | Docs | Tutorial | Return the leftmost number of characters as specified |
LENGTH() | Docs | Tutorial | Return the length of a string in bytes |
LOWER() | Docs | Tutorial | Return the argument in lowercase |
LTRIM() | Docs | Tutorial | Remove leading spaces |
REPLACE() | Docs | Tutorial | Replace occurrences of a specified string |
RIGHT() | Docs | Tutorial | Return the specified rightmost number of characters |
RTRIM() | Docs | Tutorial | Remove trailing spaces |
SUBSTRING() | Docs | Tutorial | Return the substring as specified |
SUBSTRING_INDEX() | Docs | Tutorial | Return a substring from a string before the specified number of occurrences of the delimiter |
TRIM() | Docs | Tutorial | Remove leading and trailing spaces |
UPPER() | Docs | Tutorial | Convert to uppercase |
ASCII() | Docs | Return numeric value of left-most character | |
BIN() | Docs | Return a string containing binary representation of a number | |
BIT_LENGTH() | Docs | Return length of argument in bits | |
CHAR() | Docs | Return the character for each integer passed | |
CHAR_LENGTH() | Docs | Return number of characters in argument | |
CHARACTER_LENGTH() | Docs | Synonym for CHAR_LENGTH() | |
CONCAT_WS() | Docs | Return concatenate with separator | |
ELT() | Docs | Return string at index number | |
EXPORT_SET() | Docs | 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 | |
FIELD() | Docs | Index (position) of first argument in subsequent arguments | |
FROM_BASE64() | Docs | Decode base64 encoded string and return result | |
HEX() | Docs | Hexadecimal representation of decimal or string value | |
INSERT() | Docs | Insert substring at specified position up to specified number of characters | |
LCASE() | Docs | Synonym for LOWER() | |
LIKE | Docs | Simple pattern matching | |
LOAD_FILE() | Docs | Load the named file | |
LOCATE() | Docs | Return the position of the first occurrence of substring | |
LPAD() | Docs | Return the string argument, left-padded with the specified string | |
MAKE_SET() | Docs | Return a set of comma-separated strings that have the corresponding bit in bits set | |
MATCH | Docs | Perform full-text search | |
MID() | Docs | Return a substring starting from the specified position | |
NOT LIKE | Docs | Negation of simple pattern matching | |
NOT REGEXP | Docs | Negation of REGEXP | |
OCT() | Docs | Return a string containing octal representation of a number | |
OCTET_LENGTH() | Docs | Synonym for LENGTH() | |
ORD() | Docs | Return character code for leftmost character of the argument | |
POSITION() | Docs | Synonym for LOCATE() | |
QUOTE() | Docs | Escape the argument for use in an SQL statement | |
REGEXP | Docs | Whether string matches regular expression | |
REGEXP_INSTR() | Docs | Starting index of substring matching regular expression | |
REGEXP_LIKE() | Docs | Whether string matches regular expression | |
REGEXP_REPLACE() | Docs | Replace substrings matching regular expression | |
REGEXP_SUBSTR() | Docs | Return substring matching regular expression | |
REPEAT() | Docs | Repeat a string the specified number of times | |
REVERSE() | Docs | Reverse the characters in a string | |
RLIKE | Docs | Whether string matches regular expression | |
RPAD() | Docs | Append string the specified number of times | |
SOUNDEX() | Docs | Return a soundex string | |
SOUNDS LIKE | Docs | Compare sounds | |
SPACE() | Docs | Return a string of the specified number of spaces | |
STRCMP() | Docs | Compare two strings | |
SUBSTR() | Docs | Return the substring as specified | |
TO_BASE64() | Docs | Return the argument converted to a base-64 string | |
UCASE() | Docs | Synonym for UPPER() | |
UNHEX() | Docs | Return a string containing hex representation of a number | |
WEIGHT_STRING() | Docs | Return the weight string for a string |
Math
See the official docs for more information. The Docs link for each function points to that function's reference in the official docs. The Tutorial link for each function, if there is one, points to that function's reference on the MySQL tutorial site.
| Function | Docs | Tutorial | Description |
|---|---|---|---|
ABS() | Docs | Tutorial | Return the absolute value |
CEIL() | Docs | Tutorial | Return the smallest integer value not less than the argument |
FLOOR() | Docs | Tutorial | Return the largest integer value not greater than the argument |
MOD() | Docs | Tutorial | Return the remainder |
ROUND() | Docs | Tutorial | Round the argument |
TRUNCATE() | Docs | Tutorial | Truncate to specified number of decimal places |
ACOS() | Docs | Return the arc cosine | |
ASIN() | Docs | Return the arc sine | |
ATAN() | Docs | Return the arc tangent | |
ATAN2(), ATAN() | Docs | Return the arc tangent of the two arguments | |
CEILING() | Docs | Return the smallest integer value not less than the argument | |
CONV() | Docs | Convert numbers between different number bases | |
COS() | Docs | Return the cosine | |
COT() | Docs | Return the cotangent | |
CRC32() | Docs | Compute a cyclic redundancy check value | |
DEGREES() | Docs | Convert radians to degrees | |
EXP() | Docs | Raise to the power of | |
LN() | Docs | Return the natural logarithm of the argument | |
LOG() | Docs | Return the natural logarithm of the first argument | |
LOG10() | Docs | Return the base-10 logarithm of the argument | |
LOG2() | Docs | Return the base-2 logarithm of the argument | |
PI() | Docs | Return the value of pi | |
POW() | Docs | Return the argument raised to the specified power | |
POWER() | Docs | Return the argument raised to the specified power | |
RADIANS() | Docs | Return argument converted to radians | |
RAND() | Docs | Return a random floating-point value | |
SIGN() | Docs | Return the sign of the argument | |
SIN() | Docs | Return the sine of the argument | |
SQRT() | Docs | Return the square root of the argument | |
TAN() | Docs | Return the tangent of the argument |
Dates
See the official docs for more information. The Docs link for each function points to that function's reference in the official docs. The Tutorial link for each function, if there is one, points to that function's reference on the MySQL tutorial site. Immediately below are date formatting specifiers (which can all be seen in the MySQL docs under DATE_FORMAT).
Date format specifiers and descriptions
| Specifier | Description |
|---|---|
%a | Abbreviated weekday name (Sun..Sat) |
%b | Abbreviated month name (Jan..Dec) |
%c | Month, numeric (0..12) |
%D | Day of the month with English suffix (0th, 1st, 2nd, 3rd, …) |
%d | Day of the month, numeric (00..31) |
%e | Day of the month, numeric (0..31) |
%f | Microseconds (000000..999999) |
%H | Hour (00..23) |
%h | Hour (01..12) |
%I | Hour (01..12) |
%i | Minutes, numeric (00..59) |
%j | Day of year (001..366) |
%k | Hour (0..23) |
%l | Hour (1..12) |
%M | Month name (January..December) |
%m | Month, numeric (00..12) |
%p | AM or PM |
%r | Time, 12-hour (hh:mm:ss followed by AM or PM) |
%S | Seconds (00..59) |
%s | Seconds (00..59) |
%T | Time, 24-hour (hh:mm:ss) |
%U | Week (00..53), where Sunday is the first day of the week; WEEK() mode 0 |
%u | Week (00..53), where Monday is the first day of the week; WEEK() mode 1 |
%V | Week (01..53), where Sunday is the first day of the week; WEEK() mode 2; used with %X |
%v | Week (01..53), where Monday is the first day of the week; WEEK() mode 3; used with %x |
%W | Weekday name (Sunday..Saturday) |
%w | Day of the week (0=Sunday..6=Saturday) |
%X | Year for the week where Sunday is the first day of the week, numeric, four digits; used with %V |
%x | Year for the week, where Monday is the first day of the week, numeric, four digits; used with %v |
%Y | Year, numeric, four digits |
%y | Year, numeric (two digits) |
%% | A literal % character |
%x | x, for any “x” not listed above |
| Function | Docs | Tutorial | Description |
|---|---|---|---|
CURDATE() | Docs | Tutorial | Return the current date |
DATE_ADD() | Docs | Tutorial | Add time values (intervals) to a date value |
DATE_FORMAT() | Docs | Tutorial | Format date as specified |
DATE_SUB() | Docs | Tutorial | Subtract a time value (interval) from a date |
DATEDIFF() | Docs | Tutorial | Subtract two dates |
DAY() | Docs | Tutorial | Synonym for DAYOFMONTH() |
DAYNAME() | Docs | Tutorial | Return the name of the weekday |
DAYOFWEEK() | Docs | Tutorial | Return the weekday index of the argument |
EXTRACT() | Docs | Tutorial | Extract part of a date |
LAST_DAY | Docs | Tutorial | Return the last day of the month for the argument |
MONTH() | Docs | Tutorial | Return the month from the date passed |
NOW() | Docs | Tutorial | Return the current date and time |
STR_TO_DATE() | Docs | Tutorial | Convert a string to a date |
SYSDATE() | Docs | Tutorial | Return the time at which the function executes |
TIMEDIFF() | Docs | Tutorial | Subtract time |
TIMESTAMPDIFF() | Docs | Tutorial | Subtract an interval from a datetime expression |
WEEK() | Docs | Tutorial | Return the week number |
WEEKDAY() | Docs | Tutorial | Return the weekday index |
YEAR() | Docs | Tutorial | Return the year |
ADDDATE() | Docs | Add time values (intervals) to a date value | |
ADDTIME() | Docs | Add time | |
CONVERT_TZ() | Docs | Convert from one time zone to another | |
CURRENT_DATE(), CURRENT_DATE | Docs | Synonyms for CURDATE() | |
CURRENT_TIME(), CURRENT_TIME | Docs | Synonyms for CURTIME() | |
CURRENT_TIMESTAMP(), CURRENT_TIMESTAMP | Docs | Synonyms for NOW() | |
CURTIME() | Docs | Return the current time | |
DATE() | Docs | Extract the date part of a date or datetime expression | |
DAYOFMONTH() | Docs | Return the day of the month (0-31) | |
DAYOFYEAR() | Docs | Return the day of the year (1-366) | |
FROM_DAYS() | Docs | Convert a day number to a date | |
FROM_UNIXTIME() | Docs | Format Unix timestamp as a date | |
GET_FORMAT() | Docs | Return a date format string | |
HOUR() | Docs | Extract the hour | |
LOCALTIME(), LOCALTIME | Docs | Synonym for NOW() | |
LOCALTIMESTAMP, LOCALTIMESTAMP() | Docs | Synonym for NOW() | |
MAKEDATE() | Docs | Create a date from the year and day of year | |
MAKETIME() | Docs | Create time from hour, minute, second | |
MICROSECOND() | Docs | Return the microseconds from argument | |
MINUTE() | Docs | Return the minute from the argument | |
MONTHNAME() | Docs | Return the name of the month | |
PERIOD_ADD() | Docs | Add a period to a year-month | |
PERIOD_DIFF() | Docs | Return the number of months between periods | |
QUARTER() | Docs | Return the quarter from a date argument | |
SEC_TO_TIME() | Docs | Converts seconds to 'hh:mm:ss' format | |
SECOND() | Docs | Return the second (0-59) | |
SUBDATE() | Docs | Synonym for DATE_SUB() when invoked with three arguments | |
SUBTIME() | Docs | Subtract times | |
TIME() | Docs | Extract the time portion of the expression passed | |
TIME_FORMAT() | Docs | Format as time | |
TIME_TO_SEC() | Docs | Return the argument converted to seconds | |
TIMESTAMP() | Docs | With a single argument, this function returns the date or datetime expression; with two arguments, the sum of the arguments | |
TIMESTAMPADD() | Docs | Add an interval to a datetime expression | |
TO_DAYS() | Docs | Return the date argument converted to days | |
TO_SECONDS() | Docs | Return the date or datetime argument converted to seconds since Year 0 | |
UNIX_TIMESTAMP() | Docs | Return a Unix timestamp | |
UTC_DATE() | Docs | Return the current UTC date | |
UTC_TIME() | Docs | Return the current UTC time | |
UTC_TIMESTAMP() | Docs | Return the current UTC date and time | |
WEEKOFYEAR() | Docs | Return the calendar week of the date (1-53) | |
YEARWEEK() | Docs | Return the year and week |
Comparison
See the official docs for more information. The Docs link for each function points to that function's reference in the official docs. The Tutorial link for each function, if there is one, points to that function's reference on the MySQL tutorial site.
| Function | Docs | Tutorial | Description |
|---|---|---|---|
COALESCE() | Docs | Tutorial | Return the first non-NULL argument |
GREATEST() | Docs | Tutorial | Return the largest argument |
IS NULL | Docs | Tutorial | NULL value test |
LEAST() | Docs | Tutorial | Return the smallest argument |
> | Docs | Greater than operator | |
>= | Docs | Greater than or equal operator | |
< | Docs | Less than operator | |
<>, != | Docs | Not equal operator | |
<= | Docs | Less than or equal operator | |
<=> | Docs | NULL-safe equal to operator | |
= | Docs | Equal operator | |
BETWEEN ... AND ... | Docs | Whether a value is within a range of values | |
IN() | Docs | Whether a value is within a set of values | |
INTERVAL() | Docs | Return the index of the argument that is less than the first argument | |
IS | Docs | Test a value against a boolean | |
IS NOT | Docs | Test a value against a boolean | |
IS NOT NULL | Docs | NOT NULL value test | |
ISNULL() | Docs | Test whether the argument is NULL | |
LIKE | Docs | Simple pattern matching | |
NOT BETWEEN ... AND ... | Docs | Whether a value is not within a range of values | |
NOT IN() | Docs | Whether a value is not within a set of values | |
NOT LIKE | Docs | Negation of simple pattern matching | |
STRCMP() | Docs | Compare two strings |
Window Functions
See the official docs for more information. The Docs link for each function points to that function's reference in the official docs. The Tutorial link for each function points to that function's reference on the MySQL tutorial site.
| Function | Docs | Tutorial | Description |
|---|---|---|---|
CUME_DIST() | Docs | Tutorial | Cumulative distribution value |
DENSE_RANK() | Docs | Tutorial | Rank of current row within its partition, without gaps |
FIRST_VALUE() | Docs | Tutorial | Value of argument from first row of window frame |
LAG() | Docs | Tutorial | Value of argument from row lagging current row within partition |
LAST_VALUE() | Docs | Tutorial | Value of argument from last row of window frame |
LEAD() | Docs | Tutorial | Value of argument from row leading current row within partition |
NTH_VALUE() | Docs | Tutorial | Value of argument from N-th row of window frame |
NTILE() | Docs | Tutorial | Bucket number of current row within its partition. |
PERCENT_RANK() | Docs | Tutorial | Percentage rank value |
RANK() | Docs | Tutorial | Rank of current row within its partition, with gaps |
ROW_NUMBER() | Docs | Tutorial | Number of current row within its partition |
Control Flow Functions and Expressions
See the official docs for more information. The Docs link for each function points to that function's reference in the official docs. The Tutorial link for each function, if there is one, points to that function's reference on the MySQL tutorial site.
| Function | Docs | Tutorial | Description |
|---|---|---|---|
CASE | Docs | Tutorial | Case operator |
IF() | Docs | Tutorial | If/else construct |
IFNULL() | Docs | Tutorial | Null if/else construct |
NULLIF | Docs | Tutorial | Return NULL if expr1 = expr2 |