左側 MongoDB 查詢，右側對應 MySQL SQL。涵蓋基礎 CRUD、條件運算子、投影、排序分頁、陣列/嵌套查詢、正則、MapReduce、$where 等進階特性，助關係型開發者快速上手 MongoDB。
| MongoDB | MySQL | 說明 |
|---|
db.users.find() | SELECT * FROM users | 全表查詢 |
db.users.find({"age": 27}) | SELECT * FROM users WHERE age = 27 | 等值查詢 |
db.users.find({"username": "joe", "age": 27}) | SELECT * FROM users WHERE username="joe" AND age=27 | AND 條件 |
db.users.find({}, {"username": 1, "email": 1}) | SELECT username, email FROM users | 投影 (僅返回指定列) |
db.users.find({}, {"username": 1, "_id": 0}) | SELECT username FROM users | 排除 _id (MongoDB 默認返回 _id) |
| MongoDB | MySQL | 運算子含義 |
|---|
{"age": {"$gte": 18, "$lte": 30}} | WHERE age >= 18 AND age <= 30 | 範圍：$gte≥, $lte≤, $gt>, $lt< |
{"username": {"$ne": "joe"}} | WHERE username <> "joe" | 不等於 |
{"ticket_no": {"$in": [725, 542, 390]}} | WHERE ticket_no IN (725, 542, 390) | 集合包含 |
{"ticket_no": {"$nin": [725, 542, 390]}} | WHERE ticket_no NOT IN (...) | 集合不包含 |
{"$or": [{"ticket_no": 725}, {"winner": true}]} | WHERE ticket_no=725 OR winner=true | 邏輯 OR |
{"id_num": {"$mod": [5, 1]}} | WHERE (id_num % 5) = 1 | 取模運算 |
{"$not": {"age": 27}} | WHERE NOT (age = 27) | 邏輯非 |
| MongoDB | MySQL | 說明 |
|---|
{"username": {"$in": [null], "$exists": true}} | WHERE username IS NULL | 查 NULL 值 (需加 $exists 排除缺失字段) |
{"name": /joey?/i} | WHERE name REGEXP 'joey?' | 正則查詢 (PCRE 兼容) |
{"fruit": {"$all": ["apple", "banana"]}} | WHERE fruit @> ARRAY['apple','banana'] (PG) / JSON_CONTAINS (MySQL 5.7+) | 陣列包含所有元素 |
{"fruit.2": "peach"} | WHERE JSON_EXTRACT(fruit, '$[2]') = 'peach' | 陣列索引訪問 (0-based) |
{"fruit": {"$size": 3}} | WHERE JSON_LENGTH(fruit) = 3 | 陣列長度 |
.findOne(criteria, {"comments": {"$slice": 10}}) | WHERE ... LIMIT 1 + 應用層切片 | 陣列切片 (前 10/後 10/中間) |
