MySQL исправляет пробелы автоинкремента в двух таблицах

Основная идея здесь заключается в том, чтобы сначала найти все пробелы, чтобы определить, сколько вам нужно уменьшить каждый идентификатор. Затем вам нужно пройти через обе таблицы и применить декремент. (Вам нужно будет добавить: host, db, user, pass и фактические имена таблиц)

 try { $pdo = new PDO('mysql:host=HOST;dbname=DB', 'user', 'pass'); $pdo->beginTransaction(); // Iterate through all id's in the first table $stmt = $pdo->exec('SELECT image_id FROM TableOne ORDER BY image_id ASC'); $stmt->bindColumn('image_id', $id); if(!$stmt->fetch(PDO::FETCH_BOUND)) { throw Exception('No rows in table'); } $lastId = $id; $gaps = array(); // Find all the gaps while($stmt->fetch(PDO::FETCH_BOUND)) { if($id != ($lastId + 1)) { $gaps[] = $id; } $lastId = $id; } if(!isset($gaps[0])) { throw new Exception('No gaps found'); } // For each gap, update the range from the last gap to that gap by subtracting // the number of gaps there has been from the id $lastGap = $gaps[0]; for($i = 1; $i < count($gaps); $i++) { $stmt = $pdo->prepare('UPDATE TableOne SET image_id = image_id - :i WHERE image_id BETWEEN :lastGap AND :gap'); $stmt->execute(array( ':i' => $i, ':lastGap' => $lastGap, ':gap' => $gaps[$i] )); $stmt = $pdo->prepare('UPDATE TableTwo SET image_id = image_id - :i WHERE image_id BETWEEN :lastGap AND :gap'); $stmt->execute(array( ':i' => $i, ':lastGap' => $lastGap, ':gap' => $gaps[$i] )); $lastGap = $gaps[$i]; } // Finally, fix the gap between the last found gap and the end of the table $stmt = $pdo->prepare('UPDATE TableOne SET image_id = image_id - :i WHERE image_id BETWEEN :lastGap AND :gap'); $stmt->execute(array( ':i' => $i, ':lastGap' => $lastGap, ':gap' => $gaps[$i] )); $stmt = $pdo->prepare('UPDATE TableTwo SET image_id = image_id - :i WHERE image_id BETWEEN :lastGap AND :lastId'); $stmt->execute(array( ':i' => $i, ':lastGap' => $lastGap, ':lastId' => $lastId )); // Verify everything is correct $stmt = $pdo->exec('SELECT image_id FROM TableOne ORDER BY image_id ASC'); $stmt->bindColumn('image_id', $id); if(!$stmt->fetch(PDO::FETCH_BOUND)) { throw new Exception('No rows'); // Should never be thrown } $lastId = $id; while($stmt->fetch(PDO::FETCH_BOUND)) { if($id != ($lastId + 1)) { throw new Exception('There was an error between ids ' . $lastId . ' and '. $id); } $lastId = $id; } $stmt = $pdo->exec('SELECT image_id FROM TableTwo ORDER BY image_id ASC'); $stmt->bindColumn('image_id', $id); if(!$stmt->fetch(PDO::FETCH_BOUND)) { throw new Exception('No rows in table two'); // Shouldn't hit this } $lastId = $id; $ids = array($id); while($stmt->fetch(PDO::FETCH_BOUND)) { $ids[] = $id; if(count($ids) == 2) { if($ids[0] !== $ids[1]) { throw new Exception('Table two error on ids '); } if($ids[0] !== $lastId) { throw new Exception('Table two error on id gapfix'); } $lastId = $ids[0]; $ids = array(); } } $pdo->commit(); } catch(Exception $e) { $pdo->rollBack(); var_dump($e); } 

Важный: Возможно, вы захотите выбросить это в файл и запустить через CLI: php -f gapfix.php и включить запрос до $pdo->commit() который возвращает список всех идентификаторов, чтобы вы могли проверить, что работа выполнена как ожидалось , Если это не так, вы можете откинуть назад, как будто ничего не произошло. Теперь код проверяет себя, если первая таблица находится в правильном порядке. Тем не менее, он не проверяет вторую таблицу. Вся проверка выполнена!

 ALTER TABLE table2 ADD FOREIGN KEY FK_IMAGE (id_image) REFERENCES table1 (id_image) ON DELETE CASCADE ON UPDATE CASCADE; SET @currentRow = 0; UPDATE table1 INNER JOIN ( SELECT @currentRow := @currentRow + 1 AS id_image_new, id_image AS id_image_old FROM table1 ORDER BY id_image ASC) t on t.id_image_old = table1.id_image SET table1.id_image = t.id_image_new; ALTER TABLE table1 AUTO_INCREMENT = 1; 

FK автоматически обновит идентификаторы вашей второй таблицы соответственно.

Я не совсем уверен, но в некоторых более старых версиях mysql обновляется таблица, в которую вы ссылаетесь в подзапросе обновления, может произойти сбой. Если это так, просто создайте вторую таблицу и заполните ее (вставки), затем удалите старый и переименуйте новую.

Это больно.

Создайте таблицу, такую ​​как первая, без идентификатора в Id_Image и дополнительный столбец int, называемый rownumber

Используйте команду pseudo row_number для заполнения, некоторые вроде

 Insert into NewTable Select id_image,foo,bar,@RowNumber := @RowNumber + 1 order by id_image. 

Если у вас есть внешний ключ для второй таблицы, то это простое обновление с соединением. Отбросьте старую таблицу1, переименуйте новую, добавьте личность и повторите ее, и верните свой внешний ключ, если он у вас есть.

Вы понимаете, что вам придется продолжать делать это дерьмо?

Вероятно, есть интересный способ сделать все это за один раз, если у вас есть каскадные обновления, но обратите пристальное внимание на план выполнения. Трюк RowNumber работает только в том случае, если все сделано в порядке Id_Image. Если Mysql решит, что есть более эффективный способ выполнения запроса …