Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Remove a stored package completely. This command deletes both the package specification (interface) and its body (implementation) from the database.
DROP PACKAGE [IF EXISTS] [ db_name . ] package_nameThe DROP PACKAGE statement can be used when is set.
The DROP PACKAGE statement drops a stored package entirely:
Drops the package specification (earlier created using the statement).
Drops the package implementation, if the implementation was already created using the statement.
This page is licensed: CC BY-SA / Gnu FDL
Find statements to remove database objects. This section details the syntax for deleting databases, tables, users, and other entities when they are no longer needed.
Delete a tablespace. This command removes the physical file container used for storing table data, applicable to engines like InnoDB or NDB.
This page is licensed: GPLv2, originally from fill_help_tables.sql
Remove a log file group. This statement, primarily for NDB Cluster, deletes the undo log files associated with the specified log file group.
This page is licensed: CC BY-SA / Gnu FDL
Remove an existing index from a table. This command deletes the index structure, potentially impacting query performance but freeing storage.
DROP INDEX drops the named index_name from the table tbl_name. This statement is mapped to an ALTER TABLE statement to drop the index.
If another connection is using the table, a is active, and this statement will wait until the lock is released. This is also true for non-transactional tables.
See .
Another shortcut, , allows the creation of an index.
To remove the primary key, PRIMARY must be specified as index_name. Note that are necessary, because PRIMARY is a keyword.
Executing the DROP INDEX statement requires the privilege for the table or the database.
Online DDL is used by default with InnoDB, when the drop index operation supports it.
See for more information on online DDL with .
If the IF EXISTS clause is used, then MariaDB will return a warning instead of an error if the index does not exist.
Sets the lock wait timeout. See .
MariaDB provides progress reporting for DROP INDEX statement for clients
that support the new progress reporting protocol. For example, if you were using the client, then the progress report might look like this::
This page is licensed: GPLv2, originally from
Remove a scheduled event from the server. This command stops the event from executing and deletes its definition from the system tables.
This statement drops the event named event_name. The event immediately ceases being active, and is deleted completely from the server.
If the event does not exist, the errorERROR 1517 (HY000): Unknown event 'event_name' results. You can override this and cause the statement to generate a NOTE for non-existent events instead by usingIF EXISTS. See .
This statement requires the privilege.
Using the IF EXISTS clause:
This page is licensed: GPLv2, originally from
Delete a database and all its contents. This command permanently removes the database directory and all tables, routines, and data within it.
DROP DATABASE drops all tables in the database and deletes the database. Be very careful with this statement! To use DROP DATABASE, you need the on the database. DROP SCHEMA is a synonym for DROP DATABASE.
Important: When a database is dropped, user privileges on the database are not automatically dropped. See .
Use IF EXISTS to prevent an error from occurring for databases that do not exist. A NOTE is generated for each non-existent database when using IF EXISTS. See .
supports .DROP DATABASE is implemented as
Each individual is atomic while DROP DATABASE as a whole is crash-safe.
Atomic DROP is not available.
This page is licensed: GPLv2, originally from
Remove a trigger from a table. This command deletes the trigger definition, preventing it from firing on future INSERT, UPDATE, or DELETE events.
This statement drops a trigger. The schema (database) name is optional. If the schema is omitted, the trigger is dropped from the default schema. Its use requires the TRIGGER privilege for the table associated with the trigger.
Use IF EXISTS to prevent an error from occurring for a trigger that does not exist. A NOTE is generated for a non-existent trigger when using IF EXISTS. See .
Note: Triggers for a table are also dropped if you drop the table.
DROP TRIGGER is .
DROP TRIGGER is not .
Using the IF EXISTS clause:
This page is licensed: GPLv2, originally from
DROP INDEX [IF EXISTS] index_name ON tbl_name
[WAIT n |NOWAIT]DROP EVENT [IF EXISTS] event_nameDROP {DATABASE | SCHEMA} [IF EXISTS] db_nameDROP TRIGGER [IF EXISTS] [schema_name.]trigger_nameLOOP OVER ALL tables
DROP TABLE tblThe DROP PACKAGE BODY statement drops the package body (that is, the implementation) which was created using the CREATE PACKAGE BODY statement.
Note, DROP PACKAGE BODY drops only the package implementation, but does not drop the package specification. Use DROP PACKAGE to drop the package entirely (i.e. both implementation and specification).
This page is licensed: CC BY-SA / Gnu FDL
DROP DATABASE bufg;
Query OK, 0 rows affected (0.39 sec)
DROP DATABASE bufg;
ERROR 1008 (HY000): Can't drop database 'bufg'; database doesn't exist
\W
SHOW warnings enabled.
DROP DATABASE IF EXISTS bufg;
Query OK, 0 rows affected, 1 warning (0.00 sec)
Note (Code 1008): Can't DROP DATABASE 'bufg'; DATABASE doesn't existDROP TRIGGER test.example_trigger;DROP TRIGGER IF EXISTS test.example_trigger;
Query OK, 0 rows affected, 1 warning (0.01 sec)
SHOW WARNINGS;
+-------+------+------------------------+
| Level | Code | Message |
+-------+------+------------------------+
| Note | 1360 | Trigger does not exist |
+-------+------+------------------------+DROP PACKAGE BODY [IF EXISTS] [ db_name . ] package_nameDROP EVENT myevent3;DROP EVENT IF EXISTS myevent3;
Query OK, 0 rows affected, 1 warning (0.01 sec)
SHOW WARNINGS;
+-------+------+-------------------------------+
| Level | Code | Message |
+-------+------+-------------------------------+
| Note | 1305 | Event myevent3 does not exist |
+-------+------+-------------------------------+Remove a server definition. This command deletes the connection details for a remote server used by the FEDERATED or SPIDER storage engines.
Drops the server definition for the server named server_name. The corresponding row within the mysql.servers table will be deleted. This statement requires the privilege or, from , the privilege.
Dropping a server for a table does not affect any , , or tables that used this connection information when they were created.
DROP SERVER is not written to the , irrespective of the being used. From , replicates the , and DROP SERVER statements.
If the IF EXISTS clause is used, MariaDB will not return an error if the server does not exist. Unlike all other statements, DROP SERVER IF EXISTS does not issue a note if the server does not exist. See .
IF EXISTS:
This page is licensed: GPLv2, originally from
DROP SERVER [ IF EXISTS ] server_nameDROP SERVER s;DROP SERVER s;
ERROR 1477 (HY000): The foreign server name you are trying to reference
does not exist. Data source error: s
DROP SERVER IF EXISTS s;
Query OK, 0 rows affected (0.00 sec)Delete one or more tables. This command removes the table definitions and all stored data permanently, and can also delete temporary tables.
DROP TABLE removes one or more tables. You must have the DROP privilege for each table. All table data and the table definition are removed, as well as associated to the table, so be careful with this statement! If any of the tables named in the argument list do not exist, MariaDB returns an error indicating by name which non-existing tables it was unable to drop, but it also drops all of the tables in the list that do exist.
Important: When a table is dropped, user privileges on the table are not automatically dropped. See .
If another thread is using the table in an explicit transaction or an autocommit transaction, then the thread acquires a on the table. The DROP TABLE statement will wait in the "Waiting for table metadata lock" until the MDL is released. MDLs are released in the following cases:
If an MDL is acquired in an explicit transaction, then the MDL will be released when the transaction ends.
If an MDL is acquired in an autocommit transaction, then the MDL will be released when the statement ends.
Transactional and non-transactional tables are handled the same.
Note that for a partitioned table, DROP TABLE permanently removes the table definition, all of its partitions, and all of the data which was stored in those partitions. It also removes the partitioning definition (.par) file associated with the dropped table.
For each referenced table, DROP TABLE drops a temporary table with that name, if it exists. If it does not exist, and the TEMPORARY keyword is not used, it drops a non-temporary table with the same name, if it exists. The TEMPORARY keyword ensures that a non-temporary table will not accidentally be dropped.
Use IF EXISTS to prevent an error from occurring for tables that do not
exist. A NOTE is generated for each non-existent table when usingIF EXISTS. See .
If a references this table, the table cannot be dropped. In this case, it is necessary to drop the foreign key first.
RESTRICT and CASCADE are allowed to make porting from other database systems easier. In MariaDB, they do nothing.
The comment before the table names (/*COMMENT TO SAVE*/) is stored in the . That feature can be used by replication tools to send their internal messages.
It is possible to specify table names as db_name.tab_name. This is useful to delete tables from multiple databases with one statement. See for details.
The is required to use DROP TABLE on non-temporary tables. For temporary tables, no privilege is required, because such tables are only visible for the current session.
Note: DROP TABLE automatically commits the current active transaction, unless you use the TEMPORARY keyword.
DROP TABLE reliably deletes table remnants inside a storage engine even if the .frm file is missing.
DROP TABLE does not reliably delete table remnants inside a storage engine even if the .frm file is missing. A missing .frm file will result in the statement failing.
Set the lock wait timeout. See .
DROP TABLE has the following characteristics in :
DROP TABLE IF EXISTS are always logged.
DROP TABLE without IF EXISTS for tables that don't exist are not written to the .
Dropping of TEMPORARY tables are prefixed in the log with TEMPORARY. These drops are only logged when running
DROP TABLE on the primary is treated on the replica as DROP TABLE IF EXISTS. You can change that by setting to STRICT.
if the is killed during an , you may find a table named #sql-... in your data directory. These temporary tables will always be deleted automatically.
If you want to delete one of these tables explicitly you can do so by using the following syntax:
When running an ALTER TABLE…ALGORITHM=INPLACE that rebuilds the table, InnoDB will create an internal #sql-ib table.
The same name as the .frm file is used for the intermediate copy of the table. The #sql-ib names are used by TRUNCATE and delayed DROP.
The #sql-ib tables will be deleted automatically.
The best way to drop all tables in a database is by executing , which will drop the database itself, and all tables in it.
However, if you want to drop all tables in the database, but you also want to keep the database itself and any other non-table objects in it, then you would need to execute DROP TABLE to drop each individual table. You can construct these DROP TABLE commands by querying the table in the database. For example:
MariaDB starting with
DROP TABLE for a single table is atomic () for most engines, including InnoDB, MyRocks, MyISAM and Aria. This means that if there is a crash (server down or power outage) during DROP TABLE, all tables that have been processed so far will be completely dropped, including related trigger files and status entries, and the will include a DROP TABLE statement for the dropped tables. Tables for which the drop had not started will be left intact.DROP TABLE was extended to be able to delete a table that was only partly dropped (), as explained above. Atomic DROP TABLE is the final piece to make DROP TABLE fully reliable. Dropping multiple tables is crash-safe. See for more information.
There is a small chance that, during a server crash happening in the middle of DROP TABLE, some storage engines that were using multiple storage files, like
Beware that DROP TABLE can drop both tables and . This is mainly done to allow old tools like (previously mysqldump) to work with sequences.
This page is licensed: GPLv2, originally from
DROP [TEMPORARY] TABLE [IF EXISTS] [/*COMMENT TO SAVE*/]
tbl_name [, tbl_name] ...
[WAIT n|NOWAIT]
[RESTRICT | CASCADE]One DROP TABLE statement can be logged with up to 3 different DROP statements:
DROP TEMPORARY TABLE list_of_non_transactional_temporary_tables
DROP TEMPORARY TABLE list_of_transactional_temporary_tables
DROP TABLE list_of_normal_tables
DROP TABLE was extended to be able to delete a table that was only partly dropped (DROP TABLE is the final piece to make DROP TABLE fully reliable. Dropping multiple tables is crash-safe. See DROP TABLE `#mysql50##sql-...`;SELECT CONCAT('DROP TABLE IF EXISTS `', TABLE_SCHEMA, '`.`', TABLE_NAME, '`;')
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'mydb';DROP TABLE Employees, Customers;