Description:
I have a text file which contains only 1 fields per line, here is an example of the text file:
203.81.58.122:27015
203.81.54.228:27015
203.81.54.226:27022
203.81.54.226:27018
203.81.53.254:27016
203.95.229.118:27015
203.81.53.254:27015
..............
..............
I want to import it into database, I use MySQL5.0.18_without_installer on Windows XP, and I create a table which contains 2 primary key, one is BIGINT AUTO_INCREMENT, another is VARCHAR, I use LOAD DATA INFILE statement to do this (see the below), but when it done, the AUTO_INCREMENT field (see the below) does not increased correctly.
/*
-- import...
*/
mysql> load data infile 'server all.txt' into table servers lines terminated by '\r\n' (serverAddress);
Query OK, 52672 rows affected (2.31 sec)
Records: 52672 Deleted: 0 Skipped: 0 Warnings: 0
mysql> select distinct(serverid) from servers;
+----------+
| serverid |
+----------+
| 1 |
| 2 |
| 3 |
| 4 |
+----------+
4 rows in set (0.13 sec)
mysql> describe servers;
+-----------------+-----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+-----------------------+------+-----+---------+----------------+
| ServerId | bigint(20) unsigned | NO | PRI | NULL | auto_increment |
| ServerName | varchar(100) | NO | | | |
| ServerAddress | varchar(45) | NO | PRI | | |
| ServerIPString | varchar(45) | NO | | | |
| ServerIP | int(10) unsigned | NO | | 0 | |
| ServerPort | int(10) unsigned | NO | | 0 | |
| GameDirectory | varchar(45) | NO | | | |
| GameDescription | varchar(45) | NO | | | |
| MapName | varchar(45) | NO | | | |
| ModWebsite | varchar(45) | NO | | | |
| IsSecureServer | tinyint(3) unsigned | NO | | 0 | |
| ServerType | tinyint(3) unsigned | NO | | 0 | |
| ServerOS | tinyint(3) unsigned | NO | | 0 | |
| SteamAppId | int(10) unsigned | NO | | 0 | |
| IsNeedPassword | tinyint(3) unsigned | NO | | 0 | |
| PlayerCount | mediumint(8) unsigned | NO | | 0 | |
| MaxPlayers | mediumint(8) unsigned | NO | | 0 | |
| BotCount | mediumint(8) unsigned | NO | | 0 | |
| ProtocolVersion | mediumint(8) unsigned | NO | | 0 | |
| UpdateTimes | int(10) unsigned | NO | | 0 | |
| LastUpdateTime | datetime | YES | | NULL | |
+-----------------+-----------------------+------+-----+---------+----------------+
21 rows in set (0.00 sec)
mysql> select LAST_INSERT_ID();
+------------------+
| LAST_INSERT_ID() |
+------------------+
| 1 |
+------------------+
1 row in set (0.00 sec)
How to repeat:
Step 1. just create a text file named 'server all.txt', fill it with the following text
203.81.58.122:27015
203.81.54.228:27015
203.81.54.226:27022
203.81.54.226:27018
203.81.53.254:27016
203.95.229.118:27015
203.81.53.254:27015
Step 2. Create a table named 'Servers'
CREATE TABLE `servers` (
`ServerId` bigint(20) unsigned NOT NULL auto_increment,
`ServerName` varchar(100) NOT NULL default '',
`ServerAddress` varchar(45) NOT NULL default '' COMMENT '服务器地址,形势为x.x.x.x:Port',
`ServerIPString` varchar(45) NOT NULL default '' COMMENT '服务器IP地址(字符串)',
`ServerIP` int(10) unsigned NOT NULL default '0' COMMENT '服务器IP地址',
`ServerPort` int(10) unsigned NOT NULL default '0' COMMENT '服务器端口',
`GameDirectory` varchar(45) NOT NULL default '',
`GameDescription` varchar(45) NOT NULL default '',
`MapName` varchar(45) NOT NULL default '',
`ModWebsite` varchar(45) NOT NULL default '',
`IsSecureServer` tinyint(3) unsigned NOT NULL default '0',
`ServerType` tinyint(3) unsigned NOT NULL default '0',
`ServerOS` tinyint(3) unsigned NOT NULL default '0',
`SteamAppId` int(10) unsigned NOT NULL default '0',
`IsNeedPassword` tinyint(3) unsigned NOT NULL default '0',
`PlayerCount` mediumint(8) unsigned NOT NULL default '0',
`MaxPlayers` mediumint(8) unsigned NOT NULL default '0',
`BotCount` mediumint(8) unsigned NOT NULL default '0',
`ProtocolVersion` mediumint(8) unsigned NOT NULL default '0',
`UpdateTimes` int(10) unsigned NOT NULL default '0',
`LastUpdateTime` datetime default NULL,
PRIMARY KEY (`ServerAddress`,`ServerId`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='服务器';
Step 3. Use LOAD DATA INFILE to import
load data infile 'server all.txt' into table servers lines terminated by '\r\n' (serverAddress);
Step 4. select the ServerID field to see if it it correct or not.