关于MYSQL的数据导入问题.---数据错误!请高手指教

fish_007 发表于 2004-7-22 12:04:13

以下是我的操作步骤:(实现创建各表及各表的数据输入)
1.建立school.sql文件:
#创建数据表student ,student_id为主键
create table student(
      student_id int unsigned not null auto_increment primary key,
   name varchar(20) not null,
   department_id int,
   birthdate,
   city varchar(20)
   );

#创建数据表department,department_id 为主键
create table department(
      department_id int unsigned not null auto_increment primary key,
   name varchar(40) not null,
   address varchar(20),
   dean int unsigned);

#创建数据表thacher,teacher_id为主键
create table teacher(
         teacher_id int unsigned not null auto_increment primary key,
   name varchar(30),
   department_id int,
   tlephone varchar(20)
   );


#创建数据表teaching,用于连接teacher 和student两表
create table teaching(
         student_id int unsigned not null,
      teacher_id int unsigned not null );

#创建数据表test,test_id为表的主键
create table test(
      test_id int unsigned not null auto_increment primary key,
   test_date date not null,
   test_type enum('F','M') not null);

#创建数据表mark,student_id和test_id 为表的主键
create table mark (
      student_id int unsigned not null,
   test_id int unsigned not null,
   mark int not null,
   primary key(student_id,test_id));

#从文本文件/mysql/test/school/student.txt中读取数据并装载进数据表student
load data local infile "student.txt" into table student
fields
   terminated by '\t'
   enclosed by ''
   escaped by '\\'
   lines terminated by '\n';

#从文本文件/mysql/test/school/teacher.txt中读取数据并装载进数据表teacher
load data local infile "teacher.txt" into table teacher
fields
   terminated by '\t'
   enclosed by ''
   escaped by '\\'
   lines terminated by '\n';

#从文本文件/mysql/test/school/teaching.txt中读取数据并装载进数据表teaching
load data local infile "teaching.txt" into table teaching
lines terminated by '\n';

#文本文件/mysql/test/school/test.txt中读取数据并装载进数据表test
load data local infile "test.txt" into table test
lines terminated by '\n';

load data local infile"mark.txt" into table mark
lines terminatedby '\n';

loaddata local infile"department.txt" into table department
lines terminated by '\n';
-----------------------------------------------------------------------------
然后建立各数据表,如其中的student.txt等.
其中的student.txt文件:

100 linshengzhi 2 1976-12-06 BEIJING
101 yanghua 2 1980-10-11 SHANGHAI
102 zhongxin 2 1977-12-01 CHENGDU
103 huanghe 2 1978-01-02 GUANGZHOU
104 changjiang2 1978-01-02 BEIJING
105 chaocao 3 19750-01-02 SHANGHAI
106 zhouyu' 3 1975-01-02 FUIJAN
107 zhugeliang 3 1977-01-04 XIANGYANG
108 huanggai 3 1957-04-05 JIANGDONG
-------------------------------------------------------------------------------
之后在命令窗口: (当然是进入school.sql所在目录)
# mysql -u root school < school.sql (不知这一个是否正确?)

完成表的创建和数据导入.
但发现表中的数据有问题:

root@fish school]# mysql
Welcome to the MySQL monitor.Commands end with ; or \g.
Your MySQL connection id is 3 to server version: 3.23.54

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> use school
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select * from student;
+------------+------+---------------+-------+------+
| student_id | name | department_id | birth | city |
+------------+------+---------------+-------+------+
|    100 |    |       NULL | NULL| NULL |
|    101 |    |       NULL | NULL| NULL |
|    102 |    |       NULL | NULL| NULL |
|    103 |    |       NULL | NULL| NULL |
|    104 |    |       NULL | NULL| NULL |
|    105 |    |       NULL | NULL| NULL |
|    106 |    |       NULL | NULL| NULL |
|    107 |    |       NULL | NULL| NULL |
|    108 |    |       NULL | NULL| NULL |
+------------+------+---------------+-------+------+
9 rows in set (0.01 sec)

mysql>

所有的表中的数据导入都有问题!---->只能导入数据文件中的第一列!其它的列的数据不能导入!好像上面的student.txt 导入表student (如上面显示的只有student_id能导入数据)

----------------------------------------------------------------------------
请高手帮我看看是不是哪里出错!
还有关于MYSQL 中创建数据库和表时,可以先建一个文本文件存放所有创建和操作的SQL语句,然后用重定向这个文本文件的命令有哪些??(我上面的"mysql -u root school <school.sql"是否正确?)
谢谢先~~~~~~~~~~~~~~~~~~~

jiangtao9999 发表于 2004-7-22 14:17:52

有个问题：MySQL 有 varchar 列类型么？

fish_007 发表于 2004-7-22 14:56:44

TO版主大人:
MYSQL没有varchar列类型?是什么意思?
你的意思是不是说MYSQL没有varchar数据类型还是???????????

jiangtao9999 发表于 2004-7-22 15:08:25

抱歉，我刚才试了一下，有 varchar 列类型～～～～ :oops:

fish_007 发表于 2004-7-22 15:17:01

jiangtao9999 发表于 2004-7-22 15:20:40

似乎找到原因了：

load data local infile "student.txt" into table student
fields
terminated by '\t'
enclosed by ''
escaped by '\\'
lines terminated by '\n';

我把这个 '\t' 改成了 ' ' （单引号里面是个空格），就可以成功导入。（数据是<ctrl>+<c>到我机器上的）
看来是数据的分隔符错误了。

我导入后：

-> enclosed by ''
-> escaped by '\\'
-> lines terminated by '\n';
Query OK, 9 rows affected (0.00 sec)
Records: 9Deleted: 0Skipped: 0Warnings: 1

mysql> select * from student;
+------------+-------------+---------------+------------+------------+
| student_id | name    | department_id | birth    | city    |
+------------+-------------+---------------+------------+------------+
| 100 | linshengzhi |          2 | 1976-12-06 | BEIJING
|    101 | yanghua |          2 | 1980-10-11 | SHANGHAI
| 102 | zhongxin |          2 | 1977-12-01 | CHENGDU
|    103 | huanghe |          2 | 1978-01-02 | GUANGZHOU
| 104 | changjiang|          2 | 1978-01-02 | BEIJING
|    105 | chaocao |          3 | 0000-00-00 | SHANGHAI
| 106 | zhouyu' |          3 | 1975-01-02 | FUIJAN
|    107 | zhugeliang|          3 | 1977-01-04 | XIANGYANG
|    108 | huanggai |          3 | 1957-04-05 | JIANGDONG
+------------+-------------+---------------+------------+------------+
9 rows in set (0.00 sec)

mysql>

Sam1860 发表于 2004-7-23 21:02:06

#从文本文件/mysql/test/school/student.txt中读取数据并装载进数据表student
load data local infile "student.txt" into table student
fields
terminated by '\t'
enclosed by ''
escaped by '\\'
lines terminated by '\n';

改成：
LOAD DATA LOCAL INFILE "student.txt" INTO TABLE student
LINES TERMINATED BY "\r\n"

其中LINES TERMINATED BY "\r\n"，如果你的student.txt是在Windows建立的才要，在Linux建立的不必

字段的分隔默认就是TAB键，不必特意指定

至于enclosed by 和escaped by我不知道是什么(我前天才开始学MYSQL)，但在这里应该不用

Sam1860 发表于 2004-7-23 21:13:53

似乎找到原因了：
我把这个 '\t' 改成了 ' ' （单引号里面是个空格），就可以成功导入。（数据是<ctrl>+<c>到我机器上的）
看来是数据的分隔符错误了。

楼主应该没搞错吧，数据文件和SQL语句都是楼主自已写的，他总不会连自己用了什么分隔符也不知道吧，它帖出来的student.txt表的字段间之所以只显示一个空格大概是因为论坛的排版功能太弱了

jiangtao9999 发表于 2004-7-24 09:03:39

页: [1]

中国Linux公社论坛's Archiver

关于MYSQL的数据导入问题.---数据错误!请高手指教