学习mysql 过程中，需要创建测试数据， 并让多人每人一个数据库连接并进行作业检查。

整合部分批处理创建数据

批量创建数据库

DELIMITER $

CREATE PROCEDURE CreateDatabases()

BEGIN

DECLARE i INT DEFAULT 1;

WHILE i <= 10 DO

SET @dbName = CONCAT('student_db', i);

SET @createDBQuery = CONCAT('CREATE DATABASE ', @dbName);

PREPARE createDB FROM @createDBQuery;

EXECUTE createDB;

DEALLOCATE PREPARE createDB;

SET i = i + 1;

END WHILE;

END$

DELIMITER ;

CALL CreateDatabases();

批量修改数据库编码

-- 创建完数据库，未设置编码，可能导致无法存储中文数据

-- ALTER DATABASE `db_stu1` CHARACTER SET 'utf8mb4';

DELIMITER$

CREATE PROCEDURE ModifyDatabasesCode()

BEGIN

DECLARE i INT DEFAULT 1;

WHILE i <= 10 DO

SET @dbName = CONCAT('db_stu', i);

SET @createDBQuery = CONCAT('ALTER DATABASE ', @dbName,' CHARACTER SET "utf8mb4"');

PREPARE createDB FROM @createDBQuery;

EXECUTE createDB;

DEALLOCATE PREPARE createDB;

SET i = i + 1;

END WHILE;

END$

DELIMITER ;

CALL ModifyDatabasesCode();

批量删除数据库

-- drop database db_stu5;

DELIMITER$

CREATE PROCEDURE DropDatabases()

BEGIN

DECLARE i INT DEFAULT 1;

WHILE i <= 10 DO

SET @dbName = CONCAT('db_stu', i);

SET @createDBQuery = CONCAT('DROP DATABASE if EXISTS ', @dbName);

PREPARE createDB FROM @createDBQuery;

EXECUTE createDB;

DEALLOCATE PREPARE createDB;

SET i = i + 1;

END WHILE;

END$

DELIMITER ;

CALL DropDatabases();

批量创建用户并授权

-- GRANT ALL PRIVILEGES ON test3.* TO 'test3'@'%' IDENTIFIED BY 'test3';

DELIMITER$

CREATE PROCEDURE CreateUsers()

BEGIN

DECLARE i INT DEFAULT 1;

WHILE i <= 10 DO

SET @userName = CONCAT('stu', i);

set @dbName = CONCAT('db_stu',i);

SET @grantSql = CONCAT("GRANT ALL PRIVILEGES ON ", @dbName,".* TO '",@userName,"'@'%' IDENTIFIED BY '",@userName,"'");

PREPARE createDB FROM @grantSql;

EXECUTE createDB;

DEALLOCATE PREPARE createDB;

SET i = i + 1;

END WHILE;

END$

DELIMITER ;

CALL CreateUsers();

批量插入测试数据

-- insert into student (name,age,create_time) values ('张三',15,now());

DELIMITER$

CREATE PROCEDURE insertStudent()

BEGIN

DECLARE i INT DEFAULT 1;

WHILE i <= 1000 DO

set @colAge = 30*RAND(i);

SET @colName = CONCAT('张三', i);

SET @createInsert = CONCAT("insert into student (name,age,create_time) values ('", @colName,"',",@colAge,", NOW())");

PREPARE insertDB FROM @createInsert;

EXECUTE insertDB;

DEALLOCATE PREPARE insertDB;

SET i = i + 1;

END WHILE;

END$

DELIMITER ;

CALL insertStudent();