In a new empty table, I import a CSV file by following these steps:
ID (autoincrement) PRIMARY; name; age
SET (ID = DEFAULT)
The CSV file contains 50,000 records. When finished, the next autoincrement should be 50,001, but instead, in my App the next autoincrement is eg 68,020 or 62,153 or 65,448 ...
If the CSV file contains the ID column
1; Hernan; 34
2; Mariano; 12
After import 50,000 records, the next autoincrement its OK (50,001)
Why the IDs jumps in my work?
(Tested on localhost Appserv)
Is there any way in one single query do this?
(col1, col2, @ID)
@ID = 1; <----- Where I can put this?
SET (ID = @ID + 1)
In my system you can create an event like an EXPO, in this event there are for example 3 types of signups (visitors, participants and staff)... in each signup type I need to be distinct code ranges (Visitor start at 5.000, Participants start at 15.000 and staff start with 25.000), in this case yo can see a code and then know what type of signup is that person and the secuential ID gave me an idea of how advanced is the signup records (ID 17.532 => Participant and aprox is the 2.530 signup from this signup type). All my system works perfectly only this "bug" for de LOAD DATA INFILE.