Incremento automatico dopo l’eliminazione in MySQL

Ho una tabella MySQL con un campo chiave primaria su cui è attivo AUTO_INCREMENT. Dopo aver letto altri post qui ho notato persone con lo stesso problema e con risposte varie. Alcuni raccomandano di non usare questa funzione, altri affermano che non può essere “riparato”.

Io ho:

table: course fields: courseID, courseName 

Esempio: numero di record nella tabella: 18. Se cancello i record 16, 17 e 18 – Mi aspetto che il prossimo record inserito abbia l’ID corso di 16, tuttavia sarà 19 perché l’ultimo ID corso inserito era 18.

La mia conoscenza di SQL non è sorprendente ma è comunque necessario aggiornare o aggiornare questo conteggio con una query (o un’impostazione nell’interfaccia phpMyAdmin)?

Questa tabella si riferisce ad altri in un database.


Dato tutto il consiglio, ho deciso di ignorare questo “problema”. Semplicemente cancellerò e aggiungerò i record lasciando che l’incremento automatico faccia il suo lavoro. Immagino che non importa quale sia il numero dal momento che viene utilizzato solo come identificatore univoco e non ha un significato commerciale (come detto sopra).

Per coloro che potrei aver confuso con il mio post originale: non desidero utilizzare questo campo per sapere quanti record ho. Volevo solo che il database fosse ordinato e avesse un po ‘più di coerenza.

Quello che stai cercando di fare sembra pericoloso, perché non è l’uso previsto di AUTO_INCREMENT .

Se si desidera veramente trovare il valore della chiave inutilizzato più basso, non utilizzare affatto AUTO_INCREMENT e gestire le chiavi manualmente. Tuttavia, questa NON è una pratica raccomandata.

Fai un passo indietro e chiedi ” perché hai bisogno di riciclare i valori chiave?INT (o BIGINT ) senza segno non fornisce uno spazio chiave abbastanza grande?

Hai davvero intenzione di avere più di 18,446,744,073,709,551,615 record unici nel corso della vita della tua applicazione?

 ALTER TABLE foo AUTO_INCREMENT=1 

Se hai cancellato le voci più recenti, dovresti impostarlo per utilizzare il successivo più basso disponibile. Come nel caso in cui non ci siano già 19, l’eliminazione di 16-18 ripristinerà l’autoincremento per usare 16.


EDIT: Ho perso il bit su phpmyadmin. Puoi impostarlo anche lì. Vai alla schermata della tabella e fai clic sulla scheda operazioni. C’è un campo AUTOINCREMENT cui puoi impostare qualsiasi cosa ti serva manualmente.

Le chiavi di autoincremento primario nel database vengono utilizzate per identificare in modo univoco una determinata riga e non devono avere alcun significato aziendale . Quindi lascia la chiave primaria così com’è e aggiungi un’altra colonna chiamata per esempio courseOrder . Quindi, quando si cancella un record dal database, è ansible che si desideri inviare un’istruzione UPDATE aggiuntiva per decrementare la colonna courseOrder di tutte le righe che hanno courseOrder maggiore di quella attualmente in fase di eliminazione.

Come nota a margine non si dovrebbe mai modificare il valore di una chiave primaria in un database relazionale perché potrebbero esserci altre tabelle che la fanno riferimento come chiave esterna e modificarla potrebbe violare i vincoli referenziali.

Provare :

SET @num: = 0;

UPDATE your_table SET id = @num: = (@ num + 1);

ALTER TABLE tableName AUTO_INCREMENT = 1;

Ciò ripristinerà il valore autoincrementato e quindi conteggia ogni riga mentre viene creato un nuovo valore.

esempio: prima

  • 1: primo valore qui
  • 2: secondo valore qui
  • X: valore cancellato
  • 4: il resto del tavolo
  • 5: Il resto del resto ..

quindi la tabella mostrerà l’array: 1,2,4,5

Esempio: DOPO (se usi questo comando otterrai)

  • 1: primo valore qui
  • 2: secondo valore qui
  • 3: il resto del tavolo
  • 4: il resto del resto

Nessuna traccia del valore cancellato e il resto dell’incremento continua con questo nuovo conteggio.

MA

  1. Se da qualche parte sul tuo codice qualcosa usa il valore autoincrementato … forse questa attribuzione causerà problemi.
  2. Se non si utilizza questo valore nel proprio codice, tutto dovrebbe essere ok.

Non dovresti affidarti all’ID AUTO_INCREMENT per dirti quanti record hai nella tabella. Dovresti utilizzare SELECT COUNT(*) FROM course . Gli ID sono lì per identificare in modo univoco il corso e possono essere usati come riferimenti in altre tabelle, quindi non dovresti ripetere id e non cercare di reimpostare il campo di incremento automatico.

puoi selezionare gli id ​​in questo modo:

 set @rank = 0; select id, @rank:[email protected]+1 from tbl order by id 

il risultato è un elenco di ID e le loro posizioni nella sequenza.

puoi anche resettare gli id ​​in questo modo:

 set @rank = 0; update tbl a join (select id, @rank:[email protected]+1 as rank from tbl order by id) b on a.id = b.id set a.id = b.rank; 

potresti anche solo stampare il primo ID non utilizzato in questo modo:

 select min(id) as next_id from ((select a.id from (select 1 as id) a left join tbl b on a.id = b.id where b.id is null) union (select min(a.id) + 1 as id from tbl a left join tbl b on a.id+1 = b.id where b.id is null)) c; 

dopo ogni inserimento, è ansible ripristinare l’auto_increment:

 alter table tbl auto_increment = 16 

o impostare esplicitamente il valore id quando si esegue l’inserimento:

 insert into tbl values (16, 'something'); 

in genere questo non è necessario, hai il count(*) e la possibilità di creare un numero di classifica nei tuoi set di risultati. una classifica tipica potrebbe essere:

 set @rank = 0; select a.name, a.amount, b.rank from cust a, (select amount, @rank:[email protected]+1 as rank from cust order by amount desc) b where a.amount = b.amount 

clienti classificati in base alla quantità spesa.

Sono venuto qui cercando una risposta alla domanda Titolo "MySQL - Auto Increment after delete" ma nelle domande ho potuto trovare una risposta

  • Come eliminare determinate righe dalla tabella mysql?
  • Come resettare AUTO_INCREMENT in MySQL?

Usando qualcosa come:

 DELETE FROM table; ALTER TABLE table AUTO_INCREMENT = 1; 

Nota che la risposta di Darin Dimitrov spiega davvero bene AUTO_INCREMENT ed è l’uso. Dai un’occhiata qui prima di fare qualcosa di cui potresti pentirti.

PS: La domanda in sé è più "Why you need to recycle key values?" e la risposta di Dolph lo copre.

Ho un metodo molto semplice ma complicato.

Durante l’eliminazione di una riga, è ansible conservare gli ID in un’altra tabella temporanea. Dopodiché, quando inserirai nuovi dati nella tabella principale, potrai cercare e selezionare gli ID dalla tabella temporanea. Quindi usa un controllo qui. Se la tabella temporanea non ha ID, calcola l’ID massimo nella tabella principale e imposta il nuovo ID come: new_ID = old_max_ID+1 .

NB: non è ansible utilizzare la funzione di incremento automatico qui.

Posso pensare ad un sacco di scenari in cui potrebbe essere necessario farlo, in particolare durante un processo di migrazione o di sviluppo. Ad esempio, ho appena dovuto creare una nuova tabella incrociando due tabelle esistenti (come parte di un complesso processo di configurazione), quindi ho dovuto aggiungere una chiave primaria dopo l’evento. È ansible rilasciare la colonna chiave primaria esistente, quindi eseguire questa operazione.

 ALTER TABLE my_table ADD `ID` INT NOT NULL AUTO_INCREMENT FIRST, ADD PRIMARY KEY (`ID`); 

Per un sistema live, non è una buona idea, e specialmente se ci sono altre tabelle con chiavi esterne che puntano ad esso.

C’è in realtà un modo per risolverlo. Per prima cosa cancelli la colonna della chiave primaria autoincrementata, e poi la aggiungi di nuovo, in questo modo:

 ALTER TABLE table_name DROP column_name; ALTER TABLE table_name ADD column_name int not null auto_increment primary key first; 

Puoi usare il tuo software / script mysql per specificare da dove dovrebbe partire la chiave primaria dopo aver cancellato i record richiesti.

Quello che stai cercando di fare è molto pericoloso. Pensaci attentamente. C’è una buona ragione per il comportamento predefinito dell’aumento automatico.

Considera questo:

Un record viene eliminato in una tabella che ha una relazione con un’altra tabella. Il record corrispondente nella seconda tabella non può essere cancellato per motivi di controllo. Questo record diventa orfano dalla prima tabella. Se un nuovo record viene inserito nella prima tabella e viene utilizzata una chiave primaria sequenziale, questo record viene ora collegato a orfano. Ovviamente, questo è male. Utilizzando un PK auto incrementato, un ID mai usato prima è sempre garantito. Ciò significa che gli orfani rimangono orfani, il che è corretto.

 if($id == 1){ // deleting first row mysqli_query($db,"UPDATE employees SET id=id-1 WHERE id>1"); } else if($id>1 && $id<$num){ // deleting middle row mysqli_query($db,"UPDATE employees SET id=id-1 WHERE id>$id"); } else if($id == $num){ // deleting last row mysqli_query($db,"ALTER TABLE employees AUTO_INCREMENT = $num"); } else{ echo "ERROR"; } mysqli_query($db,"ALTER TABLE employees AUTO_INCREMENT = $num"); 

Potresti pensare di creare un trigger dopo l’eliminazione in modo da poter aggiornare il valore di autoincrement e il valore ID di tutte le righe che non assomigliano a ciò che volevi vedere.

Quindi puoi lavorare con la stessa tabella e l’incremento automatico verrà corretto automaticamente ogni volta che elimini una riga che il trigger la risolverà.

Sicuramente non è raccomandabile. Se si dispone di un database di grandi dimensioni con più tabelle, è probabile che si sia salvato un ID utente come id nella tabella 2. se si riorganizza la tabella 1, probabilmente l’ID utente desiderato non sarà considerato l’id della tabella 2 desiderato.

ecco una funzione che risolve il tuo problema

  public static void fixID(Connection conn, String table) { try { Statement myStmt = conn.createStatement(); ResultSet myRs; int i = 1, id = 1, n = 0; boolean b; String sql; myRs = myStmt.executeQuery("select max(id) from " + table); if (myRs.next()) { n = myRs.getInt(1); } while (i <= n) { b = false; myRs = null; while (!b) { myRs = myStmt.executeQuery("select id from " + table + " where id=" + id); if (!myRs.next()) { id++; } else { b = true; } } sql = "UPDATE " + table + " set id =" + i + " WHERE id=" + id; myStmt.execute(sql); i++; id++; } } catch (SQLException e) { e.printStackTrace(); } }