load data infile partially cuts phone numbers?
I made a table which name is 'test' in mysql in my PC like belows.
create table test( telnum varchar(20) not null, reg_date datetime not null
default '0000-00-00 00:00:00', remarks text, primary key(telnum) );
And I uploaded a file named 1.txt into table 'test'. 1.txt's contents are
like belows :
01011112222
01022223333
01033334444
And 'load data infile' syntax are like belows :
load data infile "c:/temp/1.txt" ignore into table test;
But there is a problem.
Every phone numbers were cut like below.
Query OK, 3 rows affected, 3 warnings (0.00 sec) Records: 3 Deleted: 0
Skipped: 0 Warnings: 3
mysql> select * from test;
+-------------+---------------------+---------+ | telnum | reg_date |
remarks | +-------------+---------------------+---------+
|12222 | 0000-00-00 00:00:00 |
|23333 | 0000-00-00 00:00:00 |
|34444 | 0000-00-00 00:00:00 |
+-------------+---------------------+---------+ 3 rows in set (0.00 sec)
Only 5 characters are remained from 11 characters. 6 characters disappeared.
And second problem is 'warnings'.
Reason of warnings is like below.
mysql> show warnings;
+---------+------+---------------------------------------------------+ |
Level | Code | Message |
+---------+------+---------------------------------------------------+ |
Warning | 1264 | Out of range value for column 'reg_date' at row 1 | |
Warning | 1264 | Out of range value for column 'reg_date' at row 2 | |
Warning | 1264 | Out of range value for column 'reg_date' at row 3 |
+---------+------+---------------------------------------------------+
3 rows in set (0.00 sec)
I did'nt put anything into the reg_date field. But the message says out of
range value for column 'reg_date'.
What are the reasons and how can I solve these problems?
No comments:
Post a Comment