mysql - 连接具有可变表名的表

我不知道如何从这里开始。您可以执行以下操作吗？

我有50多个这样的桌子

`db.events_100`:

+ id     + ts      + var       + val       +
--------------------------------------------
| 1      | 5000    | some      | thing     |
| 2      | 5050    | some      | thing     |
| 3      | 5085    | some      | thing     |

或这个：

`db.events_101`:

+ id     + ts      + var       + val       +
--------------------------------------------
| 1      | 6020    | different | things    |
| 2      | 6060    | different | things    |
| 3      | 6095    | different | things    |

我想将这些表迁移到以下内容：

`db.events`:

+ id     + old_id | events_id | ts      + var       + val       +
-----------------------------------------------------------------
| 1      | 1      | 100       | 5000    | some      | thing     |
| 2      | 2      | 100       | 5050    | some      | thing     |
| 3      | 3      | 100       | 5085    | some      | thing     |
| 4      | 1      | 101       | 6020    | different | things    |
| 5      | 2      | 101       | 6060    | different | things    |
| 6      | 3      | 101       | 6095    | different | things    |

在这种情况下，events.events_id对应于表名的变量部分。什么是方便的查询来完成此操作，理想情况下无需循环。

最佳答案

对于Google员工...

确实，我无法建立变量查询。相反，我像这样“联合”了我的87个表：

drop table if exists `events`;

CREATE TABLE `events` (
    id INT AUTO_INCREMENT NOT NULL,
    event_id VARCHAR(255) NOT NULL,
    event_ts INT NOT NULL,
    event_type VARCHAR(255) NOT NULL,
    event_var VARCHAR(255) NOT NULL,
    event_val VARCHAR(255) NOT NULL,
    event_side VARCHAR(255),
    player_id VARCHAR(255) NOT NULL,
    time_edit INT NOT NULL,
    meta_author VARCHAR(255) NOT NULL,
    message VARCHAR(255) DEFAULT NULL,
    `status` VARCHAR(255) DEFAULT NULL,
    reported VARCHAR(255) DEFAULT NULL,
    reported_status VARCHAR(255) DEFAULT NULL,
    score VARCHAR(255) DEFAULT NULL,
    user_rating VARCHAR(255) DEFAULT NULL,
    user_upvotes VARCHAR(255) DEFAULT NULL,
    watched VARCHAR(255),
    old_id INT
    DEFAULT NULL, PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE = InnoDB;

INSERT INTO `events` (
    id, event_id, event_ts, event_type, event_var, event_val,   event_side, player_id, time_edit, meta_author, message, `status`, reported, reported_status, score, user_rating, user_upvotes, watched, old_id
)

SELECT
    NULL, t.event_id, t.event_ts, t.event_type, t.event_var, t.event_val, t.event_side, t.player_id, t.time_edit, t.meta_author, t.message, t.status, t.reported, t.reported_status, t.score, t.user_rating, t.user_upvotes, t.watched, t.old_id

FROM(
    select *, 1421362800 old_id from events_1421362800 union
    select *, 1421967600 old_id from events_1421967600 union
    select *, 1423782000 old_id from events_1423782000 union
    select *, 1424386800 old_id from events_1424386800 union
    select *, 1426201200 old_id from events_1426201200 union
    select *, 1426806000 old_id from events_1426806000 union
    select *, 1427410800 old_id from events_1427410800 union
    select *, 1428012000 old_id from events_1428012000 union
    select *, 1428616800 old_id from events_1428616800 union
    select *, 1429826400 old_id from events_1429826400 union
    select *, 1431036000 old_id from events_1431036000 union
    select *, 1432245600 old_id from events_1432245600 union
    select *, 1432850400 old_id from events_1432850400 union
    select *, 1434664800 old_id from events_1434664800 union
    select *, 1435269600 old_id from events_1435269600 union
    select *, 1435874400 old_id from events_1435874400 union
    select *, 1436479200 old_id from events_1436479200 union
    select *, 1437084000 old_id from events_1437084000 union

    ## more tables ####################

    select *, 1519945200 old_id from events_1519945200 union
    select *, 1524175200 old_id from events_1524175200

) t

必要的东西

万一需要漂亮的正则表达式来创建选择。例如在Notepadd ++中，使用replace with

搜索：

(events_(\d{10}))\r\n

更换

select *, \2 old_id from \1 union\r\n