load data in file to table

Oracle

 

--create table

 

create table users( 

     user_id number,           --用户 ID 

     user_name varchar2(50),   --用户名 

     login_times number,       --登陆次数 

     last_login date           --最后登录日期 

)

 

-- insert into data

 

insert into users values(1,'Unmi',3,sysdate);

insert into users values(2,NULL,5,to_date('2008-10-15','YYYY-MM-DD'));

insert into users values(3,'隔叶黄莺',8,to_date('2009-01-02','YYYY-MM-DD')); 

insert into users values(4,'Kypfos',NULL,NULL);

insert into users values(5,'不知秋',1,to_date('2008-12-23','YYYY-MM-DD'));

 

--将user_id设置为primarykey

alter table users add constraint user_id primary key(user_id);

 

--新建扩展名为ctl文件，内容如下：

 

--OPTIONS (skip=1,rows=128) -- sqlldr 命令,根据skip=1 用来跳过数据中的第一行，这里注释掉了

LOAD DATA

INFILE "users.csv" --指定外部数据文件

--这里还可以使用 BADFILE、DISCARDFILE 来指定坏数据和丢弃数据的文件，

append --操作类型

INTO TABLE users -- 要插入记录的表

Fields terminated by "," -- 数据中每行记录用 "," 分隔

Optionally enclosed by '"' -- 数据中每个字段用 '"' 框起，比如字段中有 "," 分隔符时 

trailing nullcols --表的字段没有对应的值时允许为空

(

   user_id,

   user_name,

   login_times,

   last_login DATE "YYYY-MM-DD HH24:MI:SS" -- 指定接受日期的格式，相当用 to_date() 函数转换

)

 

--users.csv内容如下：

"1","Unmi","3","2019/1/9 16:13:05"

"2","","5","2008/10/15 00:00:00"

"3","隔叶黄莺","8","2009/1/2 00:00:00"

"4","Kypfos","",""

"5","不知秋","1","2008/12/23 00:00:00"

"6","不知秋","1","2008/12/23 00:00:00"

 

 

--最后运行code如下：

sqlldr 账号/密码@实例名 control=users.ctl log = user.log

 

--其运行日志放在user.log中 错误日志放在users.bad中

 

运行结果是发现添加了unique key 后，主键重复的数据不会导入的数据库中，不重复的正常导入

 

 

 

 

oracle具体load文件用法，请查看

https://wenku.baidu.com/view/d2c43424804d2b160a4ec06d.html

 

Mysql

--

 

CREATE TABLE `t0` (

 `id` bigint(20) unsigned NOT NULL auto_increment,

 `name` char(20) NOT NULL,

 `age` tinyint(3) unsigned NOT NULL,

 `description` text NOT NULL,

 PRIMARY KEY  (`id`),

 UNIQUE KEY `idx_name` (`name`)

) ENGINE=MyISAM DEFAULT CHARSET=utf8

 

 

Mysql load infile语句如下所示：

 

---

load data infile 'C:\\Apache\\app\\mysql-8.0.13-winx64\\file\\t0.txt' ignore into table t0

---

 

 

 

 

 

运行上面的语句时有可能出现如下错误：

character set gbk fields terminated by ',' enclosed by '"' lines terminated by '\n' (`name`,`age`,`description`);

-- 报错The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

SHOW VARIABLES LIKE "secure_file_priv";

需要设置secure_file_priv参数

 

 

 

 

 

 

 

Postgresql

--CREATE

 

CREATE TABLE persons

(

  id serial NOT NULL,

  first_name character varying(50),

  last_name character varying(50),

  dob date,

  email character varying(255),

  CONSTRAINT persons_pkey PRIMARY KEY (id)

)

 

 

Postgres  Load data inflie 语句如下所示：

---

COPY persons(first_name,last_name,dob,email)

FROM 'D:\tmp\persons.csv' DELIMITER ',' CSV HEADER;

---

 

 

 

1. 导入数据最快的是用copy… from;经测试，导入数据到上面创建的表persons 12800条；insert语句需要0.820s；copy…from只需要0.102s
2. 但是copy…from只能插入不重复的数据或者往空表里面插数据，否则有一条duplicate，就会回滚，不会插入成功一条

 

 

 

 

 

 

注意

以上均是以load data infile为前提来操作的；

如果是直接运行sql insert语句，mysql，oracle，postgres均有各自的upset语句，原理上多线程，多连接池，并行运行这些insert语句，速度会快点，但是会增加机器的资源使用情况。

 

各个数据库类似的Upset语句

Mysql：

 

INSERT IGNORE INTO tab1 (col1) values(val1);

 

 

 

Oracle：

 

MERGE INTO Employee USING dual ON ( "id"=2097153 )

WHEN MATCHED THEN UPDATE SET "last"="smith" , "name"="john"

WHEN NOT MATCHED THEN INSERT ("id","last","name")

    VALUES ( 2097153,"smith", "john" )

 

 

Postgres：

 

INSERT INTO test_postgre(id,name,InputTime,age)

VALUES('1','postgre','2018-01-10 22:00:00',24)

ON conflict(id)

DO UPDATE SET name = 'postgreOk', InputTime ='2018-02-22 12:00:00'