mysql - MySQL 可以在表中创建表吗？

Question

我正在尝试将应用程序的帐户组织到“分层”网格系统中。

即Super_Grid_A 中有网格 1、2、3，网格 1 中有账户 A、B、C，网格 2 中有账户 B、C、D，等等。

有什么建议吗？

Answer 1

您不能在 MySQL 中的表内创建表。但是您可以对网格表使用主/从方法。看起来像这样的东西:

SQL Fiddle

MySQL 5.5.32 架构设置:

CREATE TABLE grid
    (`id` int, `name` varchar(12), `master_id` int)
;

INSERT INTO grid
    (`id`, `name`, `master_id`)
VALUES
    (1, 'Super_Grid_A', 0),
    (2, 'Grid 1', 1),
    (3, 'Grid 2', 1),
    (4, 'Grid 3', 1)
;

CREATE TABLE account
    (`id` int, `name` varchar(9))
;

INSERT INTO account
    (`id`, `name`)
VALUES
    (1, 'account A'),
    (2, 'account B'),
    (3, 'account C'),
    (4, 'account D')
;

CREATE TABLE grid_account
    (`grid_id` int, `acct_id` int)
;

INSERT INTO grid_account
    (`grid_id`, `acct_id`)
VALUES
    (2, 1),
    (2, 2),
    (2, 3),
    (3, 2),
    (3, 3),
    (3, 4)
;

查询 1:

select master.name master_grid, slave.name slave_grid, acct.name account
from grid master
inner join grid slave on master.id = slave.master_id
inner join grid_account grid_acct on slave.id = grid_acct.grid_id
inner join account acct on grid_acct.acct_id = acct.id
where master.master_id = 0

Results :

|  MASTER_GRID | SLAVE_GRID |   ACCOUNT |
|--------------|------------|-----------|
| Super_Grid_A |     Grid 1 | account A |
| Super_Grid_A |     Grid 1 | account B |
| Super_Grid_A |     Grid 1 | account C |
| Super_Grid_A |     Grid 2 | account B |
| Super_Grid_A |     Grid 2 | account C |
| Super_Grid_A |     Grid 2 | account D |