玖叶教程网

早前的一篇数据库学习文章双色球号码我做主，数据库编程随机生成双色球号码（MySQL），后台很多网友评论说双色球都有了，大乐透也不能落下，今天把大乐透的补上，具体的技术点，请点击上面的链接查看另一篇文章。

1、创建所需的表，大乐透红球池是35个，补充两个即可，麻烦的是篮球，篮球一次要有两个，为了完全模拟开奖的情况，也采用和红球一样的做法，生成两个篮球。具体建表语句如下：

#红球表
DROP TABLE IF EXISTS `bred`;
CREATE TABLE `bred` (
 `r` int(11) NULL DEFAULT NULL,
 `u` char(1) NULL DEFAULT NULL
);
INSERT INTO `bred` VALUES (1, '0');
INSERT INTO `bred` VALUES (2, '0');
INSERT INTO `bred` VALUES (3, '0');
INSERT INTO `bred` VALUES (4, '0');
INSERT INTO `bred` VALUES (5, '0');
INSERT INTO `bred` VALUES (6, '0');
INSERT INTO `bred` VALUES (7, '0');
INSERT INTO `bred` VALUES (8, '0');
INSERT INTO `bred` VALUES (9, '0');
INSERT INTO `bred` VALUES (10, '0');
INSERT INTO `bred` VALUES (11, '0');
INSERT INTO `bred` VALUES (12, '0');
INSERT INTO `bred` VALUES (13, '0');
INSERT INTO `bred` VALUES (14, '0');
INSERT INTO `bred` VALUES (15, '0');
INSERT INTO `bred` VALUES (16, '0');
INSERT INTO `bred` VALUES (17, '0');
INSERT INTO `bred` VALUES (18, '0');
INSERT INTO `bred` VALUES (19, '0');
INSERT INTO `bred` VALUES (20, '0');
INSERT INTO `bred` VALUES (21, '0');
INSERT INTO `bred` VALUES (22, '0');
INSERT INTO `bred` VALUES (23, '0');
INSERT INTO `bred` VALUES (24, '0');
INSERT INTO `bred` VALUES (25, '0');
INSERT INTO `bred` VALUES (26, '0');
INSERT INTO `bred` VALUES (27, '0');
INSERT INTO `bred` VALUES (28, '0');
INSERT INTO `bred` VALUES (29, '0');
INSERT INTO `bred` VALUES (30, '0');
INSERT INTO `bred` VALUES (31, '0');
INSERT INTO `bred` VALUES (32, '0');
INSERT INTO `bred` VALUES (33, '0');
INSERT INTO `bred` VALUES (34, '0');
INSERT INTO `bred` VALUES (35, '0');
#篮球表
DROP TABLE IF EXISTS `bblue`;
CREATE TABLE `bblue` (
 `b` int(11) NULL DEFAULT NULL,
 `u` char(1) NULL DEFAULT '0'
);
INSERT INTO `bblue` VALUES (1, '0');
INSERT INTO `bblue` VALUES (2, '0');
INSERT INTO `bblue` VALUES (3, '0');
INSERT INTO `bblue` VALUES (4, '0');
INSERT INTO `bblue` VALUES (5, '0');
INSERT INTO `bblue` VALUES (6, '0');
INSERT INTO `bblue` VALUES (7, '0');
INSERT INTO `bblue` VALUES (8, '0');
INSERT INTO `bblue` VALUES (9, '0');
INSERT INTO `bblue` VALUES (10, '0');
INSERT INTO `bblue` VALUES (11, '0');
INSERT INTO `bblue` VALUES (12, '0');

2、创建函数dlt()，两次循环为了生成红球和篮球

CREATE FUNCTION `dlt`() RETURNS varchar(100) CHARSET utf8
BEGIN
	declare i int default 1;
	declare red int;
	declare blue int;
	declare redballs varchar(100);
	declare blueballs varchar(100);
	update bred set u='0';
	update bblue set u='0';
	while i<=5 do
		select r into red from bred where u='0' order by rand() limit 1;
		update bred set u='1' where r=red;
		set i = i+1;
	end while;
	set i=1;
	while i<=2 do
		select b into blue from bblue where u='0' order by rand() limit 1;
		update bblue set u='1' where b=blue;
		set i = i+1;
	end while;
	select GROUP_CONCAT(r) into redballs from bred where u='1';
	select GROUP_CONCAT(b) into blueballs from bblue where u='1';
	RETURN concat('数据库预测大乐透-号码发布时间：',cast(now() as char(20)),'，号码为红球:',redballs,'|篮球:',blueballs);
END;

3、调用函数进行预测

select dlt();

综上就是使用MySQL随机模拟生成大乐透号码的过程，希望你喜欢。最后再次重申，这只是用来练习MySQL的习作。如果中将，纯属意外！