Modify mysql configuration to regularly send blog posts

The php program is a script and will be executed only when triggered by the client. Many website construction processes require timed functions, such as automatic database statistics every night in the early morning. This article describes how to use mysql event scheduling event_scheduler to implement timed blog posts.

Use the wordpress blog data table as an example to explain the principle (wp is familiar to many webmasters ):

Wordpress has a wp_posts table (which stores articles and multimedia files). This table has a field named post_status (Status ). This field has several attributes. publish indicates publishing and draft indicates draft. If you can change draft to publish at a certain point in time, the status of this article will not change from draft to publish!

I found that the wordpress background does not seem to have a scheduled option, because php itself does not support scheduled execution.

What should I do if my blog needs timed sending?

① Set the mysql event scheduler. (Post)

② The post_date field of the manual data table after the draft is set in the background.

Okay, don't worry about wordpress bloggers. Webmasters who really want to do this are a second force. It is much more convenient to use windows live writer...

This site is written in thinkphp and can be released on a regular basis in the background. Php itself does not support scheduled execution, so mysql event scheduler is used.

Next, let's get started ~

① Open the database management panel and execute the following SQL statement.

1 show variables like 'event _ schedning'

　　

 

If event_schedules is set to OFF, the event scheduler is not enabled for your database.

② Enable the event scheduler. Run the following SQL statement.

1 set global event_scheduler = ON

I found a problem during my experiment. If the database is restarted, event_scheduler becomes OFF again. Which of the following is a 365-day database server ~~

③ Modify mysql configuration file mysql. ini and enable event_scheduler by default.

Find the configuration file my. ini in the mysql installation folder and add event_schedld = ON under [mysqld. In this way, the event sheduler will be automatically started when mysql is started.

The mysql configuration file for linux is in the/etc folder, and the my. cnf file (my. ini file for windows ). In linux, use the find command to search.

④ Click to enter the data table that needs to be scheduled, and execute the following SQL statement under this table.

Drop event if exists icaigen_post;
Create event icaigen_post
On schedule every 300 SECOND
Do update sre_posts SET post_status = 0 WHERE id IN (SELECT. id FROM (SELECT tmp. * FROM sre_posts tmp) a WHERE. post_status = 1 AND (. post_time-UNIX_TIMESTAMP () <0 );

Explanation: icaigen_post is the name of the EVENT, and the name is arbitrary. The first line of code indicates that if the event exists in the previous data table, it is deleted.

The third row on schdule every 300 SECOND refers to the statement after which the DO statement is executed every s.

The point is this SQL statement.

1 UPDATE sre_posts SET post_status = 0 WHERE id IN (SELECT. id FROM (SELECT tmp. * FROM sre_posts tmp) a WHERE. post_status = 1 AND (. post_time-UNIX_TIMESTAMP () <0 );

A temporary table is used here, because mysql does not allow you to query a table in a statement and modify the table, so a temporary table is used to store a subset. This question has been specifically described in the cnblog, and I have learned this from it. Refer:

Http://www.cnblogs.com/chy1000/archive/2010/03/02/1676282.html

Post_time is the posting time of a blog post, and UNIX_TIMESTAMP () is the current timestamp. Because the time of my saved blog post is the timestamp, it is compared with the timestamp here. When the current timestamp is greater than the scheduled time (release time), change post_status = 1 (data field meaning, 1 indicates draft, 0 indicates release) to 0.

Because this event statement is executed every 30 seconds, the maximum error of posting time is 30 seconds.

For details about mysql event, refer to this article:

Http://netzp.blog.sohu.com/103852537.html