MySQL存储过程
发表于 2023-11-10 | | 开发笔记

-- 实现方式

    DROP FUNCTION IF EXISTS `rand_string`;
    SET NAMES utf8;
    CREATE FUNCTION `rand_string` (n INT) RETURNS VARCHAR(255) CHARSET 'utf8'
    BEGIN 
        DECLARE char_str varchar(100) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789';
        DECLARE return_str varchar(255) DEFAULT '';
        DECLARE i INT DEFAULT 0;
        WHILE i < n DO
            SET return_str = concat(return_str, substring(char_str, FLOOR(1 + RAND()*62), 1));
            SET i = i+1;
        END WHILE;
        RETURN return_str;
    END;

    -- 创建插入数据的存储过程
    DROP PROCEDURE IF EXISTS `add_data`;

    CREATE PROCEDURE `add_data`(IN n INT)
    BEGIN
        DECLARE i INT DEFAULT 1;
        DECLARE user_id INT DEFAULT 0;
        DECLARE pid INT DEFAULT 0;
            DECLARE eid INT DEFAULT 0;
            DECLARE toid INT DEFAULT 0;
            DECLARE mark INT DEFAULT 0;
            DECLARE fromwhere INT DEFAULT 0;
            DECLARE name VARCHAR(20) DEFAULT '';
            DECLARE mobilePhone VARCHAR(20) DEFAULT '';
        DECLARE company VARCHAR(50) DEFAULT '';
            DECLARE ctime INT DEFAULT 0;    
        DECLARE id INT DEFAULT 1;
            -- 插入语句的前半部分
            set @pre_sql = "INSERT INTO `jl_records_10013524` (`id`, `userId`, `eid`, `pid`, `toid`, `mark`, `fromwhere`, `name`, `mobilePhone`, `company`, `ctime`) VALUES ";
            set @exec_sql = @pre_sql;
        WHILE i < n DO
                    SET user_id = FLOOR(200000 + RAND() * (400000 - 200000));
                    SET pid = FLOOR(1 + RAND() * 100000);
                    SET eid = 10013524;
                    SET toid = FLOOR(20000000 + RAND() * (40000000 - 20000000));
                    SET mark = FLOOR(0 + RAND()*10);
                    SET fromwhere = FLOOR(0 + RAND()*5);
                    SET name = rand_string(7);
                    SET mobilePhone = FLOOR(13000000000 + RAND() * (20000000000 - 13000000000));
                    SET company = rand_string(10);
                    SET ctime = FLOOR(1600000000 + RAND() * (1700000000 - 1600000000));
                    SET id = i;
                    set @exec_sql = concat(@exec_sql,"(NULL,", user_id, ",", eid, ",", pid, ",", toid, ",", mark, ",", fromwhere, ",'", name, "','", mobilePhone, "','", company, "',", ctime, "),");
                    -- INSERT INTO `jl_records_10013524` (`id`, `userId`, `eid`, `pid`, `toid`, `mark`, `fromwhere`, `name`, `mobilePhone`, `company`, `ctime`) VALUES (NULL, user_id, eid, pid, toid, mark, fromwhere, name, mobilePhone, company, ctime);
                    SET i = i + 1;
            END WHILE;
            -- 出除sql最后一个逗号
            set @exec_sql = substring(@exec_sql, 1, char_length(@exec_sql)-1);
            -- 预处理并执行sql
            prepare stmt from @exec_sql;
            execute stmt;
            deallocate prepare stmt;
    END;

    CALL add_data(10000);

发表评论:

TOP