MySQL中 and or 查詢的優先級分析
這個可能是容易被忽略的問題,首選我們要清楚:
MySQL中,AND的執行優先級高於OR。也就是說,在沒有小括號()的限制下,總是優先執行AND語句,再執行OR語句。
比如:
select * from table where 條件1 AND 條件2 OR 條件3 等價於 select * from table where ( 條件1 AND 條件2 ) OR 條件3 select * from table where 條件1 AND 條件2 OR 條件3 AND 條件4 等價於 select * from table where ( 條件1 AND 條件2 ) OR ( 條件3 AND 條件4 )
來點事例深入理解下:
測試表數據:
SET NAMES utf8mb4; SET FOREIGN_KEY_CHECKS = 0; -- ---------------------------- -- Table structure for book -- ---------------------------- DROP TABLE IF EXISTS `book`; CREATE TABLE `book` ( `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, `name` varchar(25) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL, `author` varchar(25) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL, `price` decimal(10, 2) DEFAULT NULL, PRIMARY KEY (`id`) USING BTREE ) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic; -- ---------------------------- -- Records of book -- ---------------------------- INSERT INTO `book` VALUES (1, 'PHP', 'mate', 21.00); INSERT INTO `book` VALUES (2, 'JAVA', 'kaven', 23.00); INSERT INTO `book` VALUES (3, 'JAVA高級', 'loose', 45.00); INSERT INTO `book` VALUES (4, 'GO', 'jim', 46.00); INSERT INTO `book` VALUES (5, 'GO設計', 'json', 76.00); INSERT INTO `book` VALUES (6, 'PHP高級編程', 'bate', 67.00); INSERT INTO `book` VALUES (7, 'Python', 'jim', 66.00); INSERT INTO `book` VALUES (8, 'Python設計', 'mali', 54.00); INSERT INTO `book` VALUES (9, 'GO編程', 'kaven', 86.00); INSERT INTO `book` VALUES (11, 'Python3', 'jim', 55.00); SET FOREIGN_KEY_CHECKS = 1;
查詢方式1:
SELECT * FROM book WHERE author='jim' OR author='json' AND name='PHP';
上面的查詢等價於:
SELECT * FROM book WHERE author='jim' OR (author='json' AND name='PHP');
那麼上面的查詢結果就很好理解瞭。
查詢方式2:
SELECT * FROM book WHERE name='PHP' AND author='jim' OR author='json';
上面的查詢等價於:
SELECT * FROM book WHERE (name='PHP' AND author='jim') OR author='json';
查詢方式3:
SELECT * FROM book WHERE name='GO' AND (author='jim' OR author='json');
這個就很好理解瞭。瞭解and or的優先級。這些查詢也就不是呢麼”理解混淆”瞭。
到此這篇關於MySQL中 and or 查詢的優先級分析的文章就介紹到這瞭,更多相關MySQL and or 查詢內容請搜索WalkonNet以前的文章或繼續瀏覽下面的相關文章希望大傢以後多多支持WalkonNet!