MySQL基础教程11——函数—数值函数
MySQL基础教程11——函数—数值函数
ceil(向上取整)
select ceil(数字);
mysql> select ceil(1.3); +-----------+ | ceil(1.3) | +-----------+ | 2 | +-----------+ 1 row in set (0.00 sec)
loor(向下取整)
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)
