MySQL Functions
MySQL has many built-in functions. The following lists the descriptions of these functions.
MySQL String Functions
| Function | Description | Example |
|---|---|---|
| ASCII(s) | Returns the ASCII code of the first character of string s. | Returns the ASCII code of the first letter of the CustomerName field: SELECT ASCII(CustomerName) AS NumCodeOfFirstChar FROM Customers; |
| CHAR_LENGTH(s) | Returns the number of characters in string s. | Returns the number of characters in the string "tutorialpro": SELECT CHAR_LENGTH("tutorialpro") AS LengthOfString; |
| CHARACTER_LENGTH(s) | Returns the number of characters in string s, equivalent to CHAR_LENGTH(s). | Returns the number of characters in the string "tutorialpro": SELECT CHARACTER_LENGTH("tutorialpro") AS LengthOfString; |
| CONCAT(s1,s2...sn) | Concatenates strings s1, s2, etc. into a single string. | Concatenates multiple strings: SELECT CONCAT("SQL ", "tutorialpro ", "Gooogle ", "Facebook") AS ConcatenatedString; |
| CONCAT_WS(x, s1,s2...sn) | Similar to CONCAT(s1, s2, ...), but adds x between each string, where x can be a separator. | Concatenates multiple strings with a separator: SELECT CONCAT_WS("-", "SQL", "Tutorial", "is", "fun!") AS ConcatenatedString; |
| FIELD(s,s1,s2...) | Returns the position of the first string s in the list of strings (s1, s2, ...). | Returns the position of string "c" in the list of values: SELECT FIELD("c", "a", "b", "c", "d", "e"); |
| FIND_IN_SET(s1,s2) | Returns the position of string s1 in string s2. | Returns the position of string "c" in the specified string: SELECT FIND_IN_SET("c", "a,b,c,d,e"); |
| FORMAT(x,n) | Formats the number x to "#,###.##", rounding to n decimal places. | Formats the number to "#,###.##" format: SELECT FORMAT(250500.5634, 2); -- Outputs 250,500.56 |
| INSERT(s1,x,len,s2) | Replaces the substring of s1 starting at position x with length len with string s2. | Replaces the first 6 characters of the string with "tutorialpro": SELECT INSERT("google.com", 1, 6, "tutorialpro"); -- Outputs: tutorialpro.org |
| LOCATE(s1,s) | Returns the starting position of string s1 in string s. | Returns the position of 'b' in the string 'abc': SELECT LOCATE('b', 'abc') -- 2 |
| LCASE(s) | Converts all letters in string s to lowercase. | Converts the string "tutorialpro" to lowercase: SELECT LCASE('tutorialpro') -- tutorialpro |
| LEFT(s,n) | Returns the first n characters of string s. | Returns the first two characters of the string "tutorialpro": SELECT LEFT('tutorialpro',2) -- ru |
| LOWER(s) | Converts all letters in string s to lowercase. | Converts the string "tutorialpro" to lowercase: SELECT LOWER('tutorialpro') -- tutorialpro |
| LPAD(s1,len,s2) | Pads string s1 on the left with string s2 to make the string length reach len. | Pads the string "abc" with "xx" on the left: SELECT LPAD('abc',5,'xx') -- xxabc |
| LTRIM(s) | Removes spaces from the start of string s. | Removes spaces from the start of the string "tutorialpro": SELECT LTRIM(" tutorialpro") AS LeftTrimmedString; -- tutorialpro |
| MID(s,n,len) | Extracts a substring of length len from string s starting at position n, equivalent to SUBSTRING(s,n,len). | Extracts 3 characters from the string "tutorialpro" starting at the second position: SELECT MID("tutorialpro", 2, 3) AS ExtractString; -- UNO |
| POSITION(s1 IN s) | Returns the starting position of string s1 in string s. | Returns the position of 'b' in the string 'abc': SELECT POSITION('b' in 'abc') -- 2 |
| REPEAT(s,n) | Repeats string s n times. | Repeats the string "tutorialpro" three times: SELECT REPEAT('tutorialpro',3) -- tutorialprotutorialprotutorialpro |
| REPLACE(s,s1,s2) | Replaces string s1 with string s2 in string s. | Replaces 'a' with 'x' in the string 'abc': SELECT REPLACE('abc','a','x') -- xbc |
| REVERSE(s) | Reverses the order of characters in string s. | Reverses the string 'abc': SELECT REVERSE('abc') -- cba |
| RIGHT(s,n) | Returns the last n characters of string s. | Returns the last two characters of the string "tutorialpro": SELECT RIGHT('tutorialpro',2) -- ob |
| RPAD(s1,len,s2) | Appends string s2 to the end of string s1 to make the string length reach len. | Pads the string "abc" with "xx" on the right: SELECT RPAD('abc',5,'xx') -- abcxx |
| RTRIM(s) | Removes spaces from the end of string s. | Removes spaces from the end of the string "tutorialpro": SELECT RTRIM("tutorialpro ") AS RightTrimmedString; -- tutorialpro |
| SPACE(n) | Returns a string of n spaces. | Returns 10 spaces: SELECT SPACE(10); |
| STRCMP(s1,s2) | Compares strings s1 and s2. Returns 0 if s1 equals s2, 1 if s1 > s2, and -1 if s1 < s2. | Compares strings: SELECT STRCMP("tutorialpro", "tutorialpro"); -- 0 |
| SUBSTR(s, start, length) | Extracts a substring of length length from string s starting at position start. | Extracts 3 characters from the string "tutorialpro" starting at the second position: SELECT SUBSTR("tutorialpro", 2, 3) AS ExtractString; -- UNO |
| SUBSTRING(s, start, length) | Extracts a substring of length length from string s starting at position start, equivalent to SUBSTR(s, start, length). | Extracts 3 characters from the string "tutorialpro" starting at the second position: SELECT SUBSTRING("tutorialpro", 2, 3) AS ExtractString; -- UNO |
| SUBSTRING_INDEX(s, delimiter, number) | Returns the substring after the number-th occurrence of the delimiter in string s. If number is positive, returns the substring to the left of the delimiter. If number is negative, returns the substring to the right of the delimiter. | SELECT SUBSTRING_INDEX('a*b','*',1) -- a<br>SELECT SUBSTRING_INDEX('ab','',-1) -- b<br>SELECT SUBSTRING_INDEX(SUBSTRING_INDEX('abcde','',3),'',-1) -- c |
| TRIM(s) | Removes spaces from both the start and end of string s. | Removes spaces from both ends of the string "tutorialpro": SELECT TRIM(' tutorialpro ') AS TrimmedString; |
| UCASE(s) | Converts string s to uppercase. | Converts the string "tutorialpro" to uppercase: SELECT UCASE("tutorialpro"); -- tutorialpro |
| UPPER(s) | Converts string s to uppercase. | Converts the string "tutorialpro" to uppercase: SELECT UPPER("tutorialpro"); -- tutorialpro |
MySQL Numeric Functions
| Function Name | Description | Example |
|---|---|---|
| ABS(x) | Returns the absolute value of x. | Returns the absolute value of -1: SELECT ABS(-1) -- Returns 1 |
| ACOS(x) | Returns the arccosine of x in radians. | SELECT ACOS(0.25); |
| ASIN(x) | Returns the arcsine of x in radians. | SELECT ASIN(0.25); |
| ATAN(x) | Returns the arctangent of x in radians. | SELECT ATAN(2.5); |
| ATAN2(n, m) | Returns the arctangent of n and m in radians. | SELECT ATAN2(-0.8, 2); |
| AVG(expression) | Returns the average value of an expression. | Returns the average value of the Price field in the Products table: SELECT AVG(Price) AS AveragePrice FROM Products; |
| CEIL(x) | Returns the smallest integer greater than or equal to x. | SELECT CEIL(1.5) -- Returns 2 |
| CEILING(x) | Returns the smallest integer greater than or equal to x. | SELECT CEILING(1.5); -- Returns 2 |
| COS(x) | Returns the cosine of x (x is in radians). | SELECT COS(2); |
| COT(x) | Returns the cotangent of x (x is in radians). | SELECT COT(6); |
| COUNT(expression) | Returns the total count of records in the query, where expression is a field or * | Returns the total number of records in the ProductID field of the Products table: SELECT COUNT(ProductID) AS NumberOfProducts FROM Products; |
| DEGREES(x) | Converts radians to degrees | SELECT DEGREES(3.1415926535898) -- 180 |
| n DIV m | Integer division, n is the dividend, m is the divisor | Calculate 10 divided by 5: SELECT 10 DIV 5; -- 2 |
| EXP(x) | Returns e raised to the power of x | Calculate e cubed: SELECT EXP(3) -- 20.085536923188 |
| FLOOR(x) | Returns the largest integer less than or equal to x | Integer less than or equal to 1.5: SELECT FLOOR(1.5) -- returns 1 |
| GREATEST(expr1, expr2, expr3, ...) | Returns the largest value in the list | Returns the largest value from the list of numbers: SELECT GREATEST(3, 12, 34, 8, 25); -- 34 Returns the largest value from the list of strings: SELECT GREATEST("Google", "tutorialpro", "Apple"); -- tutorialpro |
| LEAST(expr1, expr2, expr3, ...) | Returns the smallest value in the list | Returns the smallest value from the list of numbers: SELECT LEAST(3, 12, 34, 8, 25); -- 3 Returns the smallest value from the list of strings: SELECT LEAST("Google", "tutorialpro", "Apple"); -- Apple |
| LN | Returns the natural logarithm of a number, base e | Returns the natural logarithm of 2: SELECT LN(2); -- 0.6931471805599453 |
| LOG(x) or LOG(base, x) | Returns the natural logarithm (base e logarithm), if base is specified, it is the specified base | SELECT LOG(20.085536923188) -- 3<br>SELECT LOG(2, 4); -- 2 |
| LOG10(x) | Returns the logarithm base 10 | SELECT LOG10(100) -- 2 |
| LOG2(x) | Returns the logarithm base 2 | Returns the logarithm base 2 of 6: SELECT LOG2(6); -- 2.584962500721156 |
| MAX(expression) | Returns the maximum value in the field expression | Returns the maximum value in the Price field of the Products table: SELECT MAX(Price) AS LargestPrice FROM Products; |
| MIN(expression) | Returns the minimum value in the field expression | Returns the minimum value in the Price field of the Products table: SELECT MIN(Price) AS MinPrice FROM Products; |
| MOD(x,y) | Returns the remainder of x divided by y | Remainder of 5 divided by 2: SELECT MOD(5,2) -- 1 |
| PI() | Returns the value of pi (3.141593) | SELECT PI() --3.141593 |
| POW(x,y) | Returns x raised to the power of y | 2 raised to the power of 3: SELECT POW(2,3) -- 8 |
| POWER(x,y) | Returns x raised to the power of y | 2 raised to the power of 3: SELECT POWER(2,3) -- 8 |
| RADIANS(x) | Converts degrees to radians | 180 degrees to radians: SELECT RADIANS(180) -- 3.1415926535898 |
| RAND() | Returns a random number between 0 and 1 | SELECT RAND() --0.93099315644334 |
| ROUND(x [,y]) | Returns the nearest integer to x, optional parameter y specifies the number of decimal places to round to, if omitted, returns an integer | SELECT ROUND(1.23456) --1<br>SELECT ROUND(345.156, 2) -- 345.16 |
| SIGN(x) | Returns the sign of x, returns -1, 0, or 1 for negative, zero, and positive numbers respectively | SELECT SIGN(-10) -- (-1) |
| SIN(x) | Returns the sine of x (x is in radians) | SELECT SIN(RADIANS(30)) -- 0.5 |
| SQRT(x) | Returns the square root of x | Square root of 25: SELECT SQRT(25) -- 5 |
| SUM(expression) | Returns the total sum of the specified field | Calculates the total sum of the Quantity field in the OrderDetails table: SELECT SUM(Quantity) AS TotalItemsOrdered FROM OrderDetails; |
| TAN(x) | Returns the tangent of x (x is in radians) | SELECT TAN(1.75); -- -5.52037992250933 |
| TRUNCATE(x,y) | Returns the value of x truncated to y decimal places (unlike ROUND, it does not round) | SELECT TRUNCATE(1.23456,3) -- 1.234 |
MySQL Date Functions
| Function Name | Description | Example |
|---|---|---|
| ADDDATE(d,n) | Calculates the date d plus n days | SELECT ADDDATE("2017-06-15", INTERVAL 10 DAY);<br>->2017-06-25 |
| ADDTIME(t,n) | Adds the time expression n to time t | Add 5 seconds: SELECT ADDTIME('2011-11-11 11:11:11', 5);<br>->2011-11-11 11:11:16 (seconds) Add 2 hours, 10 minutes, 5 seconds: SELECT ADDTIME("2020-06-15 09:34:21", "2:10:5"); <br>-> 2020-06-15 11:44:26 |
| CURDATE() | Returns the current date | SELECT CURDATE();<br>-> 2018-09-19 |
| CURRENT_DATE() | Returns the current date | SELECT CURRENT_DATE();<br>-> 2018-09-19 |
| CURRENT_TIME | Returns the current time | SELECT CURRENT_TIME();<br>-> 19:59:02 |
| CURRENT_TIMESTAMP() | Returns the current date and time | SELECT CURRENT_TIMESTAMP()<br>-> 2018-09-19 20:57:43 |
| CURTIME() | Returns the current time | SELECT CURTIME();<br>-> 19:59:02 |
| DATE() | Extracts the date part from a date or datetime expression | SELECT DATE("2017-06-15"); <br>-> 2017-06-15 |
| DATEDIFF(d1,d2) | Calculates the number of days between dates d1 and d2 | SELECT DATEDIFF('2001-01-01','2001-02-02')<br>-> -32 |
| DATE_ADD(d, INTERVAL expr type) | Adds a time/date interval to a date and then returns the date. Type values can be: MICROSECOND, SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, YEAR, SECOND_MICROSECOND, MINUTE_MICROSECOND, MINUTE_SECOND, HOUR_MICROSECOND, HOUR_SECOND, HOUR_MINUTE, DAY_MICROSECOND, DAY_SECOND, DAY_MINUTE, DAY_HOUR, YEAR_MONTH | SELECT DATE_ADD("2017-06-15", INTERVAL 10 DAY); <br>-> 2017-06-25<br>SELECT DATE_ADD("2017-06-15 09:34:21", INTERVAL 15 MINUTE);<br>-> 2017-06-15 09:49:21<br>SELECT DATE_ADD("2017-06-15 09:34:21", INTERVAL -3 HOUR);<br>->2017-06-15 06:34:21<br>SELECT DATE_ADD("2017-06-15 09:34:21", INTERVAL -3 MONTH);<br>->2017-04-15 |
| DATE_FORMAT(d,f) | Formats the date d according to the format specification f | SELECT DATE_FORMAT('2011-11-11 11:11:11','%Y-%m-%d %r')<br>-> 2011-11-11 11:11:11 AM |
| DATE_SUB(date,INTERVAL expr type) | Subtracts a specified time interval from a date | Subtracts 2 days from the OrderDate field in the Orders table: SELECT OrderId,DATE_SUB(OrderDate,INTERVAL 2 DAY) AS OrderPayDate<br>FROM Orders |
| DAY(d) | Returns the day part of the date d | SELECT DAY("2017-06-15"); <br>-> 15 |
| DAYNAME(d) | Returns the weekday name for date d, such as Monday, Tuesday | SELECT DAYNAME('2011-11-11 11:11:11')<br>->Friday |
| DAYOFMONTH(d) | Calculates which day of the month the date d is | SELECT DAYOFMONTH('2011-11-11 11:11:11')<br>->11 |
| DAYOFWEEK(d) | Returns the weekday index for date d, 1 for Sunday, 2 for Monday, etc. | SELECT DAYOFWEEK('2011-11-11 11:11:11')<br>->6 |
| DAYOFYEAR(d) | Calculates which day of the year the date d is | SELECT DAYOFYEAR('2011-11-11 11:11:11')<br>->315 |
| EXTRACT(type FROM d) | Extracts the specified value from the date d, where type specifies the value to be returned. <br>Possible values for type are: <br> MICROSECOND<br>SECOND<br>MINUTE<br>HOUR<br>DAY<br>WEEK<br>MONTH<br>QUARTER<br>YEAR<br>SECOND_MICROSECOND<br>MINUTE_MICROSECOND<br>MINUTE_SECOND<br>HOUR_MICROSECOND<br>HOUR_SECOND<br>HOUR_MINUTE<br>DAY_MICROSECOND<br>DAY_SECOND<br>DAY_MINUTE<br>DAY_HOUR<br>YEAR_MONTH | SELECT EXTRACT(MINUTE FROM '2011-11-11 11:11:11') <br>-> 11 |
| FROM_DAYS(n) | Calculates the date from n days after the start of 0000-01-01 | SELECT FROM_DAYS(1111)<br>-> 0003-01-16 |
| HOUR(t) | Returns the hour value from t | SELECT HOUR('1:2:3')<br>-> 1 |
| LAST_DAY(d) | Returns the last day of the month for the given date | SELECT LAST_DAY("2017-06-20");<br>-> 2017-06-30 |
| LOCALTIME() | Returns the current date and time | SELECT LOCALTIME()<br>-> 2018-09-19 20:57:43 |
| LOCALTIMESTAMP() | Returns the current date and time | SELECT LOCALTIMESTAMP()<br>-> 2018-09-19 20:57:43 |
| MAKEDATE(year, day-of-year) | Returns a date based on the given year and the day number within that year | SELECT MAKEDATE(2017, 3);<br>-> 2017-01-03 |
| MAKETIME(hour, minute, second) | Combines time from the given hour, minute, and second | SELECT MAKETIME(11, 35, 4);<br>-> 11:35:04 |
| MICROSECOND(date) | Returns the microseconds from the date parameter | SELECT MICROSECOND("2017-06-20 09:34:00.000023");<br>-> 23 |
| MINUTE(t) | Returns the minute value from t | SELECT MINUTE('1:2:3')<br>-> 2 |
| MONTHNAME(d) | Returns the name of the month from the date, such as November | SELECT MONTHNAME('2011-11-11 11:11:11')<br>-> November |
| MONTH(d) | Returns the month value from date d, ranging from 1 to 12 | SELECT MONTH('2011-11-11 11:11:11')<br>->11 |
| NOW() | Returns the current date and time | SELECT NOW()<br>-> 2018-09-19 20:57:43 |
| PERIOD_ADD(period, number) | Adds a period to a year-month combination | SELECT PERIOD_ADD(201703, 5); <br>-> 201708 |
| PERIOD_DIFF(period1, period2) | Returns the month difference between two periods | SELECT PERIOD_DIFF(201710, 201703);<br>-> 7 |
| QUARTER(d) | Returns the quarter of the year for date d, ranging from 1 to 4 | SELECT QUARTER('2011-11-11 11:11:11')<br>-> 4 |
| SECOND(t) | Returns the second value from t | SELECT SECOND('1:2:3')<br>-> 3 |
| SEC_TO_TIME(s) | Converts the time s in seconds to a time format of hours, minutes, and seconds | SELECT SEC_TO_TIME(4320)<br>-> 01:12:00 |
| STR_TO_DATE(string, format_mask) | Converts a string to a date | SELECT STR_TO_DATE("August 10 2017", "%M %d %Y");<br>-> 2017-08-10 |
| SUBDATE(d,n) | Subtracts n days from date d | SELECT SUBDATE('2011-11-11 11:11:11', 1)<br>->2011-11-10 11:11:11 (default is day) |
| SUBTIME(t,n) | Subtracts n seconds from time t | SELECT SUBTIME('2011-11-11 11:11:11', 5)<br>->2011-11-11 11:11:06 (seconds) |
| SYSDATE() | Returns the current date and time | SELECT SYSDATE()<br>-> 2018-09-19 20:57:43 |
| TIME(expression) | Extracts the time part from the given expression | SELECT TIME("19:30:10");<br>-> 19:30:10 |
| TIME_FORMAT(t,f) | Formats time t as specified by the format f | SELECT TIME_FORMAT('11:11:11','%r')<br>11:11:11 AM |
| TIME_TO_SEC(t) | Converts time t to seconds | SELECT TIME_TO_SEC('1:12:00')<br>-> 4320 |
| TIMEDIFF(time1, time2) | Calculates the difference between two times | mysql> SELECT TIMEDIFF("13:10:11", "13:10:10");<br>-> 00:00:01<br>mysql> SELECT TIMEDIFF('2000:01:01 00:00:00',<br> -> '2000:01:01 00:00:00.000001');<br> -> '-00:00:00.000001'<br>mysql> SELECT TIMEDIFF('2008-12-31 23:59:59.000001',<br> -> '2008-12-30 01:01:01.000002');<br> -> '46:58:57.999999' |
| TIMESTAMP(expression, interval) | With a single argument, this function returns the date or datetime expression. With two arguments, it adds the interval to the expression. | mysql> SELECT TIMESTAMP("2017-07-23", "13:10:11");<br>-> 2017-07-23 13:10:11<br>mysql> SELECT TIMESTAMP('2003-12-31');<br> -> '2003-12-31 00:00:00'<br>mysql> SELECT TIMESTAMP('2003-12-31 12:00:00','12:00:00');<br> -> '2004-01-01 00:00:00' |
| TIMESTAMPDIFF(unit,datetime_expr1,datetime_expr2) | Calculates the time difference, returning datetime_expr2 − datetime_expr1 in the specified unit | mysql> SELECT TIMESTAMPDIFF(DAY,'2003-02-01','2003-05-01'); // Calculates the number of days between two dates<br> -> 89<br>mysql> SELECT TIMESTAMPDIFF(MONTH,'2003-02-01','2003-05-01'); // Calculates the number of months between two dates<br> -> 3<br>mysql> SELECT TIMESTAMPDIFF(YEAR,'2002-05-01','2001-01-01'); // Calculates the number of years between two dates<br> -> -1<br>mysql> SELECT TIMESTAMPDIFF(MINUTE,'2003-02-01','2003-05-01 12:05:55'); // Calculates the number of minutes between two dates<br> -> 128885 |
| TO_DAYS(d) | Calculates the number of days between date d and 0000-01-01 | SELECT TO_DAYS('0001-01-01 01:01:01')<br>-> 366 |
| WEEK(d) | Calculates which week of the year the date d is in, ranging from 0 to 53 | SELECT WEEK('2011-11-11 11:11:11')<br>-> 45 |
| WEEKDAY(d) | Returns the weekday index for date d, where 0 represents Monday, 1 represents Tuesday, etc. | SELECT WEEKDAY("2017-06-15");<br>-> 3 |
| WEEKOFYEAR(d) | Calculates which week of the year the date d is in, ranging from 0 to 53 | SELECT WEEKOFYEAR('2011-11-11 11:11:11')<br>-> 45 |
| YEAR(d) | Returns the year from the date | SELECT YEAR("2017-06-15");<br>-> 2017 |
| YEARWEEK(date, mode) | Returns the year and week number (0 to 53), where mode 0 indicates Sunday, 1 indicates Monday, etc. | SELECT YEARWEEK("2017-06-15");<br>-> 201724 |
MySQL Advanced Functions
| Function Name | Description | Example |
|---|---|---|
| BIN(x) | Returns the binary representation of x | SELECT BIN(15); -- 1111 |
| BINARY(s) | Converts the string s to a binary string | SELECT BINARY "tutorialpro";<br>-> tutorialpro |
| CASE expression<br> WHEN condition1 THEN result1<br> WHEN condition2 THEN result2<br> ...<br> WHEN conditionN THEN resultN<br> ELSE result<br>END | The CASE function starts with CASE and ends with END. It returns result1 if condition1 is true, result2 if condition2 is true, and so on. If none of the conditions are true, it returns the result after ELSE. Once a condition is met, subsequent conditions are not evaluated. | SELECT CASE <br> WHEN 1 > 0<br> THEN '1 > 0'<br> WHEN 2 > 0<br> THEN '2 > 0'<br> ELSE '3 > 0'<br> END<br>->1 > 0 |
| CAST(x AS type) | Converts the data type of x | Converts a string date to a date: SELECT CAST("2017-08-29" AS DATE);<br>-> 2017-08-29 |
| COALESCE(expr1, expr2, ...., expr_n) | Returns the first non-null expression from the arguments (left to right) | SELECT COALESCE(NULL, NULL, NULL, 'tutorialpro.org', NULL, 'google.com');<br>-> tutorialpro.org |
| CONNECTION_ID() | Returns the unique connection ID | SELECT CONNECTION_ID();<br>-> 4292835 |
| CONV(x,f1,f2) | Converts a number from f1 base to f2 base | SELECT CONV(15, 10, 2);<br>-> 1111 |
| CONVERT(s USING cs) | Changes the character set of string s to cs | SELECT CHARSET('ABC')<br>->utf-8<br>SELECT CHARSET(CONVERT('ABC' USING gbk))<br>->gbk |
| CURRENT_USER() | Returns the current user | SELECT CURRENT_USER();<br>-> guest@% |
| DATABASE() | Returns the current database name | SELECT DATABASE();<br>-> tutorialpro |
| IF(expr,v1,v2) | Returns v1 if the expression expr is true, otherwise returns v2 | SELECT IF(1 > 0,'correct','incorrect')<br>-> correct |
| IFNULL(v1,v2) | Returns v1 if v1 is not NULL, otherwise returns v2 | SELECT IFNULL(null,'Hello Word')<br>-> Hello Word |
| ISNULL(expression) | Checks if the expression is NULL | SELECT ISNULL(NULL);<br>-> 1 |
| LAST_INSERT_ID() | Returns the most recently generated AUTO_INCREMENT value | SELECT LAST_INSERT_ID();<br>-> 6 |
| NULLIF(expr1, expr2) | Compares two strings, returns NULL if expr1 equals expr2, otherwise returns expr1 | SELECT NULLIF(25, 25);<br>-> |
| SESSION_USER() | Returns the current user | SELECT SESSION_USER();<br>-> guest@% |
| SYSTEM_USER() | Returns the current user | SELECT SYSTEM_USER();<br>-> guest@% |
| USER() | Returns the current user | SELECT USER();<br>-> guest@% |
| VERSION() | Returns the database version number | SELECT VERSION();<br>-> 5.6.34 |