如何在MySQL中大規模注入簡單的虛擬數據

發佈於2024-08-01

How to inject simple dummy data at a large scale in MySQL

介紹

是否曾經發現自己處於需要大量虛擬資料進行測試，但又不想花費數小時編寫腳本或手動插入記錄的情況？或者您可能對如何利用 MySQL 8.0 中的新功能來簡化資料庫任務感到好奇？好吧，你可要好好享受一下了！在這篇文章中，我們將探討如何使用通用表格運算式 (CTE) 輕鬆產生大量虛擬資料並將其插入 MySQL 資料庫中。

想像一下需要在表中填入一百萬個雜湊值以進行負載測試或效能基準測試。聽起來像是場惡夢，對吧？不再！隨著 MySQL 8.0 中 CTE 的出現，您可以在幾秒鐘內實現這一目標。讓我們深入了解它的工作原理以及如何使用這個強大的功能來簡化您的資料生成需求。

TL; DR

通用表格表達式（CTE）是MySQL 8.0中新增的功能，可以用來輕鬆輸入大量簡單的虛擬資料。例如，如果您想要將 100 萬個虛擬資料輸入到一個儲存雜湊值的名為 hashes 的表中，則可以透過以下步驟實作：

表定義

先建立表格：

CREATE TABLE hashes (
  id INT PRIMARY KEY AUTO_INCREMENT,
  hash CHAR(64)
);

查詢執行

設定會話變數以允許更高的遞歸深度：

SET SESSION cte_max_recursion_depth = 1000000;

然後，執行CTE插入100萬行：

INSERT INTO hashes(hash)
WITH RECURSIVE cte (n) AS
(
  SELECT 1
  UNION ALL
  SELECT n   1 FROM cte WHERE n 



此方法利用遞歸公用表表達式來產生虛擬資料。 


  
  
  了解 CTE


公共表表達式 (CTE) 是一個命名的臨時結果集，可以在單一語句中多次引用。 CTE 對於簡化複雜查詢和提高可讀性特別有用。 


  
  
  語法細目



  
  
  設定遞歸深度




SET SESSION cte_max_recursion_depth = 1000000;




cte_max_recursion_depth系統變數設定遞歸的上限。預設情況下，它是1000，所以要遞歸更多，你需要調整它。這裡，我們將其設定為100萬。 


  
  
  CTE 查詢




INSERT INTO hashes(hash)
WITH RECURSIVE cte (n) AS
(
  SELECT 1
  UNION ALL
  SELECT n   1 FROM cte WHERE n 



讓我們分解這個查詢：

WITH RECURSIVE cte (n)：這將啟動 CTE 定義。 cte 是臨時結果集的名稱，n 是列。
SELECT 1：這是CTE的非遞迴部分，作為起點（初始值）。
UNION ALL SELECT n 1 FROM cte WHERE n
SELECT SHA2(n, 256) FROM cte：查詢的最後部分選擇每個 n 值的 SHA-256 哈希，產生用於插入的虛擬資料。

怎麼運作的

CTE 遞歸地產生從 1 到 1,000,000 的數字。對於每個數字，它計算 SHA-256 雜湊值並將其插入雜湊表中。這種方法非常高效，並且利用 MySQL 的遞歸功能來無縫處理大數據量。

處理速度

驗證環境

為了了解此功能的影響，我使用了 Gitpod Enterprise 工作區，利用強大且短暫的環境來避免設定和安裝的麻煩。設定一覽如下：

機器：Gitpod Enterprise XXLarge 工作區
作業系統：Ubuntu 22.04.4 LTS (Jammy Jellyfish)
容器化：Docker 版本 26.0.1
MySQL 版本：官方 MySQL 8.0 Docker 映像

結果

對於100萬行，查詢執行時間約為4.46秒：

mysql> INSERT INTO hashes(hash)
    -> WITH RECURSIVE cte (n) AS
    -> (
    ->   SELECT 1
    ->   UNION ALL
    ->   SELECT n   1 FROM cte WHERE n  )
    -> SELECT SHA2(n, 256) FROM cte;
Query OK, 1000000 rows affected (4.43 sec)
Records: 1000000  Duplicates: 0  Warnings: 0