MySQL में डिलीट की गई पंक्तियों के बाद ऑटो-इन्क्रीमेंट को कैसे ठीक करें?

MySQL में पंक्तियाँ डिलीट होने के बाद ऑटो-इन्क्रीमेंट मान में गैप (1, 3, 5) रह जाते हैं। यदि क्रमिक मान (1, 2, 3) चाहिए, तो यूज़र-डिफ़ाइंड वेरिएबल के साथ UPDATE का उपयोग करके मौजूदा ID को पुन: क्रमांकित करें।

समस्या: डिलीट के बाद गैप

टेबल बनाएं और डेटा इन्सर्ट करें:

mysql> CREATE TABLE DemoTable1955 (
    UserId INT NOT NULL AUTO_INCREMENT PRIMARY KEY
);
mysql> INSERT INTO DemoTable1955 VALUES (), (), (), (), ();

डेटा (1, 2, 3, 4, 5):

+--------+
| UserId |
+--------+
| 1      |
| 2      |
| 3      |
| 4      |
| 5      |
+--------+

पंक्तियाँ 2 और 4 डिलीट करें:

mysql> DELETE FROM DemoTable1955 WHERE UserId IN (2, 4);

शेष (गैप के साथ):

+--------+
| UserId |
+--------+
| 1      |
| 3      |
| 5      |
+--------+

समाधान: वेरिएबल के साथ UPDATE से पुन: क्रमांकित करें

mysql> UPDATE DemoTable1955
SET UserId = (@increment_value := @increment_value + 1)
ORDER BY UserId;

परिणाम (क्रमिक 1, 2, 3):

+--------+
| UserId |
+--------+
| 1      |
| 2      |
| 3      |
+--------+

कैसे काम करता है

1. @increment_value सेशन वेरिएबल (प्रारंभिक NULL = 0)
2. ORDER BY UserId वर्तमान क्रम में प्रोसेस करता है (1, 3, 5)
3. प्रत्येक पंक्ति के लिए: @increment_value := @increment_value + 1 → 1, 2, 3
4. नया मान UserId में असाइन होता है

महत्वपूर्ण चेतावनियां

• प्राइमरी की अपडेट: PK अपडेट करना जोखिम भरा है; विदेशी कुंजी टूट जाएंगी!
• विदेशी कुंजी: रेफरेंस करने वाली टेबल्स में CASCADE UPDATE या मैन्युअल अपडेट आवश्यक
• ट्रांजैक्शन: ट्रांजैक्शन में चलाएं
• लॉक: पूरी टेबल लॉक होगी
• प्रतिकृति: रिप्लिकेशन में समस्या पैदा कर सकता है

बेहतर दृष्टिकोण: गैप स्वीकार करें

उत्पादन में ऑटो-इन्क्रीमेंट गैप सामान्य और स्वीकार्य हैं।

• गैप प्रदर्शन प्रभावित नहीं करते
• रीसाइक्लिंग ID डेटा इंटीग्रिटी जोखिम पैदा करता है
• ऑडिट ट्रेल, लॉग, विदेशी कुंजी संदर्भ टूट सकते हैं

यदि वास्तव में आवश्यक हो: सुरक्षित पुन: क्रमांकन

START TRANSACTION;

-- 1. नई टेबल बनाएं
CREATE TABLE new_table LIKE DemoTable1955;

-- 2. क्रमिक ID के साथ कॉपी
INSERT INTO new_table (UserId)
SELECT @rownum := @rownum + 1
FROM DemoTable1955
CROSS JOIN (SELECT @rownum := 0) r
ORDER BY UserId;

-- 3. स्वैप
RENAME TABLE DemoTable1955 TO old_table, new_table TO DemoTable1955;

COMMIT;

निष्कर्ष: गैप स्वीकार करें। केवल टेस्ट/डेव डेटा रीसेट के लिए पुन: क्रमांकित करें।