利用数据库特性和函数一次性生成多条动态数据

利用数据库特性和函数一次性生成多条动态数据

在数据库操作中，有时需要一次性生成多条动态数据，以满足测试、初始化等需求。不同的数据库系统提供了各自的特性和函数来实现这一目的。以下将分别介绍 MySQL、Oracle 和 SQL Server 中实现一次性生成多条动态数据的方法。

一、MySQL

1.1 使用 INSERT INTO... SELECT 结合自增序列和随机函数

示例场景

假设要向 users 表（包含 id、username、email 和 age 字段）中插入 10 条数据，id 自增，username 为随机生成的字符串，email 根据 username 生成，age 为 18 - 60 之间的随机整数。

代码示例

-- 创建 users 表
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL,
    age INT
);

-- 一次性插入 10 条数据
INSERT INTO users (username, email, age)
SELECT 
    CONCAT('user', FLOOR(RAND() * 10000)),
    CONCAT(CONCAT('user', FLOOR(RAND() * 10000)), '@example.com'),
    FLOOR(RAND() * 43) + 18
FROM 
    (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10) AS numbers;

代码解释

• (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10) AS numbers：创建了一个包含 1 到 10 的临时表，用于生成 10 条记录。
• FLOOR(RAND() * 10000)：生成 0 到 9999 之间的随机整数。
• FLOOR(RAND() * 43) + 18：生成 18 到 60 之间的随机整数。

1.2 使用 WITH RECURSIVE 递归 CTE（Common Table Expression）

代码示例

-- 创建 users 表
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL,
    age INT
);

-- 一次性插入 10 条数据
WITH RECURSIVE numbers AS (
    SELECT 1 AS num
    UNION ALL
    SELECT num + 1 FROM numbers WHERE num < 10
)
INSERT INTO users (username, email, age)
SELECT 
    CONCAT('user', num),
    CONCAT(CONCAT('user', num), '@example.com'),
    FLOOR(RAND() * 43) + 18
FROM 
    numbers;

代码解释

• WITH RECURSIVE numbers AS (...)：定义了一个递归的公共表表达式 numbers，生成 1 到 10 的数字序列。
• 然后使用这个序列插入 10 条数据到 users 表中。

二、Oracle

2.1 利用 CONNECT BY 生成序列插入数据

示例场景

同样向 users 表（包含 id、username、email 和 age 字段）插入 10 条数据。

代码示例

-- 创建 users 表
CREATE TABLE users (
    id NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY PRIMARY KEY,
    username VARCHAR2(50) NOT NULL,
    email VARCHAR2(100) NOT NULL,
    age NUMBER
);

-- 一次性插入 10 条数据
INSERT INTO users (username, email, age)
SELECT 
    'user' || LEVEL,
    'user' || LEVEL || '@example.com',
    TRUNC(DBMS_RANDOM.VALUE(18, 61))
FROM 
    DUAL
CONNECT BY 
    LEVEL <= 10;

代码解释

• CONNECT BY LEVEL <= 10：生成一个从 1 到 10 的序列。
• DBMS_RANDOM.VALUE(18, 61)：生成 18 到 60 之间的随机数，TRUNC 函数用于取整。

三、SQL Server

3.1 使用 VALUES 构造多行数据

示例场景

向 users 表插入 10 条数据，username 为按顺序编号，email 对应生成，age 为随机值。

代码示例

-- 创建 users 表
CREATE TABLE users (
    id INT IDENTITY(1,1) PRIMARY KEY,
    username NVARCHAR(50) NOT NULL,
    email NVARCHAR(100) NOT NULL,
    age INT
);

-- 一次性插入 10 条数据
INSERT INTO users (username, email, age)
SELECT 
    'user' + CAST(n AS NVARCHAR(10)),
    'user' + CAST(n AS NVARCHAR(10)) + '@example.com',
    FLOOR(RAND(CHECKSUM(NEWID())) * 43) + 18
FROM (
    VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10)
) AS Numbers(n);

代码解释

• VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10)：构造一个包含 1 到 10 的临时表。
• FLOOR(RAND(CHECKSUM(NEWID())) * 43) + 18：生成 18 到 60 之间的随机整数。

通过以上不同数据库系统的方法，我们可以利用各自的特性和函数一次性生成多条动态数据，提高数据插入的效率。