MySQL 8.0 Reference Manual

В этой статье мы покажем способы очистки и удаления таблиц базы данных MySQL. Это актуально в том случае, если у вас отсутствуют права доступа к базе данных для ее создания и удаления. Также на тот случай, если времени мало и нет времени искать и запоминать параметры базы, чтобы затем пересоздать её.

Содержание

1. Способ 1. Умный
2. Способ 2. Хитрый
3. Способ 3. Пыховатый
4. SQL Query to Drop All Tables
5. Empty and Recreate the Database
6. Use mysqldump to Drop Tables in MySQL
7. Verification of Deleted Tables
8. 13.1.24 DROP DATABASE Statement
9. 13.1.32 DROP TABLE Statement
10. 13.2.2 DELETE Statement
11. Single-Table Syntax
12. Main Clauses
13. Multiple-Table Syntax
14. Privileges
15. Performance
16. Subqueries
17. Partitioned Table Support
18. Auto-Increment Columns
19. Modifiers
20. Order of Deletion
21. InnoDB Tables
22. MyISAM Tables
23. Multi-Table Deletes

Способ 1. Умный

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

Пример запуска команды в консоле:

Способ 2. Хитрый

Еще один хитрый способ для запуска в консоле сервера

Способ 3. Пыховатый

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

Если вам необходимо очистить таблицы от записей, а не удалять их полностью – замените в коде DROP TABLE на TRUNCATE TABLE После запуска вы увидите какие таблицы были очищены и их количество.

Не забудьте удалить скрипт с сайта, после процедуры очистки

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

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.

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.

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.

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.

Честно говоря, вопросы типа «как из консоли, одной командой удалить все таблицы из базы 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]

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

13.1.24 DROP DATABASE Statement

DROP {DATABASE | SCHEMA} [IF EXISTS] db_name

DROP DATABASE drops all tables in
the database and deletes the database. Be
careful with this statement! To use
DROP DATABASE, you need the
DROP privilege on the database.
DROP
SCHEMA is a synonym for DROP
DATABASE.

When a database is dropped, privileges granted specifically for
the database are automatically dropped.
They must be dropped manually. See Section 13.7.1.6, “GRANT Statement”.

IF EXISTS is used to prevent an error from
occurring if the database does not exist.

If the default database is dropped, the default database is unset
(the DATABASE() function returns
NULL).

If you use DROP DATABASE on a
symbolically linked database, both the link and the original
database are deleted.

DROP DATABASE returns the number of
tables that were removed.

If other files or directories remain in the database directory
after MySQL removes those just listed, the database directory
cannot be removed. In this case, you must remove any remaining
files or directories manually and issue the
DROP DATABASE statement again.

Dropping a database does not remove any
TEMPORARY tables that were created in that
database. TEMPORARY tables are automatically
removed when the session that created them ends. See
Section 13.1.20.2, “CREATE TEMPORARY TABLE Statement”.

13.1.32 DROP TABLE Statement

DROP [TEMPORARY] TABLE [IF EXISTS] tbl_name [, tbl_name] ... [RESTRICT | CASCADE]

DROP TABLE removes one or more
tables. You must have the DROP
privilege for each table.

with this statement! For each
table, it removes the table definition and all table data. If the
table is partitioned, the statement removes the table definition,
all its partitions, all data stored in those partitions, and all
partition definitions associated with the dropped table.

Dropping a table also drops any triggers for the table.

DROP TABLE causes an implicit
commit, except when used with the TEMPORARY
keyword. See Section 13.3.3, “Statements That Cause an Implicit Commit”.

When a table is dropped, privileges granted specifically for the
table are automatically dropped. They
must be dropped manually. See Section 13.7.1.6, “GRANT Statement”.

If any tables named in the argument list do not exist,
DROP TABLE behavior depends on
whether the IF EXISTS clause is given:

• Without IF EXISTS, the statement fails with
  an error indicating which nonexisting tables it was unable to
  drop, and no changes are made.

• With IF EXISTS, no error occurs for
  nonexisting tables. The statement drops all named tables that
  do exist, and generates a NOTE diagnostic
  for each nonexistent table. These notes can be displayed with
  SHOW WARNINGS. See
  Section 13.7.7.42, “SHOW WARNINGS Statement”.

IF EXISTS can also be useful for dropping
tables in unusual circumstances under which there is an entry in
the data dictionary but no table managed by the storage engine.
(For example, if an abnormal server exit occurs after removal of
the table from the storage engine but before removal of the data
dictionary entry.)

• The statement drops only TEMPORARY tables.

• The statement does not cause an implicit commit.

• No access rights are checked. A TEMPORARY
  table is visible only with the session that created it, so no
  check is necessary.

Including the TEMPORARY keyword is a good way
to prevent accidentally dropping non-TEMPORARY
tables.

The RESTRICT and CASCADE
keywords do nothing. They are permitted to make porting easier
from other database systems.

DROP TABLE is not supported with
all innodb_force_recovery
settings. See Section 15.21.3, “Forcing InnoDB Recovery”.

13.2.2 DELETE Statement

DELETE is a DML statement that
removes rows from a table.

A DELETE statement can start with a
WITH clause to define common table
expressions accessible within the
DELETE. See Section 13.2.20, “WITH (Common Table Expressions)”.

Single-Table Syntax

DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM tbl_name [[AS] tbl_alias] [PARTITION (partition_name [, partition_name] ...)] [WHERE where_condition] [ORDER BY ...] [LIMIT row_count]

The DELETE statement deletes rows from
tbl_name and returns the number of
deleted rows. To check the number of deleted rows, call the
ROW_COUNT() function described in
Section 12.16, “Information Functions”.

Main Clauses

The conditions in the optional WHERE clause
identify which rows to delete. With no WHERE
clause, all rows are deleted.

where_condition is an expression that
evaluates to true for each row to be deleted. It is specified as
described in Section 13.2.13, “SELECT Statement”.

If the ORDER BY clause is specified, the rows
are deleted in the order that is specified. The
LIMIT clause places a limit on the number of
rows that can be deleted. These clauses apply to single-table
deletes, but not multi-table deletes.

Multiple-Table Syntax

DELETE [LOW_PRIORITY] [QUICK] [IGNORE] tbl_name[.*] [, tbl_name[.*]] ... FROM table_references [WHERE where_condition]
DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM tbl_name[.*] [, tbl_name[.*]] ... USING table_references [WHERE where_condition]

Privileges

You need the DELETE privilege on a
table to delete rows from it. You need only the
SELECT privilege for any columns
that are only read, such as those named in the
WHERE clause.

Performance

When you do not need to know the number of deleted rows, the
TRUNCATE TABLE statement is a
faster way to empty a table than a
DELETE statement with no
WHERE clause. Unlike
DELETE,
TRUNCATE TABLE cannot be used
within a transaction or if you have a lock on the table. See
Section 13.1.37, “TRUNCATE TABLE Statement” and
Section 13.3.6, “LOCK TABLES and UNLOCK TABLES Statements”.

The speed of delete operations may also be affected by factors
discussed in Section 8.2.5.3, “Optimizing DELETE Statements”.

To ensure that a given DELETE
statement does not take too much time, the MySQL-specific
LIMIT row_count
clause for DELETE specifies the
maximum number of rows to be deleted. If the number of rows to
delete is larger than the limit, repeat the
DELETE statement until the number of affected
rows is less than the LIMIT value.

Subqueries

You cannot delete from a table and select from the same table in a
subquery.

Partitioned Table Support

DELETE supports explicit partition selection
using the PARTITION clause, which takes a list
of the comma-separated names of one or more partitions or
subpartitions (or both) from which to select rows to be dropped.
Partitions not included in the list are ignored. Given a
partitioned table t with a partition named
p0, executing the statement DELETE
FROM t PARTITION (p0) has the same effect on the table
as executing ALTER
TABLE t TRUNCATE PARTITION (p0); in both cases, all rows
in partition p0 are dropped.

PARTITION can be used along with a
WHERE condition, in which case the condition is
tested only on rows in the listed partitions. For example,
DELETE FROM t PARTITION (p0) WHERE c < 5
deletes rows only from partition p0 for which
the condition c < 5 is true; rows in any
other partitions are not checked and thus not affected by the
DELETE.

The PARTITION clause can also be used in
multiple-table DELETE statements. You can use
up to one such option per table named in the
FROM option.

Auto-Increment Columns

If you delete the row containing the maximum value for an
AUTO_INCREMENT column, the value is not reused
for a MyISAM or InnoDB
table. If you delete all rows in the table with DELETE
FROM tbl_name (without a
WHERE clause) in
autocommit mode, the sequence
starts over for all storage engines except
InnoDB and MyISAM. There are
some exceptions to this behavior for InnoDB
tables, as discussed in
Section 15.6.1.6, “AUTO_INCREMENT Handling in InnoDB”.

For MyISAM tables, you can specify an
AUTO_INCREMENT secondary column in a
multiple-column key. In this case, reuse of values deleted from
the top of the sequence occurs even for MyISAM
tables. See Section 3.6.9, “Using AUTO_INCREMENT”.

Modifiers

• If you specify the LOW_PRIORITY modifier,
  the server delays execution of the
  DELETE until no other clients
  are reading from the table. This affects only storage engines
  that use only table-level locking (such as
  MyISAM, MEMORY, and
  MERGE).

• For MyISAM tables, if you use the
  QUICK modifier, the storage engine does not
  merge index leaves during delete, which may speed up some
  kinds of delete operations.

Order of Deletion

DELETE FROM somelog WHERE user = 'jcole'
ORDER BY timestamp_column LIMIT 1;

ORDER BY also helps to delete rows in an order
required to avoid referential integrity violations.

InnoDB Tables

1. Select the rows to be deleted into an
   empty table that has the same structure as the original table:

   INSERT INTO t_copy SELECT * FROM t WHERE ... ;

2. Use RENAME TABLE to atomically
   move the original table out of the way and rename the copy to
   the original name:

   RENAME TABLE t TO t_old, t_copy TO t;

3. Drop the original table:

   DROP TABLE t_old;

No other sessions can access the tables involved while
RENAME TABLE executes, so the
rename operation is not subject to concurrency problems. See
Section 13.1.36, “RENAME TABLE Statement”.

MyISAM Tables

In MyISAM tables, deleted rows are maintained
in a linked list and subsequent
INSERT operations reuse old row
positions. To reclaim unused space and reduce file sizes, use the
OPTIMIZE TABLE statement or the
utility to reorganize tables.
OPTIMIZE TABLE is easier to use,
but is faster. See
Section 13.7.3.4, “OPTIMIZE TABLE Statement”, and Section 4.6.4, “myisamchk — MyISAM Table-Maintenance Utility”.

The QUICK modifier affects whether index leaves
are merged for delete operations. DELETE QUICK
is most useful for applications where index values for deleted
rows are replaced by similar index values from rows inserted
later. In this case, the holes left by deleted values are reused.

1. Create a table that contains an indexed
   AUTO_INCREMENT column.

2. Insert many rows into the table. Each insert results in an
   index value that is added to the high end of the index.

Multi-Table Deletes

You can specify multiple tables in a
DELETE statement to delete rows
from one or more tables depending on the condition in the
WHERE clause. You cannot use ORDER
BY or LIMIT in a multiple-table
DELETE. The
table_references clause lists the
tables involved in the join, as described in
Section 13.2.13.2, “JOIN Clause”.

For the first multiple-table syntax, only matching rows from the
tables listed before the FROM clause are
deleted. For the second multiple-table syntax, only matching rows
from the tables listed in the FROM clause
(before the USING clause) are deleted. The
effect is that you can delete rows from many tables at the same
time and have additional tables that are used only for searching:

DELETE t1, t2 FROM t1 INNER JOIN t2 INNER JOIN t3
WHERE t1.id=t2.id AND t2.id=t3.id;

DELETE FROM t1, t2 USING t1 INNER JOIN t2 INNER JOIN t3
WHERE t1.id=t2.id AND t2.id=t3.id;

These statements use all three tables when searching for rows to
delete, but delete matching rows only from tables
t1 and t2.

The preceding examples use INNER JOIN, but
multiple-table DELETE statements
can use other types of join permitted in
SELECT statements, such as
LEFT JOIN. For example, to delete rows that
exist in t1 that have no match in
t2, use a LEFT JOIN:

DELETE t1 FROM t1 LEFT JOIN t2 ON t1.id=t2.id WHERE t2.id IS NULL;

The syntax permits .* after each
tbl_name for compatibility with
.

If you use a multiple-table DELETE
statement involving InnoDB tables for which
there are foreign key constraints, the MySQL optimizer might
process tables in an order that differs from that of their
parent/child relationship. In this case, the statement fails and
rolls back. Instead, you should delete from a single table and
rely on the ON DELETE capabilities that
InnoDB provides to cause the other tables to be
modified accordingly.

If you declare an alias for a table, you must use the alias when
referring to the table:

DELETE t1 FROM test AS t1, test2 WHERE ...

Table aliases in a multiple-table
DELETE should be declared only in
the table_references part of the
statement. Elsewhere, alias references are permitted but not alias
declarations.

DELETE a1, a2 FROM t1 AS a1 INNER JOIN t2 AS a2
WHERE a1.id=a2.id;
DELETE FROM a1, a2 USING t1 AS a1 INNER JOIN t2 AS a2
WHERE a1.id=a2.id;

DELETE t1 AS a1, t2 AS a2 FROM t1 INNER JOIN t2
WHERE a1.id=a2.id;
DELETE FROM t1 AS a1, t2 AS a2 USING t1 INNER JOIN t2
WHERE a1.id=a2.id;

Table aliases are also supported for single-table
DELETE statements beginning with MySQL 8.0.16.
(Bug #89410,Bug #27455809)