MySQL Stored Procedure To Insert Test Record

Create Table

create.sql

-- ------------------------------------------------------------------------------------------------
-- Target: mariadb  Ver 15.1 Distrib 10.4.10-MariaDB, for Linux (x86_64) using readline 5.1
-- Usage: mariadb -uroot -p < ${path_to_sql}/create.sql
-- ------------------------------------------------------------------------------------------------

-- Create database --------------------------------------------------------------------------------
CREATE DATABASE `ticket` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
-- ------------------------------------------------------------------------------------------------

-- Create user ------------------------------------------------------------------------------------
GRANT INSERT, UPDATE, DELETE ON `ticket`.* TO `tkt`@`%` IDENTIFIED BY '1234qwer%^&*';
FLUSH PRIVILEGES;
-- ------------------------------------------------------------------------------------------------

-- Change database --------------------------------------------------------------------------------
USE `ticket`;
-- ------------------------------------------------------------------------------------------------

-- Create tables ----------------------------------------------------------------------------------
CREATE TABLE `user_info`
(
    `uuid`        CHAR(36)    NOT NULL COMMENT 'To distinguish a record',
    `user_id`     VARCHAR(16) NOT NULL COMMENT 'User id to display and search for user',
    `nickname`    VARCHAR(16) NOT NULL COMMENT 'User nickname to display',
    `gender`      TINYINT     NOT NULL DEFAULT 0 COMMENT '0 is unknown, 1 is male, 2 is female',
    `deleted`     BOOL        NOT NULL DEFAULT FALSE COMMENT 'If record had delete is true, else if false',
    `create_date` DATETIME    NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Record create datetime',
    `create_user` VARCHAR(16) NOT NULL DEFAULT 'system' COMMENT 'Record create user',
    `update_date` DATETIME    NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Record update datetime',
    `update_user` VARCHAR(16) NOT NULL DEFAULT 'system' COMMENT 'Record update user',
    PRIMARY KEY (`uuid`),
    UNIQUE KEY `user_id` (`user_id`)
);

CREATE TABLE `login_token`
(
    `uuid`          CHAR(36)    NOT NULL COMMENT 'To distinguish a record',
    `intl_tel_code` VARCHAR(3)  NOT NULL NOT NULL COMMENT 'International telephone code, use for login',
    `tel_num`       VARCHAR(11) NOT NULL COMMENT 'User telephone number, use for login',
    `password`      VARCHAR(300) NOT NULL COMMENT 'Password for login with hash',
    `user_uuid`     CHAR(36)    NOT NULL COMMENT 'Login token related user id',
    `deleted`       BOOLEAN     NOT NULL DEFAULT FALSE COMMENT 'If record had delete is true, else if false',
    `create_date`   DATETIME    NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Record create datetime',
    `create_user`   VARCHAR(16) NOT NULL DEFAULT 'system' COMMENT 'Record create user',
    `update_date`   DATETIME    NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Record update datetime',
    `update_user`   VARCHAR(16) NOT NULL DEFAULT 'system' COMMENT 'Record update user',
    PRIMARY KEY (`uuid`),
    FOREIGN KEY `user_uuid` (`user_uuid`) REFERENCES `user_info` (uuid),
    KEY `user_tel_num` (`intl_tel_code`, `tel_num`)
) ENGINE = InnoDB COMMENT 'use for user login check id and password';
-- ------------------------------------------------------------------------------------------------

Destroy Database

destroy.sql

-- ------------------------------------------------------------------------------------------------
-- Target: mariadb  Ver 15.1 Distrib 10.4.10-MariaDB, for Linux (x86_64) using readline 5.1
-- Usage: mariadb -uroot -p < ${path_to_sql}/destroy.sql
-- ------------------------------------------------------------------------------------------------

-- Drop database ----------------------------------------------------------------------------------
DROP DATABASE IF EXISTS `ticket`;
-- ------------------------------------------------------------------------------------------------

-- Drop user --------------------------------------------------------------------------------------
DROP USER IF EXISTS `tkt`@`%`;
FLUSH PRIVILEGES;
-- ------------------------------------------------------------------------------------------------

Insert Record

init.sql

-- ------------------------------------------------------------------------------------------------
-- Target: mariadb  Ver 15.1 Distrib 10.4.10-MariaDB, for Linux (x86_64) using readline 5.1
-- Usage: mariadb -uroot -p < ${path_to_sql}/init.sql
-- ------------------------------------------------------------------------------------------------

-- Change database --------------------------------------------------------------------------------
USE `ticket`;
-- ------------------------------------------------------------------------------------------------

-- User variable
-- Total user record
SET @USER_RECORD_NUM = 1000;

-- Public stored function, generate general column ------------------------------------------------
-- Generate uuid function
DELIMITER $$
CREATE FUNCTION gen_uuid() RETURNS CHAR(36)
    COMMENT 'Used for generate uuid column default value'
    LANGUAGE SQL
    NOT DETERMINISTIC
    CONTAINS SQL
    SQL SECURITY DEFINER
BEGIN
    DECLARE uuid CHAR(36);
    SET uuid = (SELECT UUID());
    RETURN (uuid);
END;
$$
DELIMITER ;
-- Generate deleted column function
DELIMITER $$
CREATE FUNCTION gen_deleted() RETURNS BOOLEAN
    COMMENT 'Used for generate deleted column default value'
    LANGUAGE SQL
    DETERMINISTIC
    CONTAINS SQL
    SQL SECURITY DEFINER
BEGIN
    DECLARE deleted BOOLEAN;
    SET deleted = FALSE;
    RETURN (deleted);
END;
$$
DELIMITER ;
-- Generate create date column function
DELIMITER $$
CREATE FUNCTION gen_create_date() RETURNS DATETIME
    COMMENT 'Used for generate create_date column default value'
    LANGUAGE SQL
    NOT DETERMINISTIC
    CONTAINS SQL
    SQL SECURITY DEFINER
BEGIN
    DECLARE create_date DATETIME;
    SET create_date = (SELECT NOW());
    RETURN (create_date);
END;
$$
DELIMITER ;
-- Generate create user column function
DELIMITER $$
CREATE FUNCTION gen_create_user() RETURNS VARCHAR(16)
    COMMENT 'Used for generate create_user column default value'
    LANGUAGE SQL
    DETERMINISTIC
    CONTAINS SQL
    SQL SECURITY DEFINER
BEGIN
    DECLARE create_user VARCHAR(16);
    SET create_user = 'system';
    RETURN (create_user);
END;
$$
DELIMITER ;
-- Generate update date column function
DELIMITER $$
CREATE FUNCTION gen_update_date() RETURNS DATETIME
    COMMENT 'Used for generate update_date column default value'
    LANGUAGE SQL
    NOT DETERMINISTIC
    CONTAINS SQL
    SQL SECURITY DEFINER
BEGIN
    DECLARE update_date DATETIME;
    SET update_date = (SELECT NOW());
    RETURN (update_date);
END;
$$
DELIMITER ;
-- Generate update user column function
DELIMITER $$
CREATE FUNCTION gen_update_user() RETURNS VARCHAR(16)
    COMMENT 'Used for generate update_user column default value'
    LANGUAGE SQL
    DETERMINISTIC
    CONTAINS SQL
    SQL SECURITY DEFINER
BEGIN
    DECLARE update_user VARCHAR(16);
    SET update_user = 'system';
    RETURN (update_user);
END;
$$
DELIMITER ;
-- Generate telephone, be careful this function will return same result
DELIMITER $$
CREATE FUNCTION gen_tel_num(tel_num_length INT) RETURNS VARCHAR(11)
    COMMENT 'Generate random telephone number'
    LANGUAGE SQL
    NOT DETERMINISTIC
    CONTAINS SQL
    SQL SECURITY DEFINER
BEGIN
    DECLARE length_count INT DEFAULT 2;
    DECLARE tel_num VARCHAR(11) DEFAULT '1';
    -- 13xx - 19xx
    SET tel_num = CONCAT(tel_num, CAST(FLOOR((RAND() * 7) + 3) AS CHAR));
    WHILE length_count < tel_num_length
        DO
            SET length_count = length_count + 1;
            SET tel_num = CONCAT(tel_num, CAST(FLOOR((RAND() * 10)) AS CHAR));
        END WHILE;
    RETURN tel_num;
END;
$$
DELIMITER ;
-- ------------------------------------------------------------------------------------------------

-- Use stored procedure insert value --------------------------------------------------------------
-- Insert into `user_info`
DELIMITER $$
CREATE PROCEDURE init_user_info()
    COMMENT 'Insert record to init user_info'
BEGIN
    DECLARE v_uuid CHAR(36);
    DECLARE v_user_id VARCHAR(16);
    DECLARE v_nickname VARCHAR(16);
    DECLARE v_gender TINYINT;
    DECLARE v_deleted BOOLEAN;
    DECLARE v_create_date DATETIME;
    DECLARE v_create_user VARCHAR(16);
    DECLARE v_update_date DATETIME;
    DECLARE v_update_user VARCHAR(16);
    -- Use for check record unique
    DECLARE v_is_unique BIGINT;
    -- Insert count
    DECLARE v_insert_count BIGINT;
    DECLARE v_total_insert BIGINT;
    SET v_insert_count = 0;
    SELECT @USER_RECORD_NUM INTO v_total_insert;
    WHILE v_insert_count < v_total_insert
        DO
            -- Init record data
            SELECT gen_uuid() INTO v_uuid;
            SELECT CONCAT('tk_', SUBSTR(MD5(RAND()), 1, 13)) INTO v_user_id;
            SELECT SUBSTR(MD5(RAND()), 1, 7) INTO v_nickname;
            SELECT FLOOR(RAND() * 3) INTO v_gender;
            SELECT gen_deleted() INTO v_deleted;
            SELECT gen_create_date() INTO v_create_date;
            SELECT gen_create_user() INTO v_create_user;
            SELECT gen_update_date() INTO v_update_date;
            SELECT gen_update_user() INTO v_update_user;
            -- Check user_id unique
            SELECT COUNT(*) INTO v_is_unique FROM `user_info` WHERE `user_info`.`user_id` = v_user_id;
            WHILE v_is_unique > 0
                DO
                    SELECT CONCAT('tk_', SUBSTR(MD5(RAND()), 1, 13)) INTO v_user_id;
                END WHILE;
            INSERT INTO `user_info`(`uuid`, `user_id`, `nickname`, `gender`, `deleted`,
                                    `create_date`, `create_user`, `update_date`, `update_user`)
            VALUES (v_uuid, v_user_id, v_nickname, v_gender, v_deleted, v_create_date,
                    v_create_user, v_update_date, v_update_user);
            -- Output log
            SELECT CONCAT('count(s) ', (v_insert_count + 1)) AS 'Inserted into user_info: ';
            SET v_insert_count = v_insert_count + 1;
        END WHILE;
END;
$$
DELIMITER ;
-- Insert into `user_info`(`login_token` had foreign key constraint)
DELIMITER $$
CREATE PROCEDURE init_login_token()
    COMMENT 'Insert record to init user_info'
BEGIN
    DECLARE v_uuid CHAR(36);
    DECLARE v_intl_tel_code VARCHAR(3);
    DECLARE v_tel_num VARCHAR(11);
    DECLARE v_password VARCHAR(16);
    DECLARE v_user_uuid CHAR(36);
    DECLARE v_deleted BOOLEAN;
    DECLARE v_create_date DATETIME;
    DECLARE v_create_user VARCHAR(16);
    DECLARE v_update_date DATETIME;
    DECLARE v_update_user VARCHAR(16);
    -- Use cursor to read each user_info.uuid
    DECLARE v_cursor_done BOOLEAN DEFAULT FALSE;
    DECLARE v_user_info_cursor CURSOR FOR SELECT `user_info`.`uuid` FROM `user_info`;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_cursor_done = TRUE;
    OPEN v_user_info_cursor;
    user_info_loop:
    LOOP
        FETCH v_user_info_cursor INTO v_user_uuid;
        IF v_cursor_done THEN
            LEAVE user_info_loop;
        END IF;
        SELECT gen_uuid() INTO v_uuid;
        SELECT '86' INTO v_intl_tel_code;
        SELECT gen_tel_num(11) INTO v_tel_num;
        SELECT SUBSTR(SHA1(RAND()), 0) INTO v_password;
        SELECT gen_deleted() INTO v_deleted;
        SELECT gen_create_date() INTO v_create_date;
        SELECT gen_create_user() INTO v_create_user;
        SELECT gen_update_date() INTO v_update_date;
        SELECT gen_update_user() INTO v_update_user;
        INSERT INTO `login_token`(`uuid`, `intl_tel_code`, `tel_num`, `password`, `user_uuid`, `deleted`,
                                  `create_date`, `create_user`, `update_date`, `update_user`)
        VALUES (v_uuid, v_intl_tel_code, v_tel_num, v_password, v_user_uuid, v_deleted, v_create_date,
                v_create_user, v_update_date, v_update_user);
        SELECT CONCAT('user_info.user_id ', v_user_uuid) AS 'Insert into login_token: ';
    END LOOP;
    CLOSE v_user_info_cursor;
END;
$$
DELIMITER ;
-- ------------------------------------------------------------------------------------------------

-- Call procedure to init -------------------------------------------------------------------------
CALL init_user_info();
CALL init_login_token();
-- ------------------------------------------------------------------------------------------------