php 将 mySQL 查询作为 cron 作业运行？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21196613/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Run a mySQL query as a cron job?
提问by Paolo
I would like to purge my SQL database from all entires older than 1 week, and I'd like to do it nightly. So, I'm going to set up a cron job. How do I query mySQL without having to enter my password manually every time?
我想从所有超过 1 周的数据中清除我的 SQL 数据库，并且我想每晚执行一次。所以，我要建立一个 cron 工作。如何查询 mySQL 而不必每次都手动输入密码？
The query in PHP is as follows:
PHP中的查询如下：
mysql_query("DELETE FROM tbl_message WHERE DATEDIFF( NOW( ) , timestamp ) >=7");
Is there a way to run this as a shell script? If not, is there a way of making cron run a php file?
有没有办法将它作为 shell 脚本运行？如果没有，有没有办法让 cron 运行一个 php 文件？
回答by fancyPants
I personally find it easier use MySQL event scheduler than cron.
我个人发现使用 MySQL 事件调度程序比使用 cron 更容易。
Enable it with
启用它
SET GLOBAL event_scheduler = ON;
and create an event like this:
并创建一个这样的事件：
CREATE EVENT name_of_event
ON SCHEDULE EVERY 1 DAY
STARTS '2014-01-18 00:00:00'
DO
DELETE FROM tbl_message WHERE DATEDIFF( NOW( ) , timestamp ) >=7;
and that's it.
就是这样。
Read more about the syntax hereand hereis more general information about it.
回答by Andy Day
Try creating a shell script like the one below:
尝试创建一个如下所示的 shell 脚本：
#!/bin/bash
mysql --user=[username] --password=[password] --database=[db name] --execute="DELETE FROM tbl_message WHERE DATEDIFF( NOW( ) , timestamp ) >=7"
You can then add this to the cron
然后您可以将其添加到 cron
回答by miyasudokoro
It depends on what runs cron on your system, but all you have to do to run a php script from cron is to do call the location of the php installation followed by the script location. An example with crontab running every hour:
这取决于在您的系统上运行 cron 的内容，但是从 cron 运行 php 脚本所需要做的就是调用 php 安装的位置，然后是脚本位置。每小时运行一次 crontab 的示例：
# crontab -e
00 * * * * /usr/local/bin/php /home/path/script.php
On my system, I don't even have to put the path to the php installation:
在我的系统上，我什至不必放置 php 安装路径：
00 * * * * php /home/path/script.php
On another note, you should not be using mysql extension because it is deprecated, unless you are using an older installation of php. Read herefor a comparison.
另一方面，您不应该使用 mysql 扩展，因为它已被弃用，除非您使用的是较旧的 php 安装。 阅读此处进行比较。
回答by John Anderson
This was a very handy page as I have a requirement to DELETE records from a mySQL table where the expiry date is < Today.
这是一个非常方便的页面，因为我需要从到期日期为 < 今天的 mySQL 表中删除记录。
I am on a shared host and CRON did not like the suggestion AndrewKDay. it also said (and I agree) that exposing the password in this way could be insecure.
我在共享主机上，CRON 不喜欢 AndrewKDay 的建议。它还说（我同意）以这种方式公开密码可能不安全。
I then tried turning Events ON in phpMyAdmin but again being on a shared host this was a no no. Sorry fancyPants.
然后我尝试在 phpMyAdmin 中打开事件，但再次在共享主机上这是一个否决。对不起花式裤子。
So I turned to embedding the SQL script in a PHP file. I used the example [here][1]
所以我转向将 SQL 脚本嵌入到 PHP 文件中。我用了这个例子[这里][1]
[1]: https://www.w3schools.com/php/php_mysql_create_table.aspstored it in a sub folder somewhere safe and added an empty index.php for good measure. I was then able to test that this PHP file (and my SQL script) was working from the browser URL line.
[1]：https: //www.w3schools.com/php/php_mysql_create_table.asp将它存储在一个安全的子文件夹中，并添加了一个空的 index.php 以进行良好的衡量。然后我能够测试这个 PHP 文件（和我的 SQL 脚本）是否在浏览器 URL 行中工作。
All good so far. On to CRON. Following the above example almost worked. I ended up calling PHP before the path for my *.php file. Otherwise CRON didn't know what to do with the file.
到目前为止一切都很好。到 CRON。按照上面的例子几乎奏效。我最终在 *.php 文件的路径之前调用了 PHP。否则 CRON 不知道如何处理该文件。
my cron is set to run once per day and looks like this, modified for security.
我的 cron 设置为每天运行一次，看起来像这样，为了安全而进行了修改。
00 * * * * php mywebsiteurl.com/wp-content/themes/ForteChildTheme/php/DeleteExpiredAssessment.php
00 * * * * php mywebsiteurl.com/wp-content/themes/ForteChildTheme/php/DeleteExpiredAssessment.php
For the final testing with CRON I initially set it to run each minute and had email alerts turned on. This quickly confirmed that it was running as planned and I changed it back to once per day.
对于 CRON 的最终测试，我最初将其设置为每分钟运行一次并打开电子邮件警报。这很快证实它按计划运行，我将其改回每天一次。
Hope this helps.
希望这可以帮助。
