VS AI协助查询

DDL

CREATE TABLE student (
    id INT PRIMARY KEY AUTO_INCREMENT,
    createDate DATE NOT NULL,
    userName VARCHAR(255) NOT NULL,
    age INT NOT NULL,
    phone VARCHAR(15),
    sex ENUM('Male', 'Female', 'Other') NOT NULL,
    introduce TEXT
);

存储过程

DELIMITER //

CREATE PROCEDURE InsertRandomStudents(num_students INT)
BEGIN
    DECLARE counter INT DEFAULT 0;
    DECLARE random_user_name VARCHAR(255);
    DECLARE random_age INT;
    DECLARE random_phone VARCHAR(15);
    DECLARE random_sex ENUM('Male', 'Female', 'Other');
    DECLARE random_introduce TEXT;
    
    WHILE counter < num_students DO
        -- 生成随机用户名（示例：User123）
        SET random_user_name = CONCAT('User', FLOOR(RAND() * 100000));
        
        -- 生成随机年龄（示例：18-30岁之间）
        SET random_age = FLOOR(RAND() * (30 - 18 + 1)) + 18;
        
        -- 生成随机电话号码（示例：1234567890）
        SET random_phone = LPAD(FLOOR(RAND() * 10000000000), 10, '0');
        
        -- 随机选择性别
        SET random_sex = ELT(1 + FLOOR(RAND() * 3), 'Male', 'Female', 'Other');
        
        -- 生成随机介绍文本（示例：随机长度的'A'字符串）
        SET random_introduce = REPEAT('A', FLOOR(RAND() * 255));
        
        -- 插入随机生成的学生数据
        INSERT INTO student (createDate, userName, age, phone, sex, introduce)
        VALUES (CURDATE(), random_user_name, random_age, random_phone, random_sex, random_introduce);
        
        -- 增加计数器
        SET counter = counter + 1;
    END WHILE;
END //

DELIMITER ;

15万条数据

CALL InsertRandomStudents(150000);

随机生成

DELIMITER //

CREATE PROCEDURE InsertStudentData(IN p_userName VARCHAR(255), IN p_introduce TEXT)
BEGIN
    -- 检查userName和introduce是否为空
    IF p_userName IS NULL OR p_introduce IS NULL THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'userName and introduce cannot be NULL';
    END IF;
    
    -- 检查userName是否以“周武王”开头
    IF LEFT(p_userName, 3) = '周武王' THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'userName cannot start with "周武王"';
    END IF;
    
    -- 如果检查通过，则插入数据，其他字段随机生成
    INSERT INTO student (createDate, userName, age, phone, sex, introduce)
    VALUES (
        CURDATE(), -- 当前日期
        p_userName, -- 传入的userName
        FLOOR(RAND() * (30 - 18 + 1)) + 18, -- 18到30之间的随机年龄
        LPAD(FLOOR(RAND() * 10000000000), 10, '0'), -- 随机电话号码
        ELT(1 + FLOOR(RAND() * 3), 'Male', 'Female', 'Other'), -- 随机性别
        p_introduce -- 传入的introduce
    );
END //

DELIMITER ;

开始

CALL InsertStudentData('JohnDoe', 'This is John Doe\'s introduction.');

结束

SELECT 
    id,
    createDate,
    userName,
    age,
    phone,
    CASE sex
        WHEN 'Female' THEN '姑娘'
        WHEN 'Male' THEN '大老爷们'
        WHEN 'Other' THEN '泰国妹妹'
        ELSE '未知' -- 可选，用于处理除Female、Male、Other之外的其他情况
    END AS description,
    introduce
FROM 
    student;