Primary Key Index Automatic

Question

I m currently doing a project using mysql and am a perfect beginner in it.....

I made a table with the following columns.....

            ID           // A integer type column which is a primary key........
            Date         // A Date type column.........
            Day          // A String column.........

Now i just wanna know whether there exist any method by which the ID column insertion value is automatically generated......??

for eg: - If i insert a date - 4/10/1992 and Day - WED as values. The Mysql Server should automatically generate any integer value starting from 1 checking whether they exist. i.e in a table containing the values

             ID              Date              Day

             1              01/02/1987         Sun
             3              04/08/1990         Sun

If i m inserting the Date value and Day value(specified in the example) in the above table. It should be inserted as

             2              04/10/1992         WED

I tried methods like using auto incrementer.....But i m afraid it just only increments the ID value.

Primary Key with Autoincrement on ID field will generate new IDs for you. — Stelian Matei, Jun 23 '12 at 06:38
@mazzucci...i tred Autoincrement on ID....it's just increment the ID's.....rather i want to insert primary keys based on the example i've shown...... — Arjun K P, Jun 23 '12 at 06:49
In the example, did you delete row 2, and now you're inserting another? — Ami, Jun 23 '12 at 13:53
You get gaps with `AUTO_INCREMENT` and that is ok. You don't have to expose this `ID` column to users anyway. What is the problem if there are gaps? You have 4 billion numbers if the `ID` is defined as `INT UNSIGNED`. If that is not enough, make it `BIGINT UNSIGNED` and there you have about 16 quintillion available numbers. — ypercubeᵀᴹ, Jun 23 '12 at 14:10
@ypercube that's okay.....i thought abt it....but what if the table is getting manipulated every time.....and what if we are using that database for years.....won't this incremented value cross the limit...???!! Know what....if u can find a solution to this situation....i'm ready to go with Auto incrementer...... — Arjun K P, Jun 23 '12 at 17:01
possible duplicate of [For a primary key of an integral type, why is it important to avoid gaps?](http://stackoverflow.com/questions/2535483/for-a-primary-key-of-an-integral-type-why-is-it-important-to-avoid-gaps) — ypercubeᵀᴹ, Jun 23 '12 at 21:41
@ypercube ...they haven't answered my question in the link you have posted.....atleast how can we control the flow of auto incrementing if we want to use the databse for years.....?????? i.e if a user enters 100 entries and then deletes them......and this sort of operation occurs for years.... won't the value of primary key increases beyond the limit....... — Arjun K P, Jun 24 '12 at 12:09
@akp if you use an 8 byte integer (`BIGINT`) that's 8*8 bits, so 64 bits which means you can count up to 2^64. Even if your application works continuously for years and years it shouldn't reach a 20 digit number like 18446744070000000000. — Mihai Stancu, Jun 26 '12 at 11:55
@akp also if you really think you need that kind of certainty that the ids won't *run out* in a couple of years perhaps you should be using [UUIDs](http://en.wikipedia.org/wiki/Universally_unique_identifier) about which it is stated that "[Only after generating 1 billion UUIDs every second for the next 100 years, the probability of creating just one duplicate would be about 50%](http://en.wikipedia.org/wiki/Universally_unique_identifier#Random_UUID_probability_of_duplicates)". BTW UUIDs use 122 random bits and occupy 128 bits. — Mihai Stancu, Jun 26 '12 at 12:00
@MihaiStancu have any idea how can i implement it in mysql..?? — Arjun K P, Jun 26 '12 at 16:51

Ami · Answer 1 · 2012-06-23T14:20:52.070

There's a way to do this, but it's going to affect performance. Go ahead and keep auto_increment on the column, just for the first insert, or for when you want to insert more quickly.

Even with auto_increment on a column, you can specify the value, so long as it doesn't collide with an existing value.

To get the next value or first gap:

SELECT a.ID + 1 AS NextID FROM tbl a
LEFT JOIN tbl b ON b.ID = a.ID + 1
WHERE b.ID IS NULL
ORDER BY a.ID
LIMIT 1

If you get an empty set, just use 1, or let auto_increment do its thing.

For concurrency sake, you will need to lock the table to keep other sessions from using the next ID which you just found.

sorry...to say..but's it quite complicated....(maybe coz m new to mysql)....cud u explain the query..........?? — Arjun K P, Jun 23 '12 at 16:59

score 1 · Answer 2 · answered Jun 28 '12 at 15:54

Well...i understood your problem...You want to generate the entries in such a way that it can control it's limit...

Well i've got a solution which is quite whacky...you may accept it if u feel like....

create your table with your primary key in auto increment mode using unsigned int (as every one suggested here)....

now consider two situations....

If your table needs to be cleared every single year or within certain duration(if such a situation exist).... perform alter table operation to disable autoincrement mode and delete all your contents... and then enable it again......

if what you are doing is some sort of datawarehousing.....so that a database for years.... then included a sql query to find the smallest primary key value using predefined key functions before you insert and if it is more than the 2^33 create a new table with the same details and you should maintain a seperate table to track the number of tables of this types

The trick is bit complicated and i m afraid....there don't exist a simple way as you expected....

hmm.....somewhat good....will this make the process slower since everytime we are checking the maxmimum most value in the primaryy key column even though it might be indexed...... — Arjun K P, Jun 28 '12 at 16:03

Mihai Stancu · Answer 3 · 2012-06-26T22:04:30.250

You really don't need to cover the gaps created by deleting values from integer primary key columns. They were especially designed to ignore those gaps.

The auto increment mechanism could have been designed to take into consideration either the gaps at the top (after you delete some products with the biggest id values) or all gaps. But it wasn't because it was designed not to save space but to save time and to ensure that different transactions don't accidentally generate the same id.

In fact PostgreSQL implements it's SEQUENCE data type / SERIAL column (their equivalent to MySQL auto_increment) in such a way that if a transaction requests the sequence to increment a few times but ends up not using those ids, they never get used. That's also designed to avoid the possibility of transactions ever accidentally generating and using the same id.

You can't even save space because when you decide your table is going to use SMALLINT that's a fixed length 2 byte integer, it doesn't matter if the values are all 0 or maxed out. If you use a normal INTEGER that's a fixed length 4 byte integer.

If you use an UNSIGNED BIGINT that's an 8 byte integer which means it uses 8*8 bits = 64 bits. With an 8 byte integer you can count up to 2^64, even if your application works continuously for years and years it shouldn't reach a 20 digit number like 18446744070000000000 (if it does what the hell are you counting the molecules in the known universe?).

But, assuming you really have a concern that the ids might run out in a couple of years perhaps you should be using UUIDs in stead of integers.

Wikipedia states that "Only after generating 1 billion UUIDs every second for the next 100 years, the probability of creating just one duplicate would be about 50%".

UUIDs can be stored as BINARY(16) if you convert them into raw binary, as CHAR(32) if you strip the dashes or as CHAR(36) if you leave the dashes.

Out of the 16 bytes = 128 bits of data UUIDs use 122 random bits and 6 validation bits and they are constructed using information about when and where they were created. Meaning it is safe to create billions of UUIDs on different computers and the likelihood of collision would be overwhelmingly minuscule (as opposed to generating auto-incremented integers on different machines).

Primary Key Index Automatic

3 Answers3