blocks|key|1371100|text|在MySQL中管理分层数据|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|1371101|一般来说，我喜欢“嵌套集”(尤其是嵌套集)。在MySQL中，它实际上不支持分层数据。它是快速的，但是如果易于维护是件大事的话，您需要确保您的开发人员读过这篇文章。它非常灵活--在你的情况下，这似乎并不重要。|1371102|这似乎很适合您的问题--在推荐模型中，您需要找到引用树，这在嵌套的集合模型中是快速的；您还需要知道谁是给定用户的~子级，以及它们之间关系的深度；这也是快速的。|1371103|entityMap|0|LINK|mutability|MUTABLE|url|http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/^0|0|D|0|0|0|0^^$0|@$1|2|3|4|5|6|7|P|8|@]|9|@$A|Q|B|R|1|S]]|C|$]]|$1|D|3|E|5|6|7|T|8|@]|9|@]|C|$]]|$1|F|3|G|5|6|7|U|8|@]|9|@]|C|$]]|$1|H|3|-4|5|6|7|V|8|@]|9|@]|C|$]]]|I|$J|$5|K|L|M|C|$N|O]]]]

<a href="http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/" rel="nofollow">Managing Hierarchical Data in MySQL</a>

In general, I like the "nested set", esp. in MySQL which doesn't really have language support for hierarchical data. 
It's fast, but you'll need to make sure your developers read that article if ease of maintenance is a big deal. It's very flexible - which doesn't seem to matter much in your case.

It seems a good fit for your problem - in the referral model, you need to find the tree of referrers, which is fast in the nested set model; you also need to know who are the ~children@ of a given user, and the depth of their relationship; this is also fast.

blocks|key|2926783|text|使用OQGRAPH存储引擎。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2926784|您可能希望跟踪任意数量的级别，而不仅仅是5个级别。获取一个支持MySQL+(如MariaDB或OurDelta)的QGRAPH发动机叉，并使用它来存储树。它实现了邻接列表模型，但是通过使用名为latch的特殊列向存储引擎发送命令，告诉它要执行什么样的查询，您可以获得闭包表的所有优点，而无需每次有人为您的站点注册时进行簿记工作。|offset|length|style|CODE|2926785|下面是在OQGRAPH中使用的查询。参见http://openquery.com/graph-computation-engine-documentation的文档|2926786|我们将使用起跑者作为推荐人，用destid作为推荐人。|2926787|添加用户11，由用户10引用|2926788|insert+into+ancestors_table+(origid,destid)+values+(10,11)|code-block|syntax|javascript|2926789|若要查找用户3引用的所有用户，请执行以下操作。|2926790|SELECT+linkid+FROM+ancestors_table+WHERE+latch+=+2+AND+origid+=+3;|2926791|查找用户10的祖先。|2926792|SELECT+linkid+FROM+ancestors_table+WHERE+latch+=+2+AND+destid+=+10;|2926793|若要查找用户3所指的每个级别的用户数，请执行以下操作：|2926794|SELECT+count(linkid),+weight
FROM+ancestors_table
WHERE+latch+=+2+AND+origid+=+3
GROUP+BY+weight;|2926795|entityMap|0|LINK|mutability|MUTABLE|url|http://forge.mysql.com/w/images/5/53/GraphComputationEngine.pdf|1|http://openquery.com/graph-computation-engine-documentation^0|0|2O|5|1L|9|0|0|K|1N|1|0|0|0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|1E|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|1F|8|@$D|1G|E|1H|F|G]]|9|@$D|1I|E|1J|1|1K]]|A|$]]|$1|H|3|I|5|6|7|1L|8|@]|9|@$D|1M|E|1N|1|1O]]|A|$]]|$1|J|3|K|5|6|7|1P|8|@]|9|@]|A|$]]|$1|L|3|M|5|6|7|1Q|8|@]|9|@]|A|$]]|$1|N|3|O|5|P|7|1R|8|@]|9|@]|A|$Q|R]]|$1|S|3|T|5|6|7|1S|8|@]|9|@]|A|$]]|$1|U|3|V|5|P|7|1T|8|@]|9|@]|A|$Q|R]]|$1|W|3|X|5|6|7|1U|8|@]|9|@]|A|$]]|$1|Y|3|Z|5|P|7|1V|8|@]|9|@]|A|$Q|R]]|$1|10|3|11|5|6|7|1W|8|@]|9|@]|A|$]]|$1|12|3|13|5|P|7|1X|8|@]|9|@]|A|$Q|R]]|$1|14|3|-4|5|6|7|1Y|8|@]|9|@]|A|$]]]|15|$16|$5|17|18|19|A|$1A|1B]]|1C|$5|17|18|19|A|$1A|1D]]]]

<h1>Use the OQGRAPH storage engine.</h1>

You probably want to keep track of an arbitrary number of levels, rather than just 5 levels. Get one of the MySQL forks that supports the <a href="http://forge.mysql.com/w/images/5/53/GraphComputationEngine.pdf" rel="nofollow">QGRAPH engine</a> (such as MariaDB or OurDelta), and use that to store your tree. It implements the adjacency list model, but by using a special column called <code>latch</code> to send a command to the storage engine, telling it what kind of query to perform, you get all of the advantages of a closure table without needing to do the bookkeeping work each time someone registers for your site.

Here are the queries you'd use in OQGRAPH. See the documentation at 
<a href="http://openquery.com/graph-computation-engine-documentation" rel="nofollow">http://openquery.com/graph-computation-engine-documentation</a>

We're going to use origid as the referrer, and destid as the referree.

To add user 11, referred by user 10

<pre><code>insert into ancestors_table (origid,destid) values (10,11)
</code></pre>

To find all users referred by user 3.

<pre><code>SELECT linkid FROM ancestors_table WHERE latch = 2 AND origid = 3;
</code></pre>

To find the ancestors of user 10.

<pre><code>SELECT linkid FROM ancestors_table WHERE latch = 2 AND destid = 10;
</code></pre>

To find the number of users at each level, referred by user 3:

<pre><code>SELECT count(linkid), weight
FROM ancestors_table
WHERE latch = 2 AND origid = 3
GROUP BY weight;
</code></pre>

blocks|key|2926801|text|被分隔的祖先字符串|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2926802|如果您正在强烈考虑5级关系表，那么使用分隔的祖先字符串而不是5个单独的列可能会简化一些事情。|2926803|user_id++depth+++ancestors
10+++++++7+++++++9,7,4,3,2,1
9++++++++6+++++++7,4,3,2,1
...
2++++++++2+++++++1
1++++++++1+++++++(empty+string)|code-block|syntax|javascript|2926804|下面是您将在此模型中使用的一些SQL命令：|2926805|添加用户11，由用户10引用|2926806|insert+into+ancestors_table+(user_id,+depth,+ancestors)
select+11,+depth%2B1,+concat(10,',',ancestors)
from+ancestors_table
where+user_id=10;|2926807|若要查找用户3引用的所有用户，请注意此查询不能使用索引。)|2926808|select+user_id
from+ancestors_table
where+ancestors+like+'%25,3,%25'+or+ancestors+like+'3,%25'+or+ancestors+like+'%25,3';|2926809|要找到用户10的祖先，您需要在客户端程序中拆分字符串。在Ruby中，代码将是ancestorscolumn.split(",").map{%7Cx%7C+x.to_i}。在SQL中没有分解字符串的好方法。|offset|length|style|CODE|2926810|select+ancestors+from+ancestors_table+where+user_id=10;|2926811|若要查找用户3所指的每个级别的用户数，请执行以下操作：|2926812|select
+++depth-(select+depth+from+ancestors_table+where+user_id=3),
+++count(*)
from+ancestors_table
where+ancestors+like+'%25,3,%25'+or+ancestors+like+'3,%25'+or+ancestors+like+'%25,3'
group+by+depth;|2926813|您可以在这些查询的like+'%25,3,%25'部分中使用like+concat('%25,',+?,+',%25')并将用户名的整数绑定到占位符，从而避免SQL攻击。|2926814|entityMap^0|0|0|0|0|0|0|0|0|12|16|0|0|0|0|9|C|Q|Q|0^^$0|@$1|2|3|4|5|6|7|18|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|19|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|1A|8|@]|9|@]|A|$G|H]]|$1|I|3|J|5|6|7|1B|8|@]|9|@]|A|$]]|$1|K|3|L|5|6|7|1C|8|@]|9|@]|A|$]]|$1|M|3|N|5|F|7|1D|8|@]|9|@]|A|$G|H]]|$1|O|3|P|5|6|7|1E|8|@]|9|@]|A|$]]|$1|Q|3|R|5|F|7|1F|8|@]|9|@]|A|$G|H]]|$1|S|3|T|5|6|7|1G|8|@$U|1H|V|1I|W|X]]|9|@]|A|$]]|$1|Y|3|Z|5|F|7|1J|8|@]|9|@]|A|$G|H]]|$1|10|3|11|5|6|7|1K|8|@]|9|@]|A|$]]|$1|12|3|13|5|F|7|1L|8|@]|9|@]|A|$G|H]]|$1|14|3|15|5|6|7|1M|8|@$U|1N|V|1O|W|X]|$U|1P|V|1Q|W|X]]|9|@]|A|$]]|$1|16|3|-4|5|6|7|1R|8|@]|9|@]|A|$]]]|17|$]]

<h1>Delimited String of Ancestors</h1>

If you're strongly considering the 5-level relationship table, it may simplify things to use a delimited string of ancestors instead of 5 separate columns.

<pre><code>user_id depth ancestors
10 7 9,7,4,3,2,1
9 6 7,4,3,2,1
...
2 2 1
1 1 (empty string)
</code></pre>

Here are some SQL commands you'd use with this model:

To add user 11, referred by user 10

<pre><code>insert into ancestors_table (user_id, depth, ancestors)
select 11, depth+1, concat(10,',',ancestors)
from ancestors_table
where user_id=10;
</code></pre>

To find all users referred by user 3. (Note that this query can't use an index.)

<pre><code>select user_id
from ancestors_table
where ancestors like '%,3,%' or ancestors like '3,%' or ancestors like '%,3';
</code></pre>

To find the ancestors of user 10. You need to break up the string in your client program. In Ruby, the code would be <code>ancestorscolumn.split(",").map{|x| x.to_i}</code>. There's no good way to break up the string in SQL.

<pre><code>select ancestors from ancestors_table where user_id=10;
</code></pre>

To find the number of users at each level, referred by user 3:

<pre><code>select
 depth-(select depth from ancestors_table where user_id=3),
 count(*)
from ancestors_table
where ancestors like '%,3,%' or ancestors like '3,%' or ancestors like '%,3'
group by depth;
</code></pre>

You can avoid SQL injection attacks in the <code>like '%,3,%'</code> parts of these queries by using <code>like concat('%,', ?, ',%')</code> instead and binding the an integer for the user number to the placeholder.

blocks|key|2926821|text|闭合表|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2926822|ancestor_id++descendant_id++distance
++++1++++++++++++1++++++++++++++0
++++2++++++++++++2++++++++++++++0
++++3++++++++++++3++++++++++++++0
++++4++++++++++++4++++++++++++++0
++++5++++++++++++5++++++++++++++0
++++6++++++++++++6++++++++++++++0
++++2++++++++++++3++++++++++++++1|code-block|syntax|javascript|2926823|要添加用户10，由用户3引用(我不认为您需要在这两个插入之间锁定表)：|2926824|insert+into+ancestor_table
select+ancestor_id,+10,+distance%2B1
from+ancestor_table
where+descendant_id=3;

insert+into+ancestor_table+values+(10,10,0);|2926825|若要查找用户3引用的所有用户，请执行以下操作。|2926826|select+descendant_id+from+ancestor_table+where+ancestor_id=3;|2926827|若要按深度计算这些用户：|2926828|select+distance,+count(*)+from+ancestor_table+where+ancestor_id=3+group+by+distance;|2926829|查找用户10的祖先。|2926830|select+ancestor_id,+distance+from+ancestor_table+where+descendant_id=10;|2926831|此方法的缺点是该表将占用大量存储空间。|2926832|entityMap^0|0|0|0|0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|10|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|11|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|12|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|13|8|@]|9|@]|A|$E|F]]|$1|K|3|L|5|6|7|14|8|@]|9|@]|A|$]]|$1|M|3|N|5|D|7|15|8|@]|9|@]|A|$E|F]]|$1|O|3|P|5|6|7|16|8|@]|9|@]|A|$]]|$1|Q|3|R|5|D|7|17|8|@]|9|@]|A|$E|F]]|$1|S|3|T|5|6|7|18|8|@]|9|@]|A|$]]|$1|U|3|V|5|D|7|19|8|@]|9|@]|A|$E|F]]|$1|W|3|X|5|6|7|1A|8|@]|9|@]|A|$]]|$1|Y|3|-4|5|6|7|1B|8|@]|9|@]|A|$]]]|Z|$]]

<h1>Closure Table</h1>

<pre><code>ancestor_id descendant_id distance
 1 1 0
 2 2 0
 3 3 0
 4 4 0
 5 5 0
 6 6 0
 2 3 1
</code></pre>

To add user 10, referred by user 3. (I don't think you need to lock the table between these two insertions):

<pre><code>insert into ancestor_table
select ancestor_id, 10, distance+1
from ancestor_table
where descendant_id=3;

insert into ancestor_table values (10,10,0);
</code></pre>

To find all users referred by user 3.

<pre><code>select descendant_id from ancestor_table where ancestor_id=3;
</code></pre>

To count those users by depth:

<pre><code>select distance, count(*) from ancestor_table where ancestor_id=3 group by distance;
</code></pre>

To find the ancestors of user 10.

<pre><code>select ancestor_id, distance from ancestor_table where descendant_id=10;
</code></pre>

The drawback to this method is amount of storage space this table will take.

I need to have a 5 levels hierarchy for the users registered to a website. Every user is invited by another, and I need to know all descendants for a user. And also ancestors for a user.

I have in mind 2 solution. 

<ol>
<li>Keeping a table with relationships this way. A closure table:</li>
</ol>

<pre><code>
 ancestor_id descendant_id distance
 1 1 0
 2 2 0
 3 3 0
 4 4 0
 5 5 0
 6 6 0
 2 3 1
</code></pre>

<ol>
<li>Having this table for relationships. Keeping in a table 5 levels ancestors. A "ancestors" table:</li>
</ol>

<pre><code>
 user_id ancestor_level1_id ancestor_level2_id ancestor_level3_id ancestor_level4_id ancestor_level5_id
 10 9 7 4 3 2
 9 7 4 3 2 1
</code></pre>

Are these good ideas?

I know about "the adjacency list model" and "the modified preorder tree traversal algorithm", but are these good solutions for a "referral" system?

The queries that I need to perform on this tree are:

<ul>
<li>frequently adding a new users</li>
<li>when a user buys something, their referrers get a percentage commission</li>
<li>every user should be able to find out how many people they've referred (and how many people were referred by people who they referred....) at each level</li>
</ul>

Storing Hierarchical Data (MySQL) for Referral Marketing

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

我需要一个5个层次的用户注册到一个网站。每个用户都是由另一个用户邀请的，我需要了解一个用户的所有后代。也是用户的祖先。我想到了2种解决方案。用这种方式保持一张有关系的表格。收尾表：    ancestor_id  descendant_id  distance    1            1            ...

问存储层次数据(MySQL)用于推荐营销
EN

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问存储层次数据(MySQL)用于推荐营销EN

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问存储层次数据(MySQL)用于推荐营销
EN