如何在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