Hi, I have a table that look like this

Autoincrement number, rating,
1 8
2 3
3 2

If I now choose to delete the rows where rating = 3.
How will the output be ?

Like this ?
Autoincrement number, rating,
1 8
2 2

or
Autoincrement number, rating,
1 8
3 2
?

or ??

    #2, Autoincrement only affects the insertion of rows. If you delete a row then that row no longer exists, and all of the other rows stay the way they are.

    Autoincrement is usually used to be a unique index field. You don't want you're unique indexes changing just because you deleted one of them. Especially since you will probably be referancing that index in another table to link things together.

      I've been searching for an answer to a somewhat related issue. Let's say I have a series of auto-incremented rows, numbered:

      1
      2
      3
      4
      5
      6
      7
      8
      9
      10
      11
      12
      13
      14
      ...

      If I have to delete and re-insert rows 2-11 for some reason (with an example this small, they could just as easily be edited directly, but when working with several hundred that must all be changed in a dissimilar fashion...), then they get placed back in the MySQL database in a non-intuitive fashion. Something like the following might be the result:

      1
      11
      10
      9
      8
      7
      6
      5
      4
      2
      3
      12
      13
      14

      (I believe it re-uses the available row #'s, but I'm not positive. At any rate, the same pattern is the result, regardless of row numer.)

      What it appears happens is that roughly the last 80% of the rows get inserted in descending numerical order, then the rest of them in ascending order. I can understand filling gaps within the database, but I can't think of any reason to insert items out of order. I've seen the same thing on three different servers (two different hosts), and have yet to see it work as expected...

      This is problematic because the PRIMARY/auto-incremented row makes a good default search order, and for certain things that order is very important... I suppose more conditions could be added to the SELECT clauses to always include that SORT BY, but it seems like unnecessary extra work and (possibly) overhead.

      Any suggestions?

      Thanks,
      Dan

        I don't know why you are getting the results taht you are, but the way an auto_increment field should work is that everytime you insert a row with NULL in the position of the auto_increment field it will automatically fill that field with the next highest number, regardless of what gaps there may be.

        You should be able to verify this like this:

        mysql> create table auto_test (id int not null auto_increment primary key);
        Query OK, 0 rows affected (0.00 sec)

        mysql> insert into auto_test values(123);
        Query OK, 1 row affected (0.02 sec)

        mysql> insert into auto_test values(NULL);
        Query OK, 1 row affected (0.00 sec)

        mysql> select * from auto_test;
        +-----+
        | id |
        +-----+
        | 123 |
        | 124 |
        +-----+
        2 rows in set (0.00 sec)

        Now on to your belief that this number is a good way to keep your data ordered. I hate to say you're wrong, but you kinda are. Auto_increment fields are best used for creating unique ID's for every row in the table. These ID's are then best used as referances (foreign keys - MySQL doesn't have them but the idea is still valid) in other tables. So if you had a table of eye_color that had an auto_increment field as the unique ID then you could have a field in another table that is called eye_color_id, and now all you need to store in that field is the ID number insead of the text, which will save you a lot of space. And even though it causes you to make larger selects, or even multiple selects to get the job done it is a better design and works out in the long run.

          Hi Rob,

          Thanks for the reply.

          I think the difference between your example and mine is singularity, for lack of a better word. When inserting a single row, there really is no way it could be out of order, as that row is the entire "order." Where I have run into problems is inserting multiple rows in the form of file uploads (mysqlimport).

          As you said, the auto-incrementing works as it should, it's just the order the rows get inserted into the database that make no sense (at least to me).

          I understand what you're saying about the PRIMARY ID's purpose being identification, not order, but I see no reason why it can't or shouldn't easily do both. I could be wrong, but wouldn't an un-sorted search require less resources than a sorted one?

          If you load 100 rows in order into the database, and those 100 rows are supposed to be displayed in the order they are loaded in, doesn't it make sense that they would stay in that order internally and not require an additional sort to get them back into that order?

          Thanks,
          Dan

            Now you're getting into the dynamics of how your database parses it's queries and does it's work.

            Typically if you use an Order By statement you are adding overhead to your query, but if your query is using an index to do it's work, and you are Ordering on the indexed field then the overhead is limited.

              11 years later

              Dude just turn my Insert code into Delete code and it'll work for you. Here is 2 ways that I use my insert code

              The code I'm about to give you should not be used if your joining 2 tables using primary keys. You could modify it to be able to use if two tables are joined but this code is for 1 table. There are two ways to do it as far as I know.
              Way 1: It finds the missing number from 1,3,4,... and turns the 3 into a 2, then the 4 into the 3. This uses a lot of database resources if the database table holds a lot of data.
              Way 2: It inserts the data into the last field of the table. In this case it's 5 because there are only 4 things in the table. It finds the missing number from 1,3,4,... and turns the Primary Key field that was inserted into the table into a 2.
              In both of these ways, it resets the primary key autoincrementor.

              <?php
              $user = "EnterUserNameHere";
              $password = "EnterPasswordHere";
              $database = "Enter DatabaseNameHere";
              $host = "EnterHostNameHere";
              $table = "EnterTableNameHere";
              $primaryKeyID = "EnterPrimaryKeyIDFieldNameHere";
              $name=$POST['txtContactName'];
              $email=$              POST['txtContactEmail'];
              $question=$_POST['txtContactQuestion'];
              $insert = "INSERT INTO $table VALUES('NULL','$name','$email','$question')";
              mysql_connect($host,$user, $password);
              @mysql_select_db($database) or die( "Unable to select database");
              ///////////////////////////////////////////////
              //////////DO NOT EDIT BELOW THIS LINE//////////
              ///////////////////////////////////////////////
              ////////WAY 1////////
              ////////////////////////

              /////Primary Key Gap Filler Code/////
              $countFunction = "SELECT COUNT('$primaryKeyID') AS num FROM $table";
              $data = mysql_query($countFunction) or die(mysql_error());
              $row = mysql_fetch_assoc($data);
              $numRows = $row['num'];
              if ( $numRows > 1 )
              {
              $currentRow = 1;
              $PKIDvalue = $currentRow;
              while ($currentRow <= $numRows)
              {
              $rowIDquery = mysql_query("SELECT FROM $table WHERE $primaryKeyID = $PKIDvalue");
              $rowID = mysql_num_rows($rowIDquery);
              while ($rowID < 1)
              {
              $PKIDvalue = $PKIDvalue + 1;
              $rowIDquery = mysql_query("SELECT               FROM $table WHERE $primaryKeyID = $PKIDvalue");
              $rowID = mysql_num_rows($rowIDquery);
              if ($rowID != 0)
              {
              mysql_query("UPDATE $table SET $primaryKeyID=$currentRow WHERE $primaryKeyID = $PKIDvalue");
              }
              }
              $currentRow = $currentRow + 1;
              $PKIDvalue = $currentRow;
              }
              }
              $reset = "ALTER TABLE $table AUTO_INCREMENT = 1";
              mysql_query($reset);
              /////Insert Input into Database/////
              mysql_query($insert) or die ("error updating database");
              /////Redirect to View Page/////
              echo "<meta http-equiv=\"refresh\" content=\"0;URL=../View/View.php\">";
              mysql_close();
              ?>
              ///////////////////////////////////////////////
              //////////DO NOT EDIT BELOW THIS LINE//////////
              ///////////////////////////////////////////////
              ////////WAY 2////////
              ///////////////////////

              /////Primary Key Gap Filler Code/////
              $countFunction = "SELECT COUNT('$primaryKeyID') AS num FROM $table";
              $data = mysql_query($countFunction) or die(mysql_error());
              $row = mysql_fetch_assoc($data);
              $numRows = $row['num'];

              if ( $numRows > 1 )
              {
              $currentRow = 1;
              while ($currentRow <= $numRows)
              {
              $rowIDquery = mysql_query("SELECT * FROM $table WHERE $primaryKeyID = $currentRow");
              $rowID = mysql_num_rows($rowIDquery);
              if ($rowID < 1)
              {echo $rowID; echo "<br/>";
              $query = "SELECT $primaryKeyID FROM $table ORDER BY $primaryKeyID DESC LIMIT 0,1";
              $result = mysql_query($query) or die(mysql_error());
              $grab = mysql_fetch_assoc($result);
              $max = $grab[$primaryKeyID];
              echo $max;
              mysql_query("UPDATE $table SET $primaryKeyID=$currentRow WHERE $primaryKeyID = $max");
              }
              $currentRow = $currentRow + 1;
              }
              }
              $reset = "ALTER TABLE $table AUTO_INCREMENT = 1";
              mysql_query($reset);
              /////Insert Input into Database/////
              mysql_query($insert) or die ("error updating database");

              /////Redirect to View Page/////
              echo "<meta http-equiv=\"refresh\" content=\"0;URL=../View/View.php\">";
              mysql_close();
              ?>

                6 days later

                I'm relieve he finally got what he needed after 11 years of waiting!

                As a side point regarding this entire outdated thread now that it has resurfaced, it seems noone actually plointed out that there are, according to the SQL standard, no "places" in a table, or in other words, there is no specific sort order. If you want a specific order, you have to specify it. Otherwise the it will be unspecified.

                  Write a Reply...