数据导入语句use TESTING;
set names utf8;
LOAD DATA LOCAL INFILE 'C:\\Documents and Settings\\Administrator\\桌面\\数据字典导入\\DicCategory.csv' INTO TABLE DicCategory FIELDS TERMINATED BY ',' LINES TERMINATED BY '\r\n' (category,userDefine,`index`);
csv文件(utf8编码)按时,0,1
地方,0,2
挨罚,0,3
是否,0,4表DicCategory的主键是自增,执行语句会成功的将数据插入,但问题是下次插入时,主键会不连续;从上次插入的最后一行的ID 加6开始 插入的数据全部成功插入; 但是插入几行数据就会显示warnings 行数;从理论上来讲,应该是在最后一次插入后又有6次失败的插入操作;操作并且回滚;导致自增键增加了6个;但是csv文件没有多余的空行;完成导入操作后,使用insert语句插入,自增键也是跳过六个大家帮忙看看是哪里出了问题MySQLcsv数据导入
set names utf8;
LOAD DATA LOCAL INFILE 'C:\\Documents and Settings\\Administrator\\桌面\\数据字典导入\\DicCategory.csv' INTO TABLE DicCategory FIELDS TERMINATED BY ',' LINES TERMINATED BY '\r\n' (category,userDefine,`index`);
csv文件(utf8编码)按时,0,1
地方,0,2
挨罚,0,3
是否,0,4表DicCategory的主键是自增,执行语句会成功的将数据插入,但问题是下次插入时,主键会不连续;从上次插入的最后一行的ID 加6开始 插入的数据全部成功插入; 但是插入几行数据就会显示warnings 行数;从理论上来讲,应该是在最后一次插入后又有6次失败的插入操作;操作并且回滚;导致自增键增加了6个;但是csv文件没有多余的空行;完成导入操作后,使用insert语句插入,自增键也是跳过六个大家帮忙看看是哪里出了问题MySQLcsv数据导入
经过试验 应该是最后一行的数据在导入时出现了问题因为 插入的数据之间的ID 是连续的 一次导入操作完成后 通过正常的insert 语句 (肯定没有错误) 插入的数据依然会有几个ID 跳过了
所以说明 应该是 csv最后一行数据的问题
lower_case_table_names = 2mysql 版本为 5.5.28 community server
直接上传测试用的文件。不要贴图,
C:\Documents and Settings\Administrator>mysql -udatawriter -p147258369
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 252
Server version: 5.5.28 MySQL Community Server (GPL)Copyright (c) 2000, 2012, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trade of Oracle Corporation and/or its
affiliates. Other names may be trades of their respective
owners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql> use testing;
Database changed
mysql> show create table DicJobTitle;
+-------------+-------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------
-------------+
| Table | Create Table |
+-------------+-------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------
-------------+
| DicJobTitle | CREATE TABLE `DicJobTitle` (
`jobTitleID` int(11) NOT NULL AUTO_INCREMENT,
`jobTitle` char(45) DEFAULT NULL,
PRIMARY KEY (`jobTitleID`)
) ENGINE=InnoDB AUTO_INCREMENT=51 DEFAULT CHARSET=utf8 |
+-------------+-------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------
-------------+
1 row in set (0.02 sec)mysql>
测试用文件:http://img.bbs.csdn.net/upload/201305/04/1367628326_54155.jpg
.jpg 改为csv
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 252
Server version: 5.5.28 MySQL Community Server (GPL)Copyright (c) 2000, 2012, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trade of Oracle Corporation and/or its
affiliates. Other names may be trades of their respective
owners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql> use testing;
Database changed
mysql> show create table DicJobTitle;
+-------------+-------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------
-------------+
| Table | Create Table |
+-------------+-------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------
-------------+
| DicJobTitle | CREATE TABLE `DicJobTitle` (
`jobTitleID` int(11) NOT NULL AUTO_INCREMENT,
`jobTitle` char(45) DEFAULT NULL,
PRIMARY KEY (`jobTitleID`)
) ENGINE=InnoDB AUTO_INCREMENT=51 DEFAULT CHARSET=utf8 |
+-------------+-------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------
-------------+
1 row in set (0.02 sec)mysql>
测试用文件:http://img.bbs.csdn.net/upload/201305/04/1367628326_54155.jpg
.jpg 改为csv
C:\Documents and Settings\Administrator>mysql -udatawriter -p147258369
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 252
Server version: 5.5.28 MySQL Community Server (GPL)Copyright (c) 2000, 2012, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trade of Oracle Corporation and/or its
affiliates. Other names may be trades of their respective
owners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql> use testing;
Database changed
mysql> show create table DicJobTitle;
+-------------+-------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------
-------------+
| Table | Create Table |
+-------------+-------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------
-------------+
| DicJobTitle | CREATE TABLE `DicJobTitle` (
`jobTitleID` int(11) NOT NULL AUTO_INCREMENT,
`jobTitle` char(45) DEFAULT NULL,
PRIMARY KEY (`jobTitleID`)
) ENGINE=InnoDB AUTO_INCREMENT=51 DEFAULT CHARSET=utf8 |
+-------------+-------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------
-------------+
1 row in set (0.02 sec)mysql>
测试用文件:http://img.bbs.csdn.net/upload/201305/04/1367628326_54155.jpg
.jpg 改为csv