MySQL의 자동 증가 필드에서 공백을 채울 수 있습니까?

Filling Gaps in Auto-Increment Fields

Question: Is there a way to seamlessly fill gaps in auto-increment fields with new inserts?

Answer:

While it is generally not advisable to rely on auto-increment fields for ordering or semantic meaning, there are situations when filling these gaps may be desirable. In such cases, a MySQL feature can be utilized to create a new set of sequential IDs.

Create a temporary table using CREATE TEMPORARY TABLE NewIDs (NewID INT UNSIGNED AUTO INCREMENT, OldID INT UNSIGNED) and insert all existing IDs in ascending order:

INSERT INTO NewIDs (OldID)
SELECT Id
FROM OldTable
ORDER BY Id ASC

This will create a mapping between old and new IDs.

To update the references in other tables, disable foreign key constraints:

SET foreign_key_checks = 0;

Then, update the Parent and Child tables:

UPDATE
Parent, Child, NewIds
SET
Parent.ParentId = NewIds.Id,
Child.ParentId = NewIds.Id
WHERE
Parent.ParentId = NewIds.ParentId AND
Child.ParentId = NewIds.ParentId

Finally, re-enable foreign key checks:

SET foreign_key_checks = 1;

Drop the temporary table for cleanup:

DROP TABLE NewIds

It's important to note that this process should be used with caution, as modifying auto-increment fields can have implications for referential integrity and performance.

위 내용은 MySQL의 자동 증가 필드에서 공백을 채울 수 있습니까?의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!