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

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

小云云Original: 2018-01-17 10:29:363146browse

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

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 &#39;0&#39; COMMENT &#39;为0表示签到数据，1表示签到日期字典数据&#39; 
) ENGINE=InnoDB DEFAULT CHARSET=utf8

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

insert into `testsign` (`userid`, `username`, `signtime`, `type`) values(&#39;72164&#39;,&#39;字典&#39;,&#39;2017-05-21 00:00:00&#39;,&#39;1&#39;); 
insert into `testsign` (`userid`, `username`, `signtime`, `type`) values(&#39;72164&#39;,&#39;字典&#39;,&#39;2017-05-22 00:00:00&#39;,&#39;1&#39;); 
insert into `testsign` (`userid`, `username`, `signtime`, `type`) values(&#39;72164&#39;,&#39;字典&#39;,&#39;2017-05-23 00:00:00&#39;,&#39;1&#39;); 
insert into `testsign` (`userid`, `username`, `signtime`, `type`) values(&#39;72164&#39;,&#39;字典&#39;,&#39;2017-05-24 00:00:00&#39;,&#39;1&#39;); 
insert into `testsign` (`userid`, `username`, `signtime`, `type`) values(&#39;72164&#39;,&#39;字典&#39;,&#39;2017-05-25 00:00:00&#39;,&#39;1&#39;); 
insert into `testsign` (`userid`, `username`, `signtime`, `type`) values(&#39;72164&#39;,&#39;字典&#39;,&#39;2017-05-26 00:00:00&#39;,&#39;1&#39;); 
insert into `testsign` (`userid`, `username`, `signtime`, `type`) values(&#39;72164&#39;,&#39;字典&#39;,&#39;2017-05-27 00:00:00&#39;,&#39;1&#39;); 
insert into `testsign` (`userid`, `username`, `signtime`, `type`) values(&#39;72164&#39;,&#39;字典&#39;,&#39;2017-05-28 00:00:00&#39;,&#39;1&#39;); 
insert into `testsign` (`userid`, `username`, `signtime`, `type`) values(&#39;72164&#39;,&#39;字典&#39;,&#39;2017-05-29 00:00:00&#39;,&#39;1&#39;); 
insert into `testsign` (`userid`, `username`, `signtime`, `type`) values(&#39;72164&#39;,&#39;字典&#39;,&#39;2017-05-30 00:00:00&#39;,&#39;1&#39;); 
insert into `testsign` (`userid`, `username`, `signtime`, `type`) values(&#39;72164&#39;,&#39;字典&#39;,&#39;2017-05-31 00:00:00&#39;,&#39;1&#39;); 
insert into `testsign` (`userid`, `username`, `signtime`, `type`) values(&#39;72164&#39;,&#39;字典&#39;,&#39;2017-06-01 00:00:00&#39;,&#39;1&#39;); 
insert into `testsign` (`userid`, `username`, `signtime`, `type`) values(&#39;72164&#39;,&#39;字典&#39;,&#39;2017-06-02 00:00:00&#39;,&#39;1&#39;); 
insert into `testsign` (`userid`, `username`, `signtime`, `type`) values(&#39;72164&#39;,&#39;字典&#39;,&#39;2017-06-03 00:00:00&#39;,&#39;1&#39;); 
insert into `testsign` (`userid`, `username`, `signtime`, `type`) values(&#39;72164&#39;,&#39;字典&#39;,&#39;2017-06-04 00:00:00&#39;,&#39;1&#39;); 
insert into `testsign` (`userid`, `username`, `signtime`, `type`) values(&#39;72164&#39;,&#39;字典&#39;,&#39;2017-06-05 00:00:00&#39;,&#39;1&#39;); 
insert into `testsign` (`userid`, `username`, `signtime`, `type`) values(&#39;800675&#39;,&#39;吴小双签到数据&#39;,&#39;2017-05-21 00:00:00&#39;,&#39;0&#39;); 
insert into `testsign` (`userid`, `username`, `signtime`, `type`) values(&#39;800675&#39;,&#39;吴小双签到数据&#39;,&#39;2017-05-22 00:00:00&#39;,&#39;0&#39;); 
insert into `testsign` (`userid`, `username`, `signtime`, `type`) values(&#39;800675&#39;,&#39;吴小双签到数据&#39;,&#39;2017-05-23 00:00:00&#39;,&#39;0&#39;); 
insert into `testsign` (`userid`, `username`, `signtime`, `type`) values(&#39;800675&#39;,&#39;吴小双签到数据&#39;,&#39;2017-05-24 00:00:00&#39;,&#39;0&#39;); 
insert into `testsign` (`userid`, `username`, `signtime`, `type`) values(&#39;800675&#39;,&#39;吴小双签到数据&#39;,&#39;2017-05-25 00:00:00&#39;,&#39;0&#39;); 
insert into `testsign` (`userid`, `username`, `signtime`, `type`) values(&#39;800675&#39;,&#39;吴小双签到数据&#39;,&#39;2017-05-26 00:00:00&#39;,&#39;0&#39;);

3, Query the continuous sign-in data

SELECT * FROM testsign WHERE TYPE=0 AND 
 DATE_FORMAT(signtime,&#39;%Y%m%d&#39;)>( 
 SELECT IFNULL(MAX(DATE_FORMAT(signtime,&#39;%Y%m%d&#39;)),"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

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:

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!

mysql thinkphp

Statement：

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

Previous article：Example of cross-database association query method in mysqlNext article：Example of cross-database association query method in mysql

See more

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

Related articles