时间戳

首页 > 解决方案 > MySQL 过程 - 内部 for 循环条件未产生正确的输出

mysql - MySQL 过程 - 内部 for 循环条件未产生正确的输出

问题描述

我正在创建一个酒吧、啤酒、饮酒者、喜欢、频繁、交易模式。每个酒吧在一周中的每一天都有开/关时间。

具体错误：我有一个穿过每个柱的外部循环，以及一个内部循环为每个柱分配 1-7 个开盘/收盘时间条目。尽管循环并尝试插入 1-7 个条目，但 INSERT 语句仅在外部循环中的每个柱条目触发一次。

我的开/关时间表：

Bar Name varchar(45) PK FK 
Bar Phone # INT PK FK
Weekday INT PK
Open Hour TIME
Close Hour TIME

当前输出由每根柱线的单个开盘/收盘时间条目组成：

运行程序后的打开/关闭时间表

Boone's Brew Inc    2147483647  7   04:55:51    18:39:25
Boondocks Brewing Tap Room & Restaurant 2147483647  4   09:01:01    17:10:09
Boon Island Alehouse    2147483647  1   05:14:24    19:56:57
........ for 957 more bars, all with listed only once in the table with a random weekday

期望的输出：

Boone's Brew Inc    2147483647  7   04:55:51    18:39:25
Boone's Brew Inc    2147483647  6   04:55:51    18:39:25
Boone's Brew Inc    2147483647  5   04:55:51    18:39:25
Boone's Brew Inc    2147483647  4   04:55:51    18:39:25
Boondocks Brewing Tap Room & Restaurant 2147483647  4   09:01:01    17:10:09
Boondocks Brewing Tap Room & Restaurant 2147483647  3   09:01:01    17:10:09
Boondocks Brewing Tap Room & Restaurant 2147483647  2   09:01:01    17:10:09
Boon Island Alehouse    2147483647  1   05:14:24    19:56:57

期望的输出：每根柱线有 1-7 个开盘/收盘时间条目，而不仅仅是 1 个。

我的程序是：

DROP PROCEDURE fill_hours;
DELIMITER $$

CREATE PROCEDURE fill_hours()
BEGIN

    DECLARE i INT DEFAULT 0;
    DECLARE j INT DEFAULT 0;
    DECLARE num_of_bars INT DEFAULT 0;
    DECLARE bar_name varchar(45) DEFAULT "";
    DECLARE bar_phone_num INT DEFAULT 0;
    DECLARE weekday INT DEFAULT 0;
    DECLARE open_hour TIME DEFAULT 0;
    DECLARE close_hour TIME DEFAULT 0;
    DECLARE start_time_factor TIME DEFAULT 0;
    DECLARE end_time_factor TIME DEFAULT 0;


    SELECT COUNT(*) INTO num_of_bars FROM Bar;
    WHILE i < num_of_bars DO
    SELECT `Bar Name`, `Bar Phone #` INTO bar_name, bar_phone_num FROM Bar LIMIT i,1;
        weekday: WHILE j < 7 DO
            SET weekday := (1 + FLOOR(RAND() * 7));
            IF EXISTS(SELECT `Bar Name`, `Bar Phone #`, `Weekday` FROM `Opening/Closing Hours` WHERE `Bar Name` = bar_name AND `Bar Phone #` = bar_phone_num AND `Weekday` = weekday) THEN
                SELECT CONCAT("weekday: ", weekday);
                SET j := j + 1;
                ITERATE weekday;
            END IF;
            SELECT SEC_TO_TIME(FLOOR(TIME_TO_SEC('13:00:00') + RAND() * (TIME_TO_SEC(TIMEDIFF('13:00:00', '15:00:00'))))) INTO start_time_factor;
            SELECT SEC_TO_TIME(FLOOR(TIME_TO_SEC('20:00:00') + RAND() * (TIME_TO_SEC(TIMEDIFF('18:00:00', '19:00:00'))))) INTO end_time_factor;
            SELECT SEC_TO_TIME(FLOOR(TIME_TO_SEC(start_time_factor) + RAND() * (TIME_TO_SEC(TIMEDIFF(start_time_factor, '16:00:00'))))) INTO open_hour;
            SELECT SEC_TO_TIME(FLOOR(TIME_TO_SEC(end_time_factor) + RAND() * (TIME_TO_SEC(TIMEDIFF(end_time_factor, '23:59:00'))))) INTO close_hour;


            INSERT INTO `Opening/Closing Hours` VALUE(bar_name, bar_phone_num, weekday, open_hour, close_hour);

            SET j := j + 1;
        END WHILE;
    SET i := i + 1;
    SET j := 0;
    END WHILE;

END $$
DELIMITER ;

标签： mysqlsqlstored-proceduresmysql-workbenchinnodb

解决方案

第一步：去掉不相关的绒毛，比如桌子。您抱怨计算不是“随机的”，对吗？那么，这是否等同于计算？

DROP PROCEDURE fill_hours;
DELIMITER $$

CREATE PROCEDURE fill_hours()
BEGIN
    DECLARE i INT DEFAULT 0;
    DECLARE j INT DEFAULT 0;
    DECLARE num_of_bars INT DEFAULT 0;
    DECLARE bar_name varchar(45) DEFAULT "";
    DECLARE bar_phone_num INT DEFAULT 0;
    DECLARE weekday INT DEFAULT 0;
    DECLARE open_hour TIME DEFAULT 0;
    DECLARE close_hour TIME DEFAULT 0;
    DECLARE start_time_factor TIME DEFAULT 0;
    DECLARE end_time_factor TIME DEFAULT 0;

        weekday: WHILE j < 7 DO
            SET weekday := (1 + FLOOR(RAND() * 7));
            SELECT SEC_TO_TIME(FLOOR(TIME_TO_SEC('13:00:00') + RAND() * (TIME_TO_SEC(TIMEDIFF('13:00:00', '15:00:00'))))) INTO start_time_factor;
            SELECT SEC_TO_TIME(FLOOR(TIME_TO_SEC('20:00:00') + RAND() * (TIME_TO_SEC(TIMEDIFF('18:00:00', '19:00:00'))))) INTO end_time_factor;
            SELECT SEC_TO_TIME(FLOOR(TIME_TO_SEC(start_time_factor) + RAND() * (TIME_TO_SEC(TIMEDIFF(start_time_factor, '16:00:00'))))) INTO open_hour;
            SELECT SEC_TO_TIME(FLOOR(TIME_TO_SEC(end_time_factor) + RAND() * (TIME_TO_SEC(TIMEDIFF(end_time_factor, '23:59:00'))))) INTO close_hour;
            -- For debugging:
            SELECT weekday, open_hour, close_hour;
            SET j := j + 1;
        END WHILE;
END $$
DELIMITER ;

但这似乎工作正常......

mysql> call fill_hours();
+---------+-----------+------------+
| weekday | open_hour | close_hour |
+---------+-----------+------------+
|       3 | 11:19:41  | 17:07:07   |
+---------+-----------+------------+
1 row in set (0.01 sec)

+---------+-----------+------------+
| weekday | open_hour | close_hour |
+---------+-----------+------------+
|       6 | 07:43:48  | 17:06:57   |
+---------+-----------+------------+
1 row in set (0.01 sec)

+---------+-----------+------------+
| weekday | open_hour | close_hour |
+---------+-----------+------------+
|       5 | 09:55:03  | 15:56:42   |
+---------+-----------+------------+
1 row in set (0.01 sec)

+---------+-----------+------------+
| weekday | open_hour | close_hour |
+---------+-----------+------------+
|       2 | 10:11:17  | 19:42:50   |
+---------+-----------+------------+
1 row in set (0.01 sec)

+---------+-----------+------------+
| weekday | open_hour | close_hour |
+---------+-----------+------------+
|       6 | 09:58:52  | 18:58:02   |
+---------+-----------+------------+
1 row in set (0.01 sec)

+---------+-----------+------------+
| weekday | open_hour | close_hour |
+---------+-----------+------------+
|       4 | 11:26:18  | 18:02:06   |
+---------+-----------+------------+
1 row in set (0.01 sec)

+---------+-----------+------------+
| weekday | open_hour | close_hour |
+---------+-----------+------------+
|       3 | 11:35:00  | 17:18:14   |
+---------+-----------+------------+
1 row in set (0.01 sec)

Query OK, 0 rows affected (0.01 sec)

现在，我将回复“为我工作”。

如果您认为这INSERT可能是问题所在，则进行类似的缩减以隔离（尽可能多地）INSERT. 也许那时您（或我们）可以看到问题所在。

推荐阅读