MySQL Functions

MySQL has many built-in functions. The following lists 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 EXAMPLE

SELECT CHAR_LENGTH("EXAMPLE") 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 EXAMPLE

SELECT CHARACTER_LENGTH("EXAMPLE") AS LengthOfString;
CONCAT(s1,s2...sn) Merges multiple strings such as s1, s2, etc. into one string

Combine multiple strings

SELECT CONCAT("SQL ", "Example ", "Gooogle ", "Facebook") AS ConcatenatedString;
CONCAT_WS(x, s1,s2...sn) Same as CONCAT(s1,s2,...) function, but adds x between each string; x can be a separator.

Combine multiple strings and add 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 string list (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 the string in s2 that matches s1

Returns the position of string c in the specified string:

SELECT FIND_IN_SET("c", "a,b,c,d,e");
FORMAT(x,n) The function formats the number x as "#,###.##", keeping x to n decimal places, with the last digit rounded.

Format the number in the "#,###.##" form:

SELECT FORMAT(250500.5634, 2);     -- 输出 250,500.56
INSERT(s1,x,len,s2) Replaces the string in s1 starting at position x with length len with string s2.

Replace the 6 characters starting from the first position of the string with example:

SELECT INSERT("google.com", 1, 6, "example");  -- 输出：example.com
LOCATE(s1,s) Get the starting position of s1 from string s

Get the position of b in the string abc:

SELECT LOCATE('st','myteststring');  -- 5

Returns the position of b in the string abc:

SELECT LOCATE('b', 'abc') -- 2
LCASE(s) Converts all letters of string s to lowercase letters

Convert the string EXAMPLE to lowercase:

SELECT LCASE('EXAMPLE') -- example
LEFT(s,n) Returns the first n characters of string s

Returns the first two characters of the string example:

SELECT LEFT('example',2) -- ru
LOWER(s) Converts all letters of string s to lowercase letters

Convert the string EXAMPLE to lowercase:

SELECT LOWER('EXAMPLE') -- example
LPAD(s1,len,s2) Pads string s2 at the beginning of string s1 so that the string length reaches len.

Pad the string xx at the beginning of the string abc:

SELECT LPAD('abc',5,'xx') -- xxabc
LTRIM(s) Removes spaces at the beginning of string s

Remove spaces at the beginning of the string EXAMPLE:

SELECT LTRIM("    EXAMPLE") AS LeftTrimmedString;-- EXAMPLE
MID(s,n,len) Extracts a substring of length len from position n of string s, same as SUBSTRING(s,n,len)

Extract 3 characters from the 2nd position of the string EXAMPLE:

SELECT MID("EXAMPLE", 2, 3) AS ExtractString; -- UNO
POSITION(s1 IN s) Get the starting position of s1 from 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

Repeat the string example three times:

SELECT REPEAT('example',3) -- exampleexampleexample
REPLACE(s,s1,s2) Replaces string s1 in string s with string s2

Replace character a in the string abc with character x:

SELECT REPLACE('abc','a','x') --xbc
REVERSE(s) Reverses the order of string s

Reverse the order of 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 example:

SELECT RIGHT('example',2) -- ob
RPAD(s1,len,s2) Appends string s2 at the end of string s1, making the string length reach len.

Pad the string xx at the end of the string abc:

SELECT RPAD('abc',5,'xx') -- abcxx
RTRIM(s) Removes spaces at the end of string s

Remove trailing spaces from the string EXAMPLE:

SELECT RTRIM("EXAMPLE     ") AS RightTrimmedString;   -- EXAMPLE
SPACE(n) Returns n spaces

Returns 10 spaces:

SELECT SPACE(10);
STRCMP(s1,s2) Compares strings s1 and s2; returns 0 if s1 equals s2, returns 1 if s1>s2, returns -1 if s1<s2.

Compare strings:

SELECT STRCMP("example", "example");  -- 0
SUBSTR(s, start, length) Extracts a substring of length length from position start of string s.

Extract 3 characters from the 2nd position of the string EXAMPLE:

SELECT SUBSTR("EXAMPLE", 2, 3) AS ExtractString; -- UNO
SUBSTRING(s, start, length) Extracts a substring of length length from position start of string s, equivalent to SUBSTR(s, start, length)

Extract 3 characters from the 2nd position of the string EXAMPLE:

SELECT SUBSTRING("EXAMPLE", 2, 3) AS ExtractString; -- UNO
SUBSTRING_INDEX(s, delimiter, number) Returns the substring after the number-th occurrence of the delimiter delimiter in string s.
If number is positive, returns the string to the left of the number-th character.
If number is negative, returns the string to the right of the (absolute value of number (counting from the right))-th character.		 
SELECT SUBSTRING_INDEX('a*b','*',1) -- a
SELECT SUBSTRING_INDEX('a*b','*',-1)    -- b
SELECT SUBSTRING_INDEX(SUBSTRING_INDEX('a*b*c*d*e','*',3),'*',-1)    -- c
TRIM(s) Removes spaces at the beginning and end of string s

Remove leading and trailing spaces from the string EXAMPLE:

SELECT TRIM('    EXAMPLE    ') AS TrimmedString;
UCASE(s) Converts the string to uppercase

Convert the string example to uppercase:

SELECT UCASE("example"); -- EXAMPLE
UPPER(s) Converts the string to uppercase

Convert the string example to uppercase:

SELECT UPPER("example"); -- EXAMPLE

MySQL Numeric Functions

Function name Description Example
ABS(x) Returns the absolute value of x

Returns the absolute value of -1:

SELECT ABS(-1) -- 返回1
ACOS(x) Returns the arccosine of x (in radians); x is a numeric value. 
SELECT ACOS(0.25);
ASIN(x) Returns the arc sine of x (in radians); x is a numeric value. 
SELECT ASIN(0.25);
ATAN(x) Returns the arctangent of x (in radians); x is a numeric value. 
SELECT ATAN(2.5);
ATAN2(n, m) Returns the arctangent (in radians) 
SELECT ATAN2(-0.8, 2);
AVG(expression) Returns the average value of an expression; expression is a field.

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) -- 返回2
CEILING(x) Returns the smallest integer greater than or equal to x 
SELECT CEILING(1.5); -- 返回2
COS(x) Returns the cosine (parameter is in radians) 
SELECT COS(2);
COT(x) Returns the cotangent (parameter is in radians) 
SELECT COT(6);
COUNT(expression) Returns the total number of records in the query; the expression parameter is a field or *.

Returns the total number of records of the products field in 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 to the third power:

SELECT EXP(3) -- 20.085536923188
FLOOR(x) Returns the largest integer less than or equal to x

The largest integer less than or equal to 1.5:

SELECT FLOOR(1.5) -- 返回1
GREATEST(expr1, expr2, expr3, ...) Returns the maximum value in the list

Returns the maximum value in the following number list:

SELECT GREATEST(3, 12, 34, 8, 25); -- 34

Returns the maximum value in the following string list:

SELECT GREATEST("Google", "Example", "Apple");   -- Example
LEAST(expr1, expr2, expr3, ...) Returns the minimum value in the list

Returns the minimum value in the following number list:

SELECT LEAST(3, 12, 34, 8, 25); -- 3

Returns the minimum value in the following string list:

SELECT LEAST("Google", "Example", "Apple");   -- Apple
LN Returns the natural logarithm of the number, with base e.

Returns the natural logarithm of 2:

SELECT LN(2);  -- 0.6931471805599453
LOG(x) or LOG(base, x) Returns the natural logarithm (logarithm with base e); if a base parameter is provided, then base is the specified base. 
SELECT LOG(20.085536923188) -- 3
SELECT LOG(2, 4); -- 2
LOG10(x) Returns the logarithm to base 10 
SELECT LOG10(100) -- 2
LOG2(x) Returns the logarithm to base 2

Returns the logarithm of 6 to base 2:

SELECT LOG2(6);  -- 2.584962500721156
MAX(expression) Returns the maximum value in field expression

Returns the maximum value of the Price field in the Products table:

SELECT MAX(Price) AS LargestPrice FROM Products;
MIN(expression) Returns the minimum value in field expression

Returns the minimum value of the Price field in the Products table:

SELECT MIN(Price) AS MinPrice FROM Products;
MOD(x,y) Returns the remainder after dividing x by y

The remainder of 5 divided by 2:

SELECT MOD(5,2) -- 1
PI() Returns pi (3.141593) 
SELECT PI() --3.141593
POW(x,y) Returns x raised to the power of y

2 to the 3rd power:

SELECT POW(2,3) -- 8
POWER(x,y) Returns x raised to the power of y

2 to the 3rd power:

SELECT POWER(2,3) -- 8
RADIANS(x) Converts degrees to radians

Convert 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 integer closest to x; the optional parameter y indicates the number of decimal places to round to; if omitted, an integer is returned. 
SELECT ROUND(1.23456) --1

SELECT ROUND(345.156, 2) -- 345.16
SIGN(x) Returns the sign of x; if x is negative, zero, or positive, returns -1, 0, and 1 respectively. 
SELECT SIGN(-10) -- (-1)
SIN(x) Calculate the sine value (the parameter is in radians) 
SELECT SIN(RADIANS(30)) -- 0.5
SQRT(x) Return the square root of x

Square root of 25:

SELECT SQRT(25) -- 5
SUM(expression) Return the sum of the specified field

Calculate the sum of the Quantity field in the OrderDetails table:

SELECT SUM(Quantity) AS TotalItemsOrdered FROM OrderDetails;
TAN(x) Calculate the tangent value (the parameter is in radians) 
SELECT TAN(1.75);  -- -5.52037992250933
TRUNCATE(x,y) Return the value of x rounded to y decimal places (the biggest difference from ROUND is that it does not round) 
SELECT TRUNCATE(1.23456,3) -- 1.234

MySQL Date Functions

Function name Description Example
ADDDATE(d,n) Return the date after adding n days to the starting date d 
SELECT ADDDATE("2017-06-15", INTERVAL 10 DAY);
->2017-06-25
ADDTIME(t,n) n is a time expression; add the time expression n to time t

Add 5 seconds:

SELECT ADDTIME('2011-11-11 11:11:11', 5);
->2011-11-11 11:11:16 (秒)

Add 2 hours, 10 minutes, 5 seconds:

SELECT ADDTIME("2020-06-15 09:34:21", "2:10:5"); 
-> 2020-06-15 11:44:26
CURDATE() Return the current date 
SELECT CURDATE();
-> 2018-09-19
CURRENT_DATE() Return the current date 
SELECT CURRENT_DATE();
-> 2018-09-19
CURRENT_TIME Return the current time 
SELECT CURRENT_TIME();
-> 19:59:02
CURRENT_TIMESTAMP() Return the current date and time 
SELECT CURRENT_TIMESTAMP()
-> 2018-09-19 20:57:43
CURTIME() Return the current time 
SELECT CURTIME();
-> 19:59:02
DATE() Extract the date value from a date or date-time expression 
SELECT DATE("2017-06-15");    
-> 2017-06-15
DATEDIFF(d1,d2) Return the number of days between d1 and d2 
SELECT DATEDIFF('2001-01-01','2001-02-02')
-> -32
DATE_ADD(d，INTERVAL expr type) Return the date after adding a time interval to the starting date d; the type value 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);    
-> 2017-06-25

SELECT DATE_ADD("2017-06-15 09:34:21", INTERVAL 15 MINUTE);
-> 2017-06-15 09:49:21

SELECT DATE_ADD("2017-06-15 09:34:21", INTERVAL -3 HOUR);
->2017-06-15 06:34:21

SELECT DATE_ADD("2017-06-15 09:34:21", INTERVAL -3 MONTH);
->2017-03-15 09:34:21
DATE_FORMAT(d,f) Display date d according to the format expression f 
SELECT DATE_FORMAT('2011-11-11 11:11:11','%Y-%m-%d %r')
-> 2011-11-11 11:11:11 AM
DATE_SUB(date,INTERVAL expr type) The function subtracts a specified time interval from a date.

Subtract 2 days from the OrderDate field in the Orders table:

SELECT OrderId,DATE_SUB(OrderDate,INTERVAL 2 DAY) AS OrderPayDate
FROM Orders
DAY(d) Return the date part of the date value d 
SELECT DAY("2017-06-15");  
-> 15
DAYNAME(d) Return the weekday of date d, such as Monday, Tuesday 
SELECT DAYNAME('2011-11-11 11:11:11')
->Friday
DAYOFMONTH(d) Return the day of the month for date d 
SELECT DAYOFMONTH('2011-11-11 11:11:11')
->11
DAYOFWEEK(d) Return the weekday of date d, where 1 is Sunday, 2 is Monday, and so on 
SELECT DAYOFWEEK('2011-11-11 11:11:11')
->6
DAYOFYEAR(d) Return the day of the year for date d 
SELECT DAYOFYEAR('2011-11-11 11:11:11')
->315
EXTRACT(type FROM d) Get the specified value from date d; type specifies the value to return.
Possible values for type are:
  • 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 EXTRACT(MINUTE FROM '2011-11-11 11:11:11') 
-> 11
FROM_DAYS(n) Return the date n days after January 1, year 0000 
SELECT FROM_DAYS(1111)
-> 0003-01-16
HOUR(t) Return the hour value from t 
SELECT HOUR('1:2:3')
-> 1
LAST_DAY(d) Return the last day of the month for the given date 
SELECT LAST_DAY("2017-06-20");
-> 2017-06-30
LOCALTIME() Return the current date and time 
SELECT LOCALTIME()
-> 2018-09-19 20:57:43
LOCALTIMESTAMP() Return the current date and time 
SELECT LOCALTIMESTAMP()
-> 2018-09-19 20:57:43
MAKEDATE(year, day-of-year) Return a date based on the given year and day-of-year 
SELECT MAKEDATE(2017, 3);
-> 2017-01-03
MAKETIME(hour, minute, second) Combine a time from the parameters hour, minute, and second 
SELECT MAKETIME(11, 35, 4);
-> 11:35:04
MICROSECOND(date) Return the microsecond value corresponding to the date argument 
SELECT MICROSECOND("2017-06-20 09:34:00.000023");
-> 23
MINUTE(t) Return the minute value from t 
SELECT MINUTE('1:2:3')
-> 2
MONTHNAME(d) Return the month name from the date, such as November 
SELECT MONTHNAME('2011-11-11 11:11:11')
-> November
MONTH(d) Return the month value from date d, 1 to 12 
SELECT MONTH('2011-11-11 11:11:11')
->11
NOW() Return the current date and time 
SELECT NOW()
-> 2018-09-19 20:57:43
PERIOD_ADD(period, number) Add a time interval to a year-month combined date 
SELECT PERIOD_ADD(201703, 5);   
-> 201708
PERIOD_DIFF(period1, period2) Return the month difference between two time intervals 
SELECT PERIOD_DIFF(201710, 201703);
-> 7
QUARTER(d) Return the quarter of date d, from 1 to 4 
SELECT QUARTER('2011-11-11 11:11:11')
-> 4
SECOND(t) Return the second value from t 
SELECT SECOND('1:2:3')
-> 3
SEC_TO_TIME(s) Convert time s in seconds to hours, minutes, and seconds format 
SELECT SEC_TO_TIME(4320)
-> 01:12:00
STR_TO_DATE(string, format_mask) Convert a string to a date 
SELECT STR_TO_DATE("August 10 2017", "%M %d %Y");
-> 2017-08-10
SUBDATE(d,n) Return the date after subtracting n days from date d 
SELECT SUBDATE('2011-11-11 11:11:11', 1)
->2011-11-10 11:11:11 (默认是天)
SUBTIME(t,n) Return the time after subtracting n seconds from time t 
SELECT SUBTIME('2011-11-11 11:11:11', 5)
->2011-11-11 11:11:06 (秒)
SYSDATE() Return the current date and time 
SELECT SYSDATE()
-> 2018-09-19 20:57:43
TIME(expression) Extract the time part of the given expression 
SELECT TIME("19:30:10");
-> 19:30:10
TIME_FORMAT(t,f) Format time t according to the format expression f 
SELECT TIME_FORMAT('11:11:11','%r')
11:11:11 AM
TIME_TO_SEC(t) Convert time t to seconds 
SELECT TIME_TO_SEC('1:12:00')
-> 4320
TIMEDIFF(time1, time2) Return the time difference 
mysql> SELECT TIMEDIFF("13:10:11", "13:10:10");
-> 00:00:01
mysql> SELECT TIMEDIFF('2000:01:01 00:00:00',
    ->                 '2000:01:01 00:00:00.000001');
        -> '-00:00:00.000001'
mysql> SELECT TIMEDIFF('2008-12-31 23:59:59.000001',
    ->                 '2008-12-30 01:01:01.000002');
        -> '46:58:57.999999'
TIMESTAMP(expression, interval) With one argument, the function returns the date or date-time expression; with two arguments, it returns the sum of the arguments 
mysql> SELECT TIMESTAMP("2017-07-23",  "13:10:11");
-> 2017-07-23 13:10:11
mysql> SELECT TIMESTAMP('2003-12-31');
        -> '2003-12-31 00:00:00'
mysql> SELECT TIMESTAMP('2003-12-31 12:00:00','12:00:00');
        -> '2004-01-01 00:00:00'
TIMESTAMPDIFF(unit,datetime_expr1,datetime_expr2) Return the time difference, i.e., datetime_expr2 − datetime_expr1 
mysql> SELECT TIMESTAMPDIFF(DAY,'2003-02-01','2003-05-01');   // 计算两个时间相隔多少天
        -> 89
mysql> SELECT TIMESTAMPDIFF(MONTH,'2003-02-01','2003-05-01');   // 计算两个时间相隔多少月
        -> 3
mysql> SELECT TIMESTAMPDIFF(YEAR,'2002-05-01','2001-01-01');    // 计算两个时间相隔多少年
        -> -1
mysql> SELECT TIMESTAMPDIFF(MINUTE,'2003-02-01','2003-05-01 12:05:55');  // 计算两个时间相隔多少分钟
        -> 128885
TO_DAYS(d) Return the number of days from date d to January 1, year 0000 
SELECT TO_DAYS('0001-01-01 01:01:01')
-> 366
WEEK(d) Return the week number of date d within the year, ranging from 0 to 53 
SELECT WEEK('2011-11-11 11:11:11')
-> 45
WEEKDAY(d) Return the weekday of date d, where 0 is Monday, 1 is Tuesday 
SELECT WEEKDAY("2017-06-15");
-> 3
WEEKOFYEAR(d) Return the week number of date d within the year, ranging from 0 to 53 
SELECT WEEKOFYEAR('2011-11-11 11:11:11')
-> 45
YEAR(d) Return the year 
SELECT YEAR("2017-06-15");
-> 2017
YEARWEEK(date, mode) Return the year and week number (0 to 53); in mode, 0 means Sunday, 1 means Monday, and so on 
SELECT YEARWEEK("2017-06-15");
-> 201724

MySQL Advanced Functions

Function name Description Example
BIN(x) Return the binary representation of x, where x is a decimal number

Binary representation of 15:

SELECT BIN(15); -- 1111
BINARY(s) Convert string s to a binary string 
SELECT BINARY "EXAMPLE";
-> EXAMPLE

CASE expression
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
   ...
    WHEN conditionN THEN resultN
    ELSE result
END
 CASE marks the start of the function and END marks the end. If condition1 is true, return result1; if condition2 is true, return result2; if none are true, return result. Once one condition is true, the later ones are not evaluated. 
SELECT CASE 
　　WHEN 1 > 0
　　THEN '1 > 0'
　　WHEN 2 > 0
　　THEN '2 > 0'
　　ELSE '3 > 0'
　　END
->1 > 0
CAST(x AS type) Convert a data type

Convert a string date to a date:

SELECT CAST("2017-08-29" AS DATE);
-> 2017-08-29
COALESCE(expr1, expr2, ...., expr_n) Return the first non-null expression among the arguments (from left to right) 
SELECT COALESCE(NULL, NULL, NULL, 'example.com', NULL, 'google.com');
-> example.com
CONNECTION_ID() Return the unique connection ID 
SELECT CONNECTION_ID();
-> 4292835
CONV(x,f1,f2) Convert a number from base f1 to base f2 
SELECT CONV(15, 10, 2);
-> 1111
CONVERT(s USING cs) The function changes the character set of string s to cs 
SELECT CHARSET('ABC')
->utf-8    

SELECT CHARSET(CONVERT('ABC' USING gbk))
->gbk
CURRENT_USER() Return the current user 
SELECT CURRENT_USER();
-> guest@%
DATABASE() Return the current database name 
SELECT DATABASE();   
-> example
IF(expr,v1,v2) If expression expr is true, return v1; otherwise, return v2. 
SELECT IF(1 > 0,'正确','错误')    
->正确
IFNULL(v1,v2) If v1 is not NULL, return v1; otherwise, return v2. 
SELECT IFNULL(null,'Hello Word')
->Hello Word
ISNULL(expression) Check whether an expression is NULL 
SELECT ISNULL(NULL);
->1
LAST_INSERT_ID() Return the most recently generated AUTO_INCREMENT value 
SELECT LAST_INSERT_ID();
->6
NULLIF(expr1, expr2) Compare two strings; if expr1 equals expr2, return NULL; otherwise, return expr1 
SELECT NULLIF(25, 25);
->
SESSION_USER() Return the current user 
SELECT SESSION_USER();
-> guest@%
SYSTEM_USER() Return the current user 
SELECT SYSTEM_USER();
-> guest@%
USER() Return the current user 
SELECT USER();
-> guest@%
VERSION() Return the database version 
SELECT VERSION()
-> 5.6.34

The following are some common functions added in MySQL 8.0:

FunctionDescriptionExample
JSON_OBJECT()Convert key-value pairs to a JSON object 
SELECT JSON_OBJECT('key1', 'value1', 'key2', 'value2')
JSON_ARRAY()Convert values to a JSON array
SELECT JSON_ARRAY(1, 2, 'three')
JSON_EXTRACT()Extract the specified value from a JSON string
SELECT JSON_EXTRACT('{"name": "John", "age": 30}', '$.name')
JSON_CONTAINS()Check whether a JSON string contains the specified value
SELECT JSON_CONTAINS('{"name": "John", "age": 30}', 'John', '$.name')
ROW_NUMBER()Assign a unique number to each row in the query result
SELECT ROW_NUMBER() OVER(ORDER BY id) AS row_number, name FROM users
RANK()Assign a rank to each row in the query result
SELECT RANK() OVER(ORDER BY score DESC) AS rank, name, score FROM students
Other extensions