ERROR 1406 (22001): Data too long for column 'shell' at row 2 解决办法

admin

mysql> load data infile '/etc/passwd' into table gz.six fields terminated by ':' lines terminated by '\n';  
  \) I- e0 C0 }; zERROR 1406 (22001): Data too long for column 'shell' at row 2
2 S" a* ~6 {' u1 |6 `, B# `9 j

- t- ^- h* O0 @" R, ]7 [( Pmysql> load data infile '/etc/passwd' into table gz.six fields terminated by ':' lines terminated by '\n';
6 h6 S. X9 k2 b1 ?ERROR 1406 (22001): Data too long for column 'shell' at row 2
, e  I* G! e& ^2 G8 r% d
7 r5 N" Q; o7 Z. c, q  h0 q' Z* vRecords: 0  Duplicates: 0  Warnings: 0
* v+ G6 x4 Z8 x
2 n' t% M2 n+ P; R" `" yERROR 1406 (22001): Data too long for column 'shell' at row 2
( \5 M$ N8 g9 g报错提示字符太长，导致数据容量过大。
解决办法：
5 t& g$ P& a( q; nmodify 修改字段类型
# q$ d. V! j0 M/ B+ v    modify 字段名 类型（宽度） 约束条件 , modify 修改个多个在后面田间,modify
mysql> alter table six modify shell varchar(50);

+ l$ T' @4 e4 [! i' o" JQuery OK, 0 rows affected (1.85 sec)
Records: 0  Duplicates: 0  Warnings: 0
  q4 E1 k& \5 l: r! i0 b" P2 V
2 N. g( f2 Y% }" F" ^$ S* ?8 U. ~( Xmysql> alter table six modify shell varchar(50);
0 V) S1 p8 u' }6 Q- s, t) jQuery OK, 0 rows affected (1.85 sec)
! v6 h- c0 D! M: Q, t) \
mysql> load data infile '/etc/passwd' into table gz.six fields terminated by ':' lines terminated by '\n';
/ d1 U! J6 H: F4 a) M& b, {Query OK, 24 rows affected (0.29 sec)
Records: 24  Deleted: 0  Skipped: 0  Warnings: 0

7 \# n; W$ D# r3 j; \- R8 F

admin

mysql> load data infile '/etc/passwd' into table gz.six fields terminated by ':' lines terminated by '\n';  
ERROR 1406 (22001): Data too long for column 'shell' at row 2
mysql> load data infile '/etc/passwd' into table gz.six fields terminated by ':' lines terminated by'\n';
$ C& [& w' D: Z( F8 \, SERROR 1406 (22001): Data too long for column 'shell' at row 2
+ S, D6 {* B% j( J' D& gmysql> load data infile '/etc/passwd' into table gz.six fields terminated by ':' lines terminated by '\n';
/ T5 t6 ]4 B# A. b6 U8 U) _) pERROR 1406 (22001): Data too long for column 'shell' at row 2
mysql> alter table six modify shell varchar(50);
3 k3 e, ?0 k$ ]' M, D/ N7 PQuery OK, 0 rows affected (1.85 sec)
, b& Y  F0 S# c9 p# lRecords: 0  Duplicates: 0  Warnings: 0

mysql> load data infile '/etc/passwd' into table gz.six fields terminated by ':' lines terminated by '\n';
: z3 _* H  C/ C, Q; O4 a2 X8 Q8 YQuery OK, 24 rows affected (0.29 sec)
Records: 24  Deleted: 0  Skipped: 0  Warnings: 0
2 q8 _- |& B" r3 K+ W6 @0 K! j

admin

为了避免类似问题再次发生，可以使用定义参数限制条件大一点。