日常开发mysql 和 mogo❣️示例代码
一、mysql
1.1 建表语句(含索引)
CREATE TABLE `admin_bu` (
`id` BIGINT(20) NOT NULL AUTO_INCREMENT,
`admin_bu_open_id` VARCHAR(50) NOT NULL DEFAULT '' COMMENT '分布式id',
`name` VARCHAR(200) NOT NULL DEFAULT '' COMMENT '事业部名称',
`memo` VARCHAR(200) NOT NULL DEFAULT '' COMMENT '备注',
`status` TINYINT(4) NOT NULL DEFAULT '0' COMMENT '状态1有效0无效',
`create_date` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
`update_date` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '更新时间',
`create_by` VARCHAR(50) NOT NULL DEFAULT '' COMMENT '创建人',
`update_by` VARCHAR(50) NOT NULL DEFAULT '' COMMENT '更新人',
PRIMARY KEY (`id`) USING BTREE,
KEY `idx_admin_bu_open_id` (`admin_bu_open_id`)
) ENGINE=INNODB AUTO_INCREMENT=29 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='事业部表';
根据表生成代码在 ota-service
1.2 mysql把一个字段的值复制到另一个字段
update mytable set colA = colB where ...
UPDATE admin_branch SET admin_branch_open_id = branch_id;
UPDATE car_dim_branch SET admin_branch_open_id = branch_id WHERE branch_id =49;
1.3 查询表某个字段重复数据
select * from asset_warning_record
group by zrw_id having count(1)>1
1.4 表新增[修改]【删除】字段且设置默认值
ALTER TABLE car_driver_daily_health_survey ADD health_code_photo VARCHAR(255) DEFAULT '' AFTER body_temperature;
ALTER TABLE REPORT_DOWNLOAD_MANAGE ADD `early_alarm_id` VARCHAR(255) DEFAULT '' COMMENT '场景化预警事件ID';
-- 修改字段
ALTER TABLE asset_wms_license MODIFY COLUMN operating_type TINYINT NOT NULL DEFAULT 0 COMMENT '营运性质（1：营运2：非营运）';
ALTER TABLE asset_wms_license MODIFY COLUMN first_regist_time VARCHAR(30) NOT NULL DEFAULT '' COMMENT '首次注册日期';
-- 删除字段
ALTER TABLE VEHICLE_SIM DROP column simstatus;
-- 删除外键依赖
ALTER TABLE `OTA_TASK` DROP FOREIGN KEY `FK_OTA_TASK_MODEL_ID`;
1.5 查询sql执行EXPLAIN
EXPLAIN + sql
例如：EXPLAIN SELECT * FROM sso_user WHERE sso_user.status = 1
关注点：
● key：显示MySQL实际决定使用的键（索引）。如果没有选择索引，键是NULL
● rows: 显示MySQL认为它执行查询时必须检查的行数
● type结果
system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL
all:全表扫描
一般来说，得保证查询至少达到range级别，最好能达到ref，type出现index和all时，表示走的是全表扫描没有走索引，效率低下，这时需要对sql进行调优。
参考：
二、mongo
2.1 客户端
// 查询字段不为空
db.ev_remote_send_cmd_history.find({"feedbackSignals":{"$ne":null}});
db.regulationRule.find({cityName:{$in:["芜湖","绍兴"]}})
db.dtc_fault_upload_data.find({"uploadTime":{"$gt":ISODate("2021-09-28T00:00:22.474+08:00")}})
db.dtc_fault_upload_data.find({"uploadTime":{"$lt":ISODate("2021-09-28T00:00:22.474+08:00")}})
db.dtc_fault_upload_data.find({})
db.dtc_fault_upload_data.find({"$and":[{"uploadTime":{"$gt":ISODate("2021-09-26T00:15:22.474+08:00")}},
{"uploadTime":{"$lt":ISODate("2021-09-28T00:00:22.474+08:00")}}]})
db.dtc_fault_upload_data.find({"vin":"vin123"})
db.dtc_fault_upload_data.find({"$and":[{"uploadTime":{"$gt":1632585622000}},
{"uploadTime":{"$lt":1632672022000}}]})
倒叙排列
db.dtc_fault_upload_data.find({"vin":"JH11TESTVINYD0003"}).sort({"uploadTime":-1})
.limit(5)
2.2 修改代码
// 更新
public void updateModelName(String id,String modelName){
Query query = new Query();
query.addCriteria(Criteria.where("_id").is(id));
Update update = new Update();
update.set("modelName", modelName);
mongoTemplate.updateFirst(query,update, Constant.TERMINAL_LOG);
}
// 批量修改
Query query = new Query(new Criteria().and("platformUniqueCode").nin(platformUniqueCodeList));
Update update = new Update();
update.set("status", status);
update.set("updateDate", new Date());
mongoTemplate.updateMulti(query, update, RegulationRecord.class);
2.3 查询代码
// 条件查询
// 分页
public List getTerminalLogList(String logType, String vin, Page page) {
Query query = new Query();
Criteria criteria = new Criteria();
if (StringUtils.isNotBlank(logType)) {
if(logType.equals(LogType.CAN.getKey())){
criteria.and("logType").is(LogType.CAN.getValue());
}else if(logType.equals(LogType.TBOX.getKey())){
criteria.and("logType").is(LogType.TBOX.getValue());
}
}
if (StringUtils.isNotBlank(vin)) {
Pattern pattern = Pattern.compile("^.*" + vin + ".*$", Pattern.CASE_INSENSITIVE);
criteria.andOperator(Criteria.where("vin").regex(pattern)) ;
}
Sort sort = new Sort(Sort.Direction.DESC, "createdTime");
query.with(sort);
query.addCriteria(criteria);
Long totalCount = null;
if (page != null) {
query.with(new PageRequest(page.getPageIndex().intValue() - 1, page.getPageSize().intValue()));
totalCount = mongoTemplate.count(query, TerminalLog.class, TerminalLogConstants.TERMINAL_LOG);
}
List terminalLogs = mongoTemplate.find(query, TerminalLog.class, TerminalLogConstants.TERMINAL_LOG);
for (TerminalLog terminalLog : terminalLogs) {
terminalLog.setDays(countDays(terminalLog.getSendTime()));
}
page.setTotalCount(totalCount);
return terminalLogs;
}
// 时间条件
if (StringUtils.isNotBlank(violationRecordPageListDTO.getBeginDate()) && StringUtils.isNotBlank(violationRecordPageListDTO.getEndDate())) {
criteria.andOperator(
Criteria.where("violationDate").gte(beginDate),
Criteria.where("violationDate").lte(endDate));
} else if(StringUtils.isBlank(violationRecordPageListDTO.getBeginDate()) && StringUtils.isNotBlank(violationRecordPageListDTO.getEndDate())){
criteria.and("violationDate").lte(endDate);
} else if(StringUtils.isNotBlank(violationRecordPageListDTO.getBeginDate()) && StringUtils.isBlank(violationRecordPageListDTO.getEndDate())){
criteria.and("violationDate").gte(beginDate);
}
2.4 索引相关
创建索引
db.col.createIndex({"title":1})
db.集合名.createIndex( {"字段名": 1 },{"name":'idx_字段名'})
注意在 3.0.0 版本前创建索引方法为 db.collection.ensureIndex()，之后的版本使用了 db.collection.createIndex() 方法，ensureIndex() 还能用，但只是 createIndex() 的别名。
SHOW INDEX FROM admin_bu;
ALTER TABLE admin_bu ADD INDEX idx_admin_bu_open_id (admin_bu_open_id) ;
// 删除索引
alter table admin_bu drop index idx_admin_bu_open_id;
SHOW INDEX FROM car_dim_branch;
DROP INDEX idx_admin_branch_open_id ON car_dim_branch;
ALTER TABLE car_dim_branch ADD UNIQUE INDEX `uk_admin_branch_open_id` (admin_branch_open_id);