MySql数据库的自动维护

参考下贴中的几种方法。另征集定时执行mysql数据库任
http://topic.csdn.net/u/20091020/22/c51bb709-6dbc-4fd1-b874-077714ed547e.html

CREATE EVENT myevent
ON SCHEDULE EVERY 1 DAY STARTS '2009-10-24 02:00:00'
DO
delete from t where ptime <date_sub(curdate(),interval 3 day);上面的'2009-10-24 02:00:00'能不能换成'02:00:00'，
版主能不能把event和参数给我简单做个介绍，谢谢！

event对数据库引擎有限制么？

12.1.11. CREATE EVENT Syntax
CREATE
    [DEFINER = { user | CURRENT_USER }]
    EVENT
    [IF NOT EXISTS]
    event_name
    ON SCHEDULE schedule
    [ON COMPLETION [NOT] PRESERVE]
    [ENABLE | DISABLE | DISABLE ON SLAVE]
    [COMMENT 'comment']
    DO sql_statement;schedule:
    AT timestamp [+ INTERVAL interval] ...
  | EVERY interval
    [STARTS timestamp [+ INTERVAL interval] ...]
    [ENDS timestamp [+ INTERVAL interval] ...]interval:
    quantity {YEAR | QUARTER | MONTH | DAY | HOUR | MINUTE |
              WEEK | SECOND | YEAR_MONTH | DAY_HOUR | DAY_MINUTE |
              DAY_SECOND | HOUR_MINUTE | HOUR_SECOND | MINUTE_SECOND}This statement creates and schedules a new event. It requires the EVENT privilege for the schema in which the event is to be created. The minimum requirements for a valid CREATE EVENT statement are as follows: The keywords CREATE EVENT plus an event name, which uniquely identifies the event in the current schema. (Prior to MySQL 5.1.12, the event name needed to be unique only among events created by the same user on a given database.) An ON SCHEDULE clause, which determines when and how often the event executes. A DO clause, which contains the SQL statement to be executed by an event. This is an example of a minimal CREATE EVENT statement: CREATE EVENT myevent
    ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 HOUR
    DO
      UPDATE myschema.mytable SET mycol = mycol + 1;The previous statement creates an event named myevent. This event executes once — one hour following its creation — by running an SQL statement that increments the value of the myschema.mytable table's mycol column by 1. The event_name must be a valid MySQL identifier with a maximum length of 64 characters. It may be delimited using back ticks, and may be qualified with the name of a database schema. An event is associated with both a MySQL user (the definer) and a schema, and its name must be unique among names of events within that schema. In general, the rules governing event names are the same as those for names of stored routines. See Section 8.2, “Schema Object Names”. If no schema is indicated as part of event_name, the default (current) schema is assumed. Note
MySQL uses case-insensitive comparisons when checking for the uniqueness of event names. This means that, for example, you cannot have two events named myevent and MyEvent in the same database schema. The DEFINER clause specifies the MySQL account to be used when checking access privileges at event execution time. If a user value is given, it should be a MySQL account in 'user_name'@'host_name' format (the same format used in the GRANT statement). The user_name and host_name values both are required. The definer can also be given as CURRENT_USER or CURRENT_USER(). The default DEFINER value is the user who executes the CREATE EVENT statement. (This is the same as DEFINER = CURRENT_USER.) If you specify the DEFINER clause, these rules determine the legal DEFINER user values: If you do not have the SUPER privilege, the only legal user value is your own account, either specified literally or by using CURRENT_USER. You cannot set the definer to some other account. If you have the SUPER privilege, you can specify any syntactically legal account name. If the account does not actually exist, a warning is generated. Although it is possible to create events with a non-existent DEFINER value, an error occurs if the event executes with definer privileges but the definer does not exist at execution time. The DEFINER clause was added in MySQL 5.1.17. (Prior to MySQL 5.1.12, it was possible for two different users to create different events having the same name on the same database schema.) Within an event, the CURRENT_USER() function returns the account used to check privileges at event execution time, which is the DEFINER user. For information about user auditing within events, see Section 5.5.9, “Auditing MySQL Account Activity”. IF NOT EXISTS has the same meaning for CREATE EVENT as for CREATE TABLE: If an event named event_name already exists in the same schema, no action is taken, and no error results. (However, a warning is generated in such cases.) The ON SCHEDULE clause determines when, how often, and for how long the sql_statement defined for the event repeats. This clause takes one of two forms: AT timestamp is used for a one-time event. It specifies that the event executes one time only at the date and time given by timestamp, which must include both the date and time, or must be an expression that resolves to a datetime value. You may use a value of either the DATETIME or TIMESTAMP type for this purpose. If the date is in the past, a warning occurs, as shown here: mysql> SELECT NOW();
+---------------------+
| NOW()               |
+---------------------+
| 2006-02-10 23:59:01 |
+---------------------+
1 row in set (0.04 sec)mysql> CREATE EVENT e_totals
    ->     ON SCHEDULE AT '2006-02-10 23:59:00'
    ->     DO INSERT INTO test.totals VALUES (NOW());
Query OK, 0 rows affected, 1 warning (0.00 sec)mysql> SHOW WARNINGS\G
*************************** 1. row ***************************
  Level: Note
   Code: 1588
Message: Event execution time is in the past and ON COMPLETION NOT
         PRESERVE is set. The event was dropped immediately after
         creation.CREATE EVENT statements which are themselves invalid — for whatever reason — fail with an error. You may use CURRENT_TIMESTAMP to specify the current date and time. In such a case, the event acts as soon as it is created. To create an event which occurs at some point in the future relative to the current date and time — such as that expressed by the phrase “three weeks from now” — you can use the optional clause + INTERVAL interval. The interval portion consists of two parts, a quantity and a unit of time, and follows the same syntax rules that govern intervals used in the DATE_ADD() function (see Section 11.6, “Date and Time Functions”. The units keywords are also the same, except that you cannot use any units involving microseconds when defining an event. With some interval types, complex time units may be used. For example, “two minutes and ten seconds” can be expressed as + INTERVAL '2:10' MINUTE_SECOND. You can also combine intervals. For example, AT CURRENT_TIMESTAMP + INTERVAL 3 WEEK + INTERVAL 2 DAY is equivalent to “three weeks and two days from now”. Each portion of such a clause must begin with + INTERVAL. To repeat actions at a regular interval, use an EVERY clause. The EVERY keyword is followed by an interval as described in the previous dicussion of the AT keyword. (+ INTERVAL is not used with EVERY.) For example, EVERY 6 WEEK means “every six weeks”. Although + INTERVAL clauses are not allowed in an EVERY clause, you can use the same complex time units allowed in a + INTERVAL. An EVERY clause may also contain an optional STARTS clause. STARTS is followed by a timestamp value which indicates when the action should begin repeating, and may also use + INTERVAL interval in order to specify an amount of time “from now”. For example, EVERY 3 MONTH STARTS CURRENT_TIMESTAMP + INTERVAL 1 WEEK means “every three months, beginning one week from now”. Similarly, you can express “every two weeks, beginning six hours and fifteen minutes from now” as EVERY 2 WEEK STARTS CURRENT_TIMESTAMP + INTERVAL '6:15' HOUR_MINUTE. Not specifying STARTS is the same as using STARTS CURRENT_TIMESTAMP — that is, the action specified for the event begins repeating immediately upon creation of the event. An EVERY clause may also contain an optional ENDS clause. The ENDS keyword is followed by a timestamp value which tells MySQL when the event should stop repeating. You may also use + INTERVAL interval with ENDS; for instance, EVERY 12 HOUR STARTS CURRENT_TIMESTAMP + INTERVAL 30 MINUTE ENDS CURRENT_TIMESTAMP + INTERVAL 4 WEEK is equivalent to “every twelve hours, beginning thirty minutes from now, and ending four weeks from now”. Not using ENDS means that the event continues executing indefinitely. ENDS supports the same syntax for complex time units as STARTS does. You may use STARTS, ENDS, both, or neither in an EVERY clause.

参考一下英文版的帮助文档中的关于EVENT的介绍。MySQL官方文档 http://dev.mysql.com/doc/refman/5.1/zh/index.html

创建一个EVENT.
DELIMITER //
DROP EVENT IF EXISTS delOldData//
CREATE EVENT `delOldData`
ON SCHEDULE EVERY 1 DAY
STARTS '2010-03-01 00:00:01'
ENDS '2020-12-31 00:00:01'
ON COMPLETION NOT PRESERVE
ENABLE
DO
BEGIN
DELETE FROM your_table WHERE column_date<=DATE_ADD(CURDATE(),INTERVAL -7 DAY);
END;
//
DELIMITER ;每晚00：00：01删除7天前数据。

调试易

MySql数据库的自动维护

解决方案 »