3.10 函数与操作符支持列表(alpha版本)

3.10.0 注意:

  1. 如果能保证SQL会将函数整体下发给某个后端结点,函数支持度没有意义,支持度将托管于MySQL.
  2. 在2.18.09.0 版本之前(含),涉及到中文字符集的字符串相关函数有bug。
  3. 除了聚合函数经过充分测试之外,其余函数只进行了简单的测试,并没有全覆盖测试,请使用前充分测试 。
  4. 未在列表中的函数,一概不支持。

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.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 Y
%, 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 Full-Text Search Functions

not supported

3.10.10 XML Functions

not supported

3.10.11 Encryption and Compression Functions

not supported

3.10.12 Information Functions

not supported

3.10.13 Spatial Analysis Functions

not supported

3.10.14 JSON 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

参考资料:MySQL5.7 函数文档

results matching ""

    No results matching ""