mysql 建表问题，如何让查询能一层一层叠上去

[MaiCong]

是这样的，最近做一套会员系统，其中有两个字段当前用户和推荐人。

我需要列出当前推荐人的所有父级推荐人。

比如：

当前用户->推荐人->推荐人的推荐人->推荐人的推荐人的推荐人->推荐人的推荐人的推荐人的推荐人...

这样一直叠上去，请问数据库表该怎么写？

我总不能:

$ref1 ='select * form `user` where refname = 'xxx' limit 1;
$ref2 = select * form `user` where refname = '$ref1' limit 1;
$ref3 = select * form `user` where refname = '$ref2' limit 1;
...


求写法！:(

[abelyao]

可以考虑使用 存储过程 在数据库里面用 while

[MaiCong]

@abelyao 呃⊙﹏⊙怎么弄

[abelyao]

@MaiCong 搜一下 mysql 存储过程

[xudshen]

google自搜

http://stackoverflow.com/questions/10646833/using-mysql-query-to-traverse-rows-to-make-a-recursive-tree
http://dba.stackexchange.com/questions/7147/find-highest-level-of-a-hierarchical-field-with-vs-without-ctes

[bcxx]

试试 closure table? 应该那几个 tree 表的 antipattern 可解……

[txlty]

如果是我做，就直接加个字段。每次插入数据时查询一次就够了。

[zenliver]

这样，，性能会成渣渣

[abelyao]

@txlty 加个什么样的字段呢？

[msg7086]

$people = 'xxx'
$ref = []
while 还有$people
$people ='select * form `user` where refname = '$people' limit 1;
$ref[] = $people

[txlty]

@abelyao
字段 [username] [refname] [当前推荐人的所有父级推荐人]
数据： xxx . . ref1 . . ref3,ref2,ref1
新增用户xxx2，推荐人xxx
数据：xxx2 . . xxx . . ref3,ref2,ref1,xxx

[Nerrsoft]

性能堪忧啊，如果用户不怎么多的话可以一次查询所有用户，然后在程序中循环出结果

[toooddchen]

用一个char字段, 用分隔符把推荐人串起来,

id1-id2-id3-id4

使用的时候把它取出来, 在应用里拆分查询就ok

[MaiCong]

@txlty 这就是追加了？每次新增前查询推荐人信息，然后在推荐人字段里追加所属推荐人的推荐人。。
这个方法简单。感谢！

[abelyao]

@txlty @MaiCong
……好吧，确实简单又实用，就是字段的长度不能预测，就用 text 来存储吧，就是如果要检索用户 A 带来的所有下级用户，有点麻烦而已

[Aoliz]

果然还是以空间换时间哈~
实际需求里，佣金系统应该不需要这么多级吧

[Cee]

存储过程
了解一下嵌套子查询 邻接模型 物化路径模型 嵌套集合模型
可以去看看 The Art of SQL

[wingoo]

以前做多层菜单时有个方案
增加冗余列
内容类似/root/child/childchild
这样的好处是可以找到某个root下的所有子菜单用like '/root/%'
后果是更新稍微麻烦些

[MaiCong]

@xudshen 有点明白了。
@bcxx get√
@txlty 看楼下...
@zenliver 所有才问嘛
@msg7086 用 while 真的大丈夫？
@Nerrsoft 刚开始这样查询的时候我可吓了一跳...
@Aoliz 嘿嘿，发下线呗
@abelyao 用电脑了，看了一下，还是这个靠谱。
@Cee 嗯，正在看，看来得恶补一下知识了。

[MaiCong]

@wingoo 数据大了影响很大啊 这招行不通啊

[markmx]

有个什么二叉树 看看行不行

记得有个 left ，right 值。

[abelyao]

@MaiCong 用存储过程妥妥的基本满足需求了，而且是在数据库的服务器上去 while 所以性能问题不大，如果是在程序上 while 变成每往上一级就得查一次数据库那性能可能渣渣。拼字符串的方式也可以，但是检索是个问题，主要看你查询的频率了。综合之下，存储过程就是比较实际、性能又不会渣的选择。之前做过几个系统里面的无限级递归都用这个方法。

[abelyao]

@MaiCong
另外，ref 字段最好记录的是 user id 而不要用 user name，系统内部的关联最好都是用 id 来关联。

[cye3s]

oracle 倒是能一句sql搞定，普通构造树语句小改下就是这一串

[DeutschXP]

个人觉得这个应该是两种方式共同使用：存储过程加字符串字段，字符串的作用相当于缓存，
用空间换性能本来就是一个很划算的做法

我之前做这种多级代理之类的会员结构，是这样：
user 表下面两个字段
parentID: int 父会员的 ID，比如是1327
parentsID: text 所有父辈会员的 ID，用逗号间隔，比如是",1,152,927,1327,"

对于一个新会员注册添加，比如根据注册链接的 refID 读出 推荐者的记录row，那么新纪录的parentID 就是refID 或者是 row[ID]，parentsID 也只不过是row[parentsID] & row[ID] & ","

这样做的最大好处，就是这个过程只需要操作一次数据库，而且不需要用到存储过程。所有父辈的 ID 本来就不能算是动态的值，为什么需要每次注册/登录的时候都需要查询一下这个用户有哪些父辈呢，当然应该缓存下来，无论是缓存在数据库里还是文件里。
包括楼主的需求，当前用户的所有父辈，直接把 parentsID 分割一下就行了，难道就这么个打注册之后就千百年不会再变的东西还得每次都去操作一次数据库？

然后是楼上有人提到的另一个需求：列出该用户所有的下级，这东西可以用 like 简单粗暴的搜，你已经几百万会员了吗？否则的话，右模糊 like 'parentsID,%'加上索引，影响没多少。当然也可以存储过程，具体就要看你的会员结构了，目前会员的层数是5层还是500层？会员是5W 还是500W，并不是所有时候都是存储过程效率更高的。

这个解决方案仅仅适合会员推荐这种应用，如果比如是新闻内容网站，因为所谓的子分类总是会移动来移动去的，比如今天这个结点在社会新闻下面，明天可能要移动到热点新闻下面去，那就会引起结构变化了，缓存都得重建。但是会员推荐，你把张三的下线都给挪到李四的下面去，你看张三可跟你急。

[MaiCong]

@markmx 二叉树啊...
@abelyao 明白，数字索引快嘛
@cye3s 关键是mysql...
@DeutschXP 感谢分析！字符串存储这招很好！已经使用上了，差不多就是这个意思。客户说会员达到一定数量就清空重来。2333。找到一个例子： http://mat1.gtimg.com/hb/js/common/demo/tree.html

[markmx]

@MaiCong http://mat1.gtimg.com/hb/js/common/demo/tree.html
这个第二个。就是我说的。。有个left right 值。。

[datou552211]

关系网用缓存吧