mysql文章与标签简单查询

来源：互联网发布：php 搭建企业网站编辑：程序博客网时间：2024/06/06 18:46

建表

DROP TABLE IF EXISTS `news`;CREATE TABLE `news` (  `news_id` int(11) NOT NULL AUTO_INCREMENT,  `title` varchar(100) DEFAULT NULL,  `tags` varchar(100) DEFAULT NULL,  PRIMARY KEY (`news_id`)) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;insert  into `news`(`news_id`,`title`,`tags`) values (1,'文章1','1,2'),(2,'文章2','1,2,3'),(3,'文章3','3'),(4,'文章4','1,4');DROP TABLE IF EXISTS `tag`;CREATE TABLE `tag` (  `t_id` int(11) NOT NULL AUTO_INCREMENT,  `tag_name` varchar(100) DEFAULT NULL,  PRIMARY KEY (`t_id`)) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;insert  into `tag`(`t_id`,`tag_name`) values (1,'标签1'),(2,'标签2'),(3,'标签3'),(4,'标签4');

====>根据标签id,查询文章

SELECT *FROM newsWHERE FIND_IN_SET('2',news.tags);

====>查询文章所带的标签

SELECT tagsFROM newsWHERE news_id=1#代码处理成[1,2]SELECT *FROM tagWHERE t_id IN (1,2);

====>根据一个标签查询文章表所有的文章

SELECT     n.title,t.tag_name FROM news AS n LEFT JOIN tag AS t ON  LOCATE(CONCAT(t.t_id,','),CONCAT(n.tags,',')) WHERE t.tag_name='标签2'

阅读全文

0 0