delete from where id in(select ...) 只能够删除一条数据

就是不着调 发布于 2014/07/24 14:46
阅读 4K+
收藏 0

comments_closure表:

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for comments_closure
-- ----------------------------
DROP TABLE IF EXISTS `comments_closure`;
CREATE TABLE `comments_closure` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `author` varchar(255) NOT NULL,
  `comment` text NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of comments_closure
-- ----------------------------
INSERT INTO `comments_closure` VALUES ('1', 'Fran', '这个Bug的成因是什么?');
INSERT INTO `comments_closure` VALUES ('2', 'Ollie', '我觉得是一个空指针');
INSERT INTO `comments_closure` VALUES ('3', 'Fran', '不,我查过了');
INSERT INTO `comments_closure` VALUES ('4', 'Kukla', '我们需要查无效输入');
INSERT INTO `comments_closure` VALUES ('5', 'Ollie', '是的,那是一个问题');
INSERT INTO `comments_closure` VALUES ('6', 'Fran', '好, 查一下吧');
INSERT INTO `comments_closure` VALUES ('7', 'Kukla', '解决了');
INSERT INTO `comments_closure` VALUES ('8', 'Kukla', '那必然是个问题');

treepaths表:

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for treepaths
-- ----------------------------
DROP TABLE IF EXISTS `treepaths`;
CREATE TABLE `treepaths` (
  `ancestor` int(10) unsigned NOT NULL,
  `descendant` int(10) unsigned NOT NULL,
  PRIMARY KEY (`ancestor`,`descendant`),
  KEY `descendant` (`descendant`),
  CONSTRAINT `treepaths_ibfk_1` FOREIGN KEY (`descendant`) REFERENCES `comments_closure` (`id`) ON DELETE CASCADE,
  CONSTRAINT `treepaths_ibfk_2` FOREIGN KEY (`ancestor`) REFERENCES `comments_closure` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of treepaths
-- ----------------------------
INSERT INTO `treepaths` VALUES ('1', '1');
INSERT INTO `treepaths` VALUES ('1', '2');
INSERT INTO `treepaths` VALUES ('1', '3');
INSERT INTO `treepaths` VALUES ('1', '4');
INSERT INTO `treepaths` VALUES ('1', '5');
INSERT INTO `treepaths` VALUES ('1', '6');
INSERT INTO `treepaths` VALUES ('1', '7');
INSERT INTO `treepaths` VALUES ('1', '8');
INSERT INTO `treepaths` VALUES ('2', '2');
INSERT INTO `treepaths` VALUES ('2', '3');
INSERT INTO `treepaths` VALUES ('3', '3');
INSERT INTO `treepaths` VALUES ('4', '4');
INSERT INTO `treepaths` VALUES ('4', '5');
INSERT INTO `treepaths` VALUES ('4', '6');
INSERT INTO `treepaths` VALUES ('4', '7');
INSERT INTO `treepaths` VALUES ('4', '8');
INSERT INTO `treepaths` VALUES ('5', '5');
INSERT INTO `treepaths` VALUES ('5', '8');
INSERT INTO `treepaths` VALUES ('6', '6');
INSERT INTO `treepaths` VALUES ('6', '7');
INSERT INTO `treepaths` VALUES ('7', '7');
INSERT INTO `treepaths` VALUES ('8', '8');

SELECT descendant FROM treepaths WHERE ancestor=4 

查出的结果是:

我想删除评论4和所有他的子评论:

DELETE FROM comments_closure WHERE id IN(SELECT descendant FROM treepaths WHERE ancestor=4)

这样删的话只能删除  IN 条件中的第一条记录 也就是只能删除记录4

 

试了下如果这样删

DELETE FROM comments_closure WHERE id IN(4,5,6,7,8) 

则可以全部删掉 .

不明白什么原因啊.

加载中
0
mark35
mark35
mysql总是能遇到各种稀奇古怪的问题~
就是不着调
就是不着调
确实,刚才用pg试了下,没有任何问题
0
Hobo
Hobo

有外键约束  

SET FOREIGN_KEY_CHECKS=0;

DELETE FROM comments_closureWHERE idIN(SELECT descendantFROM treepathsWHERE ancestor=4)

SET FOREIGN_KEY_CHECKS=1;



返回顶部
顶部