Topic: Value of zero in auto_increment column

[wpb]

Hello,

Is it possible for an auto_increment column setup as an integer ever to have a value of zero?

I have an unique 'ID' column attached to my data, but in my PHP code, I'd like to use a value of zero to indicate 'No ID'. Is that a safe thing to do, or could SQL assign a value of zero ever to a row in the auto_increment column?

In a related area, are auto_increment values ever re-assigned? If I use a TINYINT as my 'ID' column type, add 254 entries (that should leave 1 ID spare, I think - 255), then delete entry 100, would I be able to add TWO more entries, or just the one at 255, leaving a hole at entry number 100?

Many thanks for people's help as usual!

Happy 2008, by the way!

[Barand]

As autoincrement allocates a unique id to every row added, the "No ID" situation can't arise. Attempting to write 0 will give an error.

If a zero value would have some special significance (such as a new user awaiting email verification, for example) use a flag in another column.

They are not automatically reused, and shouldn't be. See

http://www.phpfreaks.com/forums/index.php/topic,140306.msg596840.html#msg596840

The space used by deleted rows is reused, so if delete 100 and add 255 then, if there is space for the new row, then you table's id physical sequence becomes ...98, 99, 255, 101, ...

[wpb]

Perhaps I wasn't clear. I don't want to write the value of zero to the database. I want to use the value of zero in my PHP code to signify something special, so I need to know if "SELECT id FROM mytable" will ever return id=0, where the id column is an auto_increment.

In response to the second part of my question, does that mean that in an auto_inc tinyint unsigned column, if you added 255 rows, then deleted 255 rows, any subsequent attempts to add a row would fail (because the auto_inc value would have reached the max. allowable in a tinyint?

Thanks

[Barand]

As I said, attempting to put 0 in an autoinc field fails, ergo you won't have any in there.

this script should show you what happens.

First 255 add OK
record 1000 fails
delete records
record 2000 added with id=255 (last id reused)
record 3000 fails

Code: [Select]

<?php
include 'db.php';

mysql_query ("DROP TABLE IF EXISTS tinytest");
mysql_query ("CREATE TABLE tinytest (
              id tinyint unsigned not null auto_increment primary key,
              xx int
                )");

for ($i=1; $i <=255; $i++)
{
    mysql_query ("INSERT INTO tinytest (xx) VALUES ($i)") ;
}

// try adding another
$res = mysql_query ("INSERT INTO tinytest (xx) VALUES (1000)");
if (!$res) echo '<p>Attempt 1 :', mysql_error(), '</p>';


// delete them all
$res = mysql_query ("DELETE FROM tinytest");
echo '<p>Records deleted</p>';

// try adding another
$res = mysql_query ("INSERT INTO tinytest (xx) VALUES (2000)");
if (!$res) echo '<p>Attempt 2 :', mysql_error(), '</p>';
echo table2Table('tinytest');

// try adding another
$res = mysql_query ("INSERT INTO tinytest (xx) VALUES (3000)");
if (!$res) echo '<p>Attempt 3 :', mysql_error(), '</p>';
echo table2Table('tinytest');
//
// function to list results
//
function table2Table($tname) {
    $result = mysql_query("SELECT * FROM `$tname` ");

    $str = "<TABLE border='1' cellpadding='4'>\n";

    // column headings
          $str .=  "<tr>\n";
          while ($fld = mysql_fetch_field ($result)) {
                   $str .= "<th>{$fld->name}</th>\n";
          }
          $str .=  "</tr>\n";


    // list data
          while ($row = mysql_fetch_row($result)) {
          $str .=  "<tr>\n";
          foreach ($row as $field) {
                   $str .=  "<td>$field</td>\n";
          }
          $str .=  "</tr>\n";
    }

    $str .=  "</TABLE>\n";
    
    return $str;
}

?>

[wpb]

Thanks Barand,

I tried your example, and I can see how the autoinc works now. It seems to me that autoinc isn't the right choice for a database of records where you might be deleting and adding rows on a regular basis.

Is there an in-built alternative (that fills in values where rows have been deleted, for example), or do you have to write code to manage your own unique ids?

Thanks,

Will

[Barand]

Well, tinyint certainly isn't a good choice of column type but with an  unsigned INT you can get to over four billion which should keep you going for a while.

Or you could use MySQL UUID() function

[AndyB]

The best way to treat auto-increment is as if it's a property of MySQL and not something you can access.  There are plenty of ways to organize displayed data without knowing the record id.  If you have a specific problem that you feel requires accessing the auto-inc id, tell us and we'll offer a better solution.

[wpb]

I don't need my autoinc IDs for display purposes - I'm using them to link tables together. I have a list of products in one table (with autoinc product_IDs), and a list of reviews in another table (also with autoinc review_IDs). Each review in the review table references a particular product in the products table, by way of its product_ID.

I realise that if I use INT for my autoinc ID columns, I'll have billions of numbers to go at, so running out would never be an issue, even if I added and deleted rows every day for years and years to come. BUT! As a programmer, I can't help feel uneasy about the fact that one day, in the far distant future, when aliens are accessing my website, they'll hit a MySQL error because there are no IDs free, despite the fact that there may not be that many products or reviews in the database!!!

Also, it seems good practice to pick the right data type for the column. So if I know I'll never have more than 256 products in the db at one time, an int unsigned seems like the right choice.

A 128bit UUID does seem like a bit of an overkill!!! Perhaps I'll just code my own IDs, and not use autoinc at all. Gotta think about the aliens!

[Barand]

By the time that day comes, magnetic storage (like punched cards and paper tape) will only be in museums and the addressable range will far, far greater.