mysql测试语句创建写入存储过程调用8.0

-- 创建表格

 

CREATE TABLE my_table (
id INT AUTO_INCREMENT,
name VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci,
email VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci,
password VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci,
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- 插入100条随机生成的数据
INSERT INTO my_table (name, email, password)
VALUES ('John Doe', 'johndoe@example.com', 'password123'),
('Jane Doe', 'janedoe@example.com', 'password123'),
('User 99', 'user99@example.com', 'password123'),
('User 100', 'user100@example.com', 'password123');

select * from test

以下是建表语句及对应的存储过程：

-- 建表语句

CREATE TABLE test (
id INT AUTO_INCREMENT,
name VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci,
email VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci,
password VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci,
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

DELIMITER $$

-- 创建存储过程
CREATE PROCEDURE insert_data()
BEGIN
DECLARE i INT DEFAULT 1;

WHILE i <= 1000 DO
INSERT INTO test (name, email, password)
VALUES (CONCAT('User ', i), CONCAT('user', i, '@example.com'), CONCAT('password', i));

SET i = i + 1;
END WHILE;
END$$

 

-- 调用存储过程
CALL insert_data1();

DELIMITER ;