Open qw4990 opened 4 years ago
#表结构
CREATE TABLE `usertree` (
`userid` int(8) unsigned NOT NULL COMMENT '用户ID',
`username` varchar(20) NOT NULL COMMENT '用户名',
`parenttree` varchar(1024) NOT NULL DEFAULT '' COMMENT '父亲树',
PRIMARY KEY (`userid`),
FULLTEXT KEY `ft_idx_username_parenttree` (`username`,`parenttree`),
FULLTEXT KEY `ft_idx_parenttree` (`parenttree`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='用户关系树';
#插入数据:
INSERT INTO usertree(`userid`, `username`, `parenttree`) VALUES (10086, '我自己的名字', ''),(10011, 'lv1_1', '10086'),(10012, 'lv1_2', '10086'),(10013, 'lv1_3', '10086'),(20020, 'lv2_1', '10086,10011');
#查询语句:
#1.可匹配 uid:10086 的所有下级用户:
select userid from usertree where MATCH(parenttree) against(10086 IN BOOLEAN MODE);
#2.可匹配 uid:10086自己 和 他的所有下级用户:
select userid from usertree where MATCH(username,parenttree) against('"我自己的名字" 10086' IN BOOLEAN MODE);
Description
Support the
match ... against
function, which is used to do the full-text search.Score
Mentor(s)
Contact the mentors: #tidb-challenge-program channel in TiDB Community Slack Workspace
Recommended Skills
Learning Materials