MySQL版本5.7.14(如果有)。重写以避免任何混乱。这是一个非常简单/易于重现的问题。表格及其名称被混淆/泛化。
步骤1:依次运行以下查询。
CREATE TABLE `table1` (
`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`id`)
)
COLLATE='latin1_swedish_ci'
ENGINE=InnoDB
AUTO_INCREMENT=5
;
CREATE TABLE `table2` (
`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`id`)
)
COLLATE='latin1_swedish_ci'
ENGINE=InnoDB
AUTO_INCREMENT=5
;
CREATE TABLE `table1_table2` (
`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`table1_id` INT(10) UNSIGNED NULL DEFAULT NULL,
`table2_id` INT(10) UNSIGNED NULL DEFAULT NULL,
`created_at` TIMESTAMP NULL DEFAULT NULL,
`updated_at` TIMESTAMP NULL DEFAULT NULL,
PRIMARY KEY (`id`),
INDEX `table1_id_table1id` (`table1_id`),
INDEX `table2_id_table2id` (`table2_id`),
CONSTRAINT `table1_id_table1id` FOREIGN KEY (`table1_id`) REFERENCES `table1` (`id`) ON DELETE CASCADE,
CONSTRAINT `table2_id_table2id` FOREIGN KEY (`table2_id`) REFERENCES `table2` (`id`) ON DELETE CASCADE
)
COLLATE='utf8_unicode_ci'
ENGINE=InnoDB
AUTO_INCREMENT=7
;
INSERT INTO `table1` (`id`) VALUES
(1),
(2),
(3),
(4);
INSERT INTO `table2` (`id`) VALUES
(1),
(2),
(3),
(4);
INSERT INTO `table1_table2` (`id`, `table1_id`, `table2_id`, `created_at`, `updated_at`) VALUES
(1, 1, 3, '2016-12-28 14:47:40', '2016-12-28 14:47:41'),
(2, 1, 1, '2016-12-28 14:47:37', '2016-12-28 14:47:39'),
(3, 2, 3, '2016-12-28 14:59:48', '2016-12-28 14:59:48'),
(4, 3, 1, '2016-12-28 14:51:38', '2016-12-28 14:51:38'),
(5, 3, 2, '2016-12-28 14:52:33', '2016-12-28 14:52:34'),
(6, 3, 3, '2016-12-28 14:55:05', '2016-12-28 14:55:05'),
(7, 3, 4, '2016-12-28 14:56:48', '2016-12-28 14:56:48');
步骤2:运行此查询。
SELECT *
FROM `table1`
WHERE EXISTS (
SELECT *
FROM `table2`
INNER JOIN `table1_table2` ON `table2`.`id` = `table1_table2`.`table2_id`
WHERE `table1_table2`.`table1_id` = `table1`.`id`
AND `table2`.`id` = 3)
AND `table1`.`id` = 3;
请注意,未找到结果。
组合(
table1
,table2
)(1,3),(3,3)不返回结果,而组合(table1
,table2
)(1,1),(2,3), (3,1),(3,2),(3,4)确实返回。步骤3:运行此查询。
ALTER TABLE `table1_table2`
DROP COLUMN `id`;
步骤4:重新运行该查询。
SELECT *
FROM `table1`
WHERE EXISTS (
SELECT *
FROM `table2`
INNER JOIN `table1_table2` ON `table2`.`id` = `table1_table2`.`table2_id`
WHERE `table1_table2`.`table1_id` = `table1`.`id`
AND `table2`.`id` = 3)
AND `table1`.`id` = 3;
注意发现结果。
在此查询上运行
EXPLAIN
时,出现警告提示Note: Field or reference 'table1.id' of SELECT #2 was resolved in
SELECT #1 Note:/* select#1 */ select '3' AS `id` from `table1` where
(exists(/*select#2 */ select 1 from `table2` join `table1_table2` where ((`table1_table2`.`table2_id` = 3) and (`table1_table2`.`table1_id` = '3'))))
请注意,警告将
table1_id
用引号引起来(建议将其视为字符串)。我不确定这是错误还是我做错了。
最佳答案
我相信mysql对列引用感到困惑。
我相信,如果您进行更改,它将起作用
SELECT * FROM table1 ...... WHERE table1.id = 3
通过使用别名:
SELECT * FROM table1 t1 ...... WHERE t1.id = 3
优良作法是始终为查询中的表引用使用别名,以提高可读性并避免混淆。
顺便说一下,查询可以用以下更简单的方式编写。
SELECT t1.* FROM table1 t1
JOIN table1_table2 rel
ON t1.id = rel.table1_id
WHERE t1.id = 3 AND rel.table2_id = 3
如果没有重复的关系,那不会有什么区别,但是这里使用的是EXISTS。
SELECT * FROM table1 t1
WHERE EXISTS (
SELECT 0 FROM table1_table2 rel
WHERE t1.id = rel.table1_id AND rel.table2_id = 3)
AND t1.id = 3