TiDB Functions
Overview
The usage of functions and operators in TiDB is basically consistent with MySQL. For details, see: Functions and Operators
In SQL statements, expressions can be used in places such as the ORDER BY or HAVING clauses of SELECT statements, the WHERE clause of SELECT/DELETE/UPDATE statements, or SET statements.
Type Conversion in Expression Evaluation
Type conversion in expression evaluation in TiDB is basically consistent with MySQL. For details, see MySQL Type Conversion in Expression Evaluation.
Operators
| Operator | Function Description |
| AND, && | Logical AND |
| = | Assignment (can be used in SET statements, or in the SET of UPDATE statements) |
| := | Assignment |
| BETWEEN … AND … | Judge whether the value satisfies the range |
| BINARY | Convert a string to a binary string |
| & | Bitwise AND |
| ~ | Bitwise NOT |
| | | Bitwise OR |
| ^ | Bitwise XOR |
| CASE | Case operator |
| DIV | Integer division |
| / | Division |
| = | Equality comparison |
| <=> | Null-safe equality comparison |
| > | Greater than |
| >= | Greater than or equal to |
| IS | Judge whether a value equals a boolean value |
| IS NOT | Judge whether a value does not equal a boolean value |
| IS NOT NULL | Non-null judgment |
| IS NULL | Null judgment |
| << | Left shift |
| < | Less than |
| <= | Less than or equal to |
| LIKE | Simple pattern matching |
| - | Subtraction |
| %, MOD | Modulo |
| NOT, ! | Negation |
| NOT BETWEEN … AND … | Judge whether the value is not within the range |
| !=, <> | Not equal |
| NOT LIKE | Does not conform to simple pattern matching |
| NOT REGEXP | Does not conform to regular expression pattern matching |
| ||, OR | Logical OR |
| + | Addition |
| REGEXP | Use regular expressions for pattern matching |
| >> | Right shift |
| RLIKE REGEXP | Synonym |
| * | Multiplication |
| - | Sign change |
| XOR | Logical XOR |
Operator Precedence
Operator precedence is shown in the following list, from highest precedence to lowest precedence. Operators shown on the same line have the same precedence.
Flow Control Functions
TiDB supports using all flow control functions provided in MySQL 5.7.
| Function Name | Function Description |
| CASE | Case operator |
| IF() | Build if/else |
| IFNULL() | Build Null if/else |
| NULLIF() | If expr1 = expr2, return NULL |
String Functions
TiDB supports using most string functions provided in MySQL 5.7.
Supported Functions
| Function Name | Function Description |
| ASCII() | Return the numeric value of the leftmost character |
| BIN() | Return the string representation of the binary value of a number |
| BIT_LENGTH() | Return the bit length of the string |
| CHAR() | Return a string consisting of characters given by the code values of integers |
| CHAR_LENGTH() | Return the character length of the string |
| CHARACTER_LENGTH() | Same functionality as CHAR_LENGTH() |
| CONCAT() | Return the concatenated string |
| CONCAT_WS() | Return a string connected by separators |
| ELT() | Return the string at the specified position |
| EXPORT_SET() | Return a string where each bit set in the value bits gets an on string, and each unset bit gets an off string |
| FIELD() | Return the first position where the parameter appears in subsequent parameters |
| FIND_IN_SET() | Return the position of the first parameter in the second parameter |
| FORMAT() | Return a number formatted to the specified decimal places |
| FROM_BASE64() | Decode a base-64 represented string and return the result |
| HEX() | Return the hexadecimal representation of a decimal number or string value |
| INSERT() | Insert a substring at the specified position, not exceeding the specified number of characters |
| INSTR() | Return the index of the first occurrence of the substring |
| LCASE() | Same functionality as LOWER() |
| LEFT() | Return the leftmost characters of specified length |
| LENGTH() | Return the string length in bytes |
| LIKE | Perform simple pattern matching |
| LOCATE() | Return the position of the first occurrence of the substring |
| LOWER() | Return the parameter in all lowercase |
| LPAD() | Return the string parameter with the specified string added to the left |
| LTRIM() | Remove leading spaces |
| MAKE_SET() | Return a comma-separated set of strings where the bits correspond to the given bits parameter |
| MID() | Return a substring starting from the specified position |
| NOT LIKE | Negate simple pattern matching |
| NOT REGEXP | Negation of REGEXP |
| OCT() | Return the octal representation of a number as a string |
| OCTET_LENGTH() | Same functionality as LENGTH() |
| ORD() | Return the character encoding of the leftmost character of the parameter |
| POSITION() | Same functionality as LOCATE() |
| QUOTE() | Escape the parameter for use in SQL statements |
| REGEXP | Use regular expressions for pattern matching |
| REPEAT() | Repeat a string the specified number of times |
| REPLACE() | Replace all occurrences of the specified string |
| REVERSE() | Reverse all characters in the string |
| RIGHT() | Return the specified number of rightmost characters |
| RLIKE | Same functionality as REGEXP |
| RPAD() | Add string the specified number of times |
| RTRIM() | Remove trailing spaces |
| SPACE() | Return the specified number of spaces as a string |
| STRCMP() | Compare two strings |
| SUBSTR() | Return the specified substring |
| SUBSTRING() | Return the specified substring |
| SUBSTRING_INDEX() | Return the substring before the specified occurrence of the delimiter from a string |
| TO_BASE64() | Return the string parameter converted to base-64 representation |
| TRIM() | Remove leading and trailing spaces |
| UCASE() | Same functionality as UPPER() |
| UNHEX() | Return the hexadecimal representation of a number as a string |
| UPPER() | Convert parameter to uppercase |
Unsupported Functions
-
LOAD_FILE()
-
MATCH
-
SOUNDEX()
-
SOUNDS LIKE
-
WEIGHT_STRING()
Numeric Functions and Operators
TiDB supports using all numeric functions and operators provided in MySQL 5.7.
Arithmetic Operators
| Operator Name | Function Description |
| + | Plus |
| - | Minus |
| * | Multiply |
| / | Divide |
| DIV | Integer division |
| %, MOD | Modulo operation, remainder |
| - | Change parameter sign |
Mathematical Functions
| Function Name | Function Description |
| POW() | Return the result value of the specified power of the parameter |
| POWER() | Return the result value of the specified power of the parameter |
| EXP() | Return the value after the specified power of e (the base of natural logarithm) |
| SQRT() | Return the square root of a non-negative number |
| LN() | Return the natural logarithm of the parameter |
| LOG() | Return the natural logarithm of the first parameter |
| LOG2() | Return the logarithm of the parameter with base 2 |
| LOG10() | Return the logarithm of the parameter with base 10 |
| PI() | Return the value of pi |
| TAN() | Return the tangent value of the parameter |
| COT() | Return the cotangent value of the parameter |
| SIN() | Return the sine value of the parameter |
| COS() | Return the cosine value of the parameter |
| ATAN() | Return the arctangent value of the parameter |
| ATAN2(), ATAN() | Return the arctangent value of two parameters |
| ASIN() | Return the arcsine value of the parameter |
| ACOS() | Return the arccosine value of the parameter |
| RADIANS() | Return the parameter converted from degrees to radians |
| DEGREES() | Return the parameter converted from radians to degrees |
| MOD() | Return the remainder |
| ABS() | Return the absolute value of the parameter |
| CEIL() | Return the smallest integer value not less than the parameter |
| CEILING() | Return the smallest integer value not less than the parameter |
| FLOOR() | Return the largest integer value not greater than the parameter |
| ROUND() | Return the integer or value with specified decimal places closest to the parameter |
| RAND() | Return a random floating-point value |
| SIGN() | Return the sign of the parameter |
| CONV() | Convert numbers between different bases, return the string representation of the number |
| TRUNCATE() | Return the number truncated to the specified decimal places |
| CRC32() | Calculate cyclic redundancy check value and return a 32-bit unsigned value |
Date and Time Functions
| Function Name | Function Description |
| ADDDATE() | Add time interval to date |
| ADDTIME() | Add time values |
| CONVERT_TZ() | Convert timezone |
| CURDATE() | Return current date |
| CURRENT_DATE(), CURRENT_DATE | Synonym with CURDATE() |
| CURRENT_TIME(), CURRENT_TIME | Synonym with CURTIME() |
| CURRENT_TIMESTAMP(), CURRENT_TIMESTAMP | Synonym with NOW() |
| CURTIME() | Return current time |
| DATE() | Extract the date part from a date or date/time expression |
| DATE_ADD() | Add time interval to date |
| DATE_FORMAT() | Return date/time that satisfies the specified format |
| DATE_SUB() | Subtract specified time interval from date |
| DATEDIFF() | Return the number of days between two dates |
| DAY() | Synonym with DAYOFMONTH() |
| DAYNAME() | Return day name |
| DAYOFMONTH() | Return the day part corresponding to the parameter (1-31) |
| DAYOFWEEK() | Return the day of week index corresponding to the parameter |
| DAYOFYEAR() | Return which day of the year the parameter represents (1-366) |
| EXTRACT() | Extract individual parts from date/time |
| FROM_DAYS() | Convert days to date |
| FROM_UNIXTIME() | Format Unix timestamp as date |
| GET_FORMAT() | Return a string that satisfies the date format |
| HOUR() | Extract the hour part from date/time expression |
| LAST_DAY | Return the last day of the month in the parameter |
| LOCALTIME(), LOCALTIME | Synonym with NOW() |
| LOCALTIMESTAMP, LOCALTIMESTAMP() | Synonym with NOW() |
| MAKEDATE() | Generate a date based on the given year and day of the year |
| MAKETIME() | Generate a time based on the given hour, minute, and second |
| MICROSECOND() | Return the microsecond part of the parameter |
| MINUTE() | Return the minute part of the parameter |
| MONTH() | Return the month part of the parameter |
| MONTHNAME() | Return the month name of the parameter |
| NOW() | Return current date and time |
| PERIOD_ADD() | Add a period (number of months) to year-month expression |
| PERIOD_DIFF() | Return the number of months in the interval |
| QUARTER() | Return the quarter corresponding to the parameter (1-4) |
| SEC_TO_TIME() | Convert seconds to 'HH:MM:SS' format |
| SECOND() | Return seconds (0-59) |
| STR_TO_DATE() | Convert string to date |
| SUBDATE() | When three parameters are passed, it is a synonym for DATE_SUB() |
| SUBTIME() | Subtract a period from a time |
| SYSDATE() | Return the time when the method is executed |
| TIME() | Return the time expression part of the parameter |
| TIME_FORMAT() | Format time |
| TIME_TO_SEC() | Return the seconds corresponding to the parameter |
| TIMEDIFF() | Return time interval |
| TIMESTAMP() | When one parameter is passed, the method returns a date or date/time expression. When two parameters are passed, it returns the sum of the parameters |
| TIMESTAMPADD() | Add a time interval to a date/time expression |
| TIMESTAMPDIFF() | Subtract a time interval from a date/time expression |
| TO_DAYS() | Convert the parameter to the corresponding number of days (starting from year 0) |
| TO_SECONDS() | Convert date or date/time parameter to seconds (starting from year 0) |
| UNIX_TIMESTAMP() | Return a Unix timestamp |
| UTC_DATE() | Return current UTC date |
| UTC_TIME() | Return current UTC time |
| UTC_TIMESTAMP() | Return current UTC date and time |
| WEEK() | Return the week number of the year where the parameter is located |
| WEEKDAY() | Return day of week index |
| WEEKOFYEAR() | Return the week number of the year corresponding to the parameter in the calendar |
| YEAR() | Return the year corresponding to the parameter |
| YEARWEEK() | Return year and week number |
GROUP BY Aggregate Functions
The MySQL GROUP BY aggregate functions supported by TiDB are shown below:
| Function Name | Function Description |
| COUNT() | Return the number of retrieved rows |
| COUNT(DISTINCT) | Return the number of distinct values |
| SUM() | Return sum |
| AVG() | Return average |
| MAX() | Return maximum value |
| MIN() | Return minimum value |
| GROUP_CONCAT() | Return concatenated string |
Note:
-
Unless otherwise specified, aggregate functions ignore NULL values by default.
-
If aggregate functions are used in statements without a GROUP BY clause, it is equivalent to grouping all rows.
GROUP BY Modifiers
TiDB currently does not support GROUP BY modifiers, such as WITH ROLLUP.
Window Functions
The usage of window functions in TiDB is basically consistent with MySQL 8.0. For details, see MySQL Window Functions.
The window functions supported by TiDB are shown below:
| Function Name | Function Description |
| CUME_DIST() | Return the cumulative distribution in a set of values |
| DENSE_RANK() | Return the rank of the current row in the partition, and the rank is continuous |
| FIRST_VALUE() | The expression value of the first row in the current window |
| LAG() | The expression value of the Nth row before the current row in the partition |
| LAST_VALUE() | The expression value of the last row in the current window |
| LEAD() | The expression value of the Nth row after the current row in the partition |
| NTH_VALUE() | The expression value of the Nth row in the current window |
| NTILE() | Divide the partition into N buckets and assign bucket numbers to each row in the partition |
| PERCENT_RANK() | Return the percentage of rows in the partition that are less than the current row |
| RANK() | Return the rank of the current row in the partition, the rank may not be continuous |
| ROW_NUMBER() | Return the number of the current row in the partition |
Other Functions
TiDB supports using most other functions provided in MySQL 5.7.