MySQL CREATE TABLE and INSERT 2 Million Test Data for `machine_status_active`

CREATE TABLE and INSERT 2 Million Test Data for machine_status_active Table in MySQL

This script demonstrates how to create a table named machine_status_active in MySQL and populate it with 2 million test data records. The table stores information about machine status, including source, name, machine code, peripheral name, state, creation time, and machine ID.

1. CREATE TABLE Statement:

CREATE TABLE `machine_status_active` (
  `machinestatusid` bigint NOT NULL AUTO_INCREMENT COMMENT '主键',
  `source` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '来源',
  `machineName` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '设备名称',
  `machinecode` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '机器码',
  `peripheralName` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '硬件名称',
  `peripheralState` int NOT NULL COMMENT '硬件状态1正常，0异常',
  `createTime` datetime NOT NULL COMMENT '创建时间',
  `machineId` bigint NOT NULL COMMENT '设备id',
  PRIMARY KEY (`machinestatusid`) USING BTREE,
  INDEX `idx_createtime`(`createTime` ASC, `machinecode` ASC, `source` ASC) USING BTREE,
  INDEX `idx_source`(`source` ASC, `machinecode` ASC, `peripheralName` ASC) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1000001 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

2. INSERT Data Statement:

INSERT INTO `machine_status_active` (`source`, `machineName`, `machinecode`, `peripheralName`, `peripheralState`, `createTime`, `machineId`)
VALUES
('source1', 'machineName1', 'machinecode1', 'peripheralName1', 1, '2022-01-01 00:00:00', 1),
('source2', 'machineName2', 'machinecode2', 'peripheralName2', 0, '2022-01-01 00:00:01', 2),
('source3', 'machineName3', 'machinecode3', 'peripheralName3', 1, '2022-01-01 00:00:02', 3),
...
('source200000', 'machineName200000', 'machinecode200000', 'peripheralName200000', 0, '2022-01-01 00:00:200000', 200000);

3. Generating Data:

To generate 2 million rows of data, we can use a script that iterates and inserts data. It's important to optimize the insertion process for efficiency:

• Use Prepared Statements: Avoid parsing the same query repeatedly by using prepared statements. This enhances performance, especially for large datasets.
• Bulk Inserts: Insert data in batches rather than one row at a time. This reduces the number of round trips to the database server, improving performance.

Example Script:

SET @start_time = NOW();
SET @total_rows = 2000000; 

-- Prepare the INSERT statement
PREPARE stmt FROM 'INSERT INTO `machine_status_active` (`source`, `machineName`, `machinecode`, `peripheralName`, `peripheralState`, `createTime`, `machineId`) VALUES (?, ?, ?, ?, ?, ?, ?)';

-- Start inserting data in batches
SET @batch_size = 1000; -- Adjust the batch size as needed
SET @row_count = 0;
WHILE @row_count < @total_rows DO
    SET @batch_start = @row_count + 1;
    SET @batch_end = LEAST(@row_count + @batch_size, @total_rows);
    
    -- Insert data in a batch
    SET @current_row = @batch_start;
    WHILE @current_row <= @batch_end DO
        SET @source = CONCAT('source', @current_row);
        SET @machineName = CONCAT('machineName', @current_row);
        SET @machinecode = CONCAT('machinecode', @current_row);
        SET @peripheralName = CONCAT('peripheralName', @current_row);
        SET @peripheralState = (SELECT IF(@current_row % 2 = 0, 0, 1));
        SET @createTime = DATE_ADD('2022-01-01 00:00:00', INTERVAL @current_row - 1 SECOND);
        SET @machineId = @current_row;
        EXECUTE stmt USING @source, @machineName, @machinecode, @peripheralName, @peripheralState, @createTime, @machineId;
        SET @current_row = @current_row + 1;
    END WHILE;
    
    SET @row_count = @row_count + @batch_size;
END WHILE;

-- Deallocate the prepared statement
DEALLOCATE PREPARE stmt;

SET @end_time = NOW();
SELECT TIMEDIFF(@end_time, @start_time) AS 'Time taken';

This script efficiently generates and inserts 2 million test data records into the machine_status_active table. Remember to adjust the batch size and data generation logic based on your system's resources and desired data distribution.

Note: This script assumes that the machine_status_active table already exists and is defined as shown in the CREATE TABLE statement.

This script will help you efficiently generate test data for your MySQL database, facilitating performance testing and data analysis.