wake-up-neo.net

Aktualisierung des AUTO_INCREMENT-Werts aller Tabellen in einer MySQL-Datenbank

Es ist möglich, den AUTO_INCREMENT-Wert einer MySQL-Tabelle über einzustellen/zurückzusetzen

ALTER TABLE some_table AUTO_INCREMENT = 1000

Ich muss jedoch den AUTO_INCREMENTauf seinen vorhandenen Wert setzen (um die M-M-Replikation zu korrigieren), etwa

ALTER TABLE some_table SET AUTO_INCREMENT = AUTO_INCREMENT + 1 was nicht funktioniert

Tatsächlich möchte ich diese Abfrage für alle Tabellen in einer Datenbank ausführen. Aber das ist eigentlich nicht sehr wichtig.

Ich konnte keine Möglichkeit finden, mit diesem Problem umzugehen, außer die Abfragen manuell auszuführen. Würdest du bitte etwas vorschlagen oder mich auf einige Ideen hinweisen?.

Vielen Dank

28
pars

Mit:

ALTER TABLE some_table AUTO_INCREMENT = 0

... setzt den auto_increment-Wert auf den nächsten Wert basierend auf dem höchsten vorhandenen Wert in der auto_increment-Spalte zurück.

Um dies für alle Tabellen auszuführen, müssen Sie MySQLs dynamische SQL-Syntax namens PreparedStatements verwenden, da Sie den Tabellennamen einer ALTER TABLE-Anweisung nicht als Variable angeben können. Sie müssen die Ausgabe von:

SELECT t.table_name
  FROM INFORMATION_SCHEMA.TABLES t
 WHERE t.table_schema = 'your_database_name'

... die obige ALTER TABLE-Anweisung für jede Tabelle ausgeführt wird.

42
OMG Ponies
set @db = 'your_db_name';

SELECT concat('ALTER TABLE ', @db, '.', TABLE_NAME, ' AUTO_INCREMENT = 0;') 
FROM information_schema.TABLES WHERE TABLE_SCHEMA = @db

Dann kopieren und einfügen und führen Sie die Ausgabe aus, die Sie erhalten.

13
the_nuts

In den folgenden Anweisungen müssen Sie alles, was in [Klammern] steht, durch Ihren korrekten Wert ersetzen. BACKUP VOR DEM VERSUCH.

Wenn Sie sich als root über die Befehlszeile bei mysql anmelden können, können Sie das auto_increment für alle Tabellen folgendermaßen zurücksetzen. Zuerst erstellen wir unsere Abfragen, die wir ausführen möchten:

Erstellen Sie eine Datenbanksicherung:

mysqldump -u [uname] -p [dbname] | gzip -9 > [backupfile.sql.gz]

Anmeldung:

mysql -u root -p

Setzen Sie den Wert für group_concat_max_length auf einen höheren Wert, damit die Liste der Abfragen nicht abgeschnitten wird:

SET group_concat_max_len=100000;

Erstellen Sie unsere Liste mit Abfragen, indem Sie Folgendes verwenden:

SELECT GROUP_CONCAT(CONCAT("ALTER TABLE ", table_name, " AUTO_INCREMENT = 0") SEPARATOR ";") FROM information_schema.tables WHERE table_schema = "[DATABASENAME]";

Dann erhalten Sie eine lange Folge von MySQL-Abfragen, gefolgt von einer Reihe von Strichen. Kopieren Sie die Zeichenfolge der Abfragen in Ihre Zwischenablage. Sie sieht in etwa so aus:

ALTER table1 AUTO_INCREMENT = 0;ALTER table2 AUTO_INCREMENT = 0;...continued...

Wechseln Sie zu der Datenbank, in der Sie den Befehl ausführen möchten:

USE [DATABASENAME];

Fügen Sie dann die Zeichenfolge ein, die sich in Ihrer Zwischenablage befindet, und drücken Sie die Eingabetaste, um sie auszuführen. Dies sollte die Änderung für jede Tabelle in Ihrer Datenbank ausführen.

Vermasselt? Stellen Sie sicher, dass Sie sich von Ihrem Backup abmelden, bevor Sie Folgendes ausführen (geben Sie einfach exit; ein).

gzip -d < [backupfile.sql.gz] | mysql -u [uname] -p [dbname]

Ich übernehme keine Verantwortung für Schäden, die durch die Verwendung eines dieser Befehle verursacht werden. Die Verwendung erfolgt auf eigenes Risiko.

6
nicholas.alipaz

Angenommen, Sie müssen dies korrigieren, indem Sie die Spalte für die automatische Erhöhung ändern und nicht die Fremdschlüssel in der Tabelle, in der die N: M-Beziehung zerlegt wird, und dass Sie die richtigen Werte angeben können nicht automatisch inkrementieren, ordnen Sie dies dann der ursprünglichen Tabelle zu und ändern Sie den Spaltentyp anschließend in automatisch inkrementieren, oder schneiden Sie die ursprüngliche Tabelle ab und laden Sie die Daten aus der temporären Tabelle. 

1
symcbean

Ich habe dieses Gist auf Github gefunden und es hat für mich wie ein Zauber funktioniert: https://Gist.github.com/abhinavlal/4571478

Der Befehl:

mysql -Nsr -e "SELECT t.table_name FROM INFORMATION_SCHEMA.TABLES t WHERE t.table_schema = 'DB_NAME'" | xargs -I {} mysql DB_NAME -e "ALTER TABLE {} AUTO_INCREMENT = 1;"

Wenn Ihre Datenbank ein Kennwort erfordert, müssen Sie das leider in den Befehl eingeben, damit sie funktioniert. Eine Problemumgehung (immer noch nicht great, aber funktioniert) besteht darin, das Kennwort in einer sicheren Datei zu speichern. Sie können die Datei jederzeit löschen, damit das Kennwort nicht im Befehlsverlauf bleibt:

 ... | xargs -I {} mysql -u root -p`cat /path/to/pw.txt` DB_NAME -e...
1
Yes Barry

Ich habe unten geschrieben, den Datenbanknamen ändern und die Prozedur ausführen

CREATE DEFINER=`root`@`localhost` PROCEDURE `setAutoIncrement`()
BEGIN
DECLARE done int default false;
    DECLARE table_name CHAR(255);
DECLARE cur1 cursor for SELECT t.table_name FROM INFORMATION_SCHEMA.TABLES t 
        WHERE t.table_schema = "buzzer_verifone";

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    open cur1;

    myloop: loop
        fetch cur1 into table_name;
        if done then
            leave myloop;
        end if;
        set @sql = CONCAT('ALTER TABLE ',table_name, ' AUTO_INCREMENT = 1');
        prepare stmt from @sql;
        execute stmt;
        drop prepare stmt;
    end loop;

    close cur1;
END

Führen Sie die oben beschriebene Prozedur mit der folgenden Zeile aus

Call setAutoIncrement();
0
Lovesh Pareek