バックアップと削除のためにリアルタイムでステートメントを検出するMySQLトリガーの考え方の詳細な説明

問題の説明: ユーザーは、テーブルに「違反」という単語を含むフィールドが時々表示されることを要求しており、このフィールドが表示されたらデータの行全体を削除する必要があります。これは収集タスクです。「違反」という単語を含むデータが見つかった場合、その時間または特定の時間に一律に報告されます。このようなデータが生成されないようにソースを制御する方法はありません。

ここで、次の要件を満たす必要があります。

1. このデータの生成をリアルタイムで検出し、発見後に削除する

2. 削除する前にこのデータをバックアップしてください

解決：

明確な解決策が必要です。

1. まず、削除をリアルタイムで検出するにはどうすればよいでしょうか?開発者に問い合わせてください。データが挿入によって生成される場合は、テーブルが挿入されたときにデータを削除するトリガーを作成できます。

2. バックアップを実行するにはどうすればいいですか?どのような方法でバックアップしますか?各挿入の時刻を記録するテーブルでバックアップを行うことはできますか? バックアップテーブルは基本的に元のテーブル構造と同じにすることができますが、バックアップテーブルでは元のテーブルの自動増分属性、主キー、外部キーなどの属性を削除し、各バックアップデータの時刻を記録しやすくするためにタイムスタンプフィールドを追加する必要があります。バックアップテーブルにデータを書き込むことができるように、上記の属性が削除されます。

3. 削除する前にバックアップを作成するにはどうすればいいですか?最初はトリガーに入れて、まずデータをバックアップし、その後削除すればいいと考えましたが、テストしたところうまくいきませんでした。

テスト計画:

まずテストデータとテストテーブルを準備します

1. テストデータを作成する

mysql> show テーブル student を作成します。
+---------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| テーブル | テーブルの作成 |
+---------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 学生 | テーブル `学生` を作成 (
  `Sno` char(9) NOT NULL,
  `Sname` char(20) NOT NULL,
  `Ssex` char(2) デフォルト NULL,
  `Sage` smallint デフォルト NULL,
  `Sdept` char(20) デフォルト NULL,
  主キー (`Sno`)
) エンジン=InnoDB デフォルト文字セット=utf8mb4 照合=utf8mb4_0900_ai_ci |
+---------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
セット内の1行（0.01秒）

2. バックアップテーブルを作成します。テーブル作成ステートメントを確認してください。正式な環境では、元のテーブルのテーブル構造がわかりません。元のテーブル構造を変更し、新しいバックアップテーブルを作成する必要があります。

元のテーブル作成ステートメント

mysql> show テーブル student を作成します。
+---------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| テーブル | テーブルの作成 |
+---------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 学生 | テーブル `学生` を作成 (
  `Sno` char(9) NOT NULL,
  `Sname` char(20) NOT NULL,
  `Ssex` char(2) デフォルト NULL,
  `Sage` smallint デフォルト NULL,
  `Sdept` char(20) デフォルト NULL,
  主キー (`Sno`)
) エンジン=InnoDB デフォルト文字セット=utf8mb4 照合=utf8mb4_0900_ai_ci |
+---------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
セット内の1行（0.01秒）

3. バックアップステートメント、削除ステートメント、挿入テストステートメントを準備する

バックアップステートメント (バックアップテーブルには追加のタイムスタンプフィールドがあるため、バックアップステートメントを変更する必要があります)

mysql> show テーブル student_bak を作成します。
+-------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| テーブル | テーブルの作成 |
+-------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| student_bak | テーブル `student_bak` を作成します (
`Sno` char(9) NOT NULL,
`Sname` char(20) NOT NULL,
`Ssex` char(2) デフォルト NULL,
`Sage` smallint デフォルト NULL,
`Sdept` char(20) デフォルト NULL,
`create_date` タイムスタンプ NOT NULL DEFAULT CURRENT_TIMESTAMP
) エンジン=InnoDB デフォルト文字セット=utf8mb4 照合=utf8mb4_0900_ai_ci |
+-------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
セット内の 1 行 (0.00 秒)

バックアップ効果

テストステートメントを挿入します:

学生の値に挿入します('201215124','张三','男',20,'EL');

削除文（削除文は正確に記入し、削除したいものをバックアップしてください）

Sdept='EL' の学生から削除します。

4. 実際のテスト計画

4.1 1 つのトリガーに 2 つのステートメントを書き込む (操作が失敗し、ロジックの実行が失敗しました)

トリガーが存在する場合は削除する test_trigger;
区切り文字 $
トリガー test_trigger を作成する
後
学生2に挿入
各行ごとに
始める
student_bak(Sno,Sname,Ssex,Sage,Sdept) に挿入し、Sdept='EL' の場合に * を student から選択します。
Sdept='EL' の学生から削除します。
終了 $
区切り文字 ;

4.2 2 つの別々のトリガーを準備します。1 つは、データが元のテーブルに表示されたときにバックアップ効果を実現するために、データをバックアップテーブルに挿入することです。 2 番目のトリガーは、バックアップテーブルにデータをバックアップした後、元のテーブルのデータを削除するトリガーを作成し、バックアップ後に削除の効果を達成します (操作はまだ失敗します)。実行すると、トリガーの競合などがあるというエラーが報告されます。これにより、データベースは実行ロジックを認識しなくなります。

4.3 元のテーブル内のターゲットデータを削除し、そのデータをバックアップテーブルにバックアップするトリガーを作成します。最終的には、対象データをリアルタイムで検出して削除するだけです。トリガーの考え方に限定されません。スケジュールされたタスクを実行するだけです（操作は成功します）。

たとえば、次のテストでは、データベーステーブルの Sdept フィールドに「EL」というフィールドが表示された場合、データの行全体を削除する必要があります。

トリガーが存在する場合は削除します student_bak_trigger;
区切り文字 $
トリガーの作成 student_bak_trigger 
前に 
学生の削除 
各行ごとに 
始める   
student_bak(Sno,Sname,Ssex,Sage,Sdept) に挿入し、Sdept='EL' の場合に * を student から選択します。
終了 $
区切り文字 ;

このトリガーが実装されています。元のテーブルの対象データが削除された場合、トリガーがトリガーされ、データがバックアップされます。

mysql> 学生から * を選択します。
+-----------+--------+-------+-------+-------+
| 名 | 名前 | 性別 | 賢者 | 部門 |
+-----------+--------+-------+-------+-------+
| 201215121 | 李勇 | 男性 | 20 | CS |
| 201215122 | 劉晨 | 女性 | 19 | CS |
| 201215123 | ワン・ミン | 女性 | 18 | MA |
| 201215130 | 兵士 | 男性 | 20 | CH |
+-----------+--------+-------+-------+-------+
セット内の 4 行 (0.00 秒)

mysql> student_bak から * を選択します。
+-----------+--------+-------+-------+---------------------+
| 性別 | 年齢 | 部門 | 作成日 |
+-----------+--------+-------+-------+---------------------+
| 201215124 | 張三 | 男性 | 20 | EL | 2021-09-18 15:42:20 |
+-----------+--------+-------+-------+---------------------+
セット内の 1 行 (0.00 秒)

mysql> 学生の値に挿入します('201215125','王五','男',30,'EL');
クエリは正常、1 行が影響を受けました (0.00 秒)

mysql> 学生から * を選択します。
+-----------+--------+-------+-------+-------+
| 名 | 名前 | 性別 | 賢者 | 部門 |
+-----------+--------+-------+-------+-------+
| 201215121 | 李勇 | 男性 | 20 | CS |
| 201215122 | 劉晨 | 女性 | 19 | CS |
| 201215123 | ワン・ミン | 女性 | 18 | MA |
| 201215125 | 王武 | 男性 | 30 | EL |
| 201215130 | 兵士 | 男性 | 20 | CH |
+-----------+--------+-------+-------+-------+
セット内の行数は 5 です (0.00 秒)

mysql> student_bak から * を選択します。
+-----------+--------+-------+-------+-------+---------------------+
| 性別 | 年齢 | 部門 | 作成日 |
+-----------+--------+-------+-------+-------+---------------------+
| 201215124 | 張三 | 男性 | 20 | EL | 2021-09-18 15:42:20 |
+-----------+--------+-------+-------+---------------------+
セット内の 1 行 (0.00 秒)

mysql> Sdept='EL' の場合、学生から削除します。
クエリは正常、1 行が影響を受けました (0.01 秒)

mysql> student_bak から * を選択します。
+-----------+--------+-------+-------+---------------------+
| 性別 | 年齢 | 部門 | 作成日 |
+-----------+--------+-------+-------+---------------------+
| 201215124 | 張三 | 男性 | 20 | EL | 2021-09-18 15:42:20 |
| 201215125 | 王武 | 男性 | 30 | EL | 2021-09-18 15:47:28 |
+-----------+--------+-------+-------+---------------------+
セット内の 2 行 (0.00 秒)

最後に、スケジュールされたタスクを実装して、「EL」というフィールドのデータの行全体を周期的に削除します。ここでスケジュールされたタスクはグローバルであり、データベース名と特定のテーブル名を追加する必要があります。スケジュールされたタスクの実行速度は手動で調整できます。以下は、目的の効果を達成するための 3 秒/時間です。

存在しない場合はイベントを作成する e_test_event
スケジュールどおり3秒ごとに 
完了時に保存
Sdept='EL' の場合、abc.student から削除します。

スケジュールされたタスクを無効にする:

mysql> イベント e_test_event を ON COMPLETION PRESERVE DISABLE に変更します。
クエリは正常、影響を受けた行は 0 行 (0.00 秒)

スケジュールされたタスクを表示します。

mysql> select * from information_schema.events\G;**************************** 4. 行 ****************************
       EVENT_CATALOG: 定義
        イベントスキーマ: abc
          イベント名: e_test_event
             定義者: root@%
           タイムゾーン: システム
          EVENT_BODY: SQL
    EVENT_DEFINITION: Sdept='EL' の abc.student から削除
          イベントタイプ: 繰り返し
          実行時: NULL
      間隔値: 3
      INTERVAL_FIELD: 秒
            SQL_MODE: ONLY_FULL_GROUP_BY、STRICT_TRANS_TABLES、NO_ZERO_IN_DATE、NO_ZERO_DATE、ERROR_FOR_DIVISION_BY_ZERO、NO_ENGINE_SUBSTITUTION
              開始: 2021-09-17 13:35:44
                終了: NULL
              ステータス: 有効
       ON_COMPLETION: 保存
             作成日: 2021-09-17 13:35:44
        最終更新日時: 2021-09-17 13:35:44
       最終実行日時: 2021-09-18 15:43:35
       イベントコメント: 
          発信者: 3330614
文字セットクライアント: utf8mb4
照合接続: utf8mb4_0900_ai_ci
  データベース照合順序: utf8mb4_0900_ai_ci
セット内の 4 行 (0.00 秒)

トリガーを表示:

mysql> information_schema.triggers\G から * を選択します。
************************** 5. 行 ****************************
           TRIGGER_CATALOG: 定義
            トリガースキーマ: abc
              トリガー名: student_bak_trigger
        イベント操作: 削除
      EVENT_OBJECT_CATALOG: 定義
       イベントオブジェクトスキーマ: abc
        EVENT_OBJECT_TABLE: 学生
              アクション順序: 1
          アクション条件: NULL
          アクションステートメント: 開始   
student_bak(Sno,Sname,Ssex,Sage,Sdept) に挿入し、Sdept='EL' の場合に * を student から選択します。
終わり
        アクション方向: 行
             アクションタイミング: 前
ACTION_REFERENCE_OLD_TABLE: NULL
ACTION_REFERENCE_NEW_TABLE: NULL
  ACTION_REFERENCE_OLD_ROW: 古い
  ACTION_REFERENCE_NEW_ROW: 新規
                   作成日時: 2021-09-18 15:41:48.53
                  SQL_MODE: ONLY_FULL_GROUP_BY、STRICT_TRANS_TABLES、NO_ZERO_IN_DATE、NO_ZERO_DATE、ERROR_FOR_DIVISION_BY_ZERO、NO_ENGINE_SUBSTITUTION
                   定義者: root@%
      文字セットクライアント: utf8mb4
      照合接続: utf8mb4_0900_ai_ci
        データベース照合順序: utf8mb4_0900_ai_ci
セット内の行数は 5 です (0.00 秒)

結果：

これで、バックアップと削除のためのステートメントのMySQLトリガーリアルタイム検出のアイデアの詳細な説明に関するこの記事は終わりです。関連するMySQLトリガーバックアップと削除コンテンツの詳細については、123WORDPRESS.COMの以前の記事を検索するか、次の関連記事を引き続き参照してください。今後とも123WORDPRESS.COMを応援してください。

以下もご興味があるかもしれません: