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 user

Fields 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

 

---

结语：数据的具体导入导出格式大家可以自己尝试一下。但个人建议涉及格式转换的话尽量先用其他工具，为防止出错还是尽量只转换纯文本列数据（无特殊格式）。