如何在MySQL中大规模注入简单的虚拟数据

php中文网 2024-10-15 12:06:28

介绍

是否曾经发现自己处于需要大量虚拟数据进行测试，但又不想花费数小时编写脚本或手动插入记录的情况？或者您可能对如何利用 MySQL 8.0 中的新功能来简化数据库任务感到好奇？好吧，你可要好好享受一下了！在这篇文章中，我们将探索如何使用通用表表达式 (CTE) 轻松生成大量虚拟数据并将其插入到 MySQL 数据库中。

想象一下需要在表中填充一百万个哈希值以进行负载测试或性能基准测试。听起来像一场噩梦，对吧？不再！随着 MySQL 8.0 中 CTE 的出现，您可以在几秒钟内实现这一目标。让我们深入了解它的工作原理以及如何使用这个强大的功能来简化您的数据生成需求。

TL; DR

公用表表达式（CTE）是MySQL 8.0中添加的新功能，可以用来轻松输入大量简单的虚拟数据。例如，如果您想将 100 万条虚拟数据输入到一个存储哈希值的名为 hashes 的表中，您可以通过以下步骤实现：

表定义

首先，创建表：

创建表哈希（
  id INT 主键自动递增，
  哈希 CHAR(64)
）；

查询执行

设置会话变量以允许更高的递归深度：

设置会话cte_max_recursion_深度= 1000000；

然后，执行CTE插入100万行：

插入哈希值（hash）
带有递归 cte (n) AS
（
  选择1
  联合所有
  从 cte 中选择 n + 1，其中 n 



<p>此方法利用递归公用表表达式来生成虚拟数据。</p>

<h2>
  
  
  了解 CTE
</h2>

<p>公共表表达式（CTE）是一个命名的临时结果集，可以在单个语句中多次引用。 CTE 对于简化复杂查询和提高可读性特别有用。</p>

<h3>
  
  
  语法细目
</h3>

<h4>
  
  
  设置递归深度
</h4>



<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false">设置会话cte_max_recursion_深度= 1000000；

cte_max_recursion_depth系统变量设置递归的上限。默认情况下，它是1000，所以要递归更多，你需要调整它。在这里，我们将其设置为 100 万。

CTE 查询

插入哈希值（hash）
带有递归 cte (n) AS
（
  选择1
  联合所有
  从 cte 中选择 n + 1，其中 n 



<p>让我们分解这个查询：</p>

使用递归 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&gt; INSERT INTO 哈希值(hash)
    -&gt; 带有递归 cte (n) AS
    -&gt; (
    -&gt; 选择 1
    -&gt; 联合所有
    -&gt; 从 cte 中选择 n + 1，其中 n  )
    -&gt; 从 cte 中选择 SHA2(n, 256)；
查询正常，1000000 行受影响（4.43 秒）
记录：1000000 重复：0 警告：0