YashanDB Docs
search-icon Ctrl K
官网首页 立即体验 文档中心 search-icon Language

#算术运算符

YashanDB提供如下算术运算符：

运算符 操作数 含义 NULL参与运算
+ 一元/二元 一元表示正数，二元表示加法 结果为NULL
- 一元/二元 一元表示负数，二元表示减法 结果为NULL
* 二元 乘法 结果为NULL
/ 二元 普通除法 结果为NULL
% 二元 整数除法，返回余数 结果为NULL

# 除法

YashanDB中，可实现除法运算的方式有：

  • 算术运算符：/、%
  • 内置函数：MOD、DIV

%

取模运算，与MOD函数同义。

格式为：n2 % n1

含义为：将n2 整除n1后剩余的数值作为取模的结果，即余数，余数的正负符号与n2一致。

若n1为0，不报错，而是直接将n2的值作为余数结果返回。

/

普通除法运算。

/与DIV的关系：

  • 对于小数（FLOAT/DOUBLE/NUMBER），/与DIV算法一致。
  • 对于整数，/作普通除法运算，DIV作整除运算并返回商数。

# 运算优先级

从高到低的运算优先级为：+（取正）、-（取负）>*、/、%>+、-。同一优先级运算符从左向右执行。

可以使用双括号（）来调整想达到的运算优先级。

# 数据类型

如下数据类型可能会参与到算术运算中：

  • 数值型（除BIT外）

  • 字符型

  • 日期时间型

# 运算规则

在进行二元算术运算时，YashanDB将通过隐式数据转换，将参与运算的数据类型统一到某个数据类型，统一原则为：

  • 数值型加、减、乘法最小提升规则：按TINYINT->SMALLINT->INT->BIGINT->NUMBER->FLOAT->DOUBLE顺序向后统一。
  • 数值型除法最小提升规则：按TINYINT/SMALLINT/INT/BIGINT->NUMBER->FLOAT->DOUBLE顺序向后统一。
  • 字符型数据与数值型数据进行运算时，将会向数值型统一。
  • 数值型、字符型数据与日期时间型数据进行运算时，将会向日期时间型统一。
数据类型 运算符 TINYINT TINYINT UNSIGNED SMALLINT SMALLINT UNSIGNED INT INT UNSIGNED
TINYINT +, -, * SMALLINT INT UNSIGNED INT INT UNSIGNED BIGINT BIGINT UNSIGNED
/, DIV DECIMAL DECIMAL DECIMAL DECIMAL DECIMAL DECIMAL
%, MOD SMALLINT INT INT INT BIGINT BIGINT
TINYINT UNSIGNED +, -, * INT UNSIGNED INT UNSIGNED INT UNSIGNED INT UNSIGNED BIGINT UNSIGNED BIGINT UNSIGNED
/, DIV DECIMAL DECIMAL DECIMAL DECIMAL DECIMAL DECIMAL
%, MOD INT UNSIGNED INT UNSIGNED INT UNSIGNED INT UNSIGNED BIGINT UNSIGNED BIGINT UNSIGNED
SMALLINT +, -, * INT INT UNSIGNED INT INT UNSIGNED BIGINT BIGINT UNSIGNED
/, DIV DECIMAL DECIMAL DECIMAL DECIMAL DECIMAL DECIMAL
%, MOD INT INT INT INT BIGINT BIGINT
SMALLINT UNSIGNED +, -, * INT UNSIGNED INT UNSIGNED INT UNSIGNED INT UNSIGNED BIGINT UNSIGNED BIGINT UNSIGNED
/, DIV DECIMAL DECIMAL DECIMAL DECIMAL DECIMAL DECIMAL
%, MOD INT UNSIGNED INT UNSIGNED INT UNSIGNED INT UNSIGNED BIGINT UNSIGNED BIGINT UNSIGNED
INT +, -, * BIGINT BIGINT UNSIGNED BIGINT BIGINT UNSIGNED BIGINT BIGINT UNSIGNED
/, DIV DECIMAL DECIMAL DECIMAL DECIMAL DECIMAL DECIMAL
%, MOD BIGINT BIGINT BIGINT BIGINT BIGINT BIGINT
INT UNSIGNED +, -, * BIGINT UNSIGNED BIGINT UNSIGNED BIGINT UNSIGNED BIGINT UNSIGNED BIGINT UNSIGNED BIGINT UNSIGNED
/, DIV DECIMAL DECIMAL DECIMAL DECIMAL DECIMAL DECIMAL
%, MOD BIGINT UNSIGNED BIGINT UNSIGNED BIGINT UNSIGNED BIGINT UNSIGNED BIGINT UNSIGNED BIGINT UNSIGNED
BIGINT +, -, * DECIMAL BIGINT UNSIGNED DECIMAL BIGINT UNSIGNED DECIMAL BIGINT UNSIGNED
/, DIV DECIMAL DECIMAL DECIMAL DECIMAL DECIMAL DECIMAL
%, MOD DECIMAL BIGINT DECIMAL BIGINT DECIMAL BIGINT
BIGINT UNSIGNED +, -, * BIGINT UNSIGNED BIGINT UNSIGNED BIGINT UNSIGNED BIGINT UNSIGNED BIGINT UNSIGNED BIGINT UNSIGNED
/, DIV DECIMAL DECIMAL DECIMAL DECIMAL DECIMAL DECIMAL
%, MOD BIGINT UNSIGNED BIGINT UNSIGNED BIGINT UNSIGNED BIGINT UNSIGNED BIGINT UNSIGNED BIGINT UNSIGNED
DECIMAL +, -, * DECIMAL DECIMAL DECIMAL DECIMAL DECIMAL DECIMAL
/, DIV DECIMAL DECIMAL DECIMAL DECIMAL DECIMAL DECIMAL
%, MOD DECIMAL DECIMAL DECIMAL DECIMAL DECIMAL DECIMAL
FLOAT +, -, * FLOAT FLOAT FLOAT FLOAT FLOAT FLOAT
/, DIV FLOAT FLOAT FLOAT FLOAT FLOAT FLOAT
%, MOD FLOAT FLOAT FLOAT FLOAT FLOAT FLOAT
DOUBLE +, -, * DOUBLE DOUBLE DOUBLE DOUBLE DOUBLE DOUBLE
/, DIV DOUBLE DOUBLE DOUBLE DOUBLE DOUBLE DOUBLE
%, MOD DOUBLE DOUBLE DOUBLE DOUBLE DOUBLE DOUBLE
CHAR/VARCHAR +, -, * DECIMAL DECIMAL DECIMAL DECIMAL DECIMAL DECIMAL
/, DIV DECIMAL DECIMAL DECIMAL DECIMAL DECIMAL DECIMAL
%, MOD DECIMAL DECIMAL DECIMAL DECIMAL DECIMAL DECIMAL
TIMESTAMP +, -, * DECIMAL BIGINT UNSIGNED DECIMAL BIGINT UNSIGNED DECIMAL BIGINT UNSIGNED
/, DIV DECIMAL DECIMAL DECIMAL DECIMAL DECIMAL DECIMAL
%, MOD - DECIMAL - DECIMAL - DECIMAL
TIME +, -, * DECIMAL BIGINT UNSIGNED DECIMAL BIGINT UNSIGNED DECIMAL BIGINT UNSIGNED
/, DIV DECIMAL DECIMAL DECIMAL DECIMAL DECIMAL DECIMAL
%, MOD DECIMAL BIGINT DECIMAL BIGINT DECIMAL BIGINT
DATE +, -, * BIGINT INT UNSIGNED BIGINT INT UNSIGNED BIGINT BIGINT UNSIGNED
/, DIV DECIMAL DECIMAL DECIMAL DECIMAL DECIMAL DECIMAL
%, MOD BIGINT BIGINT BIGINT BIGINT BIGINT BIGINT
BINARY +, -, * DOUBLE - DOUBLE - DOUBLE -
/, DIV DOUBLE - DOUBLE - DOUBLE -
%, MOD DOUBLE - DOUBLE - DOUBLE -
CLOB +, -, * - - - - - -
/, DIV - - - - - -
%, MOD - - - - - -
数据类型 运算符 BIGINT BIGINT UNSIGNED DECIMAL FLOAT DOUBLE CHAR/VARCHAR
TINYINT +, -, * DECIMAL BIGINT UNSIGNED DECIMAL FLOAT DOUBLE DECIMAL
/, DIV DECIMAL DECIMAL DECIMAL FLOAT DOUBLE DECIMAL
%, MOD DECIMAL BIGINT DECIMAL FLOAT DOUBLE DECIMAL
TINYINT UNSIGNED +, -, * BIGINT UNSIGNED BIGINT UNSIGNED DECIMAL FLOAT DOUBLE DECIMAL
/, DIV DECIMAL DECIMAL DECIMAL FLOAT DOUBLE DECIMAL
%, MOD BIGINT UNSIGNED BIGINT UNSIGNED DECIMAL FLOAT DOUBLE DECIMAL
SMALLINT +, -, * DECIMAL BIGINT UNSIGNED DECIMAL FLOAT DOUBLE DECIMAL
/, DIV DECIMAL DECIMAL DECIMAL FLOAT DOUBLE DECIMAL
%, MOD DECIMAL BIGINT DECIMAL FLOAT DOUBLE DECIMAL
SMALLINT UNSIGNED +, -, * BIGINT UNSIGNED BIGINT UNSIGNED DECIMAL FLOAT DOUBLE DECIMAL
/, DIV DECIMAL DECIMAL DECIMAL FLOAT DOUBLE DECIMAL
%, MOD BIGINT UNSIGNED BIGINT UNSIGNED DECIMAL FLOAT DOUBLE DECIMAL
INT +, -, * DECIMAL BIGINT UNSIGNED DECIMAL FLOAT DOUBLE DECIMAL
/, DIV DECIMAL DECIMAL DECIMAL FLOAT DOUBLE DECIMAL
%, MOD DECIMAL BIGINT DECIMAL FLOAT DOUBLE DECIMAL
INT UNSIGNED +, -, * BIGINT UNSIGNED BIGINT UNSIGNED DECIMAL FLOAT DOUBLE DECIMAL
/, DIV DECIMAL DECIMAL DECIMAL FLOAT DOUBLE DECIMAL
%, MOD BIGINT UNSIGNED BIGINT UNSIGNED DECIMAL FLOAT DOUBLE DECIMAL
BIGINT +, -, * DECIMAL BIGINT UNSIGNED DECIMAL FLOAT DOUBLE DECIMAL
/, DIV DECIMAL DECIMAL DECIMAL FLOAT DOUBLE DECIMAL
%, MOD DECIMAL BIGINT DECIMAL FLOAT DOUBLE DECIMAL
BIGINT UNSIGNED +, -, * BIGINT UNSIGNED BIGINT UNSIGNED DECIMAL FLOAT DOUBLE DECIMAL
/, DIV DECIMAL DECIMAL DECIMAL FLOAT DOUBLE DECIMAL
%, MOD BIGINT UNSIGNED BIGINT UNSIGNED DECIMAL FLOAT DOUBLE DECIMAL
DECIMAL +, -, * DECIMAL DECIMAL DECIMAL FLOAT DOUBLE DECIMAL
/, DIV DECIMAL DECIMAL DECIMAL FLOAT DOUBLE DECIMAL
%, MOD DECIMAL DECIMAL DECIMAL FLOAT DOUBLE DECIMAL
FLOAT +, -, * FLOAT FLOAT FLOAT FLOAT DOUBLE FLOAT
/, DIV FLOAT FLOAT FLOAT FLOAT DOUBLE FLOAT
%, MOD FLOAT FLOAT FLOAT FLOAT DOUBLE FLOAT
DOUBLE +, -, * DOUBLE DOUBLE DOUBLE DOUBLE DOUBLE DOUBLE
/, DIV DOUBLE DOUBLE DOUBLE DOUBLE DOUBLE DOUBLE
%, MOD DOUBLE DOUBLE DOUBLE DOUBLE DOUBLE DOUBLE
CHAR/VARCHAR +, -, * DECIMAL DECIMAL DECIMAL FLOAT DOUBLE DECIMAL
/, DIV DECIMAL DECIMAL DECIMAL FLOAT DOUBLE DECIMAL
%, MOD DECIMAL DECIMAL DECIMAL FLOAT DOUBLE DECIMAL
TIMESTAMP +, -, * DECIMAL BIGINT UNSIGNED DECIMAL DOUBLE DOUBLE DOUBLE
/, DIV DECIMAL DECIMAL DECIMAL DOUBLE DOUBLE DOUBLE
%, MOD - DECIMAL DECIMAL DOUBLE DOUBLE DOUBLE
TIME +, -, * DECIMAL BIGINT UNSIGNED DECIMAL DOUBLE DOUBLE DOUBLE
/, DIV DECIMAL DECIMAL DECIMAL DOUBLE DOUBLE DOUBLE
%, MOD DECIMAL BIGINT DECIMAL DOUBLE DOUBLE DOUBLE
DATE +, -, * BIGINT BIGINT UNSIGNED DECIMAL DOUBLE DOUBLE DOUBLE
/, DIV DECIMAL DECIMAL DECIMAL DOUBLE DOUBLE DOUBLE
%, MOD BIGINT BIGINT DECIMAL DOUBLE DOUBLE DOUBLE
BINARY +, -, * DOUBLE - DOUBLE DOUBLE DOUBLE -
/, DIV DOUBLE - DOUBLE DOUBLE DOUBLE -
%, MOD DOUBLE - DOUBLE DOUBLE DOUBLE -
CLOB +, -, * - - - - - -
/, DIV - - - - - -
%, MOD - - - - - -
数据类型 运算符 TIMESTAMP TIME DATE BINARY CLOB
TINYINT +, -, * DECIMAL DECIMAL BIGINT DOUBLE -
/, DIV DECIMAL DECIMAL DECIMAL DOUBLE -
%, MOD DECIMAL DECIMAL BIGINT DOUBLE -
TINYINT UNSIGNED +, -, * BIGINT UNSIGNED BIGINT UNSIGNED INT UNSIGNED - -
/, DIV DECIMAL DECIMAL DECIMAL - -
%, MOD BIGINT UNSIGNED BIGINT UNSIGNED BIGINT UNSIGNED - -
SMALLINT +, -, * DECIMAL DECIMAL BIGINT DOUBLE -
/, DIV DECIMAL DECIMAL DECIMAL DOUBLE -
%, MOD DECIMAL DECIMAL BIGINT DOUBLE -
SMALLINT UNSIGNED +, -, * BIGINT UNSIGNED BIGINT UNSIGNED INT UNSIGNED - -
/, DIV DECIMAL DECIMAL DECIMAL - -
%, MOD BIGINT UNSIGNED BIGINT UNSIGNED BIGINT UNSIGNED - -
INT +, -, * DECIMAL DECIMAL BIGINT DOUBLE -
/, DIV DECIMAL DECIMAL DECIMAL DOUBLE -
%, MOD DECIMAL DECIMAL BIGINT DOUBLE -
INT UNSIGNED +, -, * BIGINT UNSIGNED BIGINT UNSIGNED BIGINT UNSIGNED - -
/, DIV DECIMAL DECIMAL DECIMAL - -
%, MOD BIGINT UNSIGNED BIGINT UNSIGNED BIGINT UNSIGNED - -
BIGINT +, -, * DECIMAL DECIMAL BIGINT DOUBLE -
/, DIV DECIMAL DECIMAL DECIMAL DOUBLE -
%, MOD DECIMAL DECIMAL BIGINT DOUBLE -
BIGINT UNSIGNED +, -, * BIGINT UNSIGNED BIGINT UNSIGNED BIGINT UNSIGNED - -
/, DIV DECIMAL DECIMAL DECIMAL - -
%, MOD BIGINT UNSIGNED BIGINT UNSIGNED BIGINT UNSIGNED - -
DECIMAL +, -, * DECIMAL DECIMAL DOUBLE DOUBLE -
/, DIV DECIMAL DECIMAL DECIMAL DOUBLE -
%, MOD DECIMAL DECIMAL DECIMAL DOUBLE -
FLOAT +, -, * DOUBLE DOUBLE DOUBLE DOUBLE -
/, DIV DOUBLE DOUBLE DOUBLE DOUBLE -
%, MOD DOUBLE DOUBLE DOUBLE DOUBLE -
DOUBLE +, -, * DOUBLE DOUBLE DOUBLE DOUBLE -
/, DIV DOUBLE DOUBLE DOUBLE DOUBLE -
%, MOD DOUBLE DOUBLE DOUBLE DOUBLE -
CHAR/VARCHAR +, -, * DOUBLE DOUBLE DOUBLE - -
/, DIV DOUBLE DOUBLE DOUBLE - -
%, MOD DOUBLE DOUBLE DOUBLE - -
TIMESTAMP +, -, * DECIMAL DECIMAL DECIMAL - -
/, DIV DECIMAL DECIMAL DECIMAL - -
%, MOD DECIMAL DECIMAL DECIMAL - -
TIME +, -, * DECIMAL DECIMAL DECIMAL - -
/, DIV DECIMAL DECIMAL DECIMAL - -
%, MOD DECIMAL DECIMAL DECIMAL - -
DATE +, -, * DECIMAL DECIMAL BIGINT - DOUBLE
/, DIV DECIMAL DECIMAL DECIMAL - DOUBLE
%, MOD DECIMAL DECIMAL BIGINT - DOUBLE
BINARY +, - DOUBLE - DATE DOUBLE DOUBLE
* - - - DOUBLE DOUBLE
/, DIV - - - DOUBLE DOUBLE
%, MOD DOUBLE - - DOUBLE DOUBLE
CLOB +, -, * - - DOUBLE DOUBLE -
/, DIV - - DOUBLE DOUBLE -
%, MOD - - DOUBLE DOUBLE -

Note:

一般情况下，0不可作为除数，但当0以DOUBLE、FLOAT数据类型作为除数时，将以原生yashan模式计算规则进行计算，即如果被除数是正数则计算结果为Inf，如果被除数是负数则计算结果为-Inf。

示例

--字符型与数值型运算时，将字符型转换为NUMBER类型后参与计算
SELECT '44'/2 char_cal,
TYPEOF('44'/2) type_trans
FROM dual;
   char_cal type_trans
----------- ----------
         22 number
   
CREATE TABLE date_dd(a DATE, b DATE);
INSERT INTO date_dd VALUES('2008-12-31','2018-12-31');
COMMIT;
     
--运算符 '-'  返回时间间隔
SELECT b-a FROM date_dd;

                  b-a
---------------------
               100000
     
--运算符 '-'  返回时间
SELECT b-1 FROM date_dd;

                  b-1
---------------------
             20181230

SELECT SYSDATE+2 FROM DUAL;

            sysdate+2
---------------------
       20251015141744         
    
-- DATE和TIEMSTAMP与数值类型做加减运算，数值表示天
CREATE TABLE date_dt(c1 DATE, c2 TIMESTAMP);
INSERT INTO date_dt VALUES ('2020-03-31', '2020-03-31 12:30:59.999999');
SELECT * FROM date_dt;

c1                                               c2
------------------------------------------------ ----------------------------------------------------------------
2020-03-31                                       2020-03-31 12:31:00.000000

COMMIT;
     
SELECT c1-2.5, TYPEOF(c1-2.5), c2-2.5, TYPEOF(c2-2.5) FROM date_dt;

     c1-2.5 typeof(c1-2.5)                                                        c2-2.5 typeof(c2-2.5)                 
----------- ---------------------------------------------------------------- ----------- ----------------------------------------------------------------
 20200328.5 number                                                            2.0200E+13 number                         
     
SELECT c1+'2.5', TYPEOF(c1+'2.5'), c2+'2.5', TYPEOF(c2+'2.5') FROM date_dt;

   c1+'2.5' typeof(c1+'2.5')                                                    c2+'2.5' typeof(c2+'2.5')               
----------- ---------------------------------------------------------------- ----------- ----------------------------------------------------------------
  2.02E+007 double                                                             2.02E+013 double                         
      

-- 输出结果为TIME类型的运算
CREATE TABLE date_t(c1 TIME);
INSERT INTO date_t VALUES ('23:59:59.999999');
SELECT * FROM date_t;

c1
--------------------
24:00:00.000000

COMMIT;
     
SELECT c1+INTERVAL '5' HOUR FROM date_t;

c1+INTERVAL '5' HOUR
--------------------
29:00:00.000000

SELECT C1+INTERVAL '01-01' YEAR TO MONTH FROM date_dt;

C1+INTERVAL '01-01' YEAR TO MONTH
------------------------------------------------
2021-04-30

-- 在MySQL客户端验证0以DOUBLE或FLOAT数据类型作为被除数时的运算

CREATE TABLE td(a DOUBLE, b FLOAT);

desc td;
+-------+--------+------+------+---------+-------+
| Field | Type   | Null | Key  | Default | Extra |
+-------+--------+------+------+---------+-------+
| A     | double | YES  |      | NULL    |       |
| B     | float  | YES  |      | NULL    |       |
+-------+--------+------+------+---------+-------+

SELECT * FROM td;
+------+------+
| A    | B    |
+------+------+
|    0 | NULL |
|    0 |    0 |
+------+------+

SELECT 1/a, -1/a, 1/b,-1/b FROM td;
+------+------+------+------+
| 1/a  | -1/a | 1/b  | -1/b |
+------+------+------+------+
|  Inf | -Inf | NULL | NULL |
|  Inf | -Inf |  Inf | -Inf |
+------+------+------+------+
pdf-btn 下载文档 copy-btn 复制链接
edit-icon
反馈
coperate-icon
coperate
合作
communication-icon
communicate
交流