Operators&Funtions 使用说明

说明

KVSQL是一款SQL合约执行引擎，可以运行符合范围的SQL语句，目标支持了部分常见运算符以及函数功能。以及在区块链的特殊场景下，并非所有功能都能够正常使用，部分函数被KVSQL限制以保障SQL智能合约执行的沙箱环境的安全。

限制功能主要包括如下：

Name	Description
&	Bitwise AND
>	Greater than operator
>>	Right shift
>=	Greater than or equal operator
<	Less than operator
<>,!=	Not equal operator
<<	Left shift
<=	Less than or equal operator
<=>	NULL-safe equal to operator
%,MOD	Modulo operator
*	Multiplication operator
	Addition operator
	Minus operator
	Change the sign of the argument
/	Division operator
=	Assign a value (as part of aSETstatement, or as part of theSETclause in aUPDATEstatement)
=	Equal operator
^	Bitwise XOR
AND, &&	Logical AND
BETWEEN … AND …	Whether a value is within a range of values
BINARY	Cast a string to a binary string
CASE	Case operator
DIV	Integer division
IN()	Whether a value is within a set of values
IS	Test a value against a boolean
IS NOT	Test a value against a boolean
IS NOT NULL	NOT NULL value test
IS NULL	NULL value test
LIKE	Simple pattern matching
NOT,!	Negates value
NOT BETWEEN … AND …	Whether a value is not within a range of values
NOT IN()	Whether a value is not within a set of values
NOT LIKE	Negation of simple pattern matching
NOT REGEXP	Negation of REGEXP
OR,
REGEXP	Whether string matches regular expression
RLIKE	Whether string matches regular expression
XOR	Logical XOR

~	Bitwise inversion

CASE value WHEN compare_value THEN result [WHEN compare_value THEN result …] [ELSE result] END

需要注意的是，以上四种语句中的结果表达式的类型不同时，会将所有的表达式返回类型统一。这一点与mySQL存在差异。

Name	Description
%,MOD	Modulo operator
*	Multiplication operator
	Addition operator
	Minus operator
	Change the sign of the argument
/	Division operator
ABS()	Return the absolute value
CEIL()	Return the smallest integer value not less than the argument
CEILING()	Return the smallest integer value not less than the argument
CONV()	Convert numbers between different number bases
CRC32()	Compute a cyclic redundancy check value
DEGREES()	Convert radians to degrees
DIV	Integer division
FLOOR()	Return the largest integer value not greater than the argument
MOD()	Return the remainder
PI()	Return the value of pi
RADIANS()	Return argument converted to radians
ROUND()	Round the argument
SIGN()	Return the sign of the argument
SQRT()	Return the square root of the argument
TRUNCATE()	Truncate to specified number of decimal places

Name	Description
CURDATE()	Return the current date
CURRENT_DATE(),CURRENT_DATE	Synonyms for CURDATE()
CURRENT_TIME(),CURRENT_TIME	Synonyms for CURTIME()
CURRE NT_TIMESTAMP(),CURRENT_TIMESTAMP	Synonyms for NOW()
CURTIME()	Return the current time
UTC_DATE()	Return the current UTC date
UTC_TIME()	Return the current UTC time
UTC_TIMESTAMP()	Return the current UTC date and time

Name	Description
CONCAT()	Return concatenated string
FORMAT()	Return a number formatted to specified number of decimal places
LIKE	Simple pattern matching
LOWER()	Return the argument in lowercase
NOT LIKE	Negation of simple pattern matching
NOT REGEXP	Negation of REGEXP
REGEXP	Whether string matches regular expression
RLIKE	Whether string matches regular expression
TRIM()	Remove leading and trailing spaces
UPPER()	Convert to uppercase

不支持json类型的转换与被转换；不支持指定字符集

Name	Description
BINARY	Cast a string to a binary string
CAST()	Cast a value as a certain type
CONVERT()	Cast a value as a certain type

Name	Description
&	Bitwise AND
>>	Right shift
<<	Left shift
^	Bitwise XOR
BIT_COUNT()	Return the number of bits that are set

~	Bitwise inversion

Name	Description
AVG()	Return the average value of the argument
BIT_AND()	Return bitwise AND
BIT_OR()	Return bitwise OR
BIT_XOR()	Return bitwise XOR
COUNT()	Return a count of the number of rows returned
COUNT(DISTINCT)	Return the count of a number of different values
GROUP_CONCAT()	Return a concatenated string
MAX()	Return the maximum value
MIN()	Return the minimum value
STDDEV_POP()	Return the population standard deviation
STDDEV_SAMP()	Return the sample standard deviation
SUM()	Return the sum
VAR_POP()	Return the population standard variance
VAR_SAMP()	Return the sample variance