MySQL 数据透视表 - 主键的存在更改查询结果

Question

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 用引号引起来(表明它将其视为字符串)。

我不确定这是一个错误还是我做错了什么。

Answer 1

我相信 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