创建一个多列大表的几种方式

背景：

测试时，经常需要创建一些多列大表和加载大量数据，之前也临时写过很多脚本，但往往要马上用时，又一时半会儿写不出来（心里知道，但动手就是不行了），这里专门记录一下。

系统环境

mysql5.7.29
oracle11.2.0.4
postgresql11.2

创建多列大表的几种方式

1、通过shell脚本生成建表语句及数据
2、通过存储过程生成建表语句及数据

生成多列建表语句

第一种方式，通过shell脚本生成建表语句
#!/bin/bash
v_num=$1echo"droptableifexistsaa;">creat{e}_{t}ab.sqlecho"createtableaa(">>creat{e}_{t}ab.sqlecho"col1int">>creat{e}_{t}ab.sqlfor((i=2;i<=$v_num;i++))
do
echo “, col$i int”>>create_tab.sql
done
echo “);”>> create_tab.sql
./gen_table.sh 1000

执行建表语句

mysql在sql环境执行建表语句如下

source /opt/create_tab.sql

postgresql直接在命令行执行如下语句
psql -f /opt/create_tab.sql

oracle在sql环境执行下语句
oralce>@/opt/create_tab.sql

生成测试数据

[root@lineqi ~]# cat gen_data.sh
#!/bin/bash
v_num=$1for((i=1;i<$v_num;i++))
do
echo $i,$i,$i >>data.sql
done

加载测试数据到测试表

mysql在sql环境执行建表语句如下

load data infile ‘/opt/data.sql’ into table aa fields terminated by ‘,’;

postgresql直接在命令行执行如下语句
postgres=# copy aa (col1,col2,col3) from ‘/opt/data.sql’ DELIMITER ‘,’;
oracle在sql环境执行下语句
sqlldr user/password direct=true control=sqlldr.ctl parallel=true
sqlldr.ctl配置文件内存如下
OPTIONS (skip=1,rows=128) – sqlldr 命令显示的 选项可以写到这里边来,skip=1 用来跳过数据中的第一行
LOAD DATA
INFILE “/opt/data.sql” --指定外部数据文件，可以写多 个 INFILE “another_data_file.csv” 指定多个数据文件
–这里还可以使 用 BADFILE、DISCARDFILE 来指定坏数据和丢弃数据的文件，
truncate --操作类型，用 truncate table 来清除表中原有 记录
INTO TABLE aa – 要插入记录的表
Fields terminated by “,” – 数据中每行记录用 “,” 分隔
Optionally enclosed by ‘"’ – 数据中每个字段用 ‘"’ 框起，比如字段中有 “,” 分隔符时
trailing nullcols --表的字段没有对应的值时允 许为空
(
virtual_column FILLER, --这是一个虚拟字段，用来跳 过由 PL/SQL Developer 生成的第一列序号
col1 number, --字段可以指定类型，否则认 为是 CHARACTER 类型, log 文件中有显示
col2 number,
login_times,
last_login DATE “YYYY-MM-DD HH24:MI:SS” – 指定接受日期的格式，相当用 to_date() 函数转换
)

第二种：通过数据库的存储过程来多列大表的创建
mysql
生成多列大表
drop procedure if exists gen_table1;
create procedure gen_table1(cols int)
begin
declare i int default 1;
declare v_sql text;
set v_sql=“create table aa(”;
set v_sql=concat(v_sql,concat(concat(‘col’,i),’ int’));
set i=i+1;
while i<=cols do
set v_sql=concat(v_sql,concat(concat(’,col’,i),’ int’));
set i=i+1;
end while;
set v_sql=concat(v_sql,’)’);
set @exec_sql=v_sql;
prepare pp1 from @exec_sql;
execute pp1;
DEALLOCATE prepare pp1;
end;
call gen_table1(100);
批量生成数据
drop procedure if exists gen_data;
create procedure gen_data(rows int)
begin
declare i int default 1;
while i<=rows do
insert into aa(col1,col2,col3) values(i,i+1,i+2);
set i=i+1;
end while;
end;

call gen_data(1000);