当前位置： 首页 > article >正文

MYSQL之随机数生成、保留小数位、获取年龄

article 2025/1/7 5:10:00

目录

    • 一、随机数
      • 1.1、rand函数
      • 1.2、随机整数
      • 1.3、随机小数
      • 1.4、更新随机值
    • 二、保留小数位
      • 2.1、round函数
      • 2.2、convert函数
      • 2.3、cast函数
      • 2.4、format函数
    • 三、获取年龄
      • 3.1、方法一
      • 3.2、方法二
      • 3.3、方法三（ 推荐 ）
      • 3.4、方法四
    • 四、时间段差异

一、随机数

1.1、rand函数

   RAND() 函数返回 0（包括）和 1（不包括）之间的随机数。

语法如下：

select rand(),rand(),rand();

结果如下：

mysql> select rand(),rand(),rand();
+-------------------+--------------------+--------------------+
| rand()            | rand()             | rand()             |
+-------------------+--------------------+--------------------+
| 0.778827545027088 | 0.4953759373122602 | 0.1403970822136822 |
+-------------------+--------------------+--------------------+
1 row in set (0.00 sec)

1.2、随机整数

  比如生成 [10,100] 的随机数，计算公式为：round(rand() * (m - n) + n)

语法如下：

SELECT round(rand() * (100 - 10) + 10);

结果如下：

mysql> SELECT 
    ->     round(rand() * (100 - 10) + 10) as '随机整数1',
    ->     round(rand() * (100 - 10) + 10) as '随机整数2',
    ->     round(rand() * (100 - 10) + 10) as '随机整数3';
+---------------+---------------+---------------+
| 随机整数1     | 随机整数2     | 随机整数3     |
+---------------+---------------+---------------+
|            45 |            19 |            88 |
+---------------+---------------+---------------+
1 row in set (0.01 sec)

  有些小伙伴可能会用到 floor 函数，在计算闭包区间时，比如生成 [50,200] 的随机数，计算公式为：floor(rand() * (m - n + 1) + n)，不要漏掉那个 +1 了，不然你取不到最大值。

语法如下：

SELECT floor(rand() * (200 - 50 + 1) + 50);

1.3、随机小数

  比如生成 [60,100] 范围内的随机小数，计算公式为：round(rand() * (m - n) + n, 2)，后面的数字 2 就是保留几位小数。

语法如下：

SELECT round(rand() * (100 - 60) + 60, 2);

结果如下：

mysql> SELECT 
    ->     round(rand() * (100 - 60) + 60, 2) as '随机数1',
    ->     round(rand() * (100 - 60) + 60, 2) as '随机数2',
    ->     round(rand() * (100 - 60) + 60, 2) as '随机数3';
+------------+------------+------------+
| 随机数1    | 随机数2     | 随机数3    |
+------------+------------+------------+
|      60.10 |      99.76 |      98.52 |
+------------+------------+------------+
1 row in set (0.00 sec)

1.4、更新随机值

  比如随机给优惠券金额生成 [5,10] 范围内的随机小数

UPDATE tb_inf_coupon set freeAmount=round(rand() * (10 - 5) + 5);

二、保留小数位

假设我们有们的表数据如下：

mysql> select total_amount,pay_amount,free_amount from tb_coupon;
+--------------+------------+-------------+
| total_amount | pay_amount | free_amount |
+--------------+------------+-------------+
|     10086.21 |       1000 |       86.21 |
|       520.98 |      50000 |       20.98 |
|        19.88 |       1900 |        0.88 |
+--------------+------------+-------------+
3 rows in set (0.00 sec)

  这里的三个金额

  • total_amount 为 double 型，单位是元
  • pay_amount 为 int 型，单位是分
  • free_amount 为 decimal 型，单位是元

  这里只是为了演示，实际工作中绝对不会这样的，精度不高的情况下都是按分存取，就使用 BigInt 类型，如果精度高的就使用 decimal 类型。

2.1、round函数

查询语句

SELECT 
    round(total_amount, 2) as 'round处理double型',
    round(pay_amount / 100.0, 2) as 'round处理int型',
    round(free_amount, 2) as 'round处理decimal型'
FROM
    tb_coupon;

查询结果

+----------------------+-------------------+-----------------------+
| round处理double型    | round处理int型     | round处理decimal型    |
+----------------------+-------------------+-----------------------+
|             10086.21 |             10.00 |                 86.21 |
|               520.98 |            500.00 |                 20.98 |
|                19.88 |             19.00 |                  0.88 |
+----------------------+-------------------+-----------------------+
3 rows in set (0.00 sec)

2.2、convert函数

查询语句

select 
	convert(total_amount, DECIMAL(10, 2)) as 'convert处理double型',
    convert(pay_amount/100.0, DECIMAL(10, 2)) as 'convert处理int型',
    convert(free_amount, DECIMAL(10, 2)) as 'convert处理decimal型'
from  
	tb_coupon;

查询结果

+------------------------+---------------------+-------------------------+
| convert处理double型    | convert处理int型     | convert处理decimal型    |
+------------------------+---------------------+-------------------------+
|               10086.21 |               10.00 |                   86.21 |
|                 520.98 |              500.00 |                   20.98 |
|                  19.88 |               19.00 |                    0.88 |
+------------------------+---------------------+-------------------------+
3 rows in set (0.00 sec)

2.3、cast函数

查询语句

select 
	cast(total_amount as DECIMAL(10,2)) as 'cast处理double型',
    cast(pay_amount/100.0 as DECIMAL(10,2)) as 'cast处理int型',
    cast(free_amount as DECIMAL(10,2)) as 'cast处理decimal型'
from  
	tb_coupon;

查询结果

+---------------------+------------------+----------------------+
| cast处理double型    | cast处理int型     | cast处理decimal型    |
+---------------------+------------------+----------------------+
|            10086.21 |            10.00 |                86.21 |
|              520.98 |           500.00 |                20.98 |
|               19.88 |            19.00 |                 0.88 |
+---------------------+------------------+----------------------+
3 rows in set (0.00 sec)

2.4、format函数

查询语句

select 
	format(total_amount, 2) as 'format处理double型',
    format(pay_amount/100.0, 2) as 'format处理int型',
    format(free_amount, 2) as 'format处理decimal型'
from  
	tb_coupon;

查询结果

+-----------------------+--------------------+------------------------+
| format处理double型    | format处理int型    | format处理decimal型    |
+-----------------------+--------------------+------------------------+
| 10,086.21             | 10.00              | 86.21                  |
| 520.98                | 500.00             | 20.98                  |
| 19.88                 | 19.00              | 0.88                   |
+-----------------------+--------------------+------------------------+
3 rows in set (0.00 sec)

  从上面的结果我们可以看到当位数超过3位时就会以 逗号 分隔，并且返回的结果是string类型的，所以我们可以优化下，使用 REPLACE 函数把逗号替换为空。

优化查询语句

select 
	REPLACE(format(total_amount, 2),',','') as '优化format处理double型',
	REPLACE(format(pay_amount/100.0, 2),',','') as '优化format处理int型',
	REPLACE(format(free_amount, 2),',','') as '优化format处理decimal型'
from  
	tb_coupon;

查询结果

+-----------------------------+--------------------------+------------------------------+
| 优化format处理double型       | 优化format处理int型      | 优化format处理decimal型      |
+-----------------------------+--------------------------+------------------------------+
| 10086.21                    | 10.00                    | 86.21                        |
| 520.98                      | 500.00                   | 20.98                        |
| 19.88                       | 19.00                    | 0.88                         |
+-----------------------------+--------------------------+------------------------------+
3 rows in set (0.00 sec)

三、获取年龄

  假设我们有们的表数据如下，先算出他们的年龄。

mysql> SELECT user_code,user_name,birthday FROM tb_student WHERE user_code BETWEEN 6070 AND 6072;
+-----------+-----------+------------+
| user_code | user_name | birthday   |
+-----------+-----------+------------+
|      6070 | 唐静珊    | 2010-05-13 |
|      6071 | 吴恬美    | 2009-02-17 |
|      6072 | 谢骊艳    | 2009-07-08 |
+-----------+-----------+------------+
3 rows in set (0.00 sec)

3.1、方法一

查询语句

SELECT 
    user_code,
    user_name,
    birthday,
    YEAR(FROM_DAYS(DATEDIFF(NOW(), birthday))) AS '方法一age'
FROM
    school.tb_student
WHERE
    user_code BETWEEN 6070 AND 6072;

语法解析

  • NOW() :得到当前日期和时间
  • DATEDIFF(NOW(), birthday) ：计算当前日期到出生日期的间隔天数 n
  • FROM_DAYS(n) ：计算从 0000 年 1 月 1 日开始 n 天后的日期，比如得到： 0012-10-24 、 0014-01-17 、 0013-08-29
  • YEAR() ：获取到年数，即年龄

查询结果

+-----------+-----------+------------+--------------+
| user_code | user_name | birthday   | 方法一age    |
+-----------+-----------+------------+--------------+
|      6070 | 唐静珊    | 2010-05-13 |           12 |
|      6071 | 吴恬美    | 2009-02-17 |           14 |
|      6072 | 谢骊艳    | 2009-07-08 |           13 |
+-----------+-----------+------------+--------------+
3 rows in set (0.00 sec)

3.2、方法二

查询语句

SELECT 
    user_code,
    user_name,
    birthday,
    DATE_FORMAT(FROM_DAYS(TO_DAYS(NOW()) - TO_DAYS(birthday)),'%Y') + 0 AS '方法二age'
FROM
    school.tb_student
WHERE
    user_code BETWEEN 6070 AND 6072;

语法解析

  • NOW() :得到当前日期和时间
  • TO_DAYS(NOW()) :把当前日期转成距离 0000 年 1 月 1 日的天数 m
  • TO_DAYS(birthday) :把出生日期转成距离 0000 年 1 月 1 日的天数 n
  • FROM_DAYS(m,n) :计算从 0000 年 1 月 1 日开始 m-n 天后的日期 diff ，比如得到： 0012-10-24 、 0014-01-17 、 0013-08-29
  • DATE_FORMAT(diff,‘%Y’) :格式化获取年份，比如得到： 0012 、 0014 、 0013
  • 加上 0 自动转为数字年龄

查询结果

+-----------+-----------+------------+--------------+
| user_code | user_name | birthday   | 方法二age    |
+-----------+-----------+------------+--------------+
|      6070 | 唐静珊    | 2010-05-13 |           12 |
|      6071 | 吴恬美    | 2009-02-17 |           14 |
|      6072 | 谢骊艳    | 2009-07-08 |           13 |
+-----------+-----------+------------+--------------+
3 rows in set (0.00 sec)

3.3、方法三（ 推荐 ）

查询语句

SELECT 
    user_code,
    user_name,
    birthday,
    TIMESTAMPDIFF(YEAR, birthday, CURDATE()) AS '方法三age'
FROM
    school.tb_student
WHERE
    user_code BETWEEN 6070 AND 6072;

语法解析

  • CURDATE() :得到当前日期
  • TIMESTAMPDIFF(YEAR, birthday, CURDATE()) :出生日期和当前日期的年份差值即为年龄

查询结果

+-----------+-----------+------------+--------------+
| user_code | user_name | birthday   | 方法三age    |
+-----------+-----------+------------+--------------+
|      6070 | 唐静珊    | 2010-05-13 |           12 |
|      6071 | 吴恬美    | 2009-02-17 |           14 |
|      6072 | 谢骊艳    | 2009-07-08 |           13 |
+-----------+-----------+------------+--------------+
3 rows in set (0.00 sec)

3.4、方法四

查询语句

SELECT 
    user_code,
    user_name,
    birthday,
    FLOOR(DATEDIFF(CURDATE(), birthday)/365.2422) as '方法四age'
FROM
    school.tb_student
WHERE
    user_code BETWEEN 6070 AND 6072;

语法解析

  • CURDATE() :得到当前日期
  • DATEDIFF(CURDATE(), birthday) :计算当前日期和出生日期的差值整数天 n
  • FLOOR(n/365.2422) :现代人测算得出一年是365.2422日，计算年数后向下取整得到年龄

查询结果

+-----------+-----------+------------+--------------+
| user_code | user_name | birthday   | 方法四age    |
+-----------+-----------+------------+--------------+
|      6070 | 唐静珊    | 2010-05-13 |           12 |
|      6071 | 吴恬美    | 2009-02-17 |           14 |
|      6072 | 谢骊艳    | 2009-07-08 |           13 |
+-----------+-----------+------------+--------------+
3 rows in set (0.00 sec)

四、时间段差异

  • timestampdiff(unit,datetime_expr1,datetime_expr2) 计算时间差，返回 datetime_expr2 − datetime_expr1 的时间差，unit的值可以为：year、month、day、hour、minute、second

查询语句

SELECT 
    payTime as '支付时间',
    noticeTime as '通知时间',
    timestampdiff(second,payTime,noticeTime) as '时间间隔'
FROM `tb_biz_pay_notice` 
WHERE payTime BETWEEN '2023-03-06 13:30:00' AND '2023-03-06 13:30:05';

查询结果

+---------------------+---------------------+--------------+
| 支付时间            | 通知时间            | 时间间隔     |
+---------------------+---------------------+--------------+
| 2023-03-06 13:30:00 | 2023-03-06 13:30:01 |            1 |
| 2023-03-06 13:30:00 | 2023-03-06 13:30:08 |            8 |
| 2023-03-06 13:30:01 | 2023-03-06 13:30:02 |            1 |
| 2023-03-06 13:30:01 | 2023-03-06 13:30:01 |            0 |
| 2023-03-06 13:30:03 | 2023-03-06 13:30:04 |            1 |
| 2023-03-06 13:30:03 | 2023-03-06 13:30:05 |            2 |
+---------------------+---------------------+--------------+
6 rows in set (0.00 sec)

  一般适合定位两个时间的问题，比如上述有一笔交易支付时间和通知时间差了8秒，就可以去查查是什么原因。或者是用于统计下这些慢通知的比例等。


http://www.kler.cn/a/1683.html

相关文章：

  • 前端（API）学习笔记（CLASS 4）：进阶
  • 字玩FontPlayer开发笔记6 Tauri2设置菜单
  • 3125: 【入门】求1/1+1/2+2/3+3/5+5/8+8/13+13/21……的前n项的和
  • 【ArcGISPro/GeoScenePro】检查多光谱影像的属性并优化其外观
  • Go小技巧易错点100例（二十一）
  • Android IO 问题：java.io.IOException Operation not permitted
  • C++ 手写一个WebServer
  • 你需要知道的 7 个 Vue3 技巧
  • Javascript如何截取含有表情的字符串
  • 基于STM32的ADC采样及各式滤波实现（HAL库，含VOFA+教程）
  • int *p = a、p = a、*p = a
  • 最低仅需一张入门级显卡便可运行扩散模型AI作画——Stable Diffusion Webui试玩体验 文本生成图像扩散模型本机推理
  • 4.网络爬虫—Post请求(实战演示)
  • 嵌入式硬件电路设计的基本技巧
  • springboot车辆充电桩
  • 从零到Offer -- List的那些事
  • 蓝桥杯倒计时 | 倒计时19天
  • springboot+vue驾校管理系统 idea科目一四预约考试,练车
  • 原子操作的简单介绍
  • 自动驾驶自主避障概况
  • 由文心一言发布会引发的思考，聊聊我未来的学习规划
  • jvm-题库
  • 图解如何一步步连接远程服务器——基于VScode
  • 在使用fastjson中遇到的问题
  • Linux网络概述
  • 高通开发系列 - Sensors Bring Up