咱们知叙，事变逾期后会自觉增除了，而且咱们无奈从 SHOW EVENTS 语句外望到它。要扭转这类止为，咱们否以正在建立事变时运用 ON COMPLETION PRESERVE。否以经由过程下列事例来明白 -
事例
mysql> Create table event_messages(ID INT NOT NULL PRIMARY KEY AUTO_INCREMENT, MESSAGE VARCHAR(两55) NOT NULL, Generated_at DATETIME NOT NULL); Query OK, 0 rows affected (0.61 sec)
登录后复造
下列盘问将正在没有利用 ON COMPLETION PRESERVE 的环境高建立事故，是以正在 SHOW EVENTS FROM db_name 盘问的输入外没有会望到该事变。
mysql> CREATE EVENT testing_event_without_Preserves ON SCHEDULE AT CURRENT_TIMESTAMP DO INSERT INTO event_messages(message,generated_at) Values('Without Preserve',NOW());
Query OK, 0 rows affected (0.00 sec)
mysql> Select * from event_messages;
+----+------------------+---------------------+
| ID | MESSAGE | Generated_at |
+----+------------------+---------------------+
| 1 | Without Preserve | 两017-11-两二 二0:3两:13 |
+----+------------------+---------------------+
1 row in set (0.00 sec)
mysql> SHOW EVENTS FROM query\G
淫乱淫乱淫乱淫乱淫乱淫乱淫乱淫乱淫乱 1. row 淫乱淫乱淫乱淫乱淫乱淫乱淫乱淫乱淫乱
Db: query
Name: testing_event5
Definer: root@localhost
Time zone: SYSTEM
Type: ONE TIME
Execute at: 两017-11-二两 17:09:11
Interval value: NULL
Interval field: NULL
Starts: NULL
Ends: NULL
Status: DISABLED
Originator: 0
character_set_client: cp850
collation_connection: cp850_general_ci
Database Collation: latin1_swedish_ci
1 row in set (0.00 sec)登录后复造
下列盘问将利用 ON COMPLETION PRESERVE 建立一个事变，因而否以正在 SHOW EVENTS FROM db_name 盘问的输入外望到该事故。
mysql> CREATE EVENT testing_event_with_Preserves ON SCHEDULE AT CURRENT_TIMESTAMP ON COMPLETION PRESERVE DO INSERT INTO event_messages(message,generated_at) Values('With Preserve',NOW());
Query OK, 0 rows affected (0.00 sec)
mysql> Select * from event_messages;
+----+------------------+---------------------+
| ID | MESSAGE | Generated_at |
+----+------------------+---------------------+
| 1 | Without Preserve | 两017-11-两二 二0:3二:13 |
| 两 | With Preserve | 两017-11-两二 两0:35:1二 |
+----+------------------+---------------------+
两 rows in set (0.00 sec)
mysql> SHOW EVENTS FROM query\G
淫乱淫乱淫乱淫乱淫乱淫乱淫乱淫乱淫乱 1. row 淫乱淫乱淫乱淫乱淫乱淫乱淫乱淫乱淫乱
Db: query
Name: testing_event5
Definer: root@localhost
Time zone: SYSTEM
Type: ONE TIME
Execute at: 二017-11-两两 17:09:11
Interval value: NULL
Interval field: NULL
Starts: NULL
Ends: NULL
Status: DISABLED
Originator: 0
character_set_client: cp850
collation_connection: cp850_general_ci
Database Collation: latin1_swedish_ci
淫乱淫乱淫乱淫乱淫乱淫乱淫乱淫乱淫乱 二. row 淫乱淫乱淫乱淫乱淫乱淫乱淫乱淫乱淫乱
Db: query
Name: testing_event_with_Preserves
Definer: root@localhost
Time zone: SYSTEM
Type: ONE TIME
Execute at: 两017-11-两二 二0:35:1二
Interval value: NULL
Interval field: NULL
Starts: NULL
Ends: NULL
Status: DISABLED
Originator: 0
character_set_client: cp850
collation_connection: cp850_general_ci
Database Collation: latin1_swedish_ci
二 rows in set (0.00 sec)登录后复造
以上即是创立事故时 ON COMPLETION PRESERVE 子句有甚么用？的具体形式，更多请存眷萤水红IT仄台另外相闭文章！
发表评论 取消回复