å æ»ç»ï¼
- æ°æ®éå°çæ¶åï¼ç¨joinæ´åç®
- æ°æ®é大çæ¶åï¼joinçææ¬æ´é«ï¼ä½ç¸å¯¹æ¥è¯´joinçé度ä¼æ´å¿«
- æ°æ®éè¿å¤§çæ¶åï¼inçæ°æ®éè¿å¤ï¼ä¼ææ æ³æ§è¡SQLçé®é¢ï¼å¾ 解å³
äºæ æ¯è¿æ ·çï¼å»å¹´å ¥èçæ°å ¬å¸ï¼ä¹åå¨ä»£ç reviewçæ¶å被æåºè¯´ï¼ä¸è¦åjoinï¼joinèæ§è½è¿æ¯æ ¢æ¥çï¼å½æ¶ä¹æ¯çç没æå¤æ³ï¼é£å°±åin好äºï¼æè¿åç°inçæ°æ®éè¿å¤§çæ¶åä¼å¯¼è´sqlæ ¢ï¼çè³sql太é¿ï¼ç´æ¥æ¥éäºãè¿æ¬¡æ¥æµ 究ä¸ä¸ï¼å°åºæ¯in好è¿æ¯join好ï¼ä» ç®å认ç¥æ¢å¯»ï¼æä¸å¯¹ä¹å¤æ¬¢è¿ææ£
以ä¸å®éªä» å¨æ¬æºçµèè¯éª
ä¸ã表ç»æ
1ãç¨æ·è¡¨
sqlå¤å¶ä»£ç CREATE TABLE `user` (
`id` int NOT NULL AUTO_INCREMENT,
`name` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT 'å§å',
`gender` smallint DEFAULT NULL COMMENT 'æ§å«',
`mobile` varchar(11) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT 'ææºå·',
`create_time` datetime NOT NULL COMMENT 'å建æ¶é´',
PRIMARY KEY (`id`),
UNIQUE KEY `mobile` (`mobile`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1005 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
2ã订å表
sqlå¤å¶ä»£ç CREATE TABLE `order` (
`id` int unsigned NOT NULL AUTO_INCREMENT,
`price` decimal(18,2) NOT NULL,
`user_id` int NOT NULL,
`product_id` int NOT NULL,
`status` smallint NOT NULL DEFAULT '0' COMMENT '订åç¶æ',
PRIMARY KEY (`id`),
KEY `user_id` (`user_id`),
KEY `product_id` (`product_id`)
) ENGINE=InnoDB AUTO_INCREMENT=202 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
äºãå æ¥è¯å°éæ°æ®çæ åµ
ç¨æ·è¡¨æä¸åæ¡éæºçæçæ°æ®ï¼è®¢å表æä¸ç¾æ¡éæºæ°æ®
æ¥ä¸ææç订å以å订å对åºçç¨æ·
ä¸é¢ä»ä¸ä¸ªç»´åº¦æ¥ç
å¤è¡¨è¿æ¥æ¥è¯¢ææ¬ = ä¸æ¬¡é©±å¨è¡¨ææ¬ + ä»é©±å¨è¡¨æ¥åºçè®°å½æ° * ä¸æ¬¡è¢«é©±å¨è¡¨çææ¬
1ãjoin
JOIN: explain format=json select order.id, price, user.name from order join user on order.user_id = user.id;
åæ¥è¯¢: select order.id,price,user.name from order,user where user_id=user.id;
2ãåå¼æ¥
select id,price,user_id from order;
select name from user where id in (8, 11, 20, 32, 49, 58, 64, 67, 97, 105, 113, 118, 129, 173, 179, 181, 210, 213, 215, 216, 224, 243, 244, 251, 280, 309, 319, 321, 336, 342, 344, 349, 353, 358, 363, 367, 374, 377, 380, 417, 418, 420, 435, 447, 449, 452, 454, 459, 461, 472, 480, 487, 498, 499, 515, 525, 525, 531, 564, 566, 580, 584, 586, 592, 595, 610, 633, 635, 640, 652, 658, 668, 674, 685, 687, 701, 718, 720, 733, 739, 745, 751, 758, 770, 771, 780, 806, 834, 841, 856, 856, 857, 858, 882, 934, 942, 983, 989, 994, 995); [inçæ¯orderæ¥åºæ¥çææç¨æ·id]
å¦æ¤çæ¥ï¼åå¼æ¥åjoinæ¥çææ¬å¹¶æ²¡æç¸å·®è®¸å¤
3ã代ç å±é¢
主è¦ç¨phpåçåäºèæ¬ï¼ç¨abè¿è¡10个åæ¶ç请æ±ï¼çä¸æ¶é´ï¼è¿è¡æ¯è¾
ab -n 100 -c 10
in
phpå¤å¶ä»£ç $mysqli = new mysqli('127.0.0.1', 'root', 'root', 'test');
if ($mysqli->connect_error) {
die('Connect Error (' . $mysqli->connect_errno . ') ' . $mysqli->connect_error);
}
$result = $mysqli->query('select `id`,price,user_id from `order`');
$orders = $result->fetch_all(MYSQLI_ASSOC);
$userIds = implode(',', array_column($orders, 'user_id')); // è·å订åä¸çç¨æ·id
$result = $mysqli->query("select `id`,`name` from `user` where id in ({$userIds})");
$users = $result->fetch_all(MYSQLI_ASSOC);// è·åè¿äºç¨æ·çå§å
// å°idåæ°ç»é®
$userRes = [];
foreach ($users as $user) {
$userRes[$user['id']] = $user['name'];
}
$res = [];
// æ´åæ°æ®
foreach ($orders as $order) {
$current = [];
$current['id'] = $order['id'];
$current['price'] = $order['price'];
$current['name'] = $userRes[$order['user_id']] ?: '';
$res[] = $current;
}
var_dump($res);
// å
³émysqlè¿æ¥
$mysqli->close();
join
iniå¤å¶ä»£ç $mysqli = new mysqli('127.0.0.1', 'root', 'root', 'test');
if ($mysqli->connect_error) {
die('Connect Error (' . $mysqli->connect_errno . ') ' . $mysqli->connect_error);
}
$result = $mysqli->query('select order.id, price, user.`name` from `order` join user on order.user_id = user.id;');
$orders = $result->fetch_all(MYSQLI_ASSOC);
var_dump($orders);
$mysqli->close();
çæ¶é´çè¯ï¼ææ¾joinæ´å¿«ä¸äº
ä¸ãè¯ä¸å¤ä¸äºæ°æ®çæ åµ
user表ç°å¨10000æ¡æ°æ®ï¼order表10000æ¡è¯ä¸
1ãjoin
2ãåå¼
order
user
3ã代ç å±é¢
in
join
ä¸ãè¯ä¸å¤ä¸äºæ°æ®çæ åµ
éæºæå ¥åuser表åä¸æ¡æ°æ®ï¼order表ä¸ç¾ä¸æ¡è¯ä¸
1ãjoin
2ãåå¼
order
user
orderæ¥åºæ¥çç»æè¿é¿äº,,,
3ã代ç å±é¢
in
join
åãå°åºæä¹æè½æ´å¥½
注ï¼å¯¹äºæ¬æºæ¥è¯´100000æ¡æ°æ®ä¸å°äºï¼æ´å¤§çæ°æ®é害æçµèå¡æ»
æ»çæ¥è¯´ï¼å½æ°æ®éå°æ¶ï¼å¯è½ä¸é¡µæ°æ®å°±å¤æ¾çæ¶åï¼joinçææ¬åé度é½æ´å¥½ãæ°æ®é大çæ¶åç¡®å®åå¼æ¥çææ¬æ´ä½ï¼ä½æ¯ç±äºæ°æ®é大ï¼é æ循ç¯çææ¬æ´å¤ï¼ä»£ç æ§è¡çæ¶é´ä¹å°±è¶é¿ãå®éªè¿ç¨ä¸åç°ï¼å½inçæ°æ®éè¿å¤§çæ¶åï¼sqlè¿é¿ä¼æ æ³æ§è¡ï¼å¯è½è¿è¦æå¼å¤æ¡sqlè¿è¡æ¥è¯¢ï¼è¿æ ·çæ¥è¯¢ææ¬åæ¶é´ä¸å®ä¹ä¼æ´é¿ï¼èä¸å¦ææå页çéæ±çè¯ï¼ä¹æ æ³æ»¡è¶³ããã
æè§è¿ä¸¤ä¸ªæ¹æ³é½ä¸æ¯å¤ªå¥½ï¼åä½å°ä¼ä¼´ï¼æ没ææ´å¥½çæ¹æ³å¢ï¼
ä½è ï¼åä»å¥¹ä»å®
é¾æ¥ï¼https://juejin.cn/post/7169567387527282701