How to remove all MySQL tables from the command-line without DROP database permissions? [duplicate]

Описание

SQL оператор DELETE используется для удаления одной или нескольких записей из таблицы.

В этом учебном материале вы узнаете, как использовать оператор DELETE, с синтаксисом и примерами.

В этом учебном пособии вы узнаете, как использовать MySQL оператор DELETE с синтаксисом и примерами.

Описание

MySQL оператор DELETE используется для удаления одной записи или нескольких записей из таблицы в MySQL.

The reason why you can’t outright drop all tables in a single line of code is that in a sizeable and well-designed database, there will likely be a lot of foreign key constraints.

I know I’m resurrecting quite an old question, but I recently ran into this issue, but needed something that scales to large numbers well. There wasn’t any existing performance data, and since this question has had quite a bit of attention, I thought I’d post what I found.

The solutions that actually worked were the Alex Barrett’s double sub-query/NOT IN method (similar to Bill Karwin’s), and Quassnoi’s LEFT JOIN method.

Unfortunately both of the above methods create very large intermediate temporary tables and performance degrades quickly as the number of records not being deleted gets large.

What I settled on utilizes Alex Barrett’s double sub-query (thanks!) but uses <= instead of NOT IN:

DELETE FROM `test_sandbox`
  WHERE id <= (
    SELECT id
    FROM (
      SELECT id
      FROM `test_sandbox`
      ORDER BY id DESC
      LIMIT 1 OFFSET 42 -- keep this many records
    ) foo
  );

It uses OFFSET to get the id of the Nth record and deletes that record and all previous records.

Since ordering is already an assumption of this problem (ORDER BY id DESC), <= is a perfect fit.

It is much faster, since the temporary table generated by the subquery contains just one record instead of N records.

I don’t think so (but I’ve been wrong before). What I tend to do is those cases is a two-step process.

If your DBMS has a command line interface, you can use it to create a script to do the bulk of the work, something like:

db2 "select 'db2 delete from ' | tblname from sysibm.systables
    where owner = 'pax'" >p2.sh
p2.sh

The first bit simply creates a p2.sh file (or a p2.cmd file under Windows) containing a delete from statement for every table owned by pax. Then you just run that command file to do the dirty work. You may want to check it first, of course 🙂

Not the one-step process you were looking for but still very simple. I’m assuming here that mysql also has a command line interface.

The MySQL version of the above looks like it should be:

echo "select 'mysql truncate table ' | table_name
              from information_schema.tables" | mysql >p2.sh
bash p2.sh

This uses the truncate command which is usually more efficient than delete from for deleting all rows. It also uses the proper MySQL system tables to get the table names.

One point though — you may want to put a where clause on that select to limit the tables to those you want deleted. The query as it stands will try to delete every table. One possibility is to limit it with specific table_schema and/or table_type values.

Друзья, просветите, пожалуйста.
Если мне нужно дропнуть несколько таблиц из базы одним запросом, то как это сделать?
Drop Table If EXISTS удаляет одну таблицу или несколько через запятую.
Есть ли возможность как-то соединить Drop и Select, если Select выбирает наименования таблицы?

задан 12 мая 2017 в 10:14

DROP TABLE IF EXISTS table1, table2;

ответ дан 12 мая 2017 в 10:17

6 золотых знаков95 бронзовых знаков

Точно также можно удалить несколько таблиц. Просто перечислите их через запятую. Например:

DROP TABLE IF EXISTS table1, table2;

ответ дан 12 мая 2017 в 10:19

Есть ли возможность как-то соединить Drop и Select, если Select выбирает наименования таблицы?

SET @strSQL:=(SELECT CONCAT('DROP TABLE IF EXISTS ',GROUP_CONCAT(TABLE_NAME),';')
              FROM INFORMATION_SCHEMA.TABLES 
              WHERE TABLE_SCHEMA = 'MyDatabase' 
                AND TABLE_NAME LIKE 'Prefix%');
PREPARE stmt FROM @strSQL;
EXECUTE stmt;
DEALLOCATE stmt;
SET @strSQL = NULL;

ответ дан 12 мая 2017 в 11:14

3 золотых знака20 серебряных знаков40 бронзовых знаков

Честно говоря, вопросы типа «как из консоли, одной командой удалить все таблицы из базы MySQL» иногда ставят в тупик: а зачем? Специальной команды или программы в пакете MySQL на этот случай нет, так как она бессмысленна: можно прекрасно выполнить две команды:

$ mysql -u[username] -p[password] -e "drop database [database]"

$ mysql -u[username] -p[password] -e "create database [database]"

Warning: Using a password on the command line interface can be insecure.

И это правильно — не стоит использовать пароли в командной строке: они остаются в буфере).

Особо ленивым и часто использующим эту конструкцию можно быстренько написать скриптик, объединяющий эти две команды и запрашивающий пароль.

Впрочем на просторах интернета гуляют еще два варианта решения этой задачи:

mysql --silent --skip-column-names -u[USERNAME] -p[PASSWORD] [DATABASE] -e "show tables" | gawk '{print "drop table " $1 ";"}' | mysql -u [USERNAME] -p[PASSWORD] [DATABASE]

mysqldump -u[USERNAME] -p[PASSWORD] --add-drop-table --no-data [DATABASE] | grep ^DROP | mysql -u[USERNAME] -p[PASSWORD] [DATABASE]

Чем они лучше — не представляю, на любителя. Но букв точно набирать больше.

SET FOREIGN_KEY_CHECKS = 0;
SET GROUP_CONCAT_MAX_LEN=32768;
SET @tables = NULL;
SELECT GROUP_CONCAT('`', table_name, '`') INTO @tables
  FROM information_schema.tables
  WHERE table_schema = (SELECT DATABASE());
SELECT IFNULL(@tables,'dummy') INTO @tables;

SET @tables = CONCAT('DROP TABLE IF EXISTS ', @tables);
PREPARE stmt FROM @tables;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
SET FOREIGN_KEY_CHECKS = 1;

This script will not raise error with NULL result in case when you already deleted all tables in the database by adding at least one nonexistent — «dummy» table.

And it fixed in case when you have many tables.

And This small change to drop all view exist in the Database

SET FOREIGN_KEY_CHECKS = 0;
SET GROUP_CONCAT_MAX_LEN=32768;
SET @views = NULL;
SELECT GROUP_CONCAT('`', TABLE_NAME, '`') INTO @views
  FROM information_schema.views
  WHERE table_schema = (SELECT DATABASE());
SELECT IFNULL(@views,'dummy') INTO @views;

SET @views = CONCAT('DROP VIEW IF EXISTS ', @views);
PREPARE stmt FROM @views;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
SET FOREIGN_KEY_CHECKS = 1;

It assumes that you run the script from Database you want to delete. Or run this before:

USE REPLACE_WITH_DATABASE_NAME_YOU_WANT_TO_DELETE;

Thank you to Steve Horvath to discover the issue with backticks.

Часто возникает вопрос, как удалить все таблицы в базе данных MySQL, MariaDB. Ниже рассмотрим несколько вариантов.

В общем случае удалить таблицы из базы данных можно командой в консоли mysql:

mysql  databasename; mysql  table00 table01 table02;

Если у вас таблиц десятки и сотни, очевидно, что вручную их указывать неудобно. Ниже рассмотрим несколько вариантов удаления.

Просто удаляем базу данных. Пользователя удаляемой базы данных создавать заново не надо, все его права сохранятся. И потом импортируем как обычно.

mysql u root p
 
 ;
    database_name;

В SQL запросе ниже замените testdb на имя базы данных, которую вы хотите удалить. Будет сгенерирован пакет операторов SQL, вам нужно снова запустить набор результатов, которые удалят все таблицы без удаления базы данных.

 concat'DROP TABLE IF EXISTS '  
 information_schematables
 table_schema  ;

В консоли вы получите такой набор команд:

 concat'DROP TABLE IF EXISTS '     

     cm_options;                   
     cm_usermeta;                  
     cm_users;                     
     cm_term_taxonomy;             
     cm_termmeta;                  
     cm_commentmeta;               

В базе данных mysql имеется несколько таблиц вида:
temp_ids_1
temp_ids_5
temp_ids_8
temp_ids_12
и т. д.

Т.е. различаются они лишь ID в конце имени таблицы.
Можно ли написать такой запрос, чтобы одним махом очистить все эти таблицы. Т.е. что-то вроде

TRUNCATE `temp_ids_*`

3 золотых знака26 серебряных знаков49 бронзовых знаков

задан 29 ноя 2016 в 21:35

Есть несколько вариантов:

1) sql запрос:

SELECT CONCAT( 'TRUNCATE TABLE ', GROUP_CONCAT(table_name) , ';' ) 
    AS statement
FROM 
    information_schema.tables 
WHERE 
    table_name LIKE 'temp_ids_%';

Очистети тебе все таблицы, что начинаются на «temp_ids_%». Там можно любое условие сделать;-)

2) bash скрипт:

mysql -Nse 'show tables' -pDB_PASSWORD -u DB_USER DB_NAME | while read table;
do mysql -e "TRUNCATE TABLE $table" -pDB_PASSWORD -u DB_USER DB_NAME; done

10 золотых знаков30 серебряных знаков55 бронзовых знаков

ответ дан 30 ноя 2016 в 10:40

Если все таблицы имеют одинаковую структуру, создайте единую надтаблицу на MERGE Storage. Truncate такой таблицы вызовет очистку всех входящих в объединение таблиц.

UPD: Т.е. после TRUNCATE table необходимо выполнить FLUSH TABLE table.

ответ дан 30 ноя 2016 в 10:28

3 золотых знака20 серебряных знаков40 бронзовых знаков

  
  
     -- specify DB name here.

  

Популярные сообщения из этого блога

Синтаксис

Синтаксис оператора DELETE в SQL:

Параметры или аргументы

Таблица, из которой вы хотите удалить записи.

Необязательный. Условия, которые должны быть выполнены для удаления записей. Если никаких условий не предусмотрено, все записи в таблице будут удалены.

Примечание

Вам не нужно перечислять поля в операторе DELETE, так как вы удаляете всю строку из таблицы.

Если вы запустите оператор DELETE без условий в предложении WHERE, все записи из таблицы будут удалены. В результате вы чаще всего будете включать предложение WHERE, по крайней мере с одним условием, в свой оператор DELETE.

Давайте начнем с простого примера запроса DELETE, который имеет одно условие в предложении WHERE.

В этом примере у нас есть таблица со следующими данными:

Введите следующий оператор DELETE:

Будет удалена 1 запись. Снова выберите данные из таблицы поставщиков:

Вот результаты, которые вы должны получить:

В этом примере удаляются все записи из таблицы , где — Yandex.

Вы можете проверить количество строк, которые будут удалены. Вы можете определить количество строк, которые будут удалены, выполнив следующий запрос SELECT перед выполнением удаления:

Этот запрос вернет количество записей, которые будут удалены при выполнении оператора DELETE.

# LIMITing deletes

This works in the same way as the ‘Delete with Where clause’ example, but it will stop the deletion once the limited number of rows have been removed.

If you are limiting rows for deletion like this, be aware that it will delete the first row which matches the criteria. It might not be the one you would expect, as the results can come back unsorted if they are not explicitly ordered.

Пример с модификатором LIMIT

Давайте рассмотрим пример MySQL DELETE, в котором мы используем модификатор LIMIT для управления количеством удаленных записей.
Например:

В этом MySQL примере DELETE удалит одну запись из таблицы (как указано в LIMIT 1), где — ‘Markoski’. DELETE сортируется в порядке убывания , поэтому только запись с наибольшим , чье имя ‘Markoski’ будет удалено из таблицы. Все остальные записи в таблице с = ‘Markoski’ останутся в таблице.

Если вы хотите вместо этого удалить наименьший , чье является ‘Markoski’, вы можете переписать оператор DELETE следующим образом:

Или вы можете удалить последнюю запись в таблице со следующим оператором DELETE (при условии, что является порядковым номером):

Delete All Records

It is possible to delete all rows in a table without deleting the table. This
means that the table structure, attributes, and indexes will be intact:

Example

DELETE FROM Customers;

# Delete all rows from a table

This will delete everything, all rows from the table. It is the most basic example of the syntax. It also shows that DELETE statements should really be used with extra care as they may empty a table, if the WHERE clause is omitted.

Пример — оператор DELETE с более чем одним условием

Вы можете иметь более одного условия в инструкции DELETE в SQL, используя либо условие AND, либо условие OR. Условие AND позволяет вам удалить запись, если все условия выполнены. Условие OR удаляет запись, если выполняется одно из условий.

Давайте рассмотрим пример использования оператора DELETE с двумя условиями с использованием условия AND.
В этом примере у нас есть таблица со следующими данными:

Введите следующий оператор DELETE:

Будет удалены 3 записи. Снова выберите данные из таблицы :

Вот результаты, которые вы получите:

В этом примере удаляются все записи из таблицы , у которых равен 50, а НЕ ‘Pear’.

Empty and Recreate the Database

For this to be possible, you need to make sure that other than tables, you also are willing to delete stored functions, indices, roles, and other settings apart from tables since you are dropping the whole database.

For this to be executed successfully, the current role must have drop and create privileges in the database.

  database_name;

After dropping, recreate the database.

  database_name;

This approach is most likely the most volatile method of dropping all tables. This is suitable only for small or experimental databases.

Use mysqldump to Drop Tables in MySQL

mysqldump is a console command attributed from MySQL. Assuming you already have MySQL set up in your path variables, we can use the command to drop tables within your database.

• The first step still is to disable foreign key checking in the console.

• Proceed to dump the database using mysqldump and drop the tables

• Re-enable foreign key checking in the console.

• Restore your database using the dump file

  mysql -u root -p db_name < ./temp.sql
  

This will work easier if you have knowledge over console commands and have MySQL set up in your path variable.

Пример с двумя условиями

Рассмотрим MySQL пример DELETE, где у нас есть только два условия в инструкции DELETE.
Например:

Этот MySQL пример DELETE удалит все записи из таблицы , где = ‘Markoski’, а менее 2000.

Вы можете проверить количество строк, которые будут удалены. Вы можете определить количество строк, которые будут удалены, вызвав функцию или выполнив следующую MySQL предложение SELECT перед выполнением удаления.

Пример с одним условием

Рассмотрим пример запроса MySQL DELETE, в котором у нас есть только одно условие в операторе DELETE.
Например:

В этом MySQL примере DELETE удалит все записи из таблицы , где = ‘Markoski’.
Вы можете определить количество строк, которые будут удалены, вызвав функцию или выполнив следующую MySQL предложение SELECT перед выполнением удаления.

# Basic delete

The WHERE clause is optional but without it all rows are deleted.

Demo Database

Below is a selection from the «Customers» table in the Northwind sample
database:

The MySQL DELETE Statement

The DELETE statement is used to delete existing records in a table.

DELETE Syntax

DELETE FROM table_name WHERE condition;

Note: Be careful when deleting records in a table! Notice the
WHERE clause in the
DELETE statement.
The WHERE clause specifies which record(s) should be deleted. If
you omit the WHERE clause, all records in the table will be deleted!

Примечание

• Вам не нужно указывать поля в инструкции DELETE MySQL, так как вы удаляете всю строку из таблицы.

# DELETE vs TRUNCATE

This will delete (opens new window) all the data and reset AUTO_INCREMENT index. It’s much faster than DELETE FROM tableName on a huge dataset. It can be very useful during development/testing.

When you truncate a table SQL server doesn’t delete the data, it drops the table and recreates it, thereby deallocating the pages so there is a chance to recover the truncated data before the pages where overwritten. (The space cannot immediately be recouped for innodb_file_per_table=OFF.)

# Multi-table DELETE

MySQL allows to specify from which table the matching rows must be deleted

# Syntax

# Parameters

Пример — использование EXISTS с оператором DELETE

Вы также можете выполнять более сложные удаления.

Вы можете удалить записи в одной таблице на основе значений в другой таблице. Поскольку вы не можете перечислить более одной таблицы в предложении FROM при выполнении удаления, вы можете использовать предложение EXISTS.

В этом примере у нас есть таблица со следующими данными:

И таблица со следующими данными:

Введите следующий оператор DELETE:

Будет удалена 1 запись. Выберите данные из таблицы еще раз:

Вот результаты, которые вы должны получить:

В этом примере удаляются все записи из таблицы , где в таблице есть запись с фамилией ‘Bieber’ и совпадающим значением в обеих таблицах. В этом примере была удалена запись для = 4.

# Multi-Table Deletes

MySQL’s DELETE statement can use the JOIN construct, allowing also to specify which tables to delete from. This is useful to avoid nested queries.
Given the schema:

If we want to remove Paul’s pets, the statement

can be rewritten as:

1 row deleted
Spot is deleted from Pets

p1 and p2 are aliases for the table names, especially useful for long table names and ease of readability.

To remove both the person and the pet:

2 rows deleted
Spot is deleted from Pets
Paul is deleted from People

# foreign keys

The solution in this case is to delete the row from people and rely on InnoDB‘s ON DELETE capabilities to propagate the deletion:

2 rows deleted
Paul is deleted from People
Spot is deleted on cascade from Pets

Another solution is to temporarily disable the check on foreing keys:

SQL DELETE Example

Example

DELETE FROM Customers
WHERE CustomerName=’Alfreds Futterkiste’;

The «Customers» table will now look like this:

SQL Query to Drop All Tables

• The first thing to do is to make it so that dropping tables do not need to check foreign key constraints.

   FOREIGN_KEY_CHECKS  ;
  

• Then proceed to write a script to query all tables within your database.

      table_schema  db_name;
  

• Afterwards, copy all of the tables in the result from the above query and delete them one by one.

      tableOne;
      tableTwo;
      tableThree;
      tableEtc;
  

• Reset the configuration of foreign key checking to default

   FOREIGN_KEY_CHECKS  ;
  

This approach is possible because we disabled foreign key checking at the start. So whatever order we delete the tables in, it won’t throw any constraint error. Just make sure to re-enable foreign key checking after the script.

Синтаксис

Простой синтаксис оператора DELETE в MySQL:

Параметры или аргументы

— необязательный. Если указан , удаление будет задерживаться до тех пор, пока не будет никаких процессов затрагивающих таблицу. может использоваться с таблицами MyISAM, MEMORY и MERGE, которые используют блокировку на уровне таблицы.
— необязательный. Если указан , в процессе удаления не удаляются листы индекса, что делает удаление быстрее для таблиц MyISAM
— необязательный. Если указан , все ошибки, возникающие во время удаления, игнорируются. был представлен в MySQL 4.1.1.
— таблица, из которой вы хотите удалить записи.
— необязательный. Условия, которые должны быть выполнены для записей, подлежащих удалению. Если условий не предусмотрено, все записи из таблицы будут удалены.
ORDER BY expression — необязательный. Он может использоваться в сочетании с для сортировки записей соответствующим образом при ограничении количества удаляемых записей.
— необязательный. Если указан, то он контролирует максимальное количество записей для удаления из таблицы. В лучшем случае количество записей, заданных , будет удалено из таблицы.

Verification of Deleted Tables

To verify whether all tables actually were deleted, we can check the database if it still outputs the tables with this script.

 TABLES  database_name;

If there is no output, that means that the query has been executed successfully, and all SQL tables from that specific database have been dropped.

In summary, the reason why you can’t delete tables in bulk directly in SQL is that relational databases rely heavily on relationships and constraints. If you were allowed to delete tables without explicitly disabling foreign key checking, the relationships, constraints, and foreign key configurations will be corrupted.

# Delete with Where clause

This will delete all rows from the table where the contents of the field_one for that row match ‘value_one’

The WHERE clause works in the same way as a select, so things like >, <, <> or LIKE can be used.

Notice: It is necessary to use conditional clauses (WHERE, LIKE) in delete query. If you do not use any conditional clauses then all data from that table will be deleted.

Пример c использованием условия EXISTS

Вы также можете выполнять более сложные удаления.
Вы можете удалить записи в одной таблице на основе значений в другой таблице. Поскольку вы не можете перечислить более чем одну таблицу в MySQL операторе FROM при выполнении удаления, вы можете использовать MySQL оператор EXISTS.
Например:

В этом MySQL примере DELETE удалит все записи в таблице , где есть запись в таблице , чья больше 300, а равен .

Вы можете определить количество строк, которые будут удалены, вызвав функцию или выполнив следующее MySQL предложение SELECT перед выполнением удаления.

Test case

I tested the three working methods and the new method above in two test cases.

Both test cases use 10000 existing rows, while the first test keeps 9000 (deletes the oldest 1000) and the second test keeps 50 (deletes the oldest 9950).

+-----------+------------------------+----------------------+
|           | 10000 TOTAL, KEEP 9000 | 10000 TOTAL, KEEP 50 |
+-----------+------------------------+----------------------+
| NOT IN    |         3.2542 seconds |       0.1629 seconds |
| NOT IN v2 |         4.5863 seconds |       0.1650 seconds |
| <=,OFFSET |         0.0204 seconds |       0.1076 seconds |
+-----------+------------------------+----------------------+

What’s interesting is that the <= method sees better performance across the board, but actually gets better the more you keep, instead of worse.