Re: autoincrement id missing when i do load data local into file
Posted by:
Rick James
Date: January 03, 2011 09:52AM
AUTO_INCREMENT has certain properties. MAX(id) == COUNT(*) is not one of them.
If you need the ids to be 1,2,3,..., then you should set them yourself, not depend on AUTO_INCREMENT.
Here are some of the properties / quirks (some quite subtle)...
* Each id in the table will be unique. This is the only thing you should depend on!
* For non-overlapping INSERTs, new numbers will be MAX(id)+1.
* Unlike some competing products that grab ids in batches, MySQL grabs only one at a time. (Other products 'burn' batches of ids after a crash.)
* After a crash, the next id to be used may be MAX(id)+1, or it may be the next id after where it left off. This depends on the Engine. This can be a surprise when a user grabs an id, DELETEs the record, restarts the server, then expects that grabbed id to never be assigned again.
* For overlapping INSERTs using InnoDB, it is possible to COMMIT id=5 before COMMITting id=4. (This can be a surprise in replication.)
* DELETE of the highest id -- That id may or may not be reused. It depends on the Engine you are using and whether the server was restarted in the mean time.
* DELETE of lower ids permanently waste that id. The gap will not be refilled.
* You can explicitly INSERT a row with an unused id, thereby allowing you to fill in a gap. (Not good practice.)
* REPLACE is a DELETE + INSERT.
* TRUNCATE may or may not reset the AUTO_INCREMENT value to use next. This depends on Engine, version of MySQL, SBR/RBR, etc.
* CREATE TEMPORARY TABLE (... AUTO_INCREMENT...) SELECT ... -- This will generate ids starting at 1 and having no gaps. You can use this trick to work with groupwise-max, etc.
* The setting of AUTO_INCREMENT_INCREMENT (sic) and its friend can be useful for multi-master (not recommended). Setting that VARIABLE to 2 lets you have 1,3,5,,, on one Master and 2,4,6... on the other. This leads to the likely quirk of the inserts being in, say, this order: 1,3,2,5,7,4,9,11,6,13,15,8,... (assuming the odd machine is INSERTing twice as fast as the even one).
(Caveat: This covers most quirks, perhaps not all, of MyISAM and InnoDB, crashes, replication, etc. But it does not necessarily cover NDB Cluster.)