Home > Database > Mysql Tutorial > 关于tag标签系统的实现

关于tag标签系统的实现

WBOY
Release: 2016-06-07 15:22:26
Original
1471 people have browsed it

实验室的项目,需要做对用户发布的主题进行打标签的功能,纠结甚久,实现思路如下: 一、数据库表的设计 1.tag表 create table qa_tag(tag_id int primary key auto_increment,tag_name varchar(32) not null,tag_time timestamp not null default CURRENT_T

实验室的项目,需要做对用户发布的主题进行打标签的功能,纠结甚久,实现思路如下:

一、数据库表的设计

1.tag表 

create table qa_tag
(
tag_id int primary key auto_increment,
tag_name varchar(32) not null,
tag_time timestamp not null default CURRENT_TIMESTAMP,
refered_cnt int not null default 0,
user_id int not null,
unique (tag_name),
constraint foreign key (user_id) references user_info(user_id)
);

2.topic表  
Copy after login
create table qa_topic
(
topic_id int primary key auto_increment,
topic_title varchar(128) not null,
topic_body text not null,
topic_time timestamp not null default CURRENT_TIMESTAMP,
user_id int not null,
tags varchar(128) not null default ''
);
Copy after login
3.tag与topic的映射表

create table qa_tag_topic
(
record_id int primary key auto_increment,
tag_id int not null,
topic_id int not null,
constraint foreign key (tag_id) references qa_tag(tag_id),
constraint foreign key (topic_id) references qa_topic(topic_id)
);

二、逻辑实现

1.用户创建主题时,给自己发布的主题打上了几个标签,点击提交

2.后台接受参数后,先把数据插入到qa_topic表中,获得了topicId;

3.把用户输入的标签转成数组,批量插入到数据库中,sql代码如下:

<insert id="insertTags" parameterType="java.util.List">
	    insert into qa_tag(tag_name,user_id) values
	    <foreach collection="list" item="o" index="index" separator=",">
	        (#{o.tagName},#{o.userId}) 
	    </foreach>
	    ON DUPLICATE KEY UPDATE refered_cnt = refered_cnt + 1;//如果有重复,则把tag的被引用数目+1
	    alter table qa_tag auto_increment = 1//保证tagId的连续性
</insert>
Copy after login

4. 根据标签数组,查询插入后,这些标签的tagId(返回一个链表): 


5.然后,把tagId和topicId批量插入到qa_tag_topic: 


    insert ignore into qa_tag_topic(tag_id,topic_id) values
   
        (#{o.tagId},#{o.topicId})
   


6.把topicId返回即可。
source:php.cn
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Popular Tutorials
More>
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template