mysql inner jion查询的问题
mysql inner jion 查询的问题#
在开发的时候使用 MYSQL 的 inner join 语句发现了一个很奇怪的问题，直接上表
建表#
a 表结构和数据如下
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for a
-- ----------------------------
DROP TABLE IF EXISTS `a`;
CREATE TABLE `a` (
`id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
`title` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL,
`sort_id` tinyint(1) NULL DEFAULT NULL,
`created_at` datetime NULL DEFAULT NULL,
`deleted_at` datetime NULL DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_unicode_ci ROW_FORMAT = Dynamic;
SET FOREIGN_KEY_CHECKS = 1;b 表结构如下：
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for b
-- ----------------------------
DROP TABLE IF EXISTS `b`;
CREATE TABLE `b` (
`id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
`other_id` int(11) NOT NULL,
`a_id` int(11) NOT NULL,
`created_at` datetime NULL DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_unicode_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of b
-- ----------------------------
SET FOREIGN_KEY_CHECKS = 1;
插入数据#
a 表
INSERT INTO `test`.`a` (`id`, `title`, `sort_id`, `created_at`, `deleted_at`) VALUES (1, '标题1', 1, '2021-08-20 16:14:27', NULL);
INSERT INTO `test`.`a` (`id`, `title`, `sort_id`, `created_at`, `deleted_at`) VALUES (2, '标题1', 2, '2021-08-20 16:14:38', NULL);
INSERT INTO `test`.`a` (`id`, `title`, `sort_id`, `created_at`, `deleted_at`) VALUES (3, '标题1', 2, '2021-08-20 16:14:51', NULL);b 表
INSERT INTO `test`.`b` (`id`, `other_id`, `a_id`, `created_at`) VALUES (1, 9999, 9999, '2021-08-20 16:25:14');使用 inner join 进行查询#
查询 1#
select * from `a` inner join `b` where `a`.`sort_id` = 2 and (`b`.`other_id` = 1 or `a`.`title` = "标题1") and `a`.`deleted_at` is null order by `a`.`created_at` desc limit 10 offset 0能够正常查出结果，结果如下图
查询 2#
这次查询删除掉 b 表的所有数据，然后再执行上面的查询
-- 先执行
DELETE FROM b WHERE id=1;
--后执行
select * from `a` inner join `b` where `a`.`sort_id` = 2 and (`b`.`other_id` = 1 or `a`.`title` = "标题1") and `a`.`deleted_at` is null order by `a`.`created_at` desc limit 10 offset 0;结果为空，结果如下图
疑惑#
不管是查询 1 还是查询 2，a 和 b 表不都是没有公共部分吗？为什么查询 1 能够查询出数据，而查询 2 却不行呢？请大佬解惑，非常感谢
关于 LearnKu
粤公网安备 44030502004330号
推荐文章：