mysql积累

设置隔离级别

select @@tx_isolation;
set session transaction isolation level read committed; 
set session transaction isolation level Repeatable Read;
1、读不提交(Read Uncommited，RU)
2、读提交(Read commited，RC)
3、可重复读（Repeatable Read，RR）【MySQL 默认的级别】
4、 串行化（SERIALIZABLE）

开启事务加拍他锁

SET AUTOCOMMIT=0;
select * from test.test where id<3 for update
commit;

查询所有平类成绩门店访问量前2

CREATE TABLE `score` (
  `scoreid` int(11) NOT NULL AUTO_INCREMENT,
  `sourceid` int(11) DEFAULT NULL,
  `userid` int(11) DEFAULT NULL,
  `score` int(11) DEFAULT NULL,
  PRIMARY KEY (`scoreid`)
) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=utf8;

insert  into `score`(`scoreid`,`sourceid`,`userid`,`score`) values (1,1,1,60),(2,2,1,80),(3,3,1,95),(4,1,2,75),(5,2,2,85),(6,3,2,90),(7,4,1,89),(8,4,2,88),(9,1,3,78),(10,2,3,87),(11,3,3,98),(12,4,3,76),(13,1,4,67),(14,2,4,76),(15,3,4,88),(16,4,4,77);

SELECT * FROM `score` a where(select COUNT(1) FROM  score B WHERE B.userid=A.userid AND B.score >A.score)<=1 order  by userid,score DESC ;

或者参控

DROP TABLE IF EXISTS `score`;
 
CREATE TABLE `score` (
  `s_id` varchar(20) NOT NULL DEFAULT '',
  `c_id` varchar(20) NOT NULL DEFAULT '',
  `s_score` int(3) DEFAULT NULL,
  PRIMARY KEY (`s_id`,`c_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 
/*Data for the table `score` */
 
insert  into `score`(`s_id`,`c_id`,`s_score`) values ('01','01',80),('01','02',90),('01','03',99),('02','01',70),('02','02',60),('02','03',80),('03','01',80),('03','02',80),('03','03',80),('04','01',50),('04','02',30),('04','03',20),('05','01',76),('05','02',87),('06','01',31),('06','03',34),('07','02',89),('07','03',98);
SELECT s1.* FROM score s1 WHERE
(
SELECT COUNT(1) FROM score s2 WHERE
s1.c_id=s2.c_id AND s2.s_score>=s1.s_score
)<=2
ORDER BY s1.c_id,s1.s_score DESC;
https://bbs.csdn.net/topics/392323240
--------------------- 
版权声明：本文为CSDN博主「montaellis」的原创文章，遵循CC 4.0 by-sa版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/pkgray/article/details/86190707

Mysql获取系统时间还有时间得转换本地方法
select now() from DUAL;
select sysdate() from DUAL;

select date_format(now(),'%Y-%m-%d') from dual;

MYSQL可以通过多个if条件来赛选查询条件，并且拼接在一起
select IFNULL(SUM(if(final_trans_status = 'Completed',1,0)) ,0)AS completedCount,
    IFNULL(SUM(if(final_trans_status = 'Cancelled',1,0)) ,0)AS cancelledCount,
    IFNULL(SUM(if(final_trans_status = 'Pending',1,0)) ,0)AS pendingCount
    from aaa where last_update_date  BETWEEN STR_TO_DATE('2020-04-27',"%Y-%m-%d") and STR_TO_DATE('2020-04-28',"%Y-%m-%d") 
    效果类似于
    select count(*),final_trans_status
    from AAA where last_update_date  BETWEEN STR_TO_DATE('2020-04-27',"%Y-%m-%d") and STR_TO_DATE('2020-04-28',"%Y-%m-%d") GROUP BY final_trans_status;		

https://blog.csdn.net/finalkof1983/article/details/88355314
https://cloud.tencent.com/developer/article/1653643  mysql得 oline ddl

https://www.cnblogs.com/shawn-sun/p/14444258.html
https://blog.csdn.net/weixin_34344403/article/details/85996731?utm_medium=distribute.pc_relevant.none-task-blog-2~default~baidujs_title~default-4.no_search_link&spm=1001.2101.3001.4242.4  mvcc間隙鎖
```

https://zhuanlan.zhihu.com/p/370770835

https://segmentfault.com/a/1190000016566788 理解幻读

https://www.cnblogs.com/ilifeilong/p/7257748.html  --mysql checkpoint机制

https://mp.weixin.qq.com/s/J3kCOJwyv2nzvI0_X0tlnA --汇总