【Mongo】Mongo表结构设计以及查询示例

Mongo SQL:

 //建表语句
 db.createCollection("pro_alter_info")


//插入文档语句
db.pro_alter_info.insert(
    {
        "alterTime": "2022-03-27 16:43:09",
        "alterType": 1,
        "proId": 2203271021000001,
        "venderId": 1,
        "alterId": 22032710000000001,
        "storeId": -1,
        "sku": -1,
        "proReward":-1, 
        "skuSnapshoot": [121267, 121278],
        "storeSnapshoot": [112641, 106499]
    }
)


db.pro_alter_info.find()

db.pro_alter_info.find({"_id":ObjectId("622723944a1756d2dca1a6c1")})
 
//查找 alterId为空的无效数据集   $exists 无法利用到索引， 但 $NE 和 $in 可以用上索引
db.pro_alter_info.find({alterId:{$in:[null]}})

//删除 alterId为空的记录  remove仅返回删除影响行数 和是否删除成功
db.pro_alter_info.remove({alterId:{$in:[null]}});

//多条件查询
db.pro_alter_info.find({proId:{$in:[2203271021000001]},sku: -1 ,alertUserName:"yingxiao" })

//时间查询1 查询 > 2022-03-27 14:00:00  差8个时区
db.pro_alter_info.find({alterTime:{$gt:ISODate("2022-03-27T06:00:00Z")}});


//时间查询2 范围查询   2022-03-27 14:00:00   < alterTime < 2022-03-27 18:00:00
db.pro_alter_info.find({alterTime:{$gt:ISODate("2022-03-27T06:00:00Z") , $lt:ISODate("2022-03-27T10:00:00Z")}});



//建索引  
db.pro_alter_info.createIndex({"alterId":1});
db.pro_alter_info.createIndex({"proId":1,"alterTime":-1});
db.pro_alter_info.createIndex({"storeId":1,"skuSnapshoot":1,"alterType":1,"proId":1,"alterTime":-1});
db.pro_alter_info.createIndex({"storeSnapshoot":1,"sku":1,"alterType":1,"proId":1,"alterTime":-1});
db.pro_alter_info.createIndex({"storeId":1,"alterType":1,"proId":1,"alterTime":-1});
db.pro_alter_info.createIndex({"storeSnapshoot":1,"alterType":1,"proId":1,"alterTime":-1});
//过期索引（一年过期）
db.pro_alter_info.createIndex({"alterTime":1},{{ expireAfterSeconds: 31536000 }});

//这个建立不成功，因为cannot index parallel arrays 建立联合索引的字段不能均为数组
db.pro_alter_info.createIndex({"storeSnapshoot":1,"skuSnapshoot":1,"alterType":1,"proId":1,"alterTime":-1})