MySQL 批量插入/填充数据 - 实践

最新推荐文章于 2024-08-01 09:15:00 发布

宁小法

最新推荐文章于 2024-08-01 09:15:00 发布

阅读量7.4k

点赞数

分类专栏： MYSQL 文章标签： mysql 批量插入存储过程复制表数据

本文链接：https://blog.csdn.net/william_n/article/details/90753351

版权

MYSQL 专栏收录该内容

44 篇文章 6 订阅

订阅专栏

1.应用场景

应用场景之一：

有时，我们需要创建表并填充大量测试数据。

2.学习/操作

1.文档

TBD

2. 整理输出

环境

Windows 10 64位专业版 i7 32G 机械硬盘 474G

2.1 方法一: 通过储存过程

MySQL支持的注释符： -- 与 #

1.我们先来新建一个表，一个主键列，一个普通索引列，一个普通列

CREATE TABLE IF NOT EXISTS `batch_insert_data` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `a` bigint DEFAULT NULL,
  `b` bigint DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `Normal_Index_a` (`a`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

结果

接着使用存储过程往表里插入 10w 测试数据.

Note

如果对 mysql 的存储过程不熟悉，请看代码中的注释，应该能看得懂.

同时参见：13丨什么是存储过程，在实际项目中用得多么？-极客时间

创建存储过程

#定义分割符号，mysql 默认分割符为分号; 这里定义为 //
#分隔符的作用主要是告诉mysql遇到下一个 // 符号即执行上面这一整段sql语句
DELIMITER //

#创建一个存储过程，并命名为 batch_insert_data
CREATE PROCEDURE batch_insert_data(IN number INT)

#下面这段就是表示循环往表里插入10w条数据
BEGIN
DECLARE i int;
SET i=1;
WHILE(i <= number)DO
INSERT INTO batch_insert_data VALUES(i, i, i);
SET i=i+1;
END WHILE;
END // #这里遇到//符号，即执行上面一整段sql语句

#恢复mysql分隔符为;

DELIMITER ;

结果如下：

HeidiSQL -- Windows

#调用存储过程 注意，不要重复执行上面的存储过程的创建语句，否则提示报错.

CALL batch_insert_data(100000);

插入进行中..

heidiSQL已经出现卡顿.

可以看到磁盘io很高.

原因：

机械硬盘+写操作+索引

精心等待吧....

数据插入完成! 共计用时13分钟4秒

结果如下：

备注：

个人开发机上会需要比较长的时间，预计十几分钟左右[根据电脑配置不同而有变化]

删除存储过程

DROP PROCEDURE batch_insert_data; -- 如果想删除

也可以使用可视化工具如heidiSQL进行操作.

2.2 方式二：利用事务进行批量插入 // 20191121

数据表, 为了简便，我们创建一个数据表 batch_insert_data_1，只有 id 一个字段

使用如下命令即可：

CREATE TABLE IF NOT EXISTS `batch_insert_data_1` (
`id` bigint unsigned NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

截图：

插入数据, 这里数据量仍为10w

BEGIN
-- 当前数据行
DECLARE i INT DEFAULT 0;
-- 最大数据行数
DECLARE max_num INT DEFAULT 100000;
-- 关闭自动提交
SET autocommit=0;
REPEAT
SET i=i+1;
-- 向t1表中插入数据
INSERT INTO t1(id) VALUES(i);
UNTIL i = max_num
END REPEAT;
-- 提交事务
COMMIT;
END

执行失败

TBD

结果

TBD

2.3 方法三: 通过[全量/部分]拷贝数据表数据到新表

前提

数据表结构相同[也可以不同], 如t1, t2 , 都只有id一栏

INSERT INTO t2 SELECT * FROM t1 LIMIT 99;

这里执行如下:

INSERT INTO `count_one_hundred_thousand` SELECT * FROM `batch_insert_data`;

count_one_hundred_thousand 与 batch_insert_data数据结构完全相同.

batch_insert_data表的十万条记录插入, 用时13:04, 但是上面这种方式, 只用时1.296秒.

后续补充

...

3.问题/补充

1.通过导出的SQL文件[含记录], 再导入到其他数据库中?

如果失败.

排查过程:

1.查看导出的SQL文件是否存在问题.

2.查看目标数据库的编码格式以及排序规则是否与导出的SQL文件中内容相同,

3.使用可视化客户端工具失败, 可以考虑使用另外一种可视化客户端工具,

建议: 还是使用相同的工具.

这里我在家里电脑上安装有Navicat和heidiSQL, 公司电脑上安装的heidiSQL[因为公司不允许使用破解版软件]

如下:

count_million表有一百万数据.