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

我总不能:
$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; ...

求写法！:(

refname

ref2

ref1

27 条回复 • 2014-12-26 12:13:43 +08:00

abelyao

2014-12-25 21:24:59 +08:00

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

MaiCong

2014-12-25 21:32:03 +08:00 via iPhone

@abelyao 呃⊙﹏⊙怎么弄

abelyao

2014-12-25 21:36:54 +08:00 via Android

@MaiCong 搜一下 mysql 存储过程

xudshen

2014-12-25 21:39:38 +08:00

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

2014-12-25 21:42:07 +08:00

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

txlty

2014-12-25 21:42:24 +08:00

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

zenliver

2014-12-25 21:43:53 +08:00

这样，，性能会成渣渣

abelyao

2014-12-25 21:47:36 +08:00 via Android

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

msg7086

2014-12-25 21:57:58 +08:00

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

txlty

2014-12-25 21:58:38 +08:00

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

Nerrsoft

2014-12-25 22:00:52 +08:00

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

toooddchen

2014-12-25 22:06:51 +08:00

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

id1-id2-id3-id4

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

MaiCong

2014-12-25 22:11:29 +08:00 via iPhone

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

abelyao

2014-12-25 22:22:10 +08:00

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

Aoliz

2014-12-25 22:31:07 +08:00

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

Cee

2014-12-25 22:43:10 +08:00 via Smartisan T1

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

wingoo

2014-12-25 22:47:26 +08:00

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

MaiCong

2014-12-25 23:00:48 +08:00

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

MaiCong

2014-12-25 23:09:05 +08:00

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

markmx

2014-12-25 23:11:01 +08:00

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

记得有个 left ，right 值。

abelyao

2014-12-25 23:26:34 +08:00

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

abelyao

2014-12-25 23:28:27 +08:00

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

cye3s

2014-12-25 23:41:25 +08:00 via Android

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

DeutschXP

2014-12-26 00:03:40 +08:00

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

我之前做这种多级代理之类的会员结构，是这样：
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

2014-12-26 00:36:02 +08:00

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

markmx

2014-12-26 12:12:01 +08:00

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

datou552211

2014-12-26 12:13:43 +08:00 via iPhone

关系网用缓存吧