1、随机数字
-- 随机 0-1
select floor(rand() * 2);
-- 随机1-100
select floor(rand()*100);
-- 随机八位数
select floor(rand()*90000000 + 10000000);
-- 随机2位小数
select convert(floor(rand() * 70 + 30) / 100,decimal(10,2));
-- 随机N位字符串
select substr(md5(rand()),1,8);
select left(uuid(),8);
2、随机布尔值
-- 随机布尔值
select if(floor(rand() * 2) = 1 , '是' , '否');
3、随机字符串
-- 随机8位字符串
select left(uuid(),8);
select left(md5(RAND() * 99999999),8);
-- select SUBSTRING(md5(RAND() * 99999999),1,8);
SELECT left(replace(password(RAND() * 99999999), '*', ''),8);
-- 随机1-100
select floor(rand()*100);
-- 随机百位数
select floor(rand()*90000000 + 10000000);
-- 浮点数。round() 四舍五入函数,保留2位。可用于金额。
select round(rand()*1000000,2);
4、随机生成 手机号
-- 方法一
SELECT CONCAT('1',floor(RAND()*7000000000+3000000000));
-- 方法二
SELECT
CONCAT(
1,
FLOOR(RAND() * 6 + 3),
FLOOR(RAND() * 10),
FLOOR(RAND() * 10),
FLOOR(RAND() * 10),
FLOOR(RAND() * 10),
FLOOR(RAND() * 10),
FLOOR(RAND() * 10),
FLOOR(RAND() * 10),
FLOOR(RAND() * 10),
FLOOR(RAND() * 10)
);
-- 方法三
CREATE FUNCTION `generatePhone`() RETURNS char(11) CHARSET utf8
DETERMINISTIC
BEGIN
DECLARE head VARCHAR(100) DEFAULT '000,156,136,176,183';
DECLARE content CHAR(10) DEFAULT '0123456789';
DECLARE phone CHAR(11) DEFAULT substring(head, 1+(FLOOR(1 + (RAND() * 3))*4), 3);
DECLARE i int DEFAULT 1;
DECLARE len int DEFAULT LENGTH(content);
WHILE i<9 DO
SET i=i+1;
SET phone = CONCAT(phone, substring(content, floor(1 + RAND() * len), 1));
END WHILE;
RETURN phone;
END
-- 调用:
SELECT generatePhone();
5、随机日期
-- 随机生成在2021-01-01 到 2021-11-01 之间的日期
select from_unixtime(
unix_timestamp('2020-11-01') + floor(
rand() * (
unix_timestamp('2021-01-01') - unix_timestamp('2021-11-01') + 1
)
)
);
--