mysql插入大量数据函数

 创建表和函数sql

CREATE TABLE `test_100w` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) DEFAULT NULL,
  `email` varchar(50) DEFAULT NULL,
  `phone` varchar(20) DEFAULT NULL,
  `gender` tinyint(4) DEFAULT NULL,
  `age` tinyint(4) DEFAULT NULL,
  `password` varchar(100) DEFAULT NULL,
  `gmt_create` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `gmt_update` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `id_test_100w_name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=20000001 DEFAULT CHARSET=utf8mb4 COMMENT='测试添加100万数据';

-- 插入100w数据，创造函数
DELIMITER $$   -- 写函数之前必须要写，标志
CREATE FUNCTION mock_data()
RETURNS INT
BEGIN
 DECLARE num INT DEFAULT 10000000;
 DECLARE i INT DEFAULT 0;
 WHILE i<num DO
	-- 	插入语句
 INSERT INTO test_100w (`name`, `email`,`phone`,`gender`,`password`,`age`)
VALUES(CONCAT('用户',i),'1129678591@qq.com',CONCAT('18',FLOOR(RAND()*((999999999-100000000) + 100000000))),FLOOR(RAND()*2),UUID(),FLOOR(RAND()*100));
set i = i+1;
 END WHILE;
RETURN i;
END;


-- 执行函数
select mock_data();
-- 删除函数
DROP FUNCTION mock_data;

SELECT * FROM file_upload

select * FROM	 test_100w WHERE `NAME` = '用户99999';

-- 
EXPLAIN select * FROM	 test_100w WHERE `NAME` = '用户99999';

-- 添加索引，name
CREATE INDEX id_test_100w_name ON test_100w(`name`);

SELECT count(`name`) FROM test_100w
SELECT count(1) FROM test_100w
SELECT count(0) FROM test_100w

SELECT count(*) FROM test_100w

SELECT * FROM `test_100w`  WHERE age = 23   -- LIMIT 9999000,10


-- select FLOOR(RAND()*((999999999-100000000) + 100000000))

学习自狂神的b站视频

视频地址：【狂神说Java】MySQL最新教程通俗易懂_哔哩哔哩_bilibili笔记资料交流都在我们的平台：www.kuangstudy.com秦疆老师Java系列课程之MySQL系列SQL基础语法、函数、索引、sql编程、事务、事务原则、备份恢复，权限数据库设计、三大范式、JDBC、SQL注入、数据库连接池。等所有知识都在这里了狂神说Java系列，努力打造通俗易懂的教程QQ交流群 : 664386224https://www.bilibili.com/video/BV1NJ411J79W?p=31