使用 MySQL 事件“停用”MySQL账户
我设置了一个数据库,表中的一列是“状态”(活动、非活动、锁定)。我希望事件将 NOW() 与“pdate”列的值(这是一个时间戳)进行比较,如果超过 30 天,则将“status”的值更新为“inactive”。
我写了以下内容,但出现了一些语法错误:当然,
CREATE EVENT `expireAccounts_oldPwd` ON SCHEDULE EVERY DAY
DO
USE databasename;
SELECT pdate FROM tablename WHERE status = "active";
FOR EACH ( ROW IN tablename WHERE( ( SELECT DATEDIFF(NOW(),pdate) AS age ) > 30 ) ) {
UPDATE tablename SET status = "inactive";
};
ERROR 1064 (42000): You have an error in your SQL syntax near 'USE databasename' at line 2
ERROR 1064 (42000): You have an error in your SQL syntax near 'FOR EACH ROW IN "tablename" WHERE( ( SELECT DATEDIFF(NOW(),"pdate") AS age )' at line 4
ERROR 1064 (42000): You have an error in your SQL syntax near '}' at line 6
“databasename”被替换为实际数据库的名称,“tablename”被替换为实际表的名称。 现在至少它正在做一些事情:
+---------------------+
| pdate |
+---------------------+
| 2011-08-11 18:01:02 |
| 2011-08-11 18:03:31 |
+---------------------+
2 rows in set (0.00 sec)
如果我没有在第 2 行包含 USE databasename;
,我就不会得到任何输出。
最终代码:
USE databasename;
DELIMITER %
CREATE EVENT eventname
ON SCHEDULE EVERY 1 DAY
DO UPDATE tablename SET status = "inactive" WHERE status = "inactive" AND DATEDIFF(NOW(), columnname) > 30);
%
我没有意识到事件是特定于数据库的(因此创建事件时必须位于数据库中)。
谢谢大家!
I setup a database and one of the columns in a table is "status" (active,inactive,locked). I want the event to compare NOW() to the value of column "pdate" (which is a timestamp), and if greater than 30 days, update the value of "status" to "inactive".
I wrote the following, but I get a few syntax errors :s
CREATE EVENT `expireAccounts_oldPwd` ON SCHEDULE EVERY DAY
DO
USE databasename;
SELECT pdate FROM tablename WHERE status = "active";
FOR EACH ( ROW IN tablename WHERE( ( SELECT DATEDIFF(NOW(),pdate) AS age ) > 30 ) ) {
UPDATE tablename SET status = "inactive";
};
ERROR 1064 (42000): You have an error in your SQL syntax near 'USE databasename' at line 2
ERROR 1064 (42000): You have an error in your SQL syntax near 'FOR EACH ROW IN "tablename" WHERE( ( SELECT DATEDIFF(NOW(),"pdate") AS age )' at line 4
ERROR 1064 (42000): You have an error in your SQL syntax near '}' at line 6
of course 'databasename' was replaced the actual database's name and 'tablename' the actual table's name.
Now at least it's doing something:
+---------------------+
| pdate |
+---------------------+
| 2011-08-11 18:01:02 |
| 2011-08-11 18:03:31 |
+---------------------+
2 rows in set (0.00 sec)
If I don't included USE databasename;
on line 2, I get no output.
FINAL CODE:
USE databasename;
DELIMITER %
CREATE EVENT eventname
ON SCHEDULE EVERY 1 DAY
DO UPDATE tablename SET status = "inactive" WHERE status = "inactive" AND DATEDIFF(NOW(), columnname) > 30);
%
I didn't realize events were database-specific (so you have to be in the database when you create it).
Thanks all!
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(3)
第一步是使用有效的 SQL。
总是会导致错误,因为列名不应该用引号引起来。如果您将表名括在任何内容中,那么它将是反引号 (`)。
您在其余查询中也遇到同样的问题。
First step's going to be using valid SQL.
will always cause an error, because column names shouldn't be in quotes. If you enclose a table name in anything, it'd be backticks (`).
You have the same problem in the rest of your query.
除了明显的语法问题之外,还有两个特定于事件的事情:
您的事件在哪里结束?您需要将其包含在 BEGIN/END 块中(与存储过程相同)。
定义事件时需要切换DELIMITER(与定义存储过程时相同)。
http://dev 末尾有两个相关示例。 mysql.com/doc/refman/5.1/en/create-event.html。
更新:
另请检查http: //dev.mysql.com/doc/refman/5.1/en/stored-routines-syntax.html 用于存储过程和事件中允许的 SQL 语句。不允许
使用
。还有一个更新:
建议首先尝试让 SQL 正常工作,而不是将其放入事件中。修复语句使其正常工作后,尝试用它创建一个存储过程。当您使存储过程正常工作时,您可以将其替换为事件。这样,解决其余问题就会容易得多(例如第一个 SELECT 的输出应该去哪里?等等)。
Two event specific things apart from the obvious syntax problems:
Where does your event end? You need to enclose it in a BEGIN/END block (the same way as a stored procedure).
You need to switch the DELIMITER when defining an event (the same way as when you define a stored procedure).
There are two relevant examples at the end of http://dev.mysql.com/doc/refman/5.1/en/create-event.html.
Update:
Also check http://dev.mysql.com/doc/refman/5.1/en/stored-routines-syntax.html for SQL statements which are permitted in stored procedures and events.
USE
is not permitted.One more update:
It would be advisable to first try to get your SQL working without putting it in a event. After you have fixed your statements so that they work, try creating a stored procedure out of it. When you get the stored procedure working, you can replace it with an event. This way it will be much easier to sort out the rest of the problems (such as where is the output of the first
SELECT
supposed to go? etc.).您是否有理由不能使用这样的查询?
我什至从未在 MySQL 中见过 FOR EACH...
Is there a reason you can't use a query like this one?
I've never even seen FOR EACH in MySQL...