CEIL

小数后面有位数就进1，否则就不进。

  1. mysql> SELECT CEIL(3.000000001);
  2. +-------------------+
  3. | CEIL(3.000000001) |
  4. +-------------------+
  5. |                 4 |
  6. +-------------------+
  7. -- 不进
  8. mysql> SELECT CEIL(3.000000000);
  9. +-------------------+
  10. | CEIL(3.000000000) |
  11. +-------------------+
  12. |                 3 |
  13. +-------------------+

DIV

整数除法

  1. -- 正常除法
  2. mysql> SELECT 10/3;
  3. +--------+
  4. | 10/3   |
  5. +--------+
  6. | 3.3333 |
  7. +--------+
  8. -- 整数除法
  9. mysql> SELECT 10 DIV 3;
  10. +----------+
  11. | 10 DIV 3 |
  12. +----------+
  13. |        3 |
  14. +----------+

FLOOR()

舍去法取整

  1. mysql> SELECT FLOOR(3.92311);
  2. +----------------+
  3. | FLOOR(3.92311) |
  4. +----------------+
  5. |              3 |
  6. +----------------+

MOD

取余数(取模)，和%取余数一样。

  1. -- % 取余数
  2. mysql> SELECT 10 % 3;
  3. +--------+
  4. | 10 % 3 |
  5. +--------+
  6. |      1 |
  7. +--------+
  8. -- MOD 取余数
  9. mysql> SELECT 10 MOD 3;
  10. +----------+
  11. | 10 MOD 3 |
  12. +----------+
  13. |        1 |
  14. +----------+

POWER()

幂运算，计算N的N次方

  1. mysql> SELECT POWER(2,3);
  2. +------------+
  3. | POWER(2,3) |
  4. +------------+
  5. |          8 |
  6. +------------+

ROUND()

四舍五入

  1. mysql> SELECT ROUND(3.1415926,3);
  2. +--------------------+
  3. | ROUND(3.1415926,3) |
  4. +--------------------+
  5. |              3.142 |
  6. +--------------------+

TRUNCATE()

数字截取，截取小数

  1. -- 截取正数
  2. mysql> SELECT TRUNCATE(120.123,2);
  3. +---------------------+
  4. | TRUNCATE(120.123,2) |
  5. +---------------------+
  6. |              120.12 |
  7. +---------------------+
  8. -- 截取负数
  9. mysql> SELECT TRUNCATE(120.123,-2);
  10. +----------------------+
  11. | TRUNCATE(120.123,-2) |
  12. +----------------------+
  13. |                  100 |
  14. +----------------------+