【MySQL高级】数据生成与插入脚本编写与使用

已于 2024-04-07 14:27:00 修改
阅读量2.1k 收藏 11
点赞数 2
分类专栏： # MySQL 文章标签： mysql android 数据库
于 2023-06-27 19:36:48 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/laodanqiu/article/details/131423941
版权

文章目录

批量插入数据脚本

场景：需要插入50万个数据，分10次插入，每次查词5万条数据

建表sql

--  部门表
create table dept(
id int unsigned primary key auto_increment,
--  部门号
deptno mediumint unsigned not null default 0,
--  部门名称
dname varchar(20) not null default "",
--  楼层位置
loc varchar(13) not null default ""
)engine=innodb default charset=GBK;

--  员工表
CREATE TABLE emp(
id int unsigned primary key auto_increment,
--  员工编号
empno mediumint unsigned not null default 0,
--  员工名字
ename varchar(20) not null default "",
--  工作
job varchar(9) not null default "",
--  上级编号
mgr mediumint unsigned not null default 0,
-- 入职时间
hiredate date not null,
--  薪水
sal decimal(7,2) not null,
--  红利
comm decimal(7,2) not null,
--  部门编号
deptno mediumint unsigned not null default 0
)ENGINE=INNODB DEFAULT CHARSET=GBK;

函数创建与使用

由于开启过慢查询日志，开启了bin-log，我们就必须为function指定一个参数，否则使用函数会报错。

# log_bin_trust_function_creators 默认是关闭的 需要手动开启
mysql> SHOW VARIABLES LIKE 'log_bin_trust_function_creators';
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| log_bin_trust_function_creators | OFF   |
+---------------------------------+-------+
1 row in set (0.00 sec)

mysql> SET GLOBAL log_bin_trust_function_creators=1;
Query OK, 0 rows affected (0.00 sec)

上述修改方式MySQL重启后会失败，在配置文件my.ini/my.cnf下添加如下配置之后修改永久有效。

[mysqld]
# 如果上面的不行，就换成下面的
log_bin_trust_function_creators=ON
log_bin_trust_function_creators=1

工具函数创建

随机生成字符串
--  本来执行一段sql的结束符号是;，但是因为函数里面有;，先将结束符号修改为$$
DELIMITER $$
CREATE FUNCTION rand_string(n INT) RETURNS VARCHAR(255)
BEGIN
    --  声明一个字符串并赋值
    DECLARE chars_str VARCHAR(100) DEFAULT 'abcdefghijklmnopqrstuvwsyzABCDEFGHIJKLMNOPQRSTUVWXYZ';
    DECLARE return_str VARCHAR(255) DEFAULT '';
    DECLARE i INT DEFAULT 0;
    WHILE i < n DO
    --  CONCAT：拼接；SUBSTRING：截取
    SET return_str = CONCAT(return_str,SUBSTRING(chars_str,FLOOR(1+RAND()*52),1));
    SET i = i + 1;
    END WHILE;
    RETURN return_str;
END $$
随机产生部门编号
DELIMITER $$
CREATE FUNCTION rand_num() RETURNS INT(5)
BEGIN
    DECLARE i INT DEFAULT 0;
    SET i = FLOOR(100 + RAND() * 10);
    RETURN i;
END $$

创建存储过程

向dept表批量插入数据
DELIMITER $$
--  IN START INT(10),IN max_num INT(10):从什么时候开始，到什么时候结束
CREATE PROCEDURE insert_dept(IN START INT(10),IN max_num INT(10))
BEGIN
DECLARE i INT DEFAULT 0;
--  将自动提交设置为零，后面批量提交，提高插入效率
    SET autocommit = 0;
    --  循环
    REPEAT
    SET i = i + 1;
    --  插入数据
    INSERT INTO dept(deptno,dname,loc) VALUES((START + i),rand_string(10),rand_string(8));
    --  循环的结束条件
    UNTIL i = max_num
    END REPEAT;
    COMMIT;
END $$
向emp表批量插入数据
DELIMITER $$
CREATE PROCEDURE insert_emp(IN START INT(10),IN max_num INT(10))
BEGIN
DECLARE i INT DEFAULT 0;
    SET autocommit = 0;
    REPEAT
    SET i = i + 1;
    INSERT INTO emp(empno,ename,job,mgr,hiredate,sal,comm,deptno) VALUES((START + i),rand_string(6),'SALESMAN',0001,CURDATE(),2000,400,rand_num());
    UNTIL i = max_num
    END REPEAT;
    COMMIT;
END $$

删除PROCEDURE

如果存储过程有问题，需要先删除原本的存储过程，再创建新的存储过程

drop PROCEDURE insert_emp;

调用存储过程

# 调用存储过程向dept表插入10个部门，id从100开始。
# 改回；结束
DELIMITER ;
CALL insert_dept(100,10);

# 调用存储过程向emp表插入50万条数据。
DELIMITER ;
CALL insert_emp(100001,500000);

执行过程

mysql> --  本来执行一段sql的结束符号是;，但是因为函数里面有;，先将结束符号修改为$$
mysql> DELIMITER $$
mysql> CREATE FUNCTION rand_string(n INT) RETURNS VARCHAR(255)
    -> BEGIN
    ->     --  声明一个字符串并赋值
    ->     DECLARE chars_str VARCHAR(100) DEFAULT 'abcdefghijklmnopqrstuvwsyzABCDEFGHIJKLMNOPQRSTUVWXYZ';
    ->     DECLARE return_str VARCHAR(255) DEFAULT '';
    ->     DECLARE i INT DEFAULT 0;
    ->     WHILE i < n DO
    ->     --  CONCAT：拼接；SUBSTRING：截取
    ->     SET return_str = CONCAT(return_str,SUBSTRING(chars_str,FLOOR(1+RAND()*52),1));
    ->     SET i = i + 1;
    ->     END WHILE;
    ->     RETURN return_str;
    -> END $$
Query OK, 0 rows affected (0.01 sec)

mysql> DELIMITER $$
mysql> CREATE FUNCTION rand_num() RETURNS INT(5)
    -> BEGIN
    ->     DECLARE i INT DEFAULT 0;
    ->     SET i = FLOOR(100 + RAND() * 10);
    ->     RETURN i;
    -> END $$
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> DELIMITER $$
mysql> --  IN START INT(10),IN max_num INT(10):从什么时候开始，到什么时候结束
mysql> CREATE PROCEDURE insert_dept(IN START INT(10),IN max_num INT(10))
    -> BEGIN
    -> DECLARE i INT DEFAULT 0;
    -> --  将自动提交设置为零，后面批量提交，提高插入效率
    ->     SET autocommit = 0;
    ->     --  循环
    ->     REPEAT
    ->     SET i = i + 1;
    ->     --  插入数据
    ->     INSERT INTO dept(deptno,dname,loc) VALUES((START + i),rand_string(10),rand_string(8));
    ->     --  循环的结束条件
    ->     UNTIL i = max_num
    ->     END REPEAT;
    ->     COMMIT;
    -> END $$
Query OK, 0 rows affected, 2 warnings (0.01 sec)

mysql> DELIMITER $$
mysql> CREATE PROCEDURE insert_emp(IN START INT(10),IN max_num INT(10))
    -> BEGIN
    -> DECLARE i INT DEFAULT 0;
    ->     SET autocommit = 0;
    ->     REPEAT
    ->     SET i = i + 1;
    ->     INSERT INTO emp(empno,ename,job,mgr,hiredate,sal,comm,deptno) VALUES((START + i),rand_string(6),'SALESMAN',0001,CURDATE(),2000,400,rand_num());
    ->     UNTIL i = max_num
    ->     END REPEAT;
    ->     COMMIT;
    -> END $$
Query OK, 0 rows affected, 2 warnings (0.01 sec)

mysql> --  调用存储过程向dept表插入10个部门，id从100开始。
mysql> --  改回；结束
mysql> DELIMITER ;
mysql> CALL insert_dept(100,10);
Query OK, 0 rows affected (0.01 sec)
mysql> select * from dept;
+----+--------+------------+----------+
| id | deptno | dname      | loc      |
+----+--------+------------+----------+
|  1 |    101 | wcUtjNoeHY | UCcGZduM |
|  2 |    102 | BsHUBXkijy | MrwiAcLz |
|  3 |    103 | mLUrbdokks | iOsAsKiL |
|  4 |    104 | gzcMDIDTKQ | aGdznPkG |
|  5 |    105 | zFDbskEuiG | HsQcOMsB |
|  6 |    106 | FyCoNWwssm | QtvWssWn |
|  7 |    107 | BTOmVPnXVM | ZKBDoHtW |
|  8 |    108 | DcAsLkUTHF | hSRHLGYb |
|  9 |    109 | iOtBBcJopJ | BEtgAgfh |
| 10 |    110 | wMwUjmFsYM | PQGLKrEW |
+----+--------+------------+----------+
10 rows in set (0.00 sec)

mysql> DELIMITER ;
mysql> CALL insert_emp(100001,500000);
Query OK, 0 rows affected (39.19 sec)

文章说明

本文章为本人学习尚硅谷的学习笔记，文章中大部分内容来源于尚硅谷视频（点击学习尚硅谷相关课程），也有部分内容来自于自己的思考，发布文章是想帮助其他学习的人更方便地整理自己的笔记或者直接通过文章学习相关知识，如有侵权请联系删除，最后对尚硅谷的优质课程表示感谢。

Hello Dam
关注
  • 2
    点赞
  • 踩
  • 11
    收藏
    觉得还不错? 一键收藏
  • 打赏
    打赏
  • 0
    评论
专栏目录
创建数据库脚本
weixin_42689746的博客
11-27 959
创建数据库
MySQL创建存储过程批量插入10万条数据
01-19
MySQL创建存储过程批量插入10万条数据 存储过程 1、首先防止主键冲突，我们清空表。 TRUNCATE table A_student; 2、编写存储过程 delimiter ‘$’; CREATE PROCEDURE batchInsert(in args int) BEGIN declare i int ...
mysql中造3千条数据（3种方法）
09-11
mysql中造3千条数据（3种方法） 1、存储过程 2、DataFactory 3、利用mysql中的函数
mysql快速生成百万级测试数据
最新发布
2301_81547508的博客
05-31 631
最近在跟着学习一套开源的电商项目，建了一个项目库所需的数据库。表是有了，但是数据没有。一个电商项目怎么能没有多一点的数据呢，于是决定自己一个表造个几十万数据（总共71个表）。在数据库服务器上执行命令，生成内容为1~100000的文件(修改为自己的文件名称（但是路径不要修改）。登录mysql，并执行。后修改为自己的库名，
【脚本工具】Python在MySQL批量造测试数据
fengyupotest的博客
06-29 1413
批量生成测试数据
从零开始编写数据库sql脚本并运行
热门推荐
weixin_44924882的博客
07-11 2万+
编写sql脚本 一，概述 在日常开发中，我们一般是通过数据库的可视化软件例如Navicat来管理数据库，包括创建删除修改等，这无疑是非常便捷的，但是当进行项目搬迁的时候，用Navicat一个一个的重新进行编写，就有点麻烦了，尤其是如果数据库中要插入大量的数据时，此时如果通过sql脚本，那将大大的减少我们的工作量。 二，下面我们就来编写一个简单的sql脚本： 1，创建数据库 创建数据库时我们需要指定数据库的编码格式，以及数据库的编码排序规则，详细的代码如下： drop database if EXISTS d
mysql批量插入数据脚本
weixin_44867255的博客
03-20 1779
DELIMITER $ DROP PROCEDURE if EXISTS pro_batch_insert$ CREATE PROCEDURE pro_batch_insert(IN insert_num INT) BEGIN DECLARE i INT DEFAULT 1; WHILE i<=insert_num DO INSERT INTO admin(username,`password`) VALUES(CONCAT('Rose',i),'666'); .
MySQL自动生成亿级数据
DF10f-0001A
03-20 500
MySQL自动生成亿级数据
MySQL批量插入数据脚本
09-10
本话题将详细介绍在shell环境下如何编写一个脚本来实现MySQL的批量插入。 首先，我们来看一下提供的示例脚本： ```bash #!/bin/bash i=1; MAX_INSERT_ROW_COUNT=$1; while [ $i -le $MAX_INSERT_ROW_COUNT ] do ...
MySQL 数据备份与还原的示例代码
09-09
总结来说，MySQL的数据备份与还原提供了多种策略，从使用mysqldump生成SQL脚本到直接复制物理文件，再到mysqlhotcopy的热备份。选择哪种方法取决于你的需求，如数据量、存储引擎类型、备份速度以及是否能容忍服务...
模拟数据生成写入到mysql
05-11
在开发和测试阶段，为了快速填充数据库并进行功能验证，通常会使用数据生成工具。例如，Faker是一个流行的Python库，它可以生成各种类型的假数据，如姓名、地址、电话号码等。在Java领域，我们可以使用Mockito或...
批量生成mysql中文测试数据
07-18
通过运行这个脚本，用户可以批量生成所需的中文测试数据，并将其导入到指定的MySQL数据库表中。 2. `word.php`：此文件可能用于生成随机的中文单词或短语。在生成测试数据时，随机的中文词汇可以模拟真实世界中的...
sqlite知识点（脚本批量造数据等）
の博客
04-24 135
sqlite数据库相关知识点
MySQL--执行mysql脚本及其脚本编写
03-26 8499
首要步骤(一般可省略):开启mysql服务(默认是开机时就自动运行了):控制面板-->管理工具-->服务,在里面找到名称为mysql的项,双击就可看它的服务状态是启动还是停止,把它设为启动连接mysql:在命令行下输入 mysql -h localhost -u root -p回车,然后输入密码即可;或直接运行mysql自带的连接工具,然后输入密码即可.1.编写sql脚本,假设内容如下:
Mysql造数据shell脚本
手提青灯觅佳人的博客
07-20 728
Mysql造数据shell脚本 直接上代码 #!/bin/bash i=$1; STOP=$2; while [ $i -le $STOP ] do mysql -u[user] -p[password] [database] -e "INSERT INTO test(api_user_id, app_key`, app_secret, dead_time, create_time, last_update_time, create_user_id, valid) VALUES ($i, 'om
mysql 快速生成百万条测试数据
weixin_30469895的博客
10-14 1095
1、生成思路 利用mysql内存表插入速度快的特点，先利用函数和存储过程在内存表中生成数据，然后再从内存表插入普通表中 2、创建内存表及普通表 CREATE TABLE `vote_record_memory` ( `id` INT (11) NOT NULL AUTO_INCREMENT, `user_id` VARCHAR (20) NOT NULL, `vote_...
MySQL手动生成sql脚本一次性导入并更新超大量数据
qq_45799707的博客
06-26 1848
八千万数据大表大批量一次性更新数据
用linux脚本插入10w级的数据数据库为mysql
08-12
### 回答1： 插入大量数据到MySQL数据库可以使用以下步骤： 1. 准备数据：你需要一个数据文件，包含需要插入的所有数据。每行都是一条记录，字段之间使用适当的分隔符分开。 2. 创建数据库：如果没有相应的数据库，请先创建一个。 3. 创建表：创建一个表来存储数据。 4. 导入数据：使用MySQL的"LOAD DATA INFILE"命令导入数据。该命令可以从文件中快速加载大量数据到MySQL表中。 以下是使用bash脚本的示例代码： ``` #!/bin/bash # Set database credentials user="your_username" password="your_password" database="your_database" table="your_table" # Set the file path file="/path/to/your/data.csv" # Import data mysql -u $user -p$password $database -e "LOAD DATA INFILE '$file' INTO TABLE $table FIELDS TERMINATED BY ',' ENCLOSED BY '\"' LINES TERMINATED BY '\n'" ``` 请注意，在上面的代码中，字段的分隔符是逗号，字段是用双引号括起来的，并且行的终止符是换行符。如果您的数据文件不是这样格式化的，请相应地修改代码。 ### 回答2： 使用Linux脚本插入10万级的数据到MySQL数据库是可行的。下面我将为你提供一个示例脚本： ```bash #!/bin/bash # 设置数据库连接信息 DB_HOST="localhost" DB_USER="your_username" DB_PASS="your_password" DB_NAME="your_database" # 设置循环插入数据的次数 INSERT_COUNT=100000 # 创建插入数据的SQL语句 INSERT_SQL="INSERT INTO your_table (column1, column2, column3) VALUES " # 生成待插入的数据 generate_data() { for ((i=0; i<$INSERT_COUNT; i++)); do echo "('$RANDOM', 'data$i', 'info$i')" done } # 连接到MySQL数据库并插入数据 insert_data() { mysql -h $DB_HOST -u $DB_USER -p$DB_PASS -D $DB_NAME -e "$INSERT_SQL $(generate_data);" } # 执行插入数据的函数 insert_data ``` 在这个示例脚本中，首先我们设置了数据库连接信息，包括数据库主机地址、用户名、密码和数据库名。然后设置了要插入的数据数量，可以根据需要进行调整。接下来，我们创建了插入数据的SQL语句，其中使用了一个函数generate_data()来动态生成要插入的数据。在这个示例中，我们使用了一个简单的循环来生成插入数据。最后，我们使用mysql命令连接到MySQL数据库，并执行插入数据的SQL语句。 你可以将以上脚本保存为一个.sh文件，并赋予执行权限。然后在终端中运行该脚本即可将数据插入到MySQL数据库中。请确保在运行脚本之前已经安装了MySQL数据库并正确配置了相关的权限和表结构。 ### 回答3： 使用Linux脚本插入10万条数据到MySQL数据库可以通过以下步骤实现： 1. 首先，需要编写一个脚本文件，比如示例脚本名称为`insert_data.sh`。 2. 在脚本文件中，需要设置MySQL数据库的连接信息，如数据库名称、用户名、密码等。可以使用`mysql`命令连接数据库，并指定相关参数。 3. 创建一个循环结构，用于插入数据。循环从1到10万，并逐次执行插入数据的操作。 4. 在每次循环中，生成要插入的数据。可以使用`echo`命令将生成的数据赋值给一个变量。 5. 使用`INSERT INTO`语句将生成的数据插入到数据库表中。可以使用`mysql`命令来执行这个插入操作，注意在命令行中指定要使用的数据库和数据表。 6. 执行脚本文件。可以使用`bash`命令来运行脚本，如`bash insert_data.sh`。 7. 等待脚本执行完成，即可完成10万条数据的插入操作。 需要注意的是，在插入大量数据时，可以考虑使用事务进行优化，以提高插入速度和数据的完整性。具体的事务使用方法可以参考MySQL的官方文档或相关教程。 这是一个简单的实现思路，具体的脚本内容和细节根据实际情况可以进行调整，比如生成的数据格式、插入的表结构等。

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

Hello Dam

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值