Detailed explanation of how MYSQL implements the function of continuously signing in and starting from scratch for one day (picture)-Mysql Tutorial-php.cn

Home

Database

Detailed explanation of how MYSQL implements the function of continuously signing in and starting from scratch for one day (picture)

黄舟

May 28, 2017 am 09:42 AM

mysql

This article mainly introduces MYSQL to realize the continuous sign-in function and start from scratch after one day. It is very good and has reference value. Friends who need it can refer to it

1 , create 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

Copy after login

2, insert test data, check-in time is from 5.21 to 6.5, you can write live, but you need to write stored procedure, I compare Lazy, 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;);

Copy after login

3,QueryContinuous 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

Copy after login

Unbroken data

Delete the data on the 23rd, count from the 24th, and sign for three consecutive days

# #

The above is the detailed content of Detailed explanation of how MYSQL implements the function of continuously signing in and starting from scratch for one day (picture). For more information, please follow other related articles on the PHP Chinese website!

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

Hot AI Tools

Undresser.AI Undress

AI-powered app for creating realistic nude photos

AI Clothes Remover

Online AI tool for removing clothes from photos.

Undress AI Tool

Undress images for free

Clothoff.io

AI clothes remover

Video Face Swap

Swap faces in any video effortlessly with our completely free AI face swap tool!

Hot Article

How to fix KB5055612 fails to install in Windows 10?

4 weeks ago By DDD

Roblox: Bubble Gum Simulator Infinity - How To Get And Use Royal Keys

4 weeks ago By 尊渡假赌尊渡假赌尊渡假赌

Roblox: Grow A Garden - Complete Mutation Guide

3 weeks ago By DDD

Nordhold: Fusion System, Explained

4 weeks ago By 尊渡假赌尊渡假赌尊渡假赌

Mandragora: Whispers Of The Witch Tree - How To Unlock The Grappling Hook

3 weeks ago By 尊渡假赌尊渡假赌尊渡假赌

Hot Tools

Notepad++7.3.1

Easy-to-use and free code editor

SublimeText3 Chinese version

Chinese version, very easy to use

Zend Studio 13.0.1

Powerful PHP integrated development environment

Dreamweaver CS6

Visual web development tools

SublimeText3 Mac version

God-level code editing software (SublimeText3)

Hot Topics

Java Tutorial

1673

CakePHP Tutorial

1429

Laravel Tutorial

1333

PHP Tutorial

1278

C# Tutorial

1257

Related knowledge

Laravel Introduction Example Apr 18, 2025 pm 12:45 PM

Laravel is a PHP framework for easy building of web applications. It provides a range of powerful features including: Installation: Install the Laravel CLI globally with Composer and create applications in the project directory. Routing: Define the relationship between the URL and the handler in routes/web.php. View: Create a view in resources/views to render the application's interface. Database Integration: Provides out-of-the-box integration with databases such as MySQL and uses migration to create and modify tables. Model and Controller: The model represents the database entity and the controller processes HTTP requests.

MySQL and phpMyAdmin: Core Features and Functions Apr 22, 2025 am 12:12 AM

MySQL and phpMyAdmin are powerful database management tools. 1) MySQL is used to create databases and tables, and to execute DML and SQL queries. 2) phpMyAdmin provides an intuitive interface for database management, table structure management, data operations and user permission management.

MySQL vs. Other Programming Languages: A Comparison Apr 19, 2025 am 12:22 AM

Compared with other programming languages, MySQL is mainly used to store and manage data, while other languages such as Python, Java, and C are used for logical processing and application development. MySQL is known for its high performance, scalability and cross-platform support, suitable for data management needs, while other languages have advantages in their respective fields such as data analytics, enterprise applications, and system programming.

Laravel framework installation method Apr 18, 2025 pm 12:54 PM

Article summary: This article provides detailed step-by-step instructions to guide readers on how to easily install the Laravel framework. Laravel is a powerful PHP framework that speeds up the development process of web applications. This tutorial covers the installation process from system requirements to configuring databases and setting up routing. By following these steps, readers can quickly and efficiently lay a solid foundation for their Laravel project.

Explain the purpose of foreign keys in MySQL. Apr 25, 2025 am 12:17 AM

In MySQL, the function of foreign keys is to establish the relationship between tables and ensure the consistency and integrity of the data. Foreign keys maintain the effectiveness of data through reference integrity checks and cascading operations. Pay attention to performance optimization and avoid common errors when using them.

Compare and contrast MySQL and MariaDB. Apr 26, 2025 am 12:08 AM

The main difference between MySQL and MariaDB is performance, functionality and license: 1. MySQL is developed by Oracle, and MariaDB is its fork. 2. MariaDB may perform better in high load environments. 3.MariaDB provides more storage engines and functions. 4.MySQL adopts a dual license, and MariaDB is completely open source. The existing infrastructure, performance requirements, functional requirements and license costs should be taken into account when choosing.

SQL vs. MySQL: Clarifying the Relationship Between the Two Apr 24, 2025 am 12:02 AM

SQL is a standard language for managing relational databases, while MySQL is a database management system that uses SQL. SQL defines ways to interact with a database, including CRUD operations, while MySQL implements the SQL standard and provides additional features such as stored procedures and triggers.

MySQL: The Database, phpMyAdmin: The Management Interface Apr 29, 2025 am 12:44 AM

MySQL and phpMyAdmin can be effectively managed through the following steps: 1. Create and delete database: Just click in phpMyAdmin to complete. 2. Manage tables: You can create tables, modify structures, and add indexes. 3. Data operation: Supports inserting, updating, deleting data and executing SQL queries. 4. Import and export data: Supports SQL, CSV, XML and other formats. 5. Optimization and monitoring: Use the OPTIMIZETABLE command to optimize tables and use query analyzers and monitoring tools to solve performance problems.

See all articles