如何在MySQL中大规模注入简单的虚拟数据-mysql教程-PHP中文网

首页

数据库

mysql教程

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

PHPz

Jul 18, 2024 am 08:24 AM

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 < 1000000
)
SELECT SHA2(n, 256) FROM cte;

登录后复制

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

了解 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 < 1000000
)
SELECT SHA2(n, 256) FROM cte;

登录后复制

让我们分解一下这个查询：

WITH RECURSIVE cte (n)：这将启动 CTE 定义。 cte 是临时结果集的名称，n 是列。
SELECT 1：这是CTE的非递归部分，作为起点（初始值）。
UNION ALL SELECT n + 1 FROM cte WHERE n <; 1000000：这是递归部分，它将 n 的值增加 1，直到达到 1,000,000。
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 < 1000000
    -> )
    -> SELECT SHA2(n, 256) FROM cte;
Query OK, 1000000 rows affected (4.43 sec)
Records: 1000000  Duplicates: 0  Warnings: 0

登录后复制