Czy istnieje zapytanie (polecenie), aby obciąć wszystkie tabele w bazie danych w ramach jednej operacji? Chcę wiedzieć, czy mogę to zrobić za pomocą jednego zapytania.

Upuść (tj. Usuń tabele)

mysql -Nse 'show tables' DATABASE_NAME | while read table; do mysql -e "drop table $table" DATABASE_NAME; done

Obetnij (tj. Puste tabele)

mysql -Nse 'show tables' DATABASE_NAME | while read table; do mysql -e "truncate table $table" DATABASE_NAME; done

obetnij wiele tabel bazy danych w instancji Mysql

SELECT Concat('TRUNCATE TABLE ',table_schema,'.',TABLE_NAME, ';') 
FROM INFORMATION_SCHEMA.TABLES where  table_schema in ('db1_name','db2_name');

Użyj wyniku zapytania, aby obciąć tabele

Uwaga: być może pojawi się ten błąd:

ERROR 1217 (23000): Cannot delete or update a parent row: a foreign key constraint fails

Dzieje się tak, jeśli istnieją tabele z odniesieniami do kluczy obcych do tabeli, którą próbujesz upuścić / obciąć.

Przed obcinaniem tabel Wszystko, co musisz zrobić, to:

SET FOREIGN_KEY_CHECKS=0;

Obetnij tabele i zmień je z powrotem na

SET FOREIGN_KEY_CHECKS=1;

Użyj phpMyAdmin w następujący sposób:

Widok bazy danych => Zaznacz wszystko (tabele) => Puste

Jeśli chcesz zignorować sprawdzanie klucza obcego, możesz odznaczyć pole z napisem:

[] Włącz sprawdzanie klucza obcego

Aby uzyskać to pole wyboru, musisz uruchomić przynajmniej wersję 4.5.0 lub nowszą.

To nie jest MySQL CLI-fu, ale hej, to działa!

MS SQL Server 2005+ (Usuń PRINT do faktycznego wykonania ...)

EXEC sp_MSforeachtable 'PRINT ''TRUNCATE TABLE ?'''

Jeśli platforma bazy danych obsługuje widoki INFORMACJE_SCHEMA, weź wyniki następującego zapytania i wykonaj je.

SELECT 'TRUNCATE TABLE ' + TABLE_NAME FROM INFORMATION_SCHEMA.TABLES

Wypróbuj to dla MySQL:

SELECT Concat('TRUNCATE TABLE ', TABLE_NAME) FROM INFORMATION_SCHEMA.TABLES

Dodanie średnika do Concat ułatwia korzystanie np. Z poziomu środowiska roboczego mysql.

SELECT Concat('TRUNCATE TABLE ', TABLE_NAME, ';') FROM INFORMATION_SCHEMA.TABLES

Znalazłem to, aby usunąć wszystkie tabele z bazy danych:

mysqldump -uUSERNAME -pPASSWORD --add-drop-table --no-data DATABASENAME | grep ^DROP | mysql -uUSERNAME -pPASSWORD DATABASENAME

Przydatne, jeśli masz ograniczone rozwiązanie hostingowe (nie możesz upuścić całej bazy danych).

Zmodyfikowałem go, aby obciąć tabele. Dla mysqldump nie ma „--add-truncate-table”, więc zrobiłem:

mysqldump -uUSERNAME -pPASSWORD --add-drop-table --no-data DATABASENAME | grep ^DROP | sed -e 's/DROP TABLE IF EXISTS/TRUNCATE TABLE/g' | mysql -uUSERNAME -pPASSWORD DATABASENAME

działa dla mnie - edytuj, poprawiając literówkę w ostatnim poleceniu

SET FOREIGN_KEY_CHECKS = 0;

SELECT @str := CONCAT('TRUNCATE TABLE ', table_schema, '.', table_name, ';')
FROM   information_schema.tables
WHERE  table_type   = 'BASE TABLE'
  AND  table_schema IN ('db1_name','db2_name');

PREPARE stmt FROM @str;

EXECUTE stmt;

DEALLOCATE PREPARE stmt;

SET FOREIGN_KEY_CHECKS = 1;

Uznałem, że najłatwiej jest po prostu zrobić coś takiego jak poniższy kod, po prostu zastąpić nazwy tabel własnymi. ważne, aby upewnić się, że ostatni wiersz jest zawsze ustawiony FOREIGN_KEY_CHECKS = 1;

SET FOREIGN_KEY_CHECKS=0;
TRUNCATE `table1`;
TRUNCATE `table2`;
TRUNCATE `table3`;
TRUNCATE `table4`;
TRUNCATE `table5`;
TRUNCATE `table6`;
TRUNCATE `table7`;
SET FOREIGN_KEY_CHECKS=1;

Spowoduje to wydrukowanie polecenia obcięcia wszystkich tabel:

SELECT GROUP_CONCAT(Concat('TRUNCATE TABLE ',table_schema,'.',TABLE_NAME) SEPARATOR ';') FROM INFORMATION_SCHEMA.TABLES where table_schema in ('my_db');

1. Aby obciąć tabelę, należy usunąć ograniczenia klucza obcego odwzorowane na kolumny w tej tabeli z innych tabel (w rzeczywistości na wszystkie tabele w określonej bazie danych / schemacie).
2. Tak więc wszystkie ograniczenia klucza obcego muszą zostać początkowo usunięte, a następnie obcięte tabeli.
3. Opcjonalnie użyj tabeli optymalizacji (w mysql, innodb engine esp), aby odzyskać zużyte miejsce / rozmiar danych w systemie operacyjnym po obcięciu danych.

4. Po obcięciu danych ponownie utwórz te same ograniczenia klucza obcego w tej samej tabeli. Zobacz poniżej skrypt, który wygeneruje skrypt do wykonania powyższych operacji.

   SELECT CONCAT('ALTER TABLE ',TABLE_SCHEMA,'.',TABLE_NAME,' DROP FOREIGN KEY ',CONSTRAINT_NAME,';') FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
   WHERE CONSTRAINT_TYPE='FOREIGN KEY' AND TABLE_SCHEMA='<TABLE SCHEMA>'
   UNION
   SELECT CONCAT('TRUNCATE TABLE ',TABLE_SCHEMA,'.',TABLE_NAME,';') FROM INFORMATION_SCHEMA.TABLES
   WHERE TABLE_SCHEMA='<TABLE SCHEMA>' AND TABLE_TYPE='BASE TABLE'
   UNION
   SELECT CONCAT('OPTIMIZE TABLE ',TABLE_SCHEMA,'.',TABLE_NAME,';') FROM INFORMATION_SCHEMA.TABLES
   WHERE TABLE_SCHEMA='<TABLE SCHEMA>' AND TABLE_TYPE='BASE TABLE'
   UNION
   SELECT CONCAT('ALTER TABLE ',TABLE_SCHEMA,'.',TABLE_NAME,' ADD CONSTRAINT ',CONSTRAINT_NAME,' FOREIGN KEY(',COLUMN_NAME,')',' REFERENCES ',REFERENCED_TABLE_NAME,'(',REFERENCED_COLUMN_NAME,');') FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
   WHERE CONSTRAINT_NAME LIKE 'FK%' AND TABLE_SCHEMA='<TABLE SCHEMA>'
   INTO OUTFILE "C:/DB Truncate.sql" LINES TERMINATED BY '\n';

Teraz uruchom wygenerowany skrypt Db Truncate.sql

Korzyści. 1) Odzyskaj miejsce na dysku 2) Nie trzeba upuszczać i odtwarzać schematu DB / Schema o tej samej strukturze

Wady 1) Ograniczenia FK powinny być nazwami w tabeli o nazwie zawierającej „FK” w nazwie ograniczenia.

jeśli używasz SQL Server 2005, istnieje ukryta procedura składowana, która pozwala wykonać polecenie lub zestaw poleceń dla wszystkich tabel w bazie danych. Oto jak wywołałbyś za TRUNCATE TABLEpomocą tej procedury składowanej:

EXEC [sp_MSforeachtable] @command1="TRUNCATE TABLE ?"

Oto dobry artykuł, który rozwija dalej.

W przypadku MySql można jednak użyć mysqldump i określić opcje --add-drop-tablesi, --no-dataaby upuścić i utworzyć wszystkie tabele ignorujące dane. lubię to:

mysqldump -u[USERNAME] -p[PASSWORD] --add-drop-table --no-data [DATABASE]

Podręcznik użytkowania mysqldump z dev.mysql

Użyj tego i utwórz zapytanie

SELECT Concat('TRUNCATE TABLE ',table_schema,'.',TABLE_NAME, ';') 
FROM INFORMATION_SCHEMA.TABLES where  table_schema in (db1,db2)
INTO OUTFILE '/path/to/file.sql';

Teraz użyj tego, aby użyć tego zapytania

mysql -u username -p </path/to/file.sql

jeśli pojawi się taki błąd

ERROR 1701 (42000) at line 3: Cannot truncate a table referenced in a foreign key constraint

najłatwiejszym sposobem jest przejście do górnej części pliku. Dodaj tę linię

SET FOREIGN_KEY_CHECKS=0;

co mówi, że nie chcemy sprawdzać ograniczeń klucza obcego podczas przeglądania tego pliku.

Obetnie wszystkie tabele w bazach danych db1 i bd2.

Nie. Nie ma jednego polecenia, aby obciąć wszystkie tabele mysql jednocześnie. Będziesz musiał utworzyć mały skrypt, aby obcinać tabele jeden po drugim.

ref: http://dev.mysql.com/doc/refman/5.0/en/truncate-table.html

Oto mój wariant, aby mieć „jedno zdanie, aby obciąć je wszystkie”.

Po pierwsze, używam osobnej bazy danych o nazwie „util” dla procedur przechowywanych mojego pomocnika. Kod mojej procedury składowanej do obcinania wszystkich tabel to:

DROP PROCEDURE IF EXISTS trunctables;
DELIMITER ;;
CREATE  PROCEDURE trunctables(theDb varchar(64))
BEGIN
    declare tname varchar(64);
    declare tcursor CURSOR FOR 
    SELECT table_name FROM information_schema.tables WHERE table_type <> 'VIEW' AND table_schema = theDb;
    SET FOREIGN_KEY_CHECKS = 0; 
    OPEN tcursor;
    l1: LOOP
        FETCH tcursor INTO tname;
        if tname = NULL then leave l1; end if;
        set @sql = CONCAT('truncate `', theDB, '`.`', tname, '`');
        PREPARE stmt from @sql;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
    END LOOP l1;
    CLOSE tcursor;
    SET FOREIGN_KEY_CHECKS = 1; 
END ;;
DELIMITER ;

Po zapisaniu tej procedury przechowywanej w bazie danych util można ją wywołać w ten sposób

call util.trunctables('nameofdatabase');

który jest teraz dokładnie jednym stwierdzeniem :-)

tutaj, bo wiem tutaj

   SELECT Concat('TRUNCATE TABLE ',table_schema,'.',TABLE_NAME, ';') 
    FROM INFORMATION_SCHEMA.TABLES where  table_schema in ('databasename1','databasename2');

Jeśli nie można usunąć lub zaktualizować wiersza nadrzędnego: ograniczenie klucza obcego kończy się niepowodzeniem

Dzieje się tak, jeśli istnieją tabele z odniesieniami do kluczy obcych do tabeli, którą próbujesz upuścić / obciąć.

Przed obcinaniem tabel Wszystko, co musisz zrobić, to:

SET FOREIGN_KEY_CHECKS=0;

Obetnij tabele i zmień je z powrotem na

SET FOREIGN_KEY_CHECKS=1; 

użytkownik ten kod php

    $truncate = mysql_query("SELECT Concat('TRUNCATE TABLE ',table_schema,'.',TABLE_NAME, ';') as tables_query FROM INFORMATION_SCHEMA.TABLES where table_schema in ('databasename')");

    while($truncateRow=mysql_fetch_assoc($truncate)){

        mysql_query($truncateRow['tables_query']);

    }
?>

sprawdź szczegóły tutaj link

Oto procedura, która powinna obciąć wszystkie tabele w lokalnej bazie danych.

Daj mi znać, jeśli to nie zadziała, a ja usunę tę odpowiedź.

Niesprawdzone

CREATE PROCEDURE truncate_all_tables()
BEGIN

   -- Declare local variables
   DECLARE done BOOLEAN DEFAULT 0;
   DECLARE cmd VARCHAR(2000);

   -- Declare the cursor
   DECLARE cmds CURSOR
   FOR
   SELECT CONCAT('TRUNCATE TABLE ', TABLE_NAME) FROM INFORMATION_SCHEMA.TABLES;

   -- Declare continue handler
   DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1;

   -- Open the cursor
   OPEN cmds;

   -- Loop through all rows
   REPEAT

      -- Get order number
      FETCH cmds INTO cmd;

      -- Execute the command
      PREPARE stmt FROM cmd;
      EXECUTE stmt;
      DROP PREPARE stmt;

   -- End of loop
   UNTIL done END REPEAT;

   -- Close the cursor
   CLOSE cmds;

END;

Uważam, że TRUNCATE TABLE .. ma problemy z ograniczeniami klucza obcego, nawet po NOCHECK CONSTRAINT ALL, więc zamiast tego używam instrukcji DELETE FROM. Oznacza to, że ziarna tożsamości nie są resetowane, zawsze można dodać DBCC CHECKIDENT, aby to osiągnąć.

I Użyj poniższego kodu, aby wydrukować sql do okna komunikatu, aby obciąć wszystkie tabele w bazie danych przed uruchomieniem. To tylko utrudnia popełnienie błędu.

EXEC sp_MSforeachtable 'PRINT ''ALTER TABLE ? NOCHECK CONSTRAINT ALL'''
EXEC sp_MSforeachtable 'print ''DELETE FROM ?'''
EXEC sp_MSforeachtable 'print ''ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all'''

Nie jestem pewien, ale myślę, że istnieje jedna komenda, za pomocą której można skopiować schemat bazy danych do nowej bazy danych. Po wykonaniu tej czynności można usunąć starą bazę danych, a następnie ponownie skopiować schemat bazy danych do starej nazwy.

Wiem, że to nie jest dokładnie jedna komenda, ale pożądany wynik można uzyskać z poziomu phpMyAdmin, wykonując następujące kroki:

1. Wybierz (wszystkie) tabele do usunięcia (Zaznacz wszystko)
2. Wybierz „Drop” / „Truncate” z listy „With selected:”
3. Na stronie potwierdzenia („Czy naprawdę chcesz:”) skopiować zapytanie (wszystko z czerwonym tłem)
4. Idź na górę i kliknij SQL i napisz: „SET FOREIGN_KEY_CHECKS = 0;” następnie wklej wcześniej skopiowane zapytanie
5. Kliknij „Idź”

Chodzi o to, aby szybko pobrać wszystkie tabele z bazy danych (co robisz w 5 sekund i 2 kliknięcia), ale najpierw wyłącz sprawdzanie klucza obcego. Bez interfejsu CLI i bez usuwania bazy danych i dodawania jej ponownie.

TB=$( mysql -Bse "show tables from DATABASE" );
for i in ${TB};
    do echo "Truncating table ${i}";
    mysql -e "set foreign_key_checks=0; set unique_checks=0;truncate table DATABASE.${i}; set foreign_key_checks=1; set unique_checks=1";
    sleep 1;
done

-

David

Dziękujemy za poświęcenie czasu na sformatowanie kodu, ale w ten sposób należy go zastosować.

-Kurt

W systemie UNIX lub Linux:

Upewnij się, że jesteś w powłoce bash. Te polecenia należy uruchamiać z wiersza poleceń w następujący sposób.

Uwaga:

Przechowuję moje poświadczenia w pliku ~ / .my.cnf, więc nie muszę ich podawać w wierszu polecenia.

Uwaga:

cpm to nazwa bazy danych

Pokazuję tylko małą próbkę wyników z każdego polecenia.

Znajdź ograniczenia dotyczące klucza obcego:

klarsen@Chaos:~$ mysql -Bse "select concat(table_name, ' depends on ', referenced_table_name)
             from information_schema.referential_constraints
             where constraint_schema = 'cpm'
             order by referenced_table_name"

1. system_akceptacji_zewnętrznej zależy od żądania_akceptacji
2. adres zależy od klienta
3. identyfikator klienta zależy od klienta
4. ID_zewnętrzny zależy od klienta
5. dane uwierzytelniające zależą od klienta
6. adres e-mail zależy od klienta
7. prośba o zatwierdzenie zależy od klienta
8. stan_statusu zależy od klienta
9. Customer_image zależy od klienta

Lista tabel i liczby wierszy:

klarsen@Chaos:~$ mysql -Bse "SELECT table_name, table_rows FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'cpm'" | cat -n

 1  address 297
 2  approval_external_system    0
 3  approval_request    0
 4  country 189
 5  credential  468
 6  customer    6776
 7  customer_identification 5631
 8  customer_image  2
 9  customer_status 13639

Obetnij swoje stoły:

klarsen@Chaos:~$ TB=$( mysql -Bse "show tables from cpm" ); for i in ${TB}; do echo "Truncating table ${i}"; mysql -e "set foreign_key_checks=0; set unique_checks=0;truncate table cpm.${i}; set foreign_key_checks=1; set unique_checks=1"; sleep 1; done

1. Obcinanie adresu tabeli
2. Tabela obcinania system_zatwierdzenia_zewnętrznego
3. Tabela obcinaniaEND_request
4. Kraj obcinania tabeli
5. Poświadczenie tabeli obcinania
6. Klient stołu obcinanego
7. Tabela obcinania identyfikacja klienta
8. Tabela obcinania obraz_klienta
9. Tabela przycinania status_statusu

Sprawdź, czy zadziałało:

klarsen@Chaos:~$ mysql -Bse "SELECT table_name, table_rows FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'cpm'" | cat -n

 1  address 0
 2  approval_external_system    0
 3  approval_request    0
 4  country 0
 5  credential  0
 6  customer    0
 7  customer_identification 0
 8  customer_image  0
 9  customer_status 0
10  email_address   0

W oknie systemu Windows:

UWAGA:

cpm to nazwa bazy danych

C:\>for /F "tokens=*" %a IN ('mysql -Bse "show tables" cpm') do mysql -e "set foreign_key_checks=0; set unique_checks=0; truncate table %a; foreign_key_checks=1; set unique_checks=1" cpm

Poniższe zapytanie MySQL samo wygeneruje jedno zapytanie, które obetnie wszystkie tabele w danej bazie danych. Pomija klucze ZAGRANICZNE:

SELECT CONCAT(
         'SET FOREIGN_KEY_CHECKS=0; ',
         GROUP_CONCAT(dropTableSql SEPARATOR '; '), '; ',
         'SET FOREIGN_KEY_CHECKS=1;'
       ) as dropAllTablesSql
FROM   ( SELECT  Concat('TRUNCATE TABLE ', table_schema, '.', TABLE_NAME) AS dropTableSql
         FROM    INFORMATION_SCHEMA.TABLES
         WHERE   table_schema = 'DATABASE_NAME' ) as queries

mysqldump -u root -p --no-data dbname > schema.sql
mysqldump -u root -p drop dbname
mysqldump -u root -p < schema.sql

Soln 1)

mysql> select group_concat('truncate',' ',table_name,';') from information_schema.tables where table_schema="db_name" into outfile '/tmp/a.txt';
mysql> /tmp/a.txt;

Soln 2)

- Export only structure of a db
- drop the database
- import the .sql of structure 

-- edytować ----

earlier in solution 1, i had mentioned concat() instead of group_concat() which would have not returned the desired result

To zadziałało dla mnie. Zmień odpowiednio bazę danych, nazwę użytkownika i hasło.

mysql -Nse 'show tables' -D DATABASE -uUSER -pPWD | while read table; do echo "SET FOREIGN_KEY_CHECKS = 0;drop table \`$table\`;SET FOREIGN_KEY_CHECKS = 1;"; done | mysql DATABASE -uUSER -pPWD

Pomysłem może być po prostu upuszczenie i odtworzenie tabel?

EDYTOWAĆ:

@Jathanathan Leffler: Prawda

Inne sugestie (lub przypadek, w którym nie musisz obcinać WSZYSTKICH tabel):

Dlaczego nie stworzyć podstawowej procedury składowanej, aby obciąć określone tabele

CREATE PROCEDURE [dbo].[proc_TruncateTables]
AS
TRUNCATE TABLE Table1
TRUNCATE TABLE Table2
TRUNCATE TABLE Table3
GO

<?php
// connect to database
$conn=mysqli_connect("localhost","user","password","database");

// check connection
if (mysqli_connect_errno()) {
  exit('Connect failed: '. mysqli_connect_error());
}

// sql query
$sql =mysqli_query($conn,"TRUNCATE " . TABLE_NAME);


// Print message
if ($sql === TRUE) {
  echo 'data delete successfully';
}
else {
 echo 'Error: '. $conn->error;
}

$conn->close();

?>

Oto fragment kodu, którego używam do czyszczenia tabeli. Po prostu zmień informacje o $ conn i TABLE_NAME.