MySQL AUTO_INCREMENT με Παραδείγματα
Μάρκους Άλεν
⚡ Έξυπνη Σύνοψη
MySQL Η AUTO_INCREMENT δημιουργεί αυτόματα διαδοχικούς αριθμούς για μια αριθμητική στήλη κάθε φορά που εισάγεται μια γραμμή. Το χαρακτηριστικό αυτό καταργεί την ανάγκη υπολογισμού μοναδικών αναγνωριστικών με το χέρι, γεγονός που το καθιστά τον τυπικό τρόπο συμπλήρωσης ενός πρωτεύοντος κλειδιού.
Τι είναι η αυτόματη αύξηση;
Η Αυτόματη Αύξηση είναι μια συνάρτηση που λειτουργεί σε τύπους αριθμητικών δεδομένων. Δημιουργεί αυτόματα διαδοχικές αριθμητικές τιμές κάθε φορά που μια εγγραφή εισάγεται σε έναν πίνακα για ένα πεδίο που ορίζεται ως αυτόματη αύξηση.
Το χαρακτηριστικό λειτουργεί σε οποιονδήποτε ακέραιο τύπο, από TINYINT έως BIGINT. Η στήλη πρέπει επίσης να έχει ευρετήριο, κάτι που συμβαίνει αυτόματα όταν δηλώνεται ως το πρωτεύον κλειδί.
Πότε χρησιμοποιείται η αυτόματη αύξηση;
Στο μάθημα για ομαλοποίηση βάσης δεδομένων, εξετάσαμε πώς μπορούν να αποθηκευτούν δεδομένα με ελάχιστο πλεονασμό, αποθηκεύοντας δεδομένα σε πολλούς μικρούς πίνακες, που σχετίζονται μεταξύ τους χρησιμοποιώντας πρωτεύοντα και ξένα κλειδιά.
Ένα πρωτεύον κλειδί πρέπει να είναι μοναδικό, καθώς προσδιορίζει μοναδικά μια γραμμή σε μια βάση δεδομένων. Πώς όμως μπορούμε να διασφαλίσουμε ότι το πρωτεύον κλειδί είναι πάντα μοναδικό;
Μία από τις πιθανές λύσεις θα ήταν η χρήση ενός τύπου για τη δημιουργία του πρωτεύοντος κλειδιού, ο οποίος ελέγχει την ύπαρξη του κλειδιού στον πίνακα πριν από την προσθήκη δεδομένων. Αυτό μπορεί να λειτουργήσει, αλλά η προσέγγιση είναι πολύπλοκη και όχι αλάνθαστη. Δύο συνεδρίες που εισάγονται την ίδια στιγμή μπορούν ακόμα να διαβάσουν την ίδια μέγιστη τιμή και να συγκρουστούν.
Για να αποφύγουμε τέτοια πολυπλοκότητα και να διασφαλίσουμε ότι το πρωτεύον κλειδί είναι πάντα μοναδικό, μπορούμε να χρησιμοποιήσουμε το MySQL Λειτουργία αυτόματης αύξησης για τη δημιουργία πρωτευόντων κλειδιών. Η αυτόματη αύξηση χρησιμοποιείται με τον τύπο δεδομένων INT. Ο τύπος δεδομένων INT υποστηρίζει τόσο προσημασμένες όσο και μη προσημασμένες τιμές. Οι μη προσημασμένοι τύποι δεδομένων μπορούν να περιέχουν μόνο θετικούς αριθμούς. Ως βέλτιστη πρακτική, συνιστάται να ορίσετε τον περιορισμό μη προσημασμένου αριθμού στο πρωτεύον κλειδί αυτόματης αύξησης.
Σύνταξη αυτόματης αύξησης
Αφού κατανοήσετε πλήρως τη συλλογιστική, εξετάστε το σενάριο που χρησιμοποιήθηκε για τη δημιουργία του πίνακα κατηγοριών ταινιών.
CREATE TABLE `categories` ( `category_id` int UNSIGNED NOT NULL AUTO_INCREMENT, `category_name` varchar(150) DEFAULT NULL, `remarks` varchar(500) DEFAULT NULL, PRIMARY KEY (`category_id`) );
Παρατηρήστε την ένδειξη "AUTO_INCREMENT" στο πεδίο category_id. Αυτό έχει ως αποτέλεσμα την αυτόματη δημιουργία του αναγνωριστικού κατηγορίας κάθε φορά που εισάγεται μια νέα γραμμή στον πίνακα. Δεν παρέχεται κατά την εισαγωγή δεδομένων στον πίνακα, MySQL το δημιουργεί.
Σημείωση: Η λέξη-κλειδί UNSIGNED διπλασιάζει το θετικό εύρος της στήλης και το πλάτος εμφάνισης, όταν γραφτεί ως int(11), καταργείται από MySQL 8.0.17 και μετά. Απλό int είναι η τρέχουσα μορφή.
Από προεπιλογή, η αρχική τιμή για τη συνάρτηση AUTO_INCREMENT είναι 1 και θα αυξάνεται κατά 1 για κάθε νέα εγγραφή.
Ας εξετάσουμε τα τρέχοντα περιεχόμενα του πίνακα κατηγοριών.
SELECT * FROM `categories`;
Εκτέλεση του παραπάνω σεναρίου στο MySQL Το Workbench σε σχέση με το myflixdb μας δίνει τα ακόλουθα αποτελέσματα.
| category_id | category_name | remarks |
|---|---|---|
| 1 | Comedy | Movies with humour |
| 2 | Romantic | Love stories |
| 3 | Epic | Story acient movies |
| 4 | Horror | NULL |
| 5 | Science Fiction | NULL |
| 6 | Thriller | NULL |
| 7 | Action | NULL |
| 8 | Romantic Comedy | NULL |
Υπάρχουν οκτώ γραμμές, επομένως το επόμενο αναγνωριστικό που θα δημιουργηθεί θα πρέπει να είναι 9. Ας εισαγάγουμε τώρα μια νέα κατηγορία στον πίνακα κατηγοριών, παρέχοντας μόνο το όνομα.
INSERT INTO `categories` (`category_name`) VALUES ('Cartoons');
Εκτέλεση του παραπάνω σεναρίου έναντι του myflixdb in MySQL πάγκος εργασίας μας δίνει τα ακόλουθα αποτελέσματα που φαίνονται παρακάτω.
| category_id | category_name | remarks |
|---|---|---|
| 1 | Comedy | Movies with humour |
| 2 | Romantic | Love stories |
| 3 | Epic | Story acient movies |
| 4 | Horror | NULL |
| 5 | Science Fiction | NULL |
| 6 | Thriller | NULL |
| 7 | Action | NULL |
| 8 | Romantic Comedy | NULL |
| 9 | Cartoons | NULL |
Σημειώστε ότι δεν παρείχαμε το αναγνωριστικό κατηγορίας. MySQL το δημιούργησε αυτόματα, επειδή το αναγνωριστικό κατηγορίας ορίζεται ως αυτόματη αύξηση.
Εάν θέλετε να λάβετε το τελευταίο αναγνωριστικό εισαγωγής που δημιουργήθηκε από MySQL, μπορείτε να χρησιμοποιήσετε τη συνάρτηση LAST_INSERT_ID για να το κάνετε αυτό. Το σενάριο που εμφανίζεται παρακάτω λαμβάνει το τελευταίο αναγνωριστικό που δημιουργήθηκε.
SELECT LAST_INSERT_ID();
Η εκτέλεση του παραπάνω σεναρίου δίνει τον τελευταίο αριθμό αυτόματης αύξησης που δημιουργήθηκε από το ερώτημα INSERT. Τα αποτελέσματα εμφανίζονται παρακάτω.
Συμβουλή: Η συνάρτηση LAST_INSERT_ID() έχει εύρος ζώνης για τη δική σας σύνδεση, επομένως μια τιμή που δημιουργείται από την εισαγωγή ενός άλλου χρήστη δεν μπορεί ποτέ να σας επιστραφεί κατά λάθος.
Πώς να ορίσετε ή να επαναφέρετε την αρχική τιμή AUTO_INCREMENT
Η προεπιλεγμένη ακολουθία ξεκινά από το 1, αλλά αυτό δεν είναι πάντα αυτό που χρειάζεται ένα έργο. Οι αριθμοί τιμολογίων ενδέχεται να πρέπει να συνεχιστούν από ένα παλαιότερο σύστημα και ένας πίνακας δοκιμών πρέπει συχνά να επαναφερθεί. MySQL εκθέτει τον μετρητή απευθείας, επομένως και οι δύο περιπτώσεις αντιμετωπίζονται με μία μόνο ρήτρα. Ακολουθήστε αυτά τα βήματα για να ελέγξετε τον αριθμό έναρξης.
- Ορίστε την τιμή κατά τη στιγμή της δημιουργίας. Προσαρτήστε τον όρο AUTO_INCREMENT στην πρόταση CREATE TABLE. Η πρώτη γραμμή που εισάγεται λαμβάνει στη συνέχεια αυτόν τον αριθμό αντί για 1.
- Αλλάξτε την τιμή σε έναν υπάρχοντα πίνακα. Χρήση ΠΙΝΑΚΑΣ ΕΝΑΛΛΑΓΗΣ με την ίδια ρήτρα. MySQL δέχεται τον νέο αριθμό μόνο εάν είναι υψηλότερος από το μεγαλύτερο αναγνωριστικό που είναι αποθηκευμένο αυτήν τη στιγμή.
- Επαναφέρετε έναν πίνακα που έχετε αδειάσει. Η συνάρτηση TRUNCATE TABLE καταργεί όλες τις γραμμές και επιστρέφει τον μετρητή σε 1 με μία μόνο λειτουργία, κάτι που η συνάρτηση DELETE από μόνη της δεν κάνει.
- Επιβεβαιώστε την αλλαγή. Εισαγάγετε μια γραμμή και διαβάστε ξανά το αναγνωριστικό με την LAST_INSERT_ID() πριν βασιστείτε στη νέα ακολουθία.
-- Start a brand-new table at 1000 CREATE TABLE `invoices` ( `invoice_id` int UNSIGNED NOT NULL AUTO_INCREMENT, `amount` decimal(10,2), PRIMARY KEY (`invoice_id`) ) AUTO_INCREMENT = 1000; -- Move the counter on an existing table ALTER TABLE `categories` AUTO_INCREMENT = 100; -- Empty the table and reset the counter to 1 TRUNCATE TABLE `categories`;
Το μέγεθος του βήματος μπορεί επίσης να αλλάξει με τη μεταβλητή συστήματος auto_increment_increment, αλλά ισχύει για ολόκληρο τον διακομιστή και όχι για έναν πίνακα. Χρησιμοποιείται κυρίως στην αναπαραγωγή, όπου δύο διακομιστές δεν πρέπει να δημιουργούν το ίδιο αναγνωριστικό.
Γιατί εμφανίζονται κενά σε μια ακολουθία AUTO_INCREMENT;
Αργά ή γρήγορα, ένας πίνακας εμφανίζει αναγνωριστικά όπως 1, 2, 5, 6. Τίποτα δεν είναι σπασμένο. Ο μετρητής έχει σχεδιαστεί για να εγγυάται τη μοναδικότητα, όχι για να εγγυάται μια αδιάσπαστη σειρά αριθμών, και δεν εκδίδει ποτέ την ίδια τιμή δύο φορές.
Τα κενά εμφανίζονται για τους ακόλουθους λόγους.
- Διαγραμμένες σειρές: Όταν μια γραμμή διαγράφεται από έναν πίνακα, το αυτόματα αυξανόμενο αναγνωριστικό της δεν χρησιμοποιείται ξανά. MySQL συνεχίζει να δημιουργεί νέους αριθμούς διαδοχικά.
- Συναλλαγές που ανακλήθηκαν: Ο αριθμός διεκδικείται τη στιγμή που εκτελείται η εισαγωγή. Εάν η συναλλαγή ακυρωθεί, η σειρά εξαφανίζεται, αλλά ο αριθμός έχει ήδη δαπανηθεί.
- Αποτυχημένες εισαγωγές: Μια δήλωση που απορρίπτεται από έναν περιορισμό UNIQUE μπορεί ακόμα να καταναλώσει ένα αναγνωριστικό πριν αποτύχει.
- Μαζικά ένθετα: Το InnoDB μπορεί να δεσμεύσει ένα μπλοκ αριθμών για μια εισαγωγή πολλαπλών γραμμών και να απορρίψει αυτούς που δεν χρησιμοποιεί.
Η προσπάθεια να κλείσετε αυτά τα κενά είναι λάθος. Η εκ νέου αρίθμηση γραμμών καταστρέφει κάθε ξένο κλειδί που δείχνει προς αυτές και η ίδια η τιμή δεν έχει κανένα επαγγελματικό νόημα. Εάν μια αναφορά χρειάζεται μια συνεχή λίστα, δημιουργήστε τον αριθμό γραμμής στο ερώτημα αντί να ξαναγράψετε τα αποθηκευμένα δεδομένα.
