将txt数据装载到数据库
数据无””
LOAD DATA
CHARACTER-SET ZHS16GBK
truncate into table a
FIELDS TERMINATED BY ‘,’
TRAILING NULLCOLS
()
数据有””
LOAD DATA
CHARACTER-SET ZHS16GBK
truncate into table a
FIELDS TERMINATED BY ‘,’
OPTIONALLY ENCLOSED BY ‘“‘
()
例如一个字段无分隔符
LOAD DATA
CHARACTER-SET ZHS16GBK
INFILE ‘error.txt’
BADFILE ‘error.bad’
DISCARDFILE ‘error.dsc’
truncate into table a
(acount position(1 :30))
FILEDS给出记录中字段的分隔符。
FILEDS[TERMINATED [BY] (WITHSPACE|’CHARCTER’)]
[OPTIONALLY] ENCLOSED [BY] ‘CHARCTER’]
TERMINATED 读完前一个字段读下一个字段
WITHSPACE 结束符是空格的意思。包括空格，tab,换行符，换页符及回车。
OPTIONALLY ENCLOSED 表示数据由特殊字符括起来。使用OPTIONALLY同时要使用TERMINATED
TRAILING NULLCOLS变表字段没有对应值允许为空
导入数据是，如果主键冲突
alter table a drop primary key;
数据加载后再建主键
alter table a add primary key(zz,xx);
违反唯一标志时候
alter table a enable row movement;