Home > Database > Mysql Tutorial > MYSQL implementation of continuous sign-in and one-day restart from scratch

MYSQL implementation of continuous sign-in and one-day restart from scratch

小云云
Release: 2018-01-17 10:29:36
Original
3275 people have browsed it

This article mainly introduces the MYSQL implementation of continuous sign-in function. It is very good and has reference value. Friends who need it can refer to it. I hope it can help everyone.

1, create the test table


1

2

3

4

5

6

CREATE TABLE `testsign` (

 `userid` int(5) DEFAULT NULL,

 `username` varchar(20) DEFAULT NULL,

 `signtime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

 `type` int(1) DEFAULT '0' COMMENT '为0表示签到数据,1表示签到日期字典数据'

) ENGINE=InnoDB DEFAULT CHARSET=utf8

Copy after login

2, insert the test data, the check-in time is from 5.21 to 6.5 , you can write it, but I am lazy when it comes to writing stored procedures. The focus should be on the code to get the sign-in data, which is the third point, haha


1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

insert into `testsign` (`userid`, `username`, `signtime`, `type`) values('72164','字典','2017-05-21 00:00:00','1');

insert into `testsign` (`userid`, `username`, `signtime`, `type`) values('72164','字典','2017-05-22 00:00:00','1');

insert into `testsign` (`userid`, `username`, `signtime`, `type`) values('72164','字典','2017-05-23 00:00:00','1');

insert into `testsign` (`userid`, `username`, `signtime`, `type`) values('72164','字典','2017-05-24 00:00:00','1');

insert into `testsign` (`userid`, `username`, `signtime`, `type`) values('72164','字典','2017-05-25 00:00:00','1');

insert into `testsign` (`userid`, `username`, `signtime`, `type`) values('72164','字典','2017-05-26 00:00:00','1');

insert into `testsign` (`userid`, `username`, `signtime`, `type`) values('72164','字典','2017-05-27 00:00:00','1');

insert into `testsign` (`userid`, `username`, `signtime`, `type`) values('72164','字典','2017-05-28 00:00:00','1');

insert into `testsign` (`userid`, `username`, `signtime`, `type`) values('72164','字典','2017-05-29 00:00:00','1');

insert into `testsign` (`userid`, `username`, `signtime`, `type`) values('72164','字典','2017-05-30 00:00:00','1');

insert into `testsign` (`userid`, `username`, `signtime`, `type`) values('72164','字典','2017-05-31 00:00:00','1');

insert into `testsign` (`userid`, `username`, `signtime`, `type`) values('72164','字典','2017-06-01 00:00:00','1');

insert into `testsign` (`userid`, `username`, `signtime`, `type`) values('72164','字典','2017-06-02 00:00:00','1');

insert into `testsign` (`userid`, `username`, `signtime`, `type`) values('72164','字典','2017-06-03 00:00:00','1');

insert into `testsign` (`userid`, `username`, `signtime`, `type`) values('72164','字典','2017-06-04 00:00:00','1');

insert into `testsign` (`userid`, `username`, `signtime`, `type`) values('72164','字典','2017-06-05 00:00:00','1');

insert into `testsign` (`userid`, `username`, `signtime`, `type`) values('800675','吴小双签到数据','2017-05-21 00:00:00','0');

insert into `testsign` (`userid`, `username`, `signtime`, `type`) values('800675','吴小双签到数据','2017-05-22 00:00:00','0');

insert into `testsign` (`userid`, `username`, `signtime`, `type`) values('800675','吴小双签到数据','2017-05-23 00:00:00','0');

insert into `testsign` (`userid`, `username`, `signtime`, `type`) values('800675','吴小双签到数据','2017-05-24 00:00:00','0');

insert into `testsign` (`userid`, `username`, `signtime`, `type`) values('800675','吴小双签到数据','2017-05-25 00:00:00','0');

insert into `testsign` (`userid`, `username`, `signtime`, `type`) values('800675','吴小双签到数据','2017-05-26 00:00:00','0');

Copy after login

3, Query the continuous sign-in data


1

2

3

4

5

6

7

8

9

10

SELECT * FROM testsign WHERE TYPE=0 AND

 DATE_FORMAT(signtime,'%Y%m%d')>(

 SELECT IFNULL(MAX(DATE_FORMAT(signtime,'%Y%m%d')),"20170520") FROM testsign WHERE TYPE=1

 AND DATE_FORMAT(signtime,&#39;%Y%m%d&#39;)<=DATE_ADD(NOW(), INTERVAL -1 DAY)

 AND DATE_FORMAT(signtime,&#39;%Y%m%d&#39;) NOT IN (

    SELECT DATE_FORMAT(signtime,&#39;%Y%m%d&#39;) FROM testsign WHERE TYPE=0 AND userid=800675

    )

 )

 AND DATE_FORMAT(signtime,&#39;%Y%m%d&#39;)<=&#39;20170605&#39;

 AND userid=800675

Copy after login

Unbroken data

Delete the data No. 23 , counting from the 24th, signing for three consecutive days

Have you learned this? Hurry up and try it out.

Related recommendations:

8 recommended articles about continuous sign-in

ThinkPHP continuous sign-in small case

How does mysql count and query continuous sign-ins and cumulative sign-ins

The above is the detailed content of MYSQL implementation of continuous sign-in and one-day restart from scratch. For more information, please follow other related articles on the PHP Chinese website!

Related labels:
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Latest Issues
MySQL stops process
From 1970-01-01 08:00:00
0
0
0
Error when installing mysql on linux
From 1970-01-01 08:00:00
0
0
0
phpstudy cannot start mysql?
From 1970-01-01 08:00:00
0
0
0
Popular Tutorials
More>
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template