mysql 存储多维数据库_sql – 在数据库中存储多维数组：关系或多维？

The goal is to retrieve an element with PHP by name and all its descendants.

如果这是您需要的,您可以使用LIKE搜索

SELECT *

FROM Table1

WHERE CELL LIKE 'AEE%';

使用CELL开头的索引,这是一个范围检查,这是快速的.

如果您的数据看起来不像这样,您可以创建一个路径列,它看起来像目录路径,并包含从根到元素的路径/路径上的所有节点.

| id | CELL | parent_id | path |

|====|======|===========|==========|

| 1 | A | NULL | 1/ |

| 2 | AA | 1 | 1/2/ |

| 3 | AAA | 2 | 1/2/3/ |

| 4 | AAC | 2 | 1/2/4/ |

| 5 | AB | 1 | 1/5/ |

| 6 | AE | 1 | 1/6/ |

| 7 | AEA | 6 | 1/6/7/ |

| 8 | AEE | 6 | 1/6/8/ |

| 9 | AEEB | 8 | 1/6/8/9/ |

要检索“AE”(包括自身)的所有后代,您的查询将是

SELECT *

FROM tree t

WHERE path LIKE '1/6/%';

或(MySQL特定级联)

SELECT t.*

FROM tree t

CROSS JOIN tree r -- root

WHERE r.CELL = 'AE'

AND t.path LIKE CONCAT(r.path,'%');

结果：

| id | CELL | parent_id | path |

|====|======|===========|==========|

| 6 | AE | 1 | 1/6/ |

| 7 | AEA | 6 | 1/6/7/ |

| 8 | AEE | 6 | 1/6/8/ |

| 9 | AEEB | 8 | 1/6/8/9/ |

性能

drop table if exists tree;

CREATE TABLE tree (

`id` int primary key,`CELL` varchar(50),`parent_id` int,`path` varchar(255),unique index (`CELL`),unique index (`path`)

);

DROP TRIGGER IF EXISTS `tree_after_insert`;

DELIMITER //

CREATE TRIGGER `tree_after_insert` BEFORE INSERT ON `tree` FOR EACH ROW BEGIN

if new.id = 1 then

set new.path := '1/';

else

set new.path := concat((

select path from tree where id = new.parent_id

),new.id,'/');

end if;

END//

DELIMITER ;

insert into tree

select seq as id,conv(seq,10,36) as CELL,case

when seq = 1 then null

else floor(rand(1) * (seq-1)) + 1

end as parent_id,null as path

from seq_1_to_100000

;

DROP TRIGGER IF EXISTS `tree_after_insert`;

-- runtime ~ 4 sec.

测试

计算根下的所有元素：

SELECT count(*)

FROM tree t

CROSS JOIN tree r -- root

WHERE r.CELL = '1'

AND t.path LIKE CONCAT(r.path,'%');

-- result: 100000

-- runtime: ~ 30 ms

获取特定节点下的子树元素：

SELECT t.*

FROM tree t

CROSS JOIN tree r -- root

WHERE r.CELL = '3B0'

AND t.path LIKE CONCAT(r.path,'%');

-- runtime: ~ 30 ms

结果：

| id | CELL | parent_id | path |

|=======|======|===========|=====================================|

| 4284 | 3B0 | 614 | 1/4/11/14/614/4284/ |

| 6560 | 528 | 4284 | 1/4/11/14/614/4284/6560/ |

| 8054 | 67Q | 6560 | 1/4/11/14/614/4284/6560/8054/ |

| 14358 | B2U | 6560 | 1/4/11/14/614/4284/6560/14358/ |

| 51911 | 141Z | 4284 | 1/4/11/14/614/4284/51911/ |

| 55695 | 16Z3 | 4284 | 1/4/11/14/614/4284/55695/ |

| 80172 | 1PV0 | 8054 | 1/4/11/14/614/4284/6560/8054/80172/ |

| 87101 | 1V7H | 51911 | 1/4/11/14/614/4284/51911/87101/ |

PostgreSQL的

这也适用于PostgreSQL.只有字符串连接语法必须更改：

SELECT t.*

FROM tree t

CROSS JOIN tree r -- root

WHERE r.CELL = 'AE'

AND t.path LIKE r.path || '%';

搜索如何工作

如果您查看测试示例,您会看到结果中的所有路径以’1/4/11/14/614/4284 /’开头.那就是CELL =’3B0’的子树根路径.如果路径列被索引,引擎将会有效地找到它们,因为索引按路径排序.这就像你想在100K字的字典中找到以’pol’开头的所有单词.您不需要阅读整个字典.