MySQL基础教程11——函数—数值函数
ceil(向上取整)
select ceil(数字);
mysql> select ceil(1.3);
+-----------+
| ceil(1.3) |
+-----------+
| 2 |
+-----------+
1 row in set (0.00 sec)
floor(向下取整)
select floor(数字);
mysql> select floor(1.9);
+------------+
| floor(1.9) |
+------------+
| 1 |
+------------+
1 row in set (0.00 sec)
mod(取余)
select mod(基数,模数);
mysql> select mod(8,3);
+----------+
| mod(8,3) |
+----------+
| 2 |
+----------+
1 row in set (0.00 sec)
//8%3=2
rand(随机数)
select rand();
在0~1之间随机数
mysql> select rand();
+---------------------+
| rand() |
+---------------------+
| 0.06758731237387966 |
+---------------------+
1 row in set (0.00 sec)
mysql> select rand();
+--------------------+
| rand() |
+--------------------+
| 0.6231616238347829 |
+--------------------+
1 row in set (0.00 sec)
round(保留指定个数小数并四舍五入)
select round(小数,保留位数)
mysql> select round(2.3568,2);
+-----------------+
| round(2.3568,2) |
+-----------------+
| 2.36 |
+-----------------+
1 row in set (0.00 sec)
案例
随机生成一个6位数的验证码。
先用rand随机生成小数在乘1000000取小数点后的前六位数。
mysql> select rand()*1000000;
+-------------------+
| rand()*1000000 |
+-------------------+
| 913046.2127859204 |
+-------------------+
1 row in set (0.01 sec)
然后再用round对小数点后进行去除。
mysql> select round(rand()*1000000,0);
+-------------------------+
| round(rand()*1000000,0) |
+-------------------------+
| 695746 |
+-------------------------+
1 row in set (0.01 sec)
这里我们要考虑到随机生成时有可能会生成0.03589715之类的数字导致在取前六位数的时候有效数字不足六位，因此我们用lpad或者rpan补全位数
mysql> select lpad(round(rand()*1000000,0),6,'0');
+-------------------------------------+
| lpad(round(rand()*1000000,0),6,'0') |
+-------------------------------------+
| 002215 |
+-------------------------------------+
1 row in set (0.00 sec)
