Есть ли запрос (команда) для обрезания всех таблиц в базе данных за одну операцию? Я хочу знать, могу ли я сделать это с помощью одного запроса.
mysql -Nse 'show tables' DATABASE_NAME | while read table; do mysql -e "drop table $table" DATABASE_NAME; done
mysql -Nse 'show tables' DATABASE_NAME | while read table; do mysql -e "truncate table $table" DATABASE_NAME; done
обрезать несколько таблиц базы данных на экземпляре Mysql
SELECT Concat('TRUNCATE TABLE ',table_schema,'.',TABLE_NAME, ';')
FROM INFORMATION_SCHEMA.TABLES where table_schema in ('db1_name','db2_name');
Использовать результат запроса для обрезания таблиц
Примечание: возможно, вы получите эту ошибку:
ERROR 1217 (23000): Cannot delete or update a parent row: a foreign key constraint fails
Это происходит, если есть таблицы с внешними ключами, ссылающиеся на таблицу, которую вы пытаетесь удалить/усечь.
Перед усечением таблиц Все, что вам нужно сделать, это:
SET FOREIGN_KEY_CHECKS=0;
Обрезайте свои таблицы и измените их на
SET FOREIGN_KEY_CHECKS=1;
Используйте phpMyAdmin следующим образом:
Просмотр базы данных = > Проверить все (таблицы) = > Пустой
Если вы хотите игнорировать проверки внешнего ключа, вы можете снять флажок, в котором говорится:
[] Включить проверку внешнего ключа
Чтобы получить этот флажок, вам нужно будет запускать atleast версии 4.5.0 или новее.
Это не MySQL CLI-fu, но эй, он работает!
MS SQL Server 2005+ (удалить PRINT для фактического выполнения...)
EXEC sp_MSforeachtable 'PRINT ''TRUNCATE TABLE ?'''
Если ваша платформа базы данных поддерживает представления INFORMATION_SCHEMA, возьмите результаты следующего запроса и выполните их.
SELECT 'TRUNCATE TABLE ' + TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
Попробуйте это для MySQL:
SELECT Concat('TRUNCATE TABLE ', TABLE_NAME) FROM INFORMATION_SCHEMA.TABLES
Добавление точки с запятой в Concat упрощает использование, например. изнутри workbench mysql.
SELECT Concat('TRUNCATE TABLE ', TABLE_NAME, ';') FROM INFORMATION_SCHEMA.TABLES
Я нашел это, чтобы удалить все таблицы в базе данных:
mysqldump -uUSERNAME -pPASSWORD --add-drop-table --no-data DATABASENAME | grep ^DROP | mysql -uUSERNAME -pPASSWORD DATABASENAME
Полезно, если вы ограничены решением хостинга (не можете удалить всю базу данных).
Я изменил его, чтобы обрезать таблицы. Для mysqldump нет "-add-truncate-table", поэтому я сделал:
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
работает для меня --edit, исправление опечатки в последней команде
Это приведет к печати команды для обрезания всех таблиц:
SELECT GROUP_CONCAT(Concat('TRUNCATE TABLE ',table_schema,'.',TABLE_NAME) SEPARATOR ';') FROM INFORMATION_SCHEMA.TABLES where table_schema in ('my_db');
Мне было проще всего сделать что-то вроде кода ниже, просто замените имена таблиц на свой собственный. важно, чтобы последняя строка всегда была SET 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;
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;
Как только выполняется усечение данных, создайте те же ограничения внешнего ключа в той же таблице. См. Ниже script, который будет генерировать script для выполнения вышеуказанных операций.
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';
Теперь запустите созданный
Db Truncate.sql scriptПреимущества. 1) Восстановить дисковое пространство 2) Не нужно откатывать и воссоздавать БД/Схему с той же структурой
Недостатки. 1) Ограничения FK должны быть именами в таблице с именем, содержащим "FK" в имени ограничения.
Используйте это и сформируйте запрос
SELECT Concat('TRUNCATE TABLE ',table_schema,'.',TABLE_NAME, ';')
FROM INFORMATION_SCHEMA.TABLES where table_schema in (db1,db2)
INTO OUTFILE '/path/to/file.sql';
Теперь используйте это, чтобы использовать этот запрос
mysql -u username -p </path/to/file.sql
если вы получите сообщение об ошибке
ERROR 1701 (42000) at line 3: Cannot truncate a table referenced in a foreign key constraint
Самый простой способ перейти наверху вашего файла добавить эту строку
SET FOREIGN_KEY_CHECKS=0;
в котором говорится, что мы не хотим проверять ограничения внешнего ключа при просмотре этого файла.
Он обрезает все таблицы в базах данных db1 и bd2.
если используется SQL Server 2005, существует скрытая хранимая процедура, которая позволяет выполнять команду или набор команд для всех таблиц внутри базы данных. Вот как вы могли бы вызвать TRUNCATE TABLE
с помощью этой хранимой процедуры:
EXEC [sp_MSforeachtable] @command1="TRUNCATE TABLE ?"
Здесь - хорошая статья, которая развивается дальше.
Однако для MySql вы можете использовать mysqldump и указать опции --add-drop-tables
и --no-data
для удаления и создания всех таблиц, игнорирующих данные. например:
mysqldump -u[USERNAME] -p[PASSWORD] --add-drop-table --no-data [DATABASE]
здесь, я знаю здесь
SELECT Concat('TRUNCATE TABLE ',table_schema,'.',TABLE_NAME, ';')
FROM INFORMATION_SCHEMA.TABLES where table_schema in ('databasename1','databasename2');
Если не удается удалить или обновить родительскую строку: ограничение внешнего ключа завершено
Это происходит, если есть таблицы с внешними ключами, ссылающиеся на таблицу, которую вы пытаетесь удалить/усечь.
Перед усечением таблиц Все, что вам нужно сделать, это:
SET FOREIGN_KEY_CHECKS=0;
Обрезайте свои таблицы и измените их на
SET FOREIGN_KEY_CHECKS=1;
пользователь этот 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']);
}
?>
проверьте подробности здесь ссылка
Нет. Нет единой команды для обрезания всех таблиц mysql сразу. Вам нужно будет создать небольшой script, чтобы обрезать таблицы один за другим.
ref: http://dev.mysql.com/doc/refman/5.0/en/truncate-table.html
Я обнаружил, что TRUNCATE TABLE.. имеет проблемы с ограничениями внешнего ключа, даже после NOCHECK CONSTRAINT ALL, поэтому вместо этого я использую оператор DELETE FROM. Это означает, что семена идентичности не reset, вы всегда можете добавить CHECKIDENT DBCC для достижения этого.
Я использую приведенный ниже код, чтобы распечатать в окне сообщения sql для обрезания всех таблиц в базе данных, прежде чем запускать его. Это просто усложняет ошибку.
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'''
Вот мой вариант, чтобы иметь "одно утверждение для усечения" всех.
Во-первых, я использую отдельную базу данных с именем "util" для моих вспомогательных хранимых процедур. Код моей хранимой процедуры для обрезания всех таблиц:
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 ;
Как только у вас есть эта хранимая процедура в вашей базе данных util, вы можете называть ее как
call util.trunctables('nameofdatabase');
который теперь является точно одним утверждением: -)
mysqldump -u root -p --no-data dbname > schema.sql
mysqldump -u root -p drop dbname
mysqldump -u root -p < schema.sql
Следующий запрос MySQL сам выдаст один запрос, который усечет все таблицы в данной базе данных. Он обходит клавиши FOREIGN:
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
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
-
Дэвид
Спасибо, что нашли время для форматирования кода, но так оно и должно быть применено.
-Kurt
В окне UNIX или Linux:
Убедитесь, что вы находитесь в оболочке bash. Эти команды должны запускаться из командной строки следующим образом.
Примечание:
Я храню свои учетные данные в файле ~/.my.cnf, поэтому мне не нужно предоставлять их в командной строке.
Примечание:
cpm - это имя базы данных
Я показываю только небольшую выборку результатов из каждой команды.
Найдите ограничения внешнего ключа:
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"
Список таблиц и строк:
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
Усечь свои таблицы:
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
Убедитесь, что он сработал:
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
В окне Windows:
Примечание:
cpm - это имя базы данных
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
Я знаю, что это не совсем одна команда, но желаемый результат может быть достигнут изнутри phpMyAdmin, выполнив следующие шаги:
Идея состоит в том, чтобы быстро получить все таблицы из базы данных (что вы делаете за 5 секунд и 2 клика), но сначала отключите проверку внешнего ключа. Нет CLI и не отбрасывать базу данных и добавлять ее снова.
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
- изменить ----
earlier in solution 1, i had mentioned concat() instead of group_concat() which would have not returned the desired result
Вот процедура, которая должна обрезать все таблицы в локальной базе данных.
Сообщите мне, если он не работает, и я удалю этот ответ.
Неподтвержденный
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;
Я не уверен, но я думаю, что есть одна команда, в которой вы можете скопировать схему базы данных в новую базу данных, как только вы это сделаете, вы можете удалить старую базу данных, после чего вы можете снова скопировать схему базы данных в старое имя.
Идея может состоять в том, чтобы просто отбрасывать и воссоздавать таблицы?
EDIT:
@Джонатан Леффлер: Правда
Другое предложение (или случай, когда вам не нужно обрезать ВСЕ таблицы):
Почему бы просто не создать базовую хранимую процедуру для обрезания определенных таблиц
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();
?>
Вот фрагмент кода, который я использую для очистки таблицы. Просто измените $conn info и TABLE_NAME.