MYSQL插入课程，学生，成绩测试数据14W行，并导出数据然后导入到DB2

李大川的博客

已于 2023-08-11 14:04:12 修改

阅读量137

点赞数

文章标签：数据库

于 2023-08-10 16:50:10 首次发布

本文链接：https://blog.csdn.net/liys0811/article/details/132213546

版权

课程表
create table Course
(
c_id int PRIMARY KEY,
name varchar(10)
)
数据20条

学生表:

create table Student
(
id int PRIMARY KEY,
name varchar(10)
)
数据7000条

学生成绩表SC

CREATE table SC
(
sc_id int PRIMARY KEY,
s_id int,
c_id int,
score int

)
数据 140000条

插入随机的7000个学生名
vi /home/mysql/liys/insert_Student.sql

delimiter // #定义标识符为双斜杠
drop procedure if exists insert_Student; #如果存在test存储过程则删除
create procedure insert_Student() #创建无参存储过程,名称为test
begin
declare i int; #申明变量
set @SURNAME = '王李张刘陈杨黄赵吴周徐孙马朱胡郭何高林罗郑梁谢宋唐位许韩冯邓曹彭曾萧田董潘袁于蒋蔡余杜叶程苏魏吕丁任沈姚卢姜崔钟谭陆汪范金石廖贾夏
韦傅方白邹孟熊秦邱江尹薛阎段雷侯龙史陶黎贺顾毛郝龚邵万钱严覃武戴莫孔向汤';

set @NAME = '丹举义之乐书乾云亦从代以伟佑俊修健傲儿元光兰冬冰冷凌凝凡凯初力勤千卉半华南博又友同向君听和哲嘉国坚城夏夜天奇奥如妙子存季孤宇安宛宸寒寻尔尧山>岚峻巧平幼康建开弘强彤彦彬彭心忆志念怀怜恨惜慕成擎敏文新旋旭昊明易昕映春昱晋晓晗晟景晴智曼朋朗杰松枫柏柔柳格桃梦楷槐正水沛波泽洁洋济浦浩海涛润涵渊源溥濮瀚
灵灿炎烟烨然煊煜熙熠玉珊珍理琪琴瑜瑞瑶瑾璞痴皓盼真睿碧磊祥祺秉程立竹笑紫绍经绿群翠翰致航良芙芷苍苑若茂荣莲菡菱萱蓉蓝蕊蕾薇蝶觅访诚语谷豪赋超越轩辉达远邃醉
金鑫锦问雁雅雨雪霖霜露青靖静风飞香驰骞高鸿鹏鹤黎';

set i = 1; #变量赋值
while i < 7001 do #结束循环的条件: 当i大于10时跳出while循环
insert into Student values (i,concat(substr(@surname,floor(rand()*length(@surname)/3+1),1), substr(@NAME,floor(rand()*length(@NAME)/3+1),1), substr(@NAME,floor(rand()*length(@NAME)/3+1),1))); #往test表添加数据
set i = i + 1; #循环一次,i加一
end while; #结束while循环
end
// #结束定义语句

source /home/mysql/liys/insert_Student.sql

插入20个课程
insert into Course values(1,'语文');
insert into Course values(2,'数学');
insert into Course values(3,'物理');
insert into Course values(4,'化学');
insert into Course values(5,'生物');
insert into Course values(6,'英文');
insert into Course values(7,'日语');
insert into Course values(8,'西班牙语');
insert into Course values(9,'线性代数');
insert into Course values(10,'高等数据');
insert into Course values(11,'数据物理方法');
insert into Course values(12,'计算机原理');
insert into Course values(13,'数据库原理');
insert into Course values(14,'电路结构');
insert into Course values(15,'量子力学');
insert into Course values(16,'原子物理');
insert into Course values(17,'声学');
insert into Course values(18,'电动力学');
insert into Course values(19,'光学');
insert into Course values(20,'通信原理');

数据插入140000条成绩

vi /home/mysql/liys/insert_SC.sql

delimiter // #定义标识符为双斜杠
drop procedure if exists insert_SC; #如果存在test存储过程则删除
create procedure insert_SC() #创建无参存储过程,名称为test
begin
declare i int;
declare j int;
declare y int;
set i = 1;
set j = 1;
set y = 1; #变量赋值
while j<21 do
while i < 7001 do #结束循环的条件: 当i大于10时跳出while循环
insert into SC values(y,j,i,FLOOR(RAND() * 100));
set i = i + 1;
set y = y +1; #循环一次,i加一
end while; #结束while循环
set i = 1;
set j = j + 1;
end while;
end
// #结束定义语句

source /home/mysql/liys/insert_SC.sql

以上SQL语句用于将表格中所有数据以CSV格式导出到指定路径的文件中。
SELECT * INTO OUTFILE '/tmp/Student' FROM Student;
SELECT * INTO OUTFILE '/tmp/Course' FROM Course;
SELECT * INTO OUTFILE '/tmp/SC' FROM SC;

ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement
mysql 默认对导出的目录有权限限制，也就是说使用命令行进行导出的时候，须要指定目录进行操做；

说明：

secure_file_prive=null 限制mysqld 不允许导入导出

secure_file_priv=/var/lib/mysql-files/ 限制mysqld的导入导出只能发生在/var/lib/mysql-files/目录下

secure_file_priv=' ' 不对mysqld的导入导出做限制

解决方法：

修改secure_file_prive的值或者是按照默认的路径进行导出；

在my.ini文件 [mysqld]节点下添加secure_file_priv=''，然后重启mysql服务。linux下在 /etc/my.cnf中添加secure_file_priv=''。

可以看出只能通过配置文件改。
mysql> set secure_file_priv='';
ERROR 1238 (HY000): Variable 'secure_file_priv' is a read only variable
mysql> SET @@GLOBAL.secure_file_priv='';
ERROR 1238 (HY000): Variable 'secure_file_priv' is a read only variable
mysql> SET GLOBAL secure_file_priv='';
ERROR 1238 (HY000): Variable 'secure_file_priv' is a read only variable

重启数据库后导出成功
mysql> SELECT * INTO OUTFILE '/tmp/Student' FROM Student;
Query OK, 7000 rows affected (0.01 sec)

mysql> SELECT * INTO OUTFILE '/tmp/Course' FROM Course;
Query OK, 20 rows affected (0.00 sec)

mysql> SELECT * INTO OUTFILE '/tmp/SC' FROM SC;
Query OK, 140000 rows affected (0.07 sec)

查看导出文件，是CSV格式,无法
vi /tmp/Course
1 语文
2 数学
3 物理
4 化学
5 生物
6 英文
7 日语
8 西班牙语
9 线性代数
10 高等数据
11 数据物理方法
12 计算机原理
13 数据库原理
14 电路结构
15 量子力学
16 原子物理
17 声学
18 电动力学
19 光学
20 通信原理

mysql> SELECT * INTO OUTFILE '/tmp/Course' FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' FROM Course;
Query OK, 20 rows affected (0.00 sec)

1,语文
2,数学
3,物理
4,化学
5,生物
6,英文
7,日语
8,西班牙语
9,线性代数
10,高等数据
11,数据物理方法
12,计算机原理
13,数据库原理
14,电路结构
15,量子力学
16,原子物理
17,声学
18,电动力学
19,光学
20,通信原理
~
SQL3107W At least one warning message was encountered during LOAD processing.

Number of rows read = 20
Number of rows skipped = 0
Number of rows loaded = 20
Number of rows rejected = 0
Number of rows deleted = 0
Number of rows committed = 20

[db2inst1@t3-dtpoc-dtpoc-web04 tmp]$ db2 "select * from Course"

C_ID NAME
----------- ----------
1 语文
2 数学
3 物理
4 化学
5 生物
6 英文
7 日语
8 西班牙
9 线性代
10 高等数
11 数据物
12 计算机
13 数据库
14 电路结
15 量子力
16 原子物
17 声学
18 电动力
19 光学
20 通信原