-- MYSQL LOAD DATA INFILE命令可以把csv平面文件中的数据导入到数据库中。
-- linux下:
LOAD DATA INFILE '/home/test/dump/ip_location.csv'
INTO TABLE ip_location
CHARACTER SET utf8
FIELDS TERMINATED BY ',' ENCLOSED BY '"';
-- CHARACTER SET :mysql字符集,一定要加上,免去乱码麻烦
-- INTO TABLE :导入到哪个表
-- FIELDS TERMINATED BY :以什么作为分割符
-- ENCLOSED BY :被什么包围
-- windows:
LOAD DATA INFILE "d:/insert_data.csv"
REPLACE INTO TABLE DEMO
CHARACTER SET gb2312
FIELDS TERMINATED BY "," ENCLOSED BY ""
LINES TERMINATED BY "\r\n";
-- LINES TERMINATED BY:这个与linux不同,以什么作为一行的结尾。
-- txt 或是linux
TRUNCATE TABLE adx_analysis.creative_category_imp_cli_tmp ;
LOAD DATA LOCAL INFILE 'D:\\analyst20151231.txt' INTO TABLE adx_analysis.creative_category_imp_cli_tmp CHARACTER SET utf8;
LOAD DATA INFILE语法
LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'file_name.txt'
[REPLACE | IGNORE]
INTO TABLE tbl_name
[FIELDS
[TERMINATED BY 'string']
[[OPTIONALLY] ENCLOSED BY 'char']
[ESCAPED BY 'char' ]
]
[LINES
[STARTING BY 'string']
[TERMINATED BY 'string']
]
[IGNORE number LINES]
[(col_name_or_user_var,...)]
[SET col_name = expr,...)]
-- 2017-06-14 ===================================================================================
FLUSH LOGS;
DROP TABLE IF EXISTS svr.cctv201615 ;
CREATE TABLE svr.cctv201615 (
col1 VARCHAR(128) ,
col2 VARCHAR(128) ,
col3 VARCHAR(128) ,
col4 VARCHAR(128) ,
col5 VARCHAR(128) ,
col6 VARCHAR(128) ,
id INT AUTO_INCREMENT PRIMARY KEY ,
col7 VARCHAR(128) ,
col8 VARCHAR(128) ,
col9 VARCHAR(128) ,
col10 VARCHAR(128) , `dt_update` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
col11 VARCHAR(128) ,
col12 VARCHAR(128) ,
col13 VARCHAR(128) ,
col14 VARCHAR(128) ) ;
LOAD DATA LOCAL INFILE "D:/excel/cctv201612.txt"
REPLACE INTO TABLE svr.cctv201615
CHARACTER SET utf8
FIELDS TERMINATED BY "|" ENCLOSED BY ""
LINES TERMINATED BY "\n"
IGNORE 1 LINES
(col1 ,col2 ,col3 ,col4 ,col5 ,col6 ,col7 ,col8 ,col9 ,col10,col11,col12,col13,col14 )
;
SELECT * FROM svr.cctv201615 ;
FLUSH LOGS;
TRUNCATE table hx.user_property_index_data_log;
LOAD DATA LOCAL INFILE '/Users/tata/Downloads/chenenhui20180320102301.csv'
INTO TABLE hx.user_property_index_data_log
CHARACTER SET utf8
FIELDS TERMINATED BY ',' #VENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES (property_index_id,user_id,vl)
set create_date = now()
;
# 从工具中下载后将文件载入2018-05-02
LOAD DATA LOCAL INFILE '/Downloads/job_id=6828422_dir/job_id=6828422ac.csv'
INTO TABLE dbo.tmp_operation_factTest
CHARACTER SET gbk
FIELDS TERMINATED BY ',' ENCLOSED BY ""
LINES TERMINATED BY '\n' #'\r\nz
#IGNORE 1 LINES (col1 ,col2 ,col3 ,col4 ,col5 ,col6 ,col7 ,col8 ,col9 ,col10,col11,col12,col13,col14 )
;
TRUNCATE TABLE `pro_company`;
LOAD DATA LOCAL INFILE "C:/Users/admin/Desktop/com.csv"
INTO TABLE pro_company CHARACTER SET gbk
FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\r\n'
IGNORE 1 LINES
(insure_company_code, com_code, com_name,nub,com_name_standard,com_code_standard);
SELECT * FROM `pro_company`;