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;
---
- 导入数据最快的是用copy… from;经测试,导入数据到上面创建的表persons 12800条;insert语句需要0.820s;copy…from只需要0.102s
- 但是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'