Shulou(Shulou.com)05/31 Report--

This article is about how to use digital auxiliary tables to record data in mysql. The editor thinks it is very practical, so share it with you as a reference and follow the editor to have a look.

Structure:

Create table user_baseinfo (

Userid bigint not null primary key

Createtime timestamp

);

Create table user_daily_log (

Userid bigint

Dt date

Somevalue int

Primary key (userid,dt)

);

The test data are as follows:

INSERT INTO `userid` baseinfo` (`userid`, `createtime`) VALUES (100heroine 2017-09-18 0000purve00')

INSERT INTO `userid` baseinfo` (`userid`, `createtime`) VALUES (200Jing Jing 2017-09-23 00 VALUES 00')

INSERT INTO `user_daily_ log` (`userid`, `dt`, `somatoe`) VALUES

INSERT INTO `user_daily_ log` (`userid`, `dt`, `somatoe`) VALUES

INSERT INTO `user_daily_ log` (`userid`, `dt`, `somatoe`) VALUES

INSERT INTO `user_daily_ log` (`userid`, `dt`, `somatoe`) VALUES

INSERT INTO `user_daily_ log` (`userid`, `dt`, `somatoe`) VALUES

Today's date: 2017-09-26

User_baseinfo is a table of basic user information, including user ID and creation time.

User_daily_log is a daily statistical table of users, which requires users to have a statistical data every day since it was created, even if the statistical value is 0.

Problem: the program has bug, and many users with a statistical value of 0 do not create related records. Now we need to re-record this part of the data.

Resolve:

Make up the relevant data

Insert into user_daily_log (userid,dt,somevalue)

Select t1.useriddiary t1.ctimejin0 from (

Select u.userid.u.createtimekeeper interval (id-1) day ctime from

User_baseinfo u inner join nums n on (datediff (now (), u.createtime) > = n.id)

) T1

Left join user_daily_log log on (t1.userid=log.userid and t1.ctime=log.dt)

Where log.userid is null

Thank you for reading! About "how to use the digital auxiliary table to record data in mysql" this article is shared here, I hope the above content can be of some help to you, so that you can learn more knowledge, if you think the article is good, you can share it out for more people to see it!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.