mysql 一个文章多个分类_jdbc mysql 插入一篇文章并与多个标签，一个分类建立关联关系。...

问题描述

我用servlet/jsp开发一个博客系统，我希望用一条语句：插入一篇文章并与多个标签，一个分类建立关联关系。

问题出现的环境背景及自己尝试过哪些方法

我一步步的做过，先建立文章，返回自增列，然后用这个自增列id，分别关联多个标签，关联分类，感觉这样太麻烦，有没有一条语句就搞定的方法。

相关代码

CREATE TABLE category

(

`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '类别id',

`name` VARCHAR(20) UNIQUE NOT NULL COMMENT '类别名',

PRIMARY KEY (`id`)

) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

CREATE TABLE article

(

`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '文章id',

`title` VARCHAR(20) NOT NULL COMMENT '文章标题',

`author` INT(10) UNSIGNED COMMENT '外键-文章作者',

`createTime` TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',

`updateTime` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',

`category` INT(10) UNSIGNED COMMENT '外键-文章类别',

`content` TEXT COMMENT '文章内容',

`visit` INT(10) DEFAULT 0 COMMENT '点击量',

`recommend` TINYINT(1) DEFAULT 0 COMMENT '是否推荐',

PRIMARY KEY (`id`),

KEY `author` (`author`),

CONSTRAINT `article_author` FOREIGN KEY (`author`) REFERENCES `user` (`id`) ON DELETE SET NULL ON UPDATE NO ACTION,

KEY `category` (`category`),

CONSTRAINT `article_category` FOREIGN KEY (`category`) REFERENCES `category` (`id`) ON DELETE SET NULL ON UPDATE NO ACTION

) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

CREATE TABLE article_tag (

`article` INT(10) UNSIGNED NOT NULL COMMENT '外键-文章',

`tag` INT(10) UNSIGNED NOT NULL COMMENT '外键-标签',

PRIMARY KEY (`article`, `tag`),

FOREIGN KEY (`article`) REFERENCES `article` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION,

FOREIGN KEY (`tag`) REFERENCES `tag` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION

) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;