MySQL 随机生成时间

作者: marioplus12 | 来源:发表于2018-11-04 22:27 被阅读0次

MySQL 随机生成时间
Jmeter——Dummy Sampler
2018-08-09Jmeter——Dummy Sampler
JavaScript一些技巧
随机生成密码（如MYSQL）
未明学院：Faker库批量造数据，解决Python数据来源问题（
Centos 下初次登录mysql密码错误
python必知必会6
Python模块---random随机结果获取
无处不在的随机数

目标

随机生成2018-11-01 00:00:00 ~ 2018-11-30 23:59:59中的一个时间。

随机一个自动补齐位数的数字字符串

既然要随机，无论使用何种方式，最后落地点一定是这个需求，这里介绍三个函数来达到目的。

-- 随机生成一个数，范围：[0,1)
SELECT RAND();
-- 随机生成一个数，范围：[0,10)
SELECT RAND() * 10;

-- 向下取证，参数可为数字和字符串
SELECT FLOOR(12.9);
SELECT FLOOR('12.9');

-- 字符串补齐，接受3个数字或者字符串参数，第一个为目标，第二个为长度，第三个为用什么补齐
SELECT LPAD('12', '4', '0');
SELECT LPAD(12, 4, 0);
-- 长度超过的，会从末尾开始截取
SELECT LPAD(129999, 4, 0);

SELECT RAND(), RAND() * 10, FLOOR(1.9), LPAD(12, 4, 0);

执行最后一条看看效果。

+--------------------+--------------------+------------+----------------+
| RAND()             | RAND() * 10        | FLOOR(1.9) | LPAD(12, 4, 0) |
+--------------------+--------------------+------------+----------------+
| 0.8352750948027475 | 0.6588937534567841 |          1 | 0012           |
+--------------------+--------------------+------------+----------------+
1 row in set (0.00 sec)

1 直接拼接字符串

拼接字符串函数：CONCAT(str1, str2 ...)

SELECT CONCAT('2018','-','11','-','01',' ','11',':','22',':','33');

+-------------------------------------------------------------+
| concat('2018','-','11','-','01',' ','11',':','22',':','33') |
+-------------------------------------------------------------+
| 2018-11-01 11:22:33                                         |
+-------------------------------------------------------------+
1 row in set (0.00 sec)

顺便提一句 + 在mysql中不能连接字符串

SELECT ('2018' + '-' + '11' + '-' + '01' + ' ' + '11' + ':' + '22' + ':' + '33');

+---------------------------------------------------------------------------+
| ('2018' + '-' + '11' + '-' + '01' + ' ' + '11' + ':' + '22' + ':' + '33') |
+---------------------------------------------------------------------------+
|                                                                      2096 |
+---------------------------------------------------------------------------+
1 row in set, 4 warnings (0.00 sec)

看看最终效果

SELECT CONCAT(
         '2018-11-' ,
         LPAD(FLOOR(1 + RAND() * 30), 2, 0),-- 01-30
         ' ',
         LPAD(FLOOR(RAND() * 24), 2, 0), -- 00-23
         ':',
         LPAD(FLOOR(RAND() * 60), 2, 0),-- 00-59
         ':',
         LPAD(FLOOR(RAND() * 60), 2, 0) -- 00-59
           ) 'rand time';

+---------------------+
| rand time           |
+---------------------+
| 2018-11-03 12:17:53 |
+---------------------+
1 row in set (0.00 sec)

2 时间累加

mysql提供了时间运算的函数，可以加以利用。

语法
ADDDATE(date, days)
ADDDATE(date, INTERVAL expr unit)
SUBDATE(date, days)
SUBDATE(date, INTERVAL expr unit)
DATE_ADD(date, INTERVAL expr unit)
DATE_SUB(date, INTERVAL expr unit)

用法都差不多，用前两个做下试验一下。
ADDDATE(date, days)接受两个参数，第一个是一个日期对象可以是日期字符串和date对象，第二个是增加的天数。

-- 增加时间可以是负数，负数既负增长
SELECT ADDDATE('2018-12-22 22:22:22', 1) date1, ADDDATE('2018-12-22 22:22:22', -1) date2;

+---------------------+---------------------+
| date1               | date2               |
+---------------------+---------------------+
| 2018-12-23 22:22:22 | 2018-12-21 22:22:22 |
+---------------------+---------------------+
1 row in set (0.00 sec)

ADDDATE(date, INTERVAL expr Unit)接受三个参数,第一个同上，第二个参数expr为增加的量，注意这里是表达式，所以可以是字符串，第三个Unit表示按什么单位增长。

-- 同样支持负增长
SELECT ADDDATE('2018-12-22 22:22:22', INTERVAL 1 SECOND) date1, ADDDATE('2018-12-22 22:22:22', INTERVAL 1 MINUTE ) date2;

+---------------------+---------------------+
| date1               | date2               |
+---------------------+---------------------+
| 2018-12-22 22:22:23 | 2018-12-22 22:23:22 |
+---------------------+---------------------+
1 row in set (0.00 sec)

Unit单位是关键字，如下表所示：

关键字	说明
MICROSECOND	微秒
SECOND	秒
MINUTE	分钟
HOUR	小时
DAY	天
WEEK	星期
MONTH	月
QUARTER	季度
YEAR	年
SECOND_MICROSECOND	SECONDS.MICROSECONDS 字符串形式，例如'1.1'表示1秒1微秒
MINUTE_MICROSECOND	MINUTES:SECONDS.MICROSECONDS 字符串形式，例如'1:1.1'表示1分钟1秒1微秒
MINUTE_SECOND	MINUTES:SECONDS 同上
HOUR_MICROSECOND	HOURS:MINUTES:SECONDS.MICROSECONDS 同上
HOUR_SECOND	HOURS:MINUTES:SECONDS 同上
HOUR_MINUTE	HOURS:MINUTES 同上
DAY_MICROSECOND	DAYS HOURS:MINUTES:SECONDS.MICROSECONDS 同上
DAY_SECOND	DAYS HOURS:MINUTES:SECONDS 同上
DAY_MINUTE	DAYS HOURS:MINUTES 同上
DAY_HOUR	DAYS HOURS 同上
YEAR_MONTH	YEARS-MONTHS 同上

再来个特殊的吧

-- 注意第一个和第二个
select ADDDATE('2011-11-11 11:11:11:11', INTERVAL '1 1:1:1.10' DAY_MICROSECOND)     'DAY_MICROSECOND',
       ADDDATE('2011-11-11 11:11:11:11', INTERVAL '1 1:1:1.100000' DAY_MICROSECOND) 'DAY_MICROSECOND',
       ADDDATE('2011-11-11 11:11:11:11', INTERVAL '1 1:1:1' DAY_SECOND)             'DAY_SECOND',
       ADDDATE('2011-11-11 11:11:11:11', INTERVAL '1 1:1' DAY_MINUTE)               'DAY_MINUTE',
       ADDDATE('2011-11-11 11:11:11:11', INTERVAL '1 1' DAY_HOUR)                   'DAY_HOUR';

+----------------------------+----------------------------+---------------------+---------------------+---------------------+
| DAY_MICROSECOND            | DAY_MICROSECOND            | DAY_SECOND          | DAY_MINUTE          | DAY_HOUR            |
+----------------------------+----------------------------+---------------------+---------------------+---------------------+
| 2011-11-12 12:12:12.100000 | 2011-11-12 12:12:12.100000 | 2011-11-12 12:12:12 | 2011-11-12 12:12:11 | 2011-11-12 12:11:11 |
+----------------------------+----------------------------+---------------------+---------------------+---------------------+
1 row in set, 5 warnings (0.00 sec)

来生成随机时间吧

SELECT ADDDATE(ADDDATE(ADDDATE(ADDDATE(
                                 '2018-11-01 00:00：00', FLOOR(RAND() * 30)), -- 1-30号
                               INTERVAL FLOOR(RAND()*24) HOUR), -- 0-23点
                       INTERVAL FLOOR(RAND()*60) MINUTE), -- 0-59分
               INTERVAL FLOOR(RAND()*60) SECOND -- 0-59秒
           ) rand_time;

+---------------------+
| rand_time           |
+---------------------+
| 2018-11-19 08:53:25 |
+---------------------+
1 row in set, 5 warnings (0.00 sec)

3 date生成函数

mysql提供了2个函数用于生成制定的时间和日期

语法
MAKEDATE(year, dayOfYear)
MAKETIME(hour, minute, second)
参数一看就懂，不用多做解释，支持数字和字符串。
举个例子：

SELECT MAKEDATE(2018, '100') d, MAKETIME(1, '1', 1) t;

+------------+----------+
| d          | t        |
+------------+----------+
| 2018-04-10 | 01:01:01 |
+------------+----------+
1 row in set (0.00 sec)

生成随机时间需要借助上面的时间增加函数

SELECT ADDDATE(MAKEDATE(2018, FLOOR(305 + RAND() * 30)), -- 1-30号
               INTERVAL MAKETIME(
               FLOOR(RAND()*24), -- 0-23点
               FLOOR(RAND()*60), -- 0-59分
               FLOOR(RAND()*60) -- 0-59秒
               ) HOUR_SECOND) 'rand time';

+---------------------+
| rand time           |
+---------------------+
| 2018-11-15 03:10:29 |
+---------------------+
1 row in set (0.00 sec)

4 时间戳

除了直接去随机具体的年月日，我们还可以从时间戳入手，mysql有提供时间戳和时间日期相互转换的函数。

语法
FROM_UNIXTIME(unix_timestamp)：日期转时间戳
UNIX_TIMESTAMP(date)：时间戳转日期

SELECT FROM_UNIXTIME(1541905871) date, UNIX_TIMESTAMP('2018-11-11 11:11:11') timestamp;

+---------------------+------------+
| date                | timestamp  |
+---------------------+------------+
| 2018-11-11 11:11:11 | 1541905871 |
+---------------------+------------+
1 row in set (0.00 sec)

可以先得到随机时间范围的时间戳差距，再去随机这个差距。最后转化为date对象

SELECT UNIX_TIMESTAMP('2018-11-01 00:00:00')                                             start_time,
       UNIX_TIMESTAMP('2018-11-30 23:59:59')                                             end_time,
       UNIX_TIMESTAMP('2018-11-30 23:59:59') - UNIX_TIMESTAMP('2018-11-01 00:00:00') + 1 time_range;

+------------+------------+------------+
| start_time | end_time   | time_range |
+------------+------------+------------+
| 1541001600 | 1543593599 |    2592000 |
+------------+------------+------------+
1 row in set (0.00 sec)

将差值作为随机范围

SELECT FROM_UNIXTIME(FLOOR(1541001600 + RAND() * 2592000)) rand_time;

+---------------------+
| rand_time           |
+---------------------+
| 2018-11-30 05:34:30 |
+---------------------+
1 row in set (0.00 sec)

完整sql

SELECT FROM_UNIXTIME(
         FLOOR((@s := UNIX_TIMESTAMP('2018-11-01 00:00:00')) + RAND() * (UNIX_TIMESTAMP('2018-11-30 23:59:59') - @s + 1)
)) rand_time;

批量插入

废了老半天生成的时间当然是用来插入的，批量的话就需要使用自定义函数。

-- 指定结束符
DELIMITER$$
-- 切换到指定数据库
USE test$$

DROP FUNCTION IF EXISTS makeManyData$$
-- 创建函数
CREATE FUNCTION makeManyData(startTime VARCHAR(32), endTime VARCHAR(32), minInterval INT, maxInterval INT)
  RETURNS INT DETERMINISTIC
  BEGIN
    DECLARE genTime DATETIME;
    DECLARE makeCount, genRange INT;
    DECLARE genUserId BIGINT;
    DECLARE genIp VARCHAR(20);
    SET makeCount = 0;
    SET genTime = startTime;

    #TIMESTAMPDIFF(SECOND,'2018-8-8 08:08:10','2018-8-8 08:08:09') => -1;
    WHILE (TIMESTAMPDIFF(SECOND, genTime, endTime) > maxInterval) DO
      -- 生成时间
      SET genRange = FLOOR(minInterval + RAND() * (maxInterval - minInterval + 1));
      SET genTime = ADDDATE(genTime, INTERVAL genRange SECOND);

      -- 生成user_id
      SET genUserId = FLOOR(10000 + RAND() * 10000);

      -- 生成ip
      SET genIp = CONCAT(FLOOR(100 + RAND() * 26), '.',
                         FLOOR(0 + RAND() * 256), '.',
                         FLOOR(0 + RAND() * 256), '.',
                         FLOOR(0 + RAND() * 256));

      -- 插入语句
      INSERT INTO pv (user_id, ip_addr, access_date) VALUE (genUserId, genIp, genTime);
      SET makeCount = makeCount + 1;
    END WHILE;
    RETURN makeCount;
  END$$
DELIMITER ;

需要全部选中一起执行。

使用

SELECT makeManyData('2018-11-01 00:00:00', '2018-11-02 00:00:00', 1800, 3600) makeCount;

+-----------+
| makeCount |
+-----------+
|        31 |
+-----------+
1 row in set (0.09 sec)

再看看数据

+----+---------+-----------------+---------------------+
| id | user_id | ip_addr         | access_date         |
+----+---------+-----------------+---------------------+
|  1 |   19390 | 115.18.159.232  | 2018-11-01 00:41:10 |
|  2 |   16188 | 102.148.164.118 | 2018-11-01 01:31:14 |
|  3 |   15804 | 118.219.31.11   | 2018-11-01 02:13:03 |
|  4 |   11713 | 107.214.97.101  | 2018-11-01 03:08:55 |
|  5 |   19684 | 109.215.39.63   | 2018-11-01 04:03:51 |
|  6 |   11676 | 112.241.65.115  | 2018-11-01 04:57:21 |
|  7 |   10315 | 118.141.148.63  | 2018-11-01 05:41:38 |
|  8 |   17101 | 102.68.25.176   | 2018-11-01 06:26:21 |
|  9 |   17058 | 101.51.207.116  | 2018-11-01 07:01:00 |
| 10 |   18201 | 115.127.183.23  | 2018-11-01 07:56:06 |
| 11 |   12727 | 111.89.113.43   | 2018-11-01 08:35:24 |
| 12 |   10974 | 123.81.212.50   | 2018-11-01 09:21:03 |
| 13 |   18432 | 119.66.5.84     | 2018-11-01 10:05:38 |
| 14 |   19354 | 123.206.67.229  | 2018-11-01 10:53:11 |
| 15 |   18039 | 123.49.53.117   | 2018-11-01 11:44:10 |
| 16 |   19399 | 118.190.146.164 | 2018-11-01 12:34:02 |
| 17 |   15346 | 105.95.71.68    | 2018-11-01 13:18:49 |
| 18 |   17543 | 106.233.220.143 | 2018-11-01 14:04:11 |
| 19 |   14130 | 110.207.212.182 | 2018-11-01 14:40:44 |
| 20 |   12681 | 102.169.9.48    | 2018-11-01 15:13:12 |
| 21 |   16385 | 117.108.28.74   | 2018-11-01 16:08:27 |
| 22 |   17029 | 104.190.54.213  | 2018-11-01 16:41:53 |
| 23 |   11417 | 103.47.142.60   | 2018-11-01 17:27:45 |
| 24 |   18509 | 118.7.255.228   | 2018-11-01 18:13:08 |
| 25 |   17296 | 105.208.120.234 | 2018-11-01 18:57:36 |
| 26 |   10691 | 122.17.200.180  | 2018-11-01 19:32:30 |
| 27 |   17685 | 108.65.89.248   | 2018-11-01 20:07:48 |
| 28 |   11336 | 106.204.72.2    | 2018-11-01 21:02:05 |
| 29 |   19433 | 103.218.219.186 | 2018-11-01 21:37:53 |
| 30 |   11865 | 118.249.191.211 | 2018-11-01 22:10:08 |
| 31 |   19379 | 101.98.204.215  | 2018-11-01 23:06:39 |
+----+---------+-----------------+---------------------+
31 rows in set (0.00 sec)

引用

mysql官方文档

MySQL 随机生成时间
目标随机生成2018-11-01 00:00:00 ~ 2018-11-30 23:59:59中的一个时间。随...
Jmeter——Dummy Sampler
一、Dummy Sampler 二、随机函数生成当前时间生成当前时间三、随机函数生成数据 _Random随机函...
2018-08-09Jmeter——Dummy Sampler
一、Dummy Sampler 二、随机函数生成当前时间生成当前时间三、随机函数生成数据 Random
JavaScript一些技巧
一、String Skill 时间对比：时间个位数形式需补0 格式化金钱生成随机ID 生成随机HEX色值生成星...
随机生成密码（如MYSQL）
密码包含数字，字母，特殊字符
未明学院：Faker库批量造数据，解决Python数据来源问题（
Faker数据生成第二期来了！今天使用Faker来生成随机时间，随机邮箱，随机浏览器头信息，还有随机文章句子。 ...
Centos 下初次登录mysql密码错误
mysql5.7初次登录报错(命令行安装会生成随机密码)mysql安装成功后创建的超级用户'root'@'loca...
python必知必会6
Python中生成随机整数、随机小数、0—1之间小数方法生成随机整数使用random.randint()生成随机...
Python模块---random随机结果获取
random随机结果获取生成[0, 1)之间浮点数随机生成给定区间整数随机生成区间的偶数随机生成区间的浮点...
无处不在的随机数
目录：什么是随机数随机数分类伪随机数生成器真随机数生成器各种语言中的随机数使用系统时间作为种子是否安全...