如何設計自動刪除和備份資料的資料庫

sql php html 網頁設計 mysql windows server 資料庫

cair03019816 2011-05-27 20:58:41 ‧ 10845 瀏覽

分享至

請問一下如何在資料庫上面設計自動備份資料後只留下最新一筆資料其餘資料刪除，請問一下在MYSQL上面要如何做到呢？

登入發表討論

直播研討會

{{ item.channelVendor }} {{ item.webinarstarted }} |

直播中

3 個回答

一級屠豬士

iT邦大師 1 級 ‧ 2011-05-27 23:13:29

最佳解答

&lt;pre class="c" name="code">
建立測試Table與輸入資料 

CREATE TABLE t0527a (
id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
valuex INT
);

CREATE TABLE t0527_keepnew (
newid INT NOT NULL,
newvaluex INT NOT NULL,
newtime TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TRIGGER tri_t0527a_af
AFTER INSERT
ON t0527a
BEGIN
DELETE FROM t0527_keepnew;
INSERT INTO t0527_keepnew(newid, newvaluex) VALUES (NEW.id, NEW.valuex);
END;

-----------------------------
測試:
先輸入資料到t0527a.
INSERT INTO t0527a(valuex) VALUES(123);
檢查t0527_keepnew,是否有保存資料.
select * from t0527_keepnew;
+-------+-----------+---------------------+
| newid | newvaluex | newtime             |
+-------+-----------+---------------------+
|     1 |       123 | 2011-05-27 23:09:04 |
+-------+-----------+---------------------+

再輸入一筆資料到t0527a.
INSERT INTO t0527a(valuex) VALUES(125);
檢查t0527_keepnew,是否有保存最新一筆資料,而且只有一筆.
select * from t0527_keepnew;
+-------+-----------+---------------------+
| newid | newvaluex | newtime             |
+-------+-----------+---------------------+
|     2 |       125 | 2011-05-27 23:10:50 |
+-------+-----------+---------------------+

回應 6
分享
檢舉

看更多先前的回應...收起先前的回應...

cair03019816 iT邦新手 5 級 ‧ 2011-05-29 09:59:25 檢舉

請教一件事情，您這樣的語法看起來好像是只有新增一筆資料後然後就會再去覆蓋最新的資料，但好像沒有原先舊有的資料覆蓋過去呢??

cair03019816 iT邦新手 5 級 ‧ 2011-05-29 10:00:15 檢舉

對不起喔後面打錯了，但好像沒有原先舊有的資料備份。

一級屠豬士 iT邦大師 1 級 ‧ 2011-05-29 10:30:17 檢舉

目前是用兩個Table作範例.
t0527a與t0527a_keepnew, 其他程式(或是你原本程式)新增資料到t0527a,
這可以一直增加,而t0527a_keepnew會維持只有一筆最新的.
在前面的例子中,t0527a總共兩筆資料....你不去delete,
就不會消失.select 出來就好啦.

&lt;pre class="c" name="code">select * from t0527a;
+----+--------+
| id | valuex |
+----+--------+
|  1 |    123 |
|  2 |    125 |
+----+--------+

一級屠豬士 iT邦大師 1 級 ‧ 2011-05-29 10:58:44 檢舉

&lt;pre class="c" name="code">若是非要"備份"不可.那就再建一個Table.參考以下過程:
建立備份t0527a的Table

CREATE TABLE t0527_backup (
newid INT NOT NULL,
newvaluex INT NOT NULL,
newtime TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

修改Trigger:
先刪掉原本的
DROP TRIGGER tri_t0527a_af;
再建立新的:
CREATE TRIGGER tri_t0527a_af
AFTER INSERT
ON t0527a
BEGIN
INSERT INTO t0527_backup(newid, newvaluex) VALUES (NEW.id, NEW.valuex);
DELETE FROM t0527_keepnew;
INSERT INTO t0527_keepnew(newid, newvaluex) VALUES (NEW.id, NEW.valuex);
END;

一級屠豬士 iT邦大師 1 級 ‧ 2011-05-29 10:59:41 檢舉

&lt;pre class="c" name="code">測試:

INSERT INTO t0527a(valuex) VALUES(221);

現在三個Table裡面各自的內容如下:
>select * from t0527a;
+----+--------+
| id | valuex |
+----+--------+
|  1 |    123 |
|  2 |    125 |
|  3 |    221 |
+----+--------+
>select * from t0527_backup;
+-------+-----------+---------------------+
| newid | newvaluex | newtime             |
+-------+-----------+---------------------+
|     3 |       221 | 2011-05-29 10:52:44 |
+-------+-----------+---------------------+
>select * from t0527_keepnew;
+-------+-----------+---------------------+
| newid | newvaluex | newtime             |
+-------+-----------+---------------------+
|     3 |       221 | 2011-05-29 10:52:44 |
+-------+-----------+---------------------+

一級屠豬士 iT邦大師 1 級 ‧ 2011-05-29 11:00:05 檢舉

&lt;pre class="c" name="code">再插入一筆:
INSERT INTO t0527a(valuex) VALUES(334);
現在三個Table裡面各自的內容如下:
>select * from t0527a;
+----+--------+
| id | valuex |
+----+--------+
|  1 |    123 |
|  2 |    125 |
|  3 |    221 |
|  4 |    334 |
+----+--------+
>select * from t0527_backup;
+-------+-----------+---------------------+
| newid | newvaluex | newtime             |
+-------+-----------+---------------------+
|     3 |       221 | 2011-05-29 10:52:44 |
|     4 |       334 | 2011-05-29 10:54:33 |
+-------+-----------+---------------------+
>select * from t0527_keepnew;
+-------+-----------+---------------------+
| newid | newvaluex | newtime             |
+-------+-----------+---------------------+
|     4 |       334 | 2011-05-29 10:54:33 |
+-------+-----------+---------------------+

要注意backup用的Table會一直增加...所以有加一個時間戳,以後可以視需要整理.

登入發表回應

slime

iT邦大師 1 級 ‧ 2011-05-27 23:12:33

個人想法, 以 OS 的功能來做:

到備份目錄
將現有備份檔 A 改名為 B
dump 出資料庫檔, 取名為 A
如果 A 存在(或其他判斷), 則刪除 B
如果 A 不存在, 則表示備份失敗, 發出通報 (可選擇是否要將 B 還原)

回應 1
分享
檢舉

cair03019816 iT邦新手 5 級 ‧ 2011-05-29 10:00:50 檢舉

請問一下這樣的語法要怎樣寫呢??

登入發表回應

wiseguy

iT邦超人 1 級 ‧ 2011-06-07 15:48:44

假設：

要留只剩一筆的 table 叫 ORIGIN_TABLE
要備份的 table 叫 BACKUP_TABLE
兩者的欄位一模一樣，ORIGIN_TABLE 有個 id 欄位是 auto_increment，BACKUP_TABLE 有相同型別的 id 欄位，但不必是 auto_increment。

然後你的需求就只要兩條 SQL：

&lt;pre class="c" name="code">insert ignore into `BACKUP_TABLE` select * from `ORIGIN_TABLE`;
delete from `ORIGIN_TABLE` where id &lt; (select max(id) from `BACKUP_TABLE`);

不過若是 server 頗 busy，可能有其它 insert 到 ORIGIN_TABLE 的 SQL 在這兩條 SQL 之間執行，就不保證 ORIGIN_TABLE 只會剩一條。如果非得保證 ORIGIN_TABLE 剩一條不可，就用 transaction 把這兩條 SQL 包在一起即可。

&lt;pre class="c" name="code">START TRANSACTION;
insert ignore into `BACKUP_TABLE`  select * from `ORIGIN_TABLE`;
delete from ORIGIN_TABLE where id &lt; (select max(id) from `BACKUP_TABLE`);
COMMIT;