MySQL 檔案儲存修改與刪除操作詳解

– 8 分鐘閱讀

MySQL 檔案儲存提供了一種有效管理 JSON 檔案的方式。本文將探討如何使用各種方法修改和刪除檔案，包含更新特定欄位、操作陣列元素以及刪除單個或多個檔案。文章將使用 Python 和 MySQL Connector/Python 示範實際操作，並提供 Elasticsearch 檔案刪除的實作範例和最佳化建議，以提升操作效率和安全性。同時也探討瞭如何使用 set() 更新檔案欄位、使用 array_append() 和 array_insert() 操作陣列，以及使用 remove() 和 remove_one() 刪除檔案。文章更進一步探討了 Elasticsearch 中的刪除操作，包含單一檔案刪除和批次刪除的程式碼實作、流程控制以及最佳化建議，以確保操作的效率和安全性。

MySQL 檔案儲存的修改操作詳解與實戰應用

前言

在現代資料函式倉管理系統中，MySQL 檔案儲存提供了一種靈活的方式來儲存和操作 JSON 檔案。本文將深入探討如何在 MySQL 檔案儲存中修改檔案，涵蓋 set()、unset()、array_append()、array_insert() 和 patch() 方法的使用，並提供 Python 程式碼範例，示範如何透過 MySQL Connector/Python 與資料函式庫互動，執行修改操作。

MySQL 檔案儲存修改操作的核心方法

MySQL 檔案儲存提供了一系列強大的修改檔案的方法，包括 set()、unset()、array_append()、array_insert() 和 patch()。這些方法使得更新檔案中的特定欄位或陣列元素變得非常方便。

使用 set() 和 unset() 修改檔案

set() 方法用於更新檔案中的指定欄位，如果該欄位不存在，則會新增該欄位。相反，unset() 方法則用於刪除指定的欄位。

修改維多利亞州城市人口的範例

以下範例展示瞭如何使用 modify() 方法和 set() 來更新維多利亞州的人口，增加10%：

import mysqlx
from config import connect_args

# 建立資料函式庫連線
db = mysqlx.get_session(**connect_args)
schema = db.get_schema("py_test_db")
city_col = schema.get_collection("city")

# 開始事務
db.start_transaction()

try:
 # 取得維多利亞州城市的當前人口
 statement = city_col.find("Geography.State = :state")
 statement = statement.fields("Name AS CityName", "Demographics.Population AS Population")
 statement = statement.sort("Name")
 statement = statement.bind("state", "Victoria")
 before = statement.execute()

 # 更新維多利亞州城市的人口，增加10%
 result = city_col.modify("Geography.State = :state") \
 .set("Demographics.Population", "FLOOR(Demographics.Population * 1.10)") \
 .bind("state", "Victoria") \
 .execute()

 print("受影響的檔案數量：{0}".format(result.get_affected_items_count()))

 # 再次取得更新後的人口資料
 after = statement.execute()

 # 列印更新前後的人口資料
 before_cities = before.fetch_all()
 after_cities = after.fetch_all()
 print("{0:10s} {1:^17s}".format("城市", "人口"))
 print("{0:10s} {1:7s} {2:7s}".format("", "更新前", "更新後"))
 print("-" * 30)
 for before_city, after_city in zip(before_cities, after_cities):
 print("{0:10s} {1:7d} {2:7d}".format(
 before_city["CityName"],
 int(before_city["Population"]),
 int(after_city["Population"])
 ))
except Exception as e:
 print(f"An error occurred: {e}")
 db.rollback()
else:
 db.commit()
finally:
 db.close()

程式碼解密：

此範例展示瞭如何使用 modify() 方法和 set() 來更新維多利亞州城市的人口。首先，我們取得了維多利亞州城市的當前人口，然後使用 modify() 方法更新人口資料，將人口增加10%。最後，我們列印出更新前後的人口資料。

圖表翻譯：更新維多利亞州城市人口的流程

  flowchart TD
 A[開始] --> B[檢查狀態]
 B -->|維多利亞州| C[更新人口資料]
 B -->|其他州| D[結束]
 C --> E[計算新人口]
 E --> F[更新資料函式庫]
 F --> D

此圖示展示了更新維多利亞州城市人口的流程。首先檢查狀態，如果是維多利亞州，則更新人口資料，計算新人口後更新資料函式庫，最後結束操作。

MySQL 檔案儲存中的陣列操作

MySQL 檔案儲存提供了多種方法來操作陣列，包括 array_append() 和 array_insert()。這些方法使得更新檔案中的陣列元素變得非常方便。

使用 array_append() 和 array_insert() 修改陣列

array_append() 方法將新元素新增到現有陣列的末尾，而 array_insert() 方法可以在陣列的指定位置插入新元素。

更新雪梨市郊區的範例

以下範例展示瞭如何使用 array_append() 和 array_insert() 方法更新雪梨市的郊區資訊：

import mysqlx
from config import connect_args
import pprint

# 建立資料函式庫連線
db = mysqlx.get_session(**connect_args)
schema = db.get_schema("py_test_db")
city_col = schema.get_collection("city")

# 開始事務
db.start_transaction()

try:
 # 取得雪梨市的當前郊區資訊
 statement = city_col.find("Name = :city_name")
 statement = statement.fields("_id", "Suburbs", "JSON_SEARCH(Suburbs, 'one', 'Central Business District') AS Index")
 statement = statement.bind("city_name", "Sydney")
 before = statement.execute().fetch_one()

 # 列印更新前的郊區資訊
 print("更新前的郊區：")
 print("-" * 27)
 pprint.pprint(before["Suburbs"])

 # 取得檔案的 ID 和 Central Business District 的索引
 docid = before["_id"]
 index = before["Index"][1:]

 # 使用 array_append() 更新 Central Business District 郊區
 modify = city_col.modify("_id = :id")
 modify.array_append("Suburbs{0}".format(index), ["Circular Quay", "Town Hall"])
 modify.bind("id", docid)
 modify.execute()

 # 取得更新後的郊區資訊
 after1 = statement.execute().fetch_one()
 print("使用 array_append() 更新後的郊區：")
 print("-" * 31)
 pprint.pprint(after1["Suburbs"])
except Exception as e:
 print(f"An error occurred: {e}")
 db.rollback()
else:
 db.commit()
finally:
 db.close()

圖表翻譯：客戶端與 MySQL 伺服器之間的互動過程

  sequenceDiagram
 participant Client as 客戶端
 participant Server as MySQL 伺服器
 Client->>Server: 傳送更新請求
 Server->>Server: 處理更新操作
 Server->>Client: 傳回更新結果

此圖示展示了客戶端與 MySQL 伺服器之間的互動過程。客戶端傳送更新請求，伺服器處理更新操作後傳回結果給客戶端。

MySQL 檔案儲存的 CRUD 操作：刪除檔案

在大多數應用程式中，某些檔案最終需要被刪除。移除檔案可以減少資料的大小，不僅降低磁碟使用率，也使得查詢更有效率，因為需要處理的資料量減少了。檔案儲存提供了兩種方法來從集合中移除檔案：remove() 和 remove_one()。

使用 remove_one() 刪除檔案

remove_one() 方法是最簡單的刪除方法，因為它只需要提供檔案 ID 並直接傳回一個 Result 物件。

# 取得 Canberra 的檔案 ID
statement = city_col.find("Name = :city_name")
statement = statement.fields("_id")
statement = statement.bind("city_name", "Canberra")
result = statement.execute()
canberra_id = result.fetch_one()["_id"]

# 刪除 Canberra 的檔案
result = city_col.remove_one(canberra_id)
items = result.get_affected_items_count()
print(f"Number of rows deleted: {items}")

使用 remove() 刪除檔案

remove() 方法允許根據條件刪除檔案，並且可以限制刪除的最大檔案數量。

# 刪除所有澳洲城市的檔案
statement = city_col.remove("Country = :country")
statement = statement.bind("country", "Australia")
result = statement.execute()
print(f"Number of rows deleted: {result.get_affected_items_count()}")

刪除操作的注意事項

  • 在使用 remove() 方法時，必須指定條件；否則，將會引發例外。
  • 在 MySQL Connector/Python 8.0.12 及更高版本中，呼叫 remove() 方法時必須提供條件。
  • 若要刪除所有檔案，可以使用 True 作為條件，或刪除並重新建立集合。

實際範例：刪除檔案

import mysqlx
from config import connect_args

# 建立資料函式庫連線
db = mysqlx.get_session(**connect_args)
schema = db.get_schema("py_test_db")
city_col = schema.get_collection("city")

# 開始交易
db.start_transaction()

try:
 # 取得 Canberra 的檔案 ID
 statement = city_col.find("Name = :city_name")
 statement = statement.fields("_id")
 statement = statement.bind("city_name", "Canberra")
 result = statement.execute()
 canberra_id = result.fetch_one()["_id"]

 # 刪除 Canberra 的檔案
 result = city_col.remove_one(canberra_id)
 print(f"Number of rows deleted by remove_one: {result.get_affected_items_count()}")

 # 刪除所有澳洲城市的檔案
 statement = city_col.remove("Country = :country")
 statement = statement.bind("country", "Australia")
 result = statement.execute()
 print(f"Number of rows deleted by remove: {result.get_affected_items_count()}")
except Exception as e:
 print(f"An error occurred: {e}")
 db.rollback()
else:
 db.commit()
finally:
 db.close()

本文詳細介紹了 MySQL 檔案儲存的修改操作，包括 set()、unset()、array_append()、array_insert() 和 patch() 方法的使用，並提供了 Python 程式碼範例，示範如何透過 MySQL Connector/Python 與資料函式庫互動，執行修改操作。同時，本文也介紹了刪除檔案的操作，包括 remove() 和 remove_one() 方法的使用。這些技術對於需要處理非結構化資料或希望提高資料函式庫操作效率的開發者來說至關重要。

資料刪除機制實作與最佳化

刪除操作的核心實作

在資料刪除機制的設計中，正確的刪除操作不僅需要考慮單一資料的刪除，也需要支援批次刪除功能。以下展示如何透過 Python 實作一個完整的刪除機制：

import logging
from elasticsearch import Elasticsearch, exceptions

# 建立 ES 連線
es = Elasticsearch([{'host': 'localhost', 'port': 9200}])

def delete_document(index_name, doc_id):
    """
    刪除指定 ID 的檔案
    :param index_name: 索引名稱
    :param doc_id: 檔案 ID
    :return: 刪除結果
    """
    try:
        # 執行刪除操作
        response = es.delete(index=index_name, id=doc_id)
        # 紀錄成功日誌
        logging.info(f'檔案 {doc_id} 刪除成功')
        return response
    except exceptions.NotFoundError:
        # 處理檔案不存在的情況
        logging.warning(f'檔案 {doc_id} 不存在')
        return None
    except Exception as e:
        # 紀錄錯誤日誌
        logging.error(f'刪除檔案 {doc_id} 失敗: {str(e)}')
        raise

def delete_documents_by_query(index_name, query):
    """
    根據查詢條件刪除多個檔案
    :param index_name: 索引名稱
    :param query: 查詢條件
    :return: 刪除結果
    """
    try:
        # 執行批次刪除
        response = es.delete_by_query(index=index_name, body=query)
        # 紀錄操作結果
        logging.info(f'根據查詢條件刪除 {response["deleted"]} 個檔案')
        return response
    except Exception as e:
        logging.error(f'批次刪除失敗: {str(e)}')
        raise

# 使用範例：刪除單一檔案
delete_document(index_name="my_index", doc_id="document_id_1")

# 使用範例：批次刪除
query = {
    "query": {
        "match": {
            "status": "inactive"
        }
    }
}
delete_documents_by_query(index_name="my_index", query=query)

程式碼解析：

此範例展示瞭如何在 Elasticsearch 中實作單一檔案刪除與批次刪除功能。程式碼主要包含兩個核心函式：delete_document 和 delete_documents_by_query。前者用於刪除單一檔案，後者支援根據查詢條件刪除多個檔案。實作中包含了完整的錯誤處理機制，能有效處理檔案不存在、連線錯誤等各種可能的例外狀況，並透過 logging 機制記錄操作結果。

刪除操作的流程控制

刪除操作的正確執行需要完善的流程控制機制。以下 Mermaid 圖表展示了刪除操作的完整流程：

  flowchart LR
    A[開始刪除操作] --> B{選擇刪除方式}
    B -->|單一刪除| C[輸入檔案 ID]
    B -->|批次刪除| D[輸入查詢條件]
    C --> E[執行刪除]
    D --> F{條件檢查}
    F -->|有效條件| G[執行批次刪除]
    F -->|無效條件| H[傳回錯誤]
    E --> I[結束]
    G --> I
    H --> I

圖表解析：

此流程圖清晰地展示了刪除操作的決策路徑。在刪除操作開始時，系統會先判斷要執行的刪除方式。如果選擇單一刪除，則直接輸入檔案 ID 並執行刪除；如果選擇批次刪除，則需要輸入查詢條件並進行有效性檢查。只有當查詢條件有效時才會執行刪除操作，否則會傳回錯誤訊息。最終，所有操作路徑都會回到流程的終點。

最佳化建議

  1. 效能最佳化：

    • 在執行批次刪除時，建議使用 delete_by_query 搭配 scroll API 以避免大批次操作造成的效能問題。
    • 對於大量刪除操作，建議分批執行以避免對叢集效能造成影響。

  2. 錯誤處理：

    • 實作全面的錯誤處理機制，特別是針對 NotFoundError 和連線超時等常見錯誤。
    • 建議實作重試機制以處理臨時性錯誤。

  3. 安全性考慮：

    • 刪除操作應實施嚴格的許可權控制，避免未授權的刪除請求。
    • 建議在生產環境中記錄所有刪除操作的詳細日誌，以供日後稽核使用。

  4. 備份機制：

    • 在執行刪除操作前，建議先建立快照或備份相關資料。
    • 可以考慮實作版本控制機制，以便在誤刪時能夠快速還原資料。

透過上述最佳化措施，可以有效提升刪除操作的穩定性、安全性與執行效率。

從技術架構視角來看，MySQL 檔案儲存的修改與刪除操作，展現了其處理非結構化資料的靈活性。set()、unset()、array_append() 和 array_insert() 等方法提供了便捷的途徑操作 JSON 檔案，而 remove() 和 remove_one() 則簡化了資料刪除的流程。然而，操作 JSON 欄位時效能仍是一大挑戰，尤其在處理大型檔案和複雜查詢時。開發者需要仔細評估效能影響，並考慮使用索引或其他最佳化策略。此外，雖然 MySQL 檔案儲存提供了原子性操作，但在涉及多個檔案修改的場景下，仍需考量事務管理以確保資料一致性。隨著 MySQL 持續最佳化 JSON 功能，預期效能瓶頸將逐步得到改善，並在更多應用場景中發揮其優勢。對於追求資料函式庫靈活性和效率的開發者而言，MySQL 檔案儲存值得深入研究和應用，並持續關注其版本更新和最佳實務。

玄貓

玄貓

技術愛好者，專注於分享程式開發、雲端技術與 AI 應用的心得體會。