MySQL資料表格匯入匯出

標籤：

一 初始資料（共6條記錄）

username	userpwd	age
admin	admin	18
root	root	18
cat	cat	17
dog	dog	15
dog1	dog1	15
zhu	zhu	21

二 建立資料庫、建立表user

create database youku;   --建立資料庫use youku;  -- 選擇資料庫DROP TABLE IF EXISTS `user`;CREATE TABLE `user` (  `username` varchar(11) DEFAULT NULL,  `userpwd` varchar(11) DEFAULT NULL,  `age` int(11) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8; //插入資料INSERT INTO `user` VALUES (‘admin‘,‘admin‘,18),(‘root‘,‘root‘,18),(‘cat‘,‘cat‘,17),(‘dog‘,‘dog‘,15),(‘dog1‘,‘dog1‘,15),(‘zhu‘,‘zhu‘,21);-- 表資料匯出SELECT * FROM user    INTO OUTFILE ‘F:/user.txt‘;    -- 根據需要設定輸出的格式,每一行資料‘,‘分隔,同時字串型的資料用雙引號""包含SELECT * FROM user INTO OUTFILE ‘F:/user1.txt‘    Fields TERMINATED BY ‘,‘ OPTIONALLY ENCLOSED BY ‘"‘;    -- 表資料匯入 (注意,匯入的資料的列數要對應表的各列)-- 先刪除資料use youku;delete from user where age>10;select * from user;LOAD DATA INFILE ‘F:/user.txt‘ INTO TABLE user;select * from user; -- 匯入有特殊格式的表user1.txt(去除分隔號‘,‘和字串的引號“”)use youku;delete from user where age>10;select * from user;LOAD DATA INFILE ‘F:/user1.txt‘ INTO TABLE userFields TERMINATED BY ‘,‘ OPTIONALLY ENCLOSED BY ‘"‘; select * from user;

 

 

=================

 三 txt檔案查看

-- 表資料匯出SELECT * FROM user    INTO OUTFILE ‘F:/user.txt‘;

 

 組建檔案user.txt

admin admin 18

root root 18

cat cat 17

dog dog 15

dog1 dog1 15

zhu zhu 21

 

-- 根據需要設定輸出的格式,每一行資料‘,‘分隔,同時字串型的資料用雙引號""包含SELECT * FROM user INTO OUTFILE ‘F:/user1.txt‘    Fields TERMINATED BY ‘,‘ OPTIONALLY ENCLOSED BY ‘"‘;

 

組建檔案 user1.txt

"admin","admin",18

"root","root",18

"cat","cat",17

"dog","dog",15

"dog1","dog1",15

"zhu","zhu",21

 

結語：資料的具體匯入匯出格式大家可以自己嘗試一下。但個人建議涉及格式轉換的話盡量先用其他工具，為防止出錯還是盡量只轉換純文字列資料（無特殊格式）。

    

 

MySQL資料表格匯入匯出