MongoDB 踩过的坑之大小写问题

如果数据写入mongoDB的时候, 没办法明确的限制大小写, 那么该字段用作查询条件时, 就可能因为大小写不匹配, 而导致查不出数据. 此时就需要查询时忽略大小写.

方案1: 用正则匹配
该方案比较通用, 但是注意: 正则匹配的效率比较低

db.getCollection("deviceDataCollection_1475652926985990144").find({
    "deviceType": "20",
    "commonDeviceData.commonDeviceExtraData.identityNo": {
        $regex: "^41010519811005161x$",
        $options: "i"
    }
})
----------
"字段名":{"$regex":"^字段$",$options:"i"}

Query query = new Query();
Criteria criteria = new Criteria();
if (StringUtils.isNoBlank(deviceType)) {
    criteria.and("deviceType").is(deviceType);
}
criteria.and("commonDeviceExtraData").exists(true);
criteria.and("identityNo").regex("^" + identityNo.replace("*",".*") + "$", "i");
query.addCriteria(criteria);
query.with(Sort.by(Sort.Direction.DESC, "ts"));
query.limit(1);

DeviceDataEntity entity = mongoTemplate.findOne(query, DeviceDataEntity.class, getCollectionName(tenantId));

方案2: 用 or 做处理
该方案适用于 身份证号等 字母比较明确的场景

Query query = new Query();
Criteria criteria = new Criteria();
if (StringUtils.isNoBlank(deviceType)) {
    criteria.and("deviceType").is(deviceType);
}
criteria.and("commonDeviceExtraData").exists(true);
if (identityNo.contains("x") || identityNo.contains("X")){
    criteria.orOperator(Criteria.where("identityNo").is(identityNo.toLowerCase()),Criteria.where("identityNo").is(identityNo.toUpperCase()));
}else {
    criteria.and("identityNo").is(identityNo);
}
query.addCriteria(criteria);
query.with(Sort.by(Sort.Direction.DESC, "ts"));
query.limit(1);

DeviceDataEntity entity = mongoTemplate.findOne(query, DeviceDataEntity.class, getCollectionName(tenantId));