MariaDB事件自動將早於特定時間的數據移動 到另一個沒有臨時表的表？
我正在嘗試設置一個事件，該事件會自動將超過指定時間的數據從大量使用的表移動到某個“存檔”表以提高性能。
到目前為止，我只遇到過答案，這些答案是基於一個
WHERE同時用於語句INSERT INTO SELECT和DELETE語句的子句。由於兩個語句之間的時間流逝，從WHERE理論上講，該子句可能會產生不同的結果，從而導致數據失去。這是我的活動：
DELIMITER $$ CREATE EVENT IF NOT EXISTS archive_6_month_old_entries ON SCHEDULE EVERY 1 MINUTE STARTS CURRENT_TIMESTAMP DO begin START TRANSACTION; CREATE TEMPORARY TABLE data_to_move SELECT * FROM api_log WHERE (datum - UNIX_TIMESTAMP(DATE_ADD(NOW(), INTERVAL -6 MONTH))) < 0 LIMIT 5000; INSERT INTO api_log_archive SELECT * FROM data_to_move; DELETE FROM api_log WHERE id in (SELECT id FROM data_to_move); COMMIT; end $$ DELIMITER ;沒有臨時表有沒有辦法做到這一點？
你說：
但是我們有一個問題，如果理論上insert查詢最多需要5分鐘，那麼INSERT INTO和DELETE語句的結果就會有差異，導致5分鐘的數據失去。
不，您不必冒失去數據的風險！這就是
TRANSACTIONs 的用途！InnoDB 使用（多版本並發控制 -
MVCC）。您應該閱讀這個重要主題，以便更好地利用伺服器的功能。像這樣的東西會起作用：
SET TRANSACTION LEVEL SERIALIZABLE; BEGIN TRANSACTION; INSERT INTO api_log_archive SELECT * FROM api_log WHERE (datum - UNIX_TIMESTAMP(DATE_ADD(NOW(), INTERVAL -6 MONTH))) < 0; -- removed LIMIT 5000, it's not necessary DELETE FROM api_log WHERE (datum - UNIX_TIMESTAMP(DATE_ADD(NOW(), INTERVAL -6 MONTH))) < 0; -- removed LIMIT COMMIT; SET TRANSACTION LEVEL READ COMMITTED; -- or whatever your default is將其放入
EVENT並安排它在每天晚上（例如）03:30 執行。一旦您對您的活動在您需要的時間正常執行感到滿意，那麼您所要做的就是將其作為日常監控程序的一部分進行監控。
隔離級別
READ COMMITTED下面簡單解釋一下事務隔離級別和事務隔離級別的區別SERIALIZABLE：
TRANSACTION ISOLATION LEVELMariaDB的預設設置是READ COMMITTED。所以，在終端一，我做了以下
Terminal_1_> SHOW TRANSACTION ISOLATION LEVEL;結果：
transaction_isolation ----------------------- read committed (1 row)這就是我們想要的——所以現在我們發出這個命令：
Terminal_1_> SELECT * FROM t;結果：
x | y ---+----- 1 | One 2 | Two (2 rows)這正是我們想要的——所以，現在我們深入研究如下事務：
Terminal_1_> BEGIN TRANSACTION; BEGIN Terminal_1_> SELECT * FROM t; x | y ---+----- 1 | One 2 | Two (2 rows)在這裡，我們通過切換到另一個終端 2 來“暫停”交易……
我們執行這個查詢：
SELECT pid, age(clock_timestamp(), query_start), usename, query FROM pg_stat_activity WHERE datname = 'test' AND query NOT ILIKE '%pg_stat_activity%' ORDER BY query_start desc;結果（不是上述的活動查詢）：
pid | age | usename | query ------+-----------------+----------+------------------ 3500 | 00:05:46.113281 | postgres | SELECT * FROM t; (1 row)因此，我們可以看到我的
SELECT * FROM t;查詢仍然處於活動狀態並且已經超過 5 分鐘。所以，現在，再次在終端 2 中，我們在表中插入一行：
INSERT INTO t VALUES (3, 'Three');並且（仍然是 2 號航站樓），我檢查：
Terminal_2_> SELECT * FROM t; x | y ---+------- 1 | One 2 | Two 3 | Three (3 rows)我回到終端 1 並發出 a
SELECT * FROM t;，結果是：x | y ---+------- 1 | One 2 | Two 3 | Three (3 rows)現在，查看
TRANSACTION ISOLATION LEVEL設置為的行為SERIALIZABLE。我們發出命令：
SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL SERIALIZABLE我正在使用本地 PostgreSQL 實例，因為這是我唯一執行的東西，並且使用小提琴無法執行這些命令 - MariaDB 的等價物在這裡- 我讓你來使用 MariaDB 建構所需的測試.
所以，從終端1，我們檢查我們的設置：
SELECT name, setting, short_desc FROM pg_settings WHERE name ILIKE ('default%transaction%o%');結果：
name | setting | short_desc -------------------------------+--------------+--------------------------------------------------------------- default_transaction_isolation | serializable | Sets the transaction isolation level of each new transaction. default_transaction_read_only | off | Sets the default read-only status of new transactions. (2 rows)所以，我們可以走了。從 terminal_1_> (
SERIALIZABLE) 我們執行；BEGIN TRANSACTION; SELECT * FROM t;結果：
x | y ---+----- 1 | One 2 | Two (2 rows)現在，從 terminal_2_> 我們再次對活動查詢進行檢查：
SELECT pid, age(clock_timestamp(), query_start), usename, query FROM pg_stat_activity WHERE datname = 'test' AND query NOT ILIKE '%pg_stat_activity%' ORDER BY query_start desc;結果：
pid | age | usename | query -------+-----------------+----------+------------------ 11064 | 00:03:37.694149 | postgres | SELECT * FROM t; (1 row)terminal_1_> 中的查詢坐在那裡，現在 > 3 分鐘前…
再次從終端_2_>，我們更新表：
INSERT INTO t VALUES (3, 'Three');和：
Terminal_2_> SELECT * FROM t; x | y ---+------- 1 | One 2 | Two 3 | Three (3 rows)現在我們回到terminal_1_> 並在同一個
SELECT * FROM t;可序列化事務中第二次執行。我們得到：
Terminal_1_> SELECT * FROM t; x | y ---+----- 1 | One 2 | Two (2 rows)該
SERIALIZABLE事務具有與事務開始時相同的表視圖t，儘管terminal_2_>已經INSERT編輯了一條新記錄……我敦促您嘗試不同的設置，以了解您的伺服器的功能以及如何最好地利用它們…
就您的問題而言，這意味著：
場景 1（您在伺服器上保留預設值 - 即
REPEATABLE READ）：在時間 t1，您執行以下命令：
CREATE TEMPORARY TABLE data_to_move SELECT * FROM api_log WHERE (datum - UNIX_TIMESTAMP(DATE_ADD(NOW(), INTERVAL -6 MONTH))) < 0 LIMIT 5000;比如說，你有
SELECT10 條記錄。這是一個日誌表，所以它會不斷更新……在時間 t2，您
INSERT進入存檔表。INSERT INTO api_log_archive SELECT * FROM data_to_move;在時間 t3，您從 api_log 中刪除
DELETE FROM api_log WHERE id in (SELECT id FROM data_to_move);現在，您的流程將錯過任何在 t1 和 t3 之間老化超過 6 個月的記錄 - 所以，好吧，你說，我下次會撿起它們……
場景 2（您執行歸檔過程並
TRANSACTION ISOLATION LEVEL設置為SERIALIZABLE）。
CREATE TEMPORARY TABLE對於初學者，您可以按如下方式剪切階段：SET TRANSACTION ISOLATION LEVEL SERIALIZABLE -- check out MariaDB syntax for this BEGIN TRANSACTION; INSERT INTO api_log_archive SELECT id FROM api_log WHERE (datum - UNIX_TIMESTAMP(DATE_ADD(NOW(), INTERVAL -6 MONTH))) < 0; DELETE FROM api_log WHERE id IN SELECT id FROM api_log WHERE (datum - UNIX_TIMESTAMP(DATE_ADD(NOW(), INTERVAL -6 MONTH))) < 0; COMMIT; -- clear up - set your transaction defaults back to your preferred default...這樣做的好處是您沒有創建可能很大的成本
TEMPORARY TABLE，您將步驟數從 3 減少到 2，並且您的系統將更加健壯。
- 還有另一種可能性-您可以在更新期間鎖定表-但是，這意味著
api_log在事務期間不會對錶進行寫入-如果可以避免，通常認為這不是一個好習慣。如果您想探索這種可能性，那麼您可能想問另一個問題……這個小提琴應該有助於澄清事情 - PostgreSQL 具有有用（和智能顯式）的功能
TRANSACTION_TIMESTAMP，並且CLOCK_TIMESTAMP-READ COMMITTED任何查詢都可以看到數據，CLOCK_TIMESTAMP事務SERIALIZABLE可以看到數據TRANSACTION TIMESTAMP！