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

Question

我正在创建一个酒吧、啤酒、饮酒者、喜欢、常客、交易模式。每个酒吧每周都有营业/休息时间。

特定错误:我有一个遍历每个柱的外循环，以及为每个柱分配 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 ;

Answer 1

第1步:去掉不相关的绒毛，比如 table 。您提示计算未能“随机”，对吗？那么，这相当于计算吗？

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。也许那时您(或我们)就能看到问题所在。