- 这句必须先执行:
SET SQL_LOG_BIN = 0;
全局修改:使用global修饰符(set global sql_log_bin=0) 版本低得无法用。
- 从服务器导入:这里导出只能导出到服务器上,不会在本地。如果数据库装在linux服务器,输出文件名目录要用"/tmp/xxx.csv", 然后去服务器对应目录找即可。
存在了数据库服务器上:
select * from channel_profile limit 13
into outfile '/tmp/channelProfile.zhibin'
CHARACTER SET utf8
fields terminated by ',' optionally enclosed by '"'
escaped by '"' lines terminated by '\n'
select * into outfile 'mydata.cvs' fields terminated by ',' from table1;
LOAD DATA INFILE '/tmp/channelProfile.zhibin' ignore into table channel_profile
CHARACTER SET utf8
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n'
load data LOCAL infile 'mydata.cvs' into table table2 fields terminated by ',';
加上LOCAL, 会从本地导入。否则是数据库服务器上的路径。
- 默认导入导出的目录
是服务器上的:/usr/local/infobright-4.0.7-x86_64/data/statistics/mydata.cvs
其中statistics是数据库名称。
如果是mysql,导出目录是/var/lib/mysql/statisticsystem/+ mydata.cvs。任何你写的windows目录都会变成文件名。
- 可以从本地导入到远程:
LOAD DATA LOCAL INFILE 'D:/play_20170110.csv' into table log_play_2017_test FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n';
;
select count(*) from log_play_2017_test
where request_time >=unix_timestamp("2017-01-10")
and request_time < unix_timestamp("2017-01-11")
;
当写在JDBC时,必须用\\ 替换上面的\, 因为是写在java的String字符串中,如\n需要替换成 \\n。
@PersistenceContext(unitName = "statLog") private EntityManager em; @Transactional public boolean importtest() { em.createNativeQuery(" SET SQL_LOG_BIN = 0 ").executeUpdate(); String sql = " LOAD DATA INFILE '/tmp/mydata.csv' ignore into table test_table" + " CHARACTER SET utf8 FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"' LINES TERMINATED BY '\\n' "; ; logger.info(sql); int num=0; num= emStatLog.createNativeQuery(sql).executeUpdate(); output("import result: the number of lines is " + num); output("Import success!"); return true; }
jpa不能执行 select * from xxx into outfile test.csv, 只能用jdbc的方式。从hibernate取到jdbc session示例:
@PersistenceContext(unitName = "primary") private EntityManager em; public void export() { //到处profile // filepath = filepath.replace('\\', '/');
//escaped by '\"' 这个会导致字段是null时,输出 ,"N,"xxxx" 到处是坑啊
String sql="select * from channel_profile into outfile '/tmp/test.csv' fields terminated by ',' optionally enclosed by '\"' lines terminated by '\\n'";
logger.info(sql); Session session = em.unwrap(org.hibernate.Session.class); session.doWork( new Work() { @Override public void execute(Connection con) throws SQLException { try ( PreparedStatement ps = con.prepareStatement( sql) ) { ps.execute(); } catch (Exception e) { e.printStackTrace(); } } } ); }
pom.xml添加:
<!-- https://mvnrepository.com/artifact/org.hibernate/hibernate-core -->
<dependency>
<groupId>org.hibernate</groupId>
<artifactId>hibernate-core</artifactId>
<version>5.1.4.Final</version>
<scope>provided</scope> <!-- 只是在编译时候用hibernate jar包。执行时候不要hibernate jar包。 -->
</dependency>