设置隔离级别
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 --汇总