INSERT INTO行数+ 1 [关闭]

I have a code which works fine but instead ID with autoincrement i want to insert id=Number of rows + 1 - of mediamall_favourite_media table

I want to do this because now though i set auto_increment option when i delete all rows . Next row is inserted with the last number . For example - I insert 50 rows and i delete all off them then if i insert one more row(though the table is empty) the id is 51...

$user =& JFactory::getUser();
    $db2 =& JFactory::getDBO();
    $mediaid = $_POST['addMediaId'];
    //$delid = $_POST['delRow'];


if(isset($_POST['submitCheck']) and $_POST['submitCheck'] == '1') {             
  $query = ' INSERT INTO `#__mediamall_favourite_media` (`id`, `userid`, `mediaid`) VALUES (NULL,"'.$user->id.'","'.$mediaid.'")';
 }    
elseif(isset($_POST['submitCheck']) and $_POST['submitCheck'] == '0') {     
$query = ' DELETE FROM `#__mediamall_favourite_media` WHERE `id` = "'.$delid.'" '; 
}  


if($query) {
   $db2->setQuery($query);
   $db2->query();
}

Please , have you got any solutions ?

No, you cannot do that in transactional database. Here is the scenario:

• Transaction A starts, gets max(id) + 1 as new id (say 101).
• Transaction B starts, gets max(id) + 1 as new id (say 101).
• Transaction A commits.
• Transaction B tries to commit - boom! It can't - you've got duplicate id 101.

(instead of max(id)+1 you could use count(*)+1 - outcome will be the same).

Trying to reuse holes does not work either:

• Transaction A starts, gets id as autoincrement (say 101).
• Transaction B starts, gets id as autoincrement (say 102).
• Transaction B commits.
• Transaction A aborts for some weird reason (for example client lost connection). Now, id 101 will be unused. If you try to reuse it, it will be very expensive.

To make this picture even more complicated, imagine that it was not 2, but say 10 concurrent transactions working at the same time?

In other words, stop fighting this and live up with it.