3.10 函数与操作符支持列表(alpha版本)
3.10.0 注意：
- 如果能保证SQL会将函数整体下发给某个后端结点，函数支持度没有意义，支持度将托管于MySQL.
- 在2.18.09.0 版本之前（含），涉及到中文字符集的字符串相关函数有bug。
- 除了聚合函数经过充分测试之外，其余函数只进行了简单的测试，并没有全覆盖测试，请使用前充分测试 。
- 未在列表中的函数，一概不支持。
3.10.1 Operators
| Name | Description | Support |
|---|---|---|
| AND, && | Logical AND | Y |
| = | Assign a value (as part of a SET statement, or as part of the SET clause in an UPDATE statement) | Y |
| := | Assign a value | N |
| BETWEEN ... AND ... | Check whether a value is within a range of values | Y |
| BINARY | Cast a string to a binary string | N |
| & | Bitwise AND | Y |
| ~ | Bitwise inversion | Y |
| | | Bitwise OR | Y |
| ^ | Bitwise XOR | Y |
| CASE | Case operator | Y |
| DIV | Integer division | Y |
| / | Division operator | Y |
| = | Equal operator | Y |
| <=> | NULL-safe equal to operator | Y |
| > | Greater than operator | Y |
| >= | Greater than or equal operator | Y |
| IS | Test a value against a boolean | Y |
| IS NOT | Test a value against a boolean | Y |
| IS NOT NULL | NOT NULL value test | Y |
| IS NULL | NULL value test | Y |
| -> | Return value from JSON column after evaluating path; equivalent to JSON_EXTRACT(). | N |
| ->> | Return value from JSON column after evaluating path and unquoting the result; equivalent to JSON_UNQUOTE(JSON_EXTRACT()). | N |
| << | Left shift | Y |
| < | Less than operator | Y |
| <= | Less than or equal operator | Y |
| LIKE | Simple pattern matching | Y |
| - | Minus operator | Y |
| %, MOD | Modulo operator | Y |
| NOT, ! | Negates value | Y |
| NOT BETWEEN ... AND ... | Check whether a value is not within a range of values | Y |
| !=, <> | Not equal operator | Y |
| NOT LIKE | Negation of simple pattern matching | Y |
| NOT REGEXP | Negation of REGEXP | Y |
| ||, OR | Logical OR | Y |
| + | Addition operator | Y |
| REGEXP | Whether string matches regular expression | Y |
| >> | Right shift | Y |
| RLIKE | Whether string matches regular expression | N |
| SOUNDS LIKE | Compare sounds | N |
| * | Multiplication operator | N |
| - | Change the sign of the argument | Y |
| XOR | Logical XOR | Y |
| COALESCE() | Return the first non-NULL argument | Y |
| GREATEST() | Return the largest argument | Y |
| IN() | Check whether a value is within a set of values | Y |
| INTERVAL() | Return the index of the argument that is less than the first argument | Y |
| ISNULL() | Test whether the argument is NULL | Y |
| LEAST() | Return the smallest argument | Y |
| STRCMP() | Compare two strings | Y |
3.10.2 Control Flow Functions
| Name | Description | Support |
|---|---|---|
| CASE | Case operator | Y |
| IF() | If/else construct | Y |
| IFNULL() | Null if/else construct | Y |
| NULLIF() | Return NULL if expr1 = expr2 | Y |
3.10.3 String Functions
| Name | Description | Support |
|---|---|---|
| ASCII() | Return numeric value of left-most character | Y |
| BIN() | Return a string containing binary representation of a number | N |
| BIT_LENGTH() | Return length of argument in bits | Y |
| CHAR() | Return the character for each integer passed | Y |
| CHAR_LENGTH() | Return number of characters in argument | Y |
| CHARACTER_LENGTH() | Synonym for CHAR_LENGTH() | Y |
| CONCAT() | Return concatenated string | Y |
| CONCAT_WS() | Return concatenate with separator | Y |
| ELT() | Return string at index number | Y |
| EXPORT_SET() | 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 | N |
| FIELD() | Return the index (position) of the first argument in the subsequent arguments | Y |
| FIND_IN_SET() | Return the index position of the first argument within the second argument | Y |
| FORMAT() | Return a number formatted to specified number of decimal places | Y |
| FROM_BASE64() | Decode base64 encoded string and return result | N |
| HEX() | Return a hexadecimal representation of a decimal or string value | Y |
| INSERT() | Insert a substring at the specified position up to the specified number of characters | Y |
| INSTR() | Return the index of the first occurrence of substring | Y |
| LCASE() | Synonym for LOWER() | Y |
| LEFT() | Return the leftmost number of characters as specified | Y |
| LENGTH() | Return the length of a string in bytes | Y |
| LIKE | Simple pattern matching | Y |
| LOAD_FILE() | Load the named file | N |
| LOCATE() | Return the position of the first occurrence of substring | Y |
| LOWER() | Return the argument in lowercase | Y |
| LPAD() | Return the string argument, left-padded with the specified string | Y |
| LTRIM() | Remove leading spaces | Y |
| MAKE_SET() | Return a set of comma-separated strings that have the corresponding bit in bits set | Y |
| MATCH | Perform full-text search | N |
| MID() | Return a substring starting from the specified position | N |
| NOT LIKE | Negation of simple pattern matching | Y |
| NOT REGEXP | Negation of REGEXP | Y |
| OCT() | Return a string containing octal representation of a number | N |
| OCTET_LENGTH() | Synonym for LENGTH() | N |
| ORD() | Return character code for leftmost character of the argument | Y |
| POSITION() | Synonym for LOCATE() | N |
| QUOTE() | Escape the argument for use in an SQL statement | Y |
| REGEXP | Whether string matches regular expression | Y |
| REPEAT() | Repeat a string the specified number of times | Y |
| REPLACE() | Replace occurrences of a specified string | Y |
| REVERSE() | Reverse the characters in a string | Y |
| RIGHT() | Return the specified rightmost number of characters | Y |
| RLIKE | Whether string matches regular expression | N |
| RPAD() | Append string the specified number of times | Y |
| RTRIM() | Remove trailing spaces | Y |
| SOUNDEX() | Return a soundex string | Y |
| SOUNDS LIKE | Compare sounds | Y |
| SPACE() | Return a string of the specified number of spaces | Y |
| STRCMP() | Compare two strings | Y |
| SUBSTR() | Return the substring as specified | Y |
| SUBSTRING() | Return the substring as specified | Y |
| SUBSTRING_INDEX() | Return a substring from a string before the specified number of occurrences of the delimiter | Y |
| TO_BASE64() | Return the argument converted to a base-64 string | N |
| TRIM() | Remove leading and trailing spaces | Y |
| UCASE() | Synonym for UPPER() | Y |
| UNHEX() | Return a string containing hex representation of a number | Y |
| UPPER() | Convert to uppercase | Y |
| WEIGHT_STRING() | Return the weight string for a string | N |
3.10.3.1 HEX 注意事项
由于 MySQL 文档中，只说明了 HEX 作用于小整数（<=64位）和string 的情况。没有具体说明大整数（>64位）和浮点数这类比较极端的情况。在以上这些极端情况下，MySQL 8.0 和 MySQL 5.7 的结果不一定一致。 dble 的 HEX 结果和 MySQL 8 实现保持一致，但建议用户不要过度依赖这个结果。
3.10.4 Numeric Functions and Operators
| Name | Description | Support |
|---|---|---|
| ABS() | Return the absolute value | Y |
| ACOS() | Return the arc cosine | Y |
| ASIN() | Return the arc sine | Y |
| ATAN() | Return the arc tangent | Y |
| ATAN2(), ATAN() | Return the arc tangent of the two arguments | Y |
| CEIL() | Return the smallest integer value not less than the argument | Y |
| CEILING() | Return the smallest integer value not less than the argument | Y |
| CONV() | Convert numbers between different number bases | Y |
| COS() | Return the cosine | Y |
| COT() | Return the cotangent | Y |
| CRC32() | Compute a cyclic redundancy check value | Y |
| DEGREES() | Convert radians to degrees | Y |
| DIV | Integer division | Y |
| / | Division operator | Y |
| EXP() | Raise to the power of | Y |
| FLOOR() | Return the largest integer value not greater than the argument | Y |
| LN() | Return the natural logarithm of the argument | Y |
| LOG() | Return the natural logarithm of the first argument | Y |
| LOG10() | Return the base-10 logarithm of the argument | Y |
| LOG2() | Return the base-2 logarithm of the argument | Y |
| - | Minus operator | Y |
| MOD() | Return the remainder | N (If the SQL pass through, still supported) |
| %, MOD | Modulo operator | Y |
| PI() | Return the value of pi | Y |
| + | Addition operator | Y |
| POW() | Return the argument raised to the specified power | Y |
| POWER() | Return the argument raised to the specified power | Y |
| RADIANS() | Return argument converted to radians | Y |
| RAND() | Return a random floating-point value | Y |
| ROUND() | Round the argument | Y |
| SIGN() | Return the sign of the argument | Y |
| SIN() | Return the sine of the argument | Y |
| SQRT() | Return the square root of the argument | Y |
| TAN() | Return the tangent of the argument | Y |
| * | Multiplication operator | Y |
| TRUNCATE() | Truncate to specified number of decimal places | Y |
| - | Change the sign of the argument | Y |
3.10.5 Date and Time Functions
| Name | Description | Support |
|---|---|---|
| ADDDATE() | Add time values (intervals) to a date value | Y |
| ADDTIME() | Add time | Y |
| CONVERT_TZ() | Convert from one time zone to another | N |
| CURDATE() | Return the current date | Y |
| CURRENT_DATE() | Synonyms for CURDATE() | Y |
| CURRENT_TIME() | Synonyms for CURTIME() | Y |
| CURRENT_TIMESTAMP() | Synonyms for NOW() | Y |
| CURTIME() | Return the current time | Y |
| DATE() | Extract the date part of a date or datetime expression | Y |
| DATE_ADD() | Add time values (intervals) to a date value | Y |
| DATE_FORMAT() | Format date as specified | Y |
| DATE_SUB() | Subtract a time value (interval) from a date | Y |
| DATEDIFF() | Subtract two dates | Y |
| DAY() | Synonym for DAYOFMONTH() | N |
| DAYNAME() | Return the name of the weekday | Y |
| DAYOFMONTH() | Return the day of the month (0-31) | Y |
| DAYOFWEEK() | Return the weekday index of the argument | Y |
| DAYOFYEAR() | Return the day of the year (1-366) | Y |
| EXTRACT() | Extract part of a date | Y |
| FROM_DAYS() | Convert a day number to a date | Y |
| FROM_UNIXTIME() | Format Unix timestamp as a date | Y |
| GET_FORMAT() | Return a date format string | Y |
| HOUR() | Extract the hour | Y |
| LAST_DAY | Return the last day of the month for the argument | N |
| LOCALTIME() | Synonym for NOW() | Y |
| LOCALTIMESTAMP() | Synonym for NOW() | Y |
| MAKEDATE() | Create a date from the year and day of year | Y |
| MAKETIME() | Create time from hour, minute, second | Y |
| MICROSECOND() | Return the microseconds from argument | Y |
| MINUTE() | Return the minute from the argument | Y |
| MONTH() | Return the month from the date passed | Y |
| MONTHNAME() | Return the name of the month | Y |
| NOW() | Return the current date and time | Y |
| PERIOD_ADD() | Add a period to a year-month | Y |
| PERIOD_DIFF() | Return the number of months between periods | Y |
| QUARTER() | Return the quarter from a date argument | Y |
| SEC_TO_TIME() | Converts seconds to 'HH:MM:SS' format | Y |
| SECOND() | Return the second (0-59) | Y |
| STR_TO_DATE() | Convert a string to a date | Y |
| SUBDATE() | Synonym for DATE_SUB() when invoked with three arguments | Y |
| SUBTIME() | Subtract times | Y |
| SYSDATE() | Return the time at which the function executes | Y |
| TIME() | Extract the time portion of the expression passed | Y |
| TIME_FORMAT() | Format as time | Y |
| TIME_TO_SEC() | Return the argument converted to seconds | Y |
| TIMEDIFF() | Subtract time | Y |
| TIMESTAMP() | With a single argument, this function returns the date or datetime expression; with two arguments, the sum of the arguments | N |
| TIMESTAMPADD() | Add an interval to a datetime expression | Y |
| TIMESTAMPDIFF() | Subtract an interval from a datetime expression | Y |
| TO_DAYS() | Return the date argument converted to days | Y |
| TO_SECONDS() | Return the date or datetime argument converted to seconds since Year 0 | Y |
| UNIX_TIMESTAMP() | Return a Unix timestamp | Y |
| UTC_DATE() | Return the current UTC date | Y |
| UTC_TIME() | Return the current UTC time | Y |
| UTC_TIMESTAMP() | Return the current UTC date and time | Y |
| WEEK() | Return the week number | Y |
| WEEKDAY() | Return the weekday index | Y |
| WEEKOFYEAR() | Return the calendar week of the date (1-53) | Y |
| YEAR() | Return the year | Y |
| YEARWEEK() | Return the year and week | Y |
| CURRENT_DATE | Synonyms for CURDATE() | N |
| CURRENT_TIME | Synonyms for CURTIME() | N |
| CURRENT_TIMESTAMP | Synonyms for NOW() | N |
| LOCALTIME | Synonym for NOW() | N |
| LOCALTIMESTAMP() | Synonym for NOW() | N |
3.10.6 Cast Functions and Operators
| Name | Description | Support |
|---|---|---|
| BINARY | Cast a string to a binary string | N |
| CAST() | Cast a value as a certain type | Y |
| CONVERT() | Cast a value as a certain type | Y |
3.10.6.1 CAST 不支持以下类型或语法
BINARY
CHAR[(N)] [charset_info] 包含charset_info时
JSON
SIGNED [INTEGER] 包含INTEGER时(druid解析问题)
UNSIGNED [INTEGER] 包含INTEGER时 (druid解析问题)
3.10.6.2 CONVERT 不支持以下类型或语法
BINARY
CHAR[(N)] [charset_info] 包含charset_info时
JSON
SIGNED [INTEGER] 包含INTEGER时(druid解析问题)
UNSIGNED [INTEGER] 包含INTEGER时 (druid解析问题)
3.10.7 Bit Functions and Operators
| Name | Description | Support |
|---|---|---|
| BIT_COUNT() | Return the number of bits that are set | Y |
| & | Bitwise AND | Y |
| ~ | Bitwise inversion | Y |
| | | Bitwise OR | Y |
| ^ | Bitwise XOR | Y |
| << | Left shift | Y |
| >> | Right shift | Y |
3.10.8 Aggregate (GROUP BY) Functions
| Name | Description | Support |
|---|---|---|
| AVG() | Return the average value of the argument | Y |
| BIT_AND() | Return bitwise AND | Y |
| BIT_OR() | Return bitwise OR | Y |
| BIT_XOR() | Return bitwise XOR | Y |
| COUNT() | Return a count of the number of rows returned | Y |
| COUNT(DISTINCT) | Return the count of a number of different values | Y |
| GROUP_CONCAT() | Return a concatenated string | Y |
| JSON_ARRAYAGG() | Return result set as a single JSON array | N |
| JSON_OBJECTAGG() | Return result set as a single JSON object | N |
| MAX() | Return the maximum value | Y |
| MIN() | Return the minimum value | Y |
| STD() | Return the population standard deviation | Y |
| STDDEV() | Return the population standard deviation | Y |
| STDDEV_POP() | Return the population standard deviation | Y |
| STDDEV_SAMP() | Return the sample standard deviation | Y |
| SUM() | Return the sum | Y |
| VAR_POP() | Return the population standard variance | Y |
| VAR_SAMP() | Return the sample variance | Y |
| VARIANCE() | Return the population standard variance | Y |
备注:
STD 和VARIANCE相关函数，因为分布式计算的局限性，精度会有一些问题，见 STD()/STDDEV()/STDDEV_POP()/STDDEV_SAMP()/VAR_POP()/VAR_SAMP()/VARIANCE() result precision is not correct
在AVG\SUM等相关计算函数的时候，由于dble的数据来源于MySQL的查询返回，
当MySQL默认返回的数据精度不够时，可能出现最终查询的结果和MySQL的计算结果有少许差异的情况,见
for data type float, dble and mysql may get different results
3.10.9 JSON Functions
| Name | Description | Support |
|---|---|---|
| JSON_EXTRACT() | selected from the parts of the json document matched by the path arguments | Y |
| JSON_UNQUOTE() | Unquotes JSON value and returns the result as a utf8mb4 string. | Y |
3.10.10 Full-Text Search Functions
not supported
3.10.11 XML Functions
not supported
3.10.12 Encryption and Compression Functions
not supported
3.10.13 Information Functions
not supported
3.10.14 Spatial Analysis Functions
not supported
3.10.15 Functions Used with Global Transaction IDs
not supported
3.10.16 MySQL Enterprise Encryption Functions
not supported
3.10.17 Miscellaneous Functions
not supported