如何在MySQL存储过程中生成5个随机数？
要生成随机数，请使用RAND()MySQL中的ORDERBY函数。让我们首先创建一个表-
mysql> create table DemoTable (Value int);
使用插入命令在表中插入一些记录-
mysql> insert into DemoTable values(89); mysql> insert into DemoTable values(98); mysql> insert into DemoTable values(10); mysql> insert into DemoTable values(78); mysql> insert into DemoTable values(75); mysql> insert into DemoTable values(76); mysql> insert into DemoTable values(74); mysql> insert into DemoTable values(99); mysql> insert into DemoTable values(101);
使用select语句显示表中的所有记录-
mysql> select *from DemoTable;
这将产生以下输出-
+-------+ | Value | +-------+ | 89 | | 98 | | 10 | | 78 | | 75 | | 76 | | 74 | | 99 | | 101 | +-------+ 9 rows in set (0.00 sec)
以下是在MySQL存储过程中生成5个随机数的查询-
mysql> DELIMITER //
mysql> CREATE PROCEDURE generate_Random5Numbers()
BEGIN
SELECT GROUP_CONCAT(Value SEPARATOR '/')
FROM ( SELECT Value
FROM ( SELECT Value
FROM DemoTable
WHERE Value BETWEEN 10 AND 100
ORDER BY RAND() LIMIT 5) Ascending_Order
ORDER BY Value) 5Values;
END
//
mysql> DELIMITER ;借助call命令调用存储过程。
mysql> call generate_Random5Numbers();
这将产生以下输出-
+-----------------------------------+ | GROUP_CONCAT(Value SEPARATOR '/') | +-----------------------------------+ | 89/74/78/99/10 | +-----------------------------------+ 1 row in set (0.00 sec)