原程序代码如下:
create database cy;
use cy;
create table cyx(
xueyuan varchar(20) not null,
zhuanye varchar(20)not null,
xuehao varchar (15)not null,
xingming varchar(20)not null,
xingbie varchar (20)not null
);
select * from cyx;
load data infile 'g:/sy.txt'into table cyx
原txt内容为:
自动化 电气 3090421090 徐园 男
自动化 电气 3090421091 刘勇 男
自动化 电气 3090421092 秦丹 女
自动化 电气 3090421093 段卫鹏 男
自动化 电气 3090421094 魏田中 男
运行结果为不正确,报错row1 doesn‘t contain data for all columns。
请大侠指点迷津 救命啊
create database cy;
use cy;
create table cyx(
xueyuan varchar(20) not null,
zhuanye varchar(20)not null,
xuehao varchar (15)not null,
xingming varchar(20)not null,
xingbie varchar (20)not null
);
select * from cyx;
load data infile 'g:/sy.txt'into table cyx
原txt内容为:
自动化 电气 3090421090 徐园 男
自动化 电气 3090421091 刘勇 男
自动化 电气 3090421092 秦丹 女
自动化 电气 3090421093 段卫鹏 男
自动化 电气 3090421094 魏田中 男
运行结果为不正确,报错row1 doesn‘t contain data for all columns。
请大侠指点迷津 救命啊
用过后代码为:load data local infile 'G:/sy.txt' into table cyx fields terminated by '\n' lines terminated by ' ';
依旧报错为:row1 doesn‘t contain data for all columns
row2 doesn‘t contain data for all columns
。。
大侠救命啊
用过后代码为:load data local infile 'G:/sy.txt' into table cyx fields terminated by '\n' lines terminated by ' ';
依旧报错为:row1 doesn‘t contain data for all columns
row2 doesn‘t contain data for all columns
。。
大侠救命啊
load data local infile 'G:/sy.txt' into table cyx fields terminated by ','
改为逗号后 load data local infile 'G:/sy.txt' into table cyx fields terminated by ','
依旧报错。
1 25 16:39:32 load data local infile 'G:/sy.txt' into table cyx fields terminated by ',' 5 row(s) affected, 19 warning(s):
1366 Incorrect string value: '\xD7\xD4\xB6\xAF\xBB\xAF' for column 'xueyuan' at row 1
1366 Incorrect string value: '\xB5\xE7\xC6\xF8' for column 'zhuanye' at row 1
1366 Incorrect string value: '\xD0\xEC\xD4\xB0' for column 'xingming' at row 1
1366 Incorrect string value: '\xC4\xD0\x0D' for column 'xingbie' at row 1
1366 Incorrect string value: '\xD7\xD4\xB6\xAF\xBB\xAF' for column 'xueyuan' at row 2
1366 Incorrect string value: '\xB5\xE7\xC6\xF8' for column 'zhuanye' at row 2
1366 Incorrect string value: '\xC1\xF5\xD3\xC2' for column 'xingming' at row 2
1366 Incorrect string value: '\xC4\xD0\x0D' for column 'xingbie' at row 2
1366 Incorrect string value: '\xD7\xD4\xB6\xAF\xBB\xAF' for column 'xueyuan' at row 3
1366 Incorrect string value: '\xB5\xE7\xC6\xF8' for column 'zhuanye' at row 3
1366 Incorrect string value: '\xC7\xD8\xB5\xA4' for column 'xingming' at row 3
1366 Incorrect string value: '\xD7\xD4\xB6\xAF\xBB\xAF' for column 'xueyuan' at row 4
1366 Incorrect string value: '\xB5\xE7\xC6\xF8' for column 'zhuanye' at row 4
1366 Incorrect string value: '\xB6\xCE\xCE\xC0\xC5\xF4' for column 'xingming' at row 4
1366 Incorrect string value: '\xC4\xD0\x0D' for column 'xingbie' at row 4
1366 Incorrect string value: '\xD7\xD4\xB6\xAF\xBB\xAF' for column 'xueyuan' at row 5
1366 Incorrect string value: '\xB5\xE7\xC6\xF8' for column 'zhuanye' at row 5
1366 Incorrect string value: '\xCC\xEF\xD6\xD0' for column 'xingming' at row 5
1366 Incorrect string value: '\xC4\xD0\x0D' for column 'xingbie' at row 5
Records: 5 Deleted: 0 Skipped: 0 Warnings: 19 0.032 sec
show variables like 'char%';
运行后是:
character_set_client utf8
character_set_connection utf8
character_set_database utf8
character_set_filesystem binary
character_set_results utf8
character_set_server utf8
character_set_system utf8
character_sets_dir G:\Program Files\MySQL\MySQL Server 5.6\share\charsets\
请问正确吗?小的新手
再导入试试
set names gbk;正确执行后 再导入:
load data local infile 'G:/sy.txt' into table cyx fields terminated by ','
报错为:
1 28 16:59:59 load data local infile 'G:/sy.txt' into table cyx fields terminated by ',' 5 row(s) affected, 19 warning(s):
1366 Incorrect string value: '\xD7\xD4\xB6\xAF\xBB\xAF' for column 'xueyuan' at row 1
1366 Incorrect string value: '\xB5\xE7\xC6\xF8' for column 'zhuanye' at row 1
1366 Incorrect string value: '\xD0\xEC\xD4\xB0' for column 'xingming' at row 1
1366 Incorrect string value: '\xC4\xD0\x0D' for column 'xingbie' at row 1
1366 Incorrect string value: '\xD7\xD4\xB6\xAF\xBB\xAF' for column 'xueyuan' at row 2
1366 Incorrect string value: '\xB5\xE7\xC6\xF8' for column 'zhuanye' at row 2
1366 Incorrect string value: '\xC1\xF5\xD3\xC2' for column 'xingming' at row 2
1366 Incorrect string value: '\xC4\xD0\x0D' for column 'xingbie' at row 2
1366 Incorrect string value: '\xD7\xD4\xB6\xAF\xBB\xAF' for column 'xueyuan' at row 3
1366 Incorrect string value: '\xB5\xE7\xC6\xF8' for column 'zhuanye' at row 3
1366 Incorrect string value: '\xC7\xD8\xB5\xA4' for column 'xingming' at row 3
1366 Incorrect string value: '\xD7\xD4\xB6\xAF\xBB\xAF' for column 'xueyuan' at row 4
1366 Incorrect string value: '\xB5\xE7\xC6\xF8' for column 'zhuanye' at row 4
1366 Incorrect string value: '\xB6\xCE\xCE\xC0\xC5\xF4' for column 'xingming' at row 4
1366 Incorrect string value: '\xC4\xD0\x0D' for column 'xingbie' at row 4
1366 Incorrect string value: '\xD7\xD4\xB6\xAF\xBB\xAF' for column 'xueyuan' at row 5
1366 Incorrect string value: '\xB5\xE7\xC6\xF8' for column 'zhuanye' at row 5
1366 Incorrect string value: '\xCC\xEF\xD6\xD0' for column 'xingming' at row 5
1366 Incorrect string value: '\xC4\xD0\x0D' for column 'xingbie' at row 5
Records: 5 Deleted: 0 Skipped: 0 Warnings: 19 0.125 sec
UTF8、GBK之类的
用word打开后并没有 UTF8 GBK 类型啊,我是否重新新建一个txt文档试试?
是的 另存为,有下拉菜单,但没有您说的两种,有.txt,.doc等
1 29 17:23:46 load data local infile 'G:/syc.txt' into table cyx fields terminated by ',' 5 row(s) affected, 19 warning(s):
1366 Incorrect string value: '\xD7\xD4\xB6\xAF\xBB\xAF' for column 'xueyuan' at row 1
1366 Incorrect string value: '\xB5\xE7\xC6\xF8' for column 'zhuanye' at row 1
1366 Incorrect string value: '\xD0\xEC\xD4\xB0' for column 'xingming' at row 1
1366 Incorrect string value: '\xC4\xD0\x0D' for column 'xingbie' at row 1
1366 Incorrect string value: '\xD7\xD4\xB6\xAF\xBB\xAF' for column 'xueyuan' at row 2
1366 Incorrect string value: '\xB5\xE7\xC6\xF8' for column 'zhuanye' at row 2
1366 Incorrect string value: '\xC1\xF5\xD3\xC2' for column 'xingming' at row 2
1366 Incorrect string value: '\xC4\xD0\x0D' for column 'xingbie' at row 2
1366 Incorrect string value: '\xD7\xD4\xB6\xAF\xBB\xAF' for column 'xueyuan' at row 3
1366 Incorrect string value: '\xB5\xE7\xC6\xF8' for column 'zhuanye' at row 3
1366 Incorrect string value: '\xC7\xD8\xB5\xA4' for column 'xingming' at row 3
1366 Incorrect string value: '\xD7\xD4\xB6\xAF\xBB\xAF' for column 'xueyuan' at row 4
1366 Incorrect string value: '\xB5\xE7\xC6\xF8' for column 'zhuanye' at row 4
1366 Incorrect string value: '\xB6\xCE\xCE\xC0\xC5\xF4' for column 'xingming' at row 4
1366 Incorrect string value: '\xC4\xD0\x0D' for column 'xingbie' at row 4
1366 Incorrect string value: '\xD7\xD4\xB6\xAF\xBB\xAF' for column 'xueyuan' at row 5
1366 Incorrect string value: '\xB5\xE7\xC6\xF8' for column 'zhuanye' at row 5
1366 Incorrect string value: '\xCC\xEF\xD6\xD0' for column 'xingming' at row 5
1366 Incorrect string value: '\xC4' for column 'xingbie' at row 5
Records: 5 Deleted: 0 Skipped: 0 Warnings: 19 0.032 sec
正确输出:
cyx CREATE TABLE `cyx` (
`xueyuan` varchar(20) NOT NULL,
`zhuanye` varchar(20) NOT NULL,
`xuehao` varchar(15) NOT NULL,
`xingming` varchar(20) NOT NULL,
`xingbie` varchar(20) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
自动化,电气,3090421090,徐园,男
自动化,电气,3090421091,刘勇,男
自动化,电气,3090421092,秦丹,女
自动化,电气,3090421093,段卫鹏,男
自动化,电气,3090421094,魏田中,男
CREATE TABLE `cyx` (
`xueyuan` varchar(20) NOT NULL,
`zhuanye` varchar(20) NOT NULL,
`xuehao` varchar(15) NOT NULL,
`xingming` varchar(20) NOT NULL,
`xingbie` varchar(20) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;TRUNCATE cyx;
SET NAMES utf8;
LOAD DATA INFILE 'r:/temp/a1.txt' INTO TABLE cyx FIELDS
TERMINATED BY ',' ;
SHOW WARNINGS;
SELECT * FROM cyx;