创建数据库
create database if not exists `mytestdb` default charset=utf8;
use mytestdb;
说明:
如果使用utf8字符集,则数据库和数据表创建时,都应该指定默认的字符集;
创建数据表
create table if not exists `t_user`(
`userid` int(11) not null ,
`userName` varchar(32) not null DEFAULT '',
`password` varchar(32) DEFAULT null,
`createTime` time DEFAULT null,
`currTime` timestamp not null DEFAULT CURRENT_TIMESTAMP,
`status` tinyint(4) default null,
`lastLoginTime` datetime DEFAULT null
)ENGINE=BRIGHTHOUSE DEFAULT CHARSET=utf8;
time/datetime/timestamp的区别:
- time: 表示的仅仅是时间,格式形如:13:45:12;
- datetime: 表示时间和日期,格式形如:2016-12-14 14:45:12;
- timestamp: 和datatime对比,表示的时间范围窄,占用的字节少;
待导入的数据文件
文件为: /tmp/t_userFile,如下是内容:
111,Tom ,pass1 , 13:45:12, \N,1,2016-12-14 14:45:12
222,Lily ,pass2 , 14:45:12 ,\N,1,2016-12-14 14:45:12
333,Keoj ,"pass3,333", 15:03:14,\N, 1 ,2016-12-14 14:45:12
444,Ladiu, "pass4" , 15:03:14,\N,1,2016-12-14 14:45:12
555,Jenny,pass5 , 15:03:14,\N,0,2016-12-14 14:45:12\n
666,Jams ,pass6 , 15:03:14,\N,1,2016-12-14 14:45:12\n
导入数据
load data infile '/tmp/t_userFile' into table `mytestdb`.`t_user` fields terminated by ',' optionally enclosed by '"' lines terminated by '\n';
导入SQL说明:
- fields terminated by ',': 表示字段以“,”分割;
- optionally enclosed by '"' :表示字段被"\""双引号包围;
- lines terminated by '\n':表示行以"\n"作为结束符(这是linux系统下的行结束符);
对待导入文件的说明:
- \N:表示null,这里timestamp被置为null,则使用当前时间进行填充;
- 111记录:\N前面有空格,则转换后的时间为:0000-00-00 00:00:00 ==> \N前面不能有空格
- ==>若字段为varchar或char类型,则空格也表示为varchar或char的一部分;
- 222记录:time 前后都有空格,无影响;
- 333记录:tinyint 前后都有空格,无影响;
- 333记录:password用引号引起来,由optionally enclosed by '"'可知,引号里面为字段内容,此时“,”不作为字段的分割符,即字段值为 pass3,333;
- 444记录:password用引号引起来,且前后都有空格,则字段值为:\t"pass"\t(空格算varchar的一部分);
- 555和666记录:最后都有"\n",这是linux系统默认的换行符,在导入时,有时有问题,有时没问题,没搞清楚;
数据导出后再导入测试
导出文件:
select * from t_user into outfile '/tmp/t_user_split' fields terminated by '|' OPTIONALLY ENCLOSED BY '$' lines terminated by '\n';
说明:
- 使用“|” 作为分隔符;
- 使用“$”表示括起字段;
导出后结果:
再进行导入测试:
load data infile '/tmp/t_user_split' into table `mytestdb`.`t_user` fields terminated by '|' optionally enclosed by '$' lines terminated by '\n';
说明:(保持与导出的数据对应)
- 使用“|” 作为分隔符;
- 使用“$”表示括起字段;