建设大型网站制作品牌,国内十大咨询公司排名,免费ppt模板软件,网站后台查询软件CSV文件导入MySQLLOAD DATA INFILE语句允许您从文本文件读取数据#xff0c;并将文件的数据快速导入数据库的表中。导入文件操作之前#xff0c;需要准备以下内容#xff1a;一、将要导入文件的数据对应的数据库表。二、准备好一个CSV文件#xff0c;其数据与表的列数和每列…CSV文件导入MySQLLOAD DATA INFILE语句允许您从文本文件读取数据并将文件的数据快速导入数据库的表中。导入文件操作之前需要准备以下内容一、将要导入文件的数据对应的数据库表。二、准备好一个CSV文件其数据与表的列数和每列中的数据类型相匹配。三、连接到MySQL数据库服务器的帐户具有FILE和INSERT权限。首先创建discounts表use testdb;CREATE TABLE discounts (id INT NOT NULL AUTO_INCREMENT,title VARCHAR(255) NOT NULL,expired_date DATE NOT NULL,amount DECIMAL(10 , 2) NULL,PRIMARY KEY (id));discounts.csv文件的内容第一行作为列标题和其他三行则为数据。id,title,expired date,amout1,Spring Break 2018,20180401,202,Back to Scholl 2017,20170901,293,Summer 2018,20180820,100接下来将数据从F:/worksp/mysql/discounts.csv文件导入到discounts表。LOAD DATA INFILE F:/worksp/mysql/discounts.csvINTO TABLE discountsFIELDS TERMINATED BY,ENCLOSED BYLINES TERMINATED BY\nIGNORE1 ROWS;文件的字段由FIELD TERMINATED BY ,指示的逗号终止并由ENCLOSED BY 指定的双引号括起来。因为文件第一行包含列标题列标题不需要导入到表中因此通过指定IGNORE 1 ROWS选项来忽略第一行。导入时转换数据数据格式与表中的目标列不匹配用LOAD DATA INFILE语句中的SET子句进行转换。有一个discount_2.csv文件中它存储的过期日期列是mm/dd/yyyy格式。内容如下id,title,expired date,amout4,Item-4,01/04/2018,2005,Item-5,01/09/2017,2906,Item-6,12/08/2018,122将数据导入discounts表时必须使用str_to_date()函数将其转换为MySQL日期格式LOAD DATA INFILE F:/worksp/mysql/discounts_2.csvINTO TABLE discountsFIELDS TERMINATED BY , ENCLOSED BY LINES TERMINATED BY \nIGNORE 1 ROWS(id,title,expired_date,amount)SET expired_date STR_TO_DATE(expired_date, %m/%d/%Y);将文件从客户端导入远程MySQL数据库服务器使用LOAD DATA INFILE语句将数据从客户端(本地计算机)导入远程MySQL数据库服务器。LOAD DATA INFILE中使用LOCAL选项时客户端程序会读取客户端上的文件并将其发送到MySQL服务器。该文件将被上传到数据库服务器操作系统的临时文件夹Windows上的C:\windows\temp,此文件夹不可由MySQL配置或确定。示例LOAD DATA LOCAL INFILEc:/tmp/discounts.csvINTO TABLE discountsFIELDS TERMINATED BY,ENCLOSED BYLINES TERMINATED BY\nIGNORE1 ROWS;如果加载一个大的CSV文件将会看到使用LOCAL选项来加载该文件将会稍微慢些因为需要时间将文件传输到数据库服务器。使用LOCAL选项时连接到MySQL服务器的帐户不需要具有FILE权限来导入文件。MySQL导出CSV文件导出数据之前必须确保一、MySQL服务器的进程对包含目标CSV文件的目标文件夹具有写访问权限。二、要导出的目标CSV文件不能存在。示例查询从orders表中查询选择已取消的订单将此结果集导出为CSV文件SELECTorderNumber, status, orderDate, requiredDate, commentsFROMordersWHEREstatus CancelledINTO OUTFILEF:/worksp/mysql/cancelled_orders.csvFIELDS ENCLOSED BYTERMINATED BY;ESCAPED BYLINES TERMINATED BY\r\n;该语句在F:/worksp/mysql/目录下创建一个包含结果集名称为cancelled_orders.csv的CSV文件。CSV文件包含结果集中的行集合。每行由一个回车序列和由LINES TERMINATED BY \r\n子句指定的换行字符终止。文件中的每行包含表的结果集的每一行记录。每个值由FIELDS ENCLOSED BY 子句指示的双引号括起来。 这样可以防止可能包含逗号()的值被解释为字段分隔符。 当用双引号括住这些值时该值中的逗号不会被识别为字段分隔符。将数据导出到文件名包含时间戳的CSV文件将数据导出到CSV文件中该文件的名称包含创建文件的时间戳。将整个orders表导出为将时间戳作为文件名的一部分的CSV文件。SET TS DATE_FORMAT(NOW(),_%Y%m%d_%H%i%s);SET FOLDER F:/worksp/mysql/;SET PREFIX orders;SET EXT .csv;SET CMD CONCAT(SELECT * FROM orders INTO OUTFILE ,FOLDER,PREFIX,TS,EXT, FIELDS ENCLOSED BY \ TERMINATED BY ; ESCAPED BY \,LINES TERMINATED BY \r\n;);PREPARE statement FROM CMD;EXECUTE statement;首先构造了一个具有当前时间戳的查询作为文件名的一部分。其次使用PREPARE语句FROM命令准备执行语句。最后使用EXECUTE命令执行语句。使用列标题导出数据要添加列标题需要使用UNION语句如下(SELECT Order Number,Order Date,Status)UNION(SELECT orderNumber,orderDate, statusFROM ordersINTO OUTFILEF:/worksp/mysql/orders_union_title.csvFIELDS ENCLOSED BY TERMINATED BY ; ESCAPED BY LINES TERMINATED BY\r\n);处理NULL值如果结果集中的值包含NULL值则目标文件将使用“N/A”来代替数据中的NULL值。要解决此问题您需要将NULLSELECTorderNumber, orderDate, IFNULL(shippedDate,N/A)FROMorders INTO OUTFILEF:/worksp/mysql/orders_null2na.csvFIELDS ENCLOSED BYTERMINATED BY;ESCAPED BYLINESTERMINATED BY\r\n;用N/A字符串替换了shippingDate列中的NULL值。 CSV文件将显示N/A而不是NULL值。值替换为另一个值例如不适用(N/A)方法是使用IFNULL函数如下
