資料結構
網路
RDBMS
作業系統
Java
MS Excel
iOS
HTML
CSS
Android
Python
C 程式設計
C++
C#
MongoDB
MySQL
Javascript
PHP
物理學
化學
生物學
數學
英語
經濟學
心理學
社會學
時尚研究
法律研究在建立事件時，ON COMPLETION PRESERVE 子句有什麼用？
眾所周知，一個事件在過期後將被自動刪除，我們無法從 SHOW EVENTS 語句中看到它。為了更改此類行為，我們可以在建立事件時使用 ON COMPLETION PRESERVE。可以從以下示例中進行理解：-
示例
mysql> Create table event_messages(ID INT NOT NULL PRIMARY KEY AUTO_INCREMENT, MESSAGE VARCHAR(255) 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 | 2017-11-22 20:32: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: 2017-11-22 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 | 2017-11-22 20:32:13 |
| 2 | With Preserve | 2017-11-22 20:35:12 |
+----+------------------+---------------------+
2 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: 2017-11-22 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
*************************** 2. row ***************************
Db: query
Name: testing_event_with_Preserves
Definer: root@localhost
Time zone: SYSTEM
Type: ONE TIME
Execute at: 2017-11-22 20:35:12
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
2 rows in set (0.00 sec)
更新於： 2020 年 6 月 22 日
2K+ 瀏覽量
廣告