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;


请注意,未找到结果。

组合(table1table2)(1,3),(3,3)不返回结果,而组合(table1table2)(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

10-07 19:33
查看更多