Mysql delete data and data table method example

It is very easy to delete data and tables in MySQL, but you need to be careful because all data will disappear once deleted.

Deleting Data

To delete data in a table, use the delete keyword.

Delete data with specified conditions

Delete the user with id 1 in the user table:

delete from User where id = 1;

Delete all data in the table

Delete all data in the table without changing the table structure.

For MyISAM, disk space will be released immediately, but InnoDB will not release disk space.

delete from User;

Free up disk space

optimize table User;

Delete a table

There are two ways to delete a data table:

1. Delete the data and table structure in the data table
2. Only delete the data in the table and keep the table structure
   

drop

Using the drop keyword will delete the entire table, leaving nothing behind.

drop table User;

truncate

The truncate keyword only deletes the data in the table and retains the table structure.

truncate table User;

Question for consideration: How to batch delete tables with the same prefix?

There is no direct command to implement drop table like 'wp_%' , but it can be spliced ​​through MySQL syntax.

-- Delete the table starting with "wp_":
SELECT CONCAT( 'drop table ', table_name, ';' ) AS statement
FROM information_schema.tables
WHERE table_schema = 'database_name' AND table_name LIKE 'wp_%';

Here, replace database_name with the name of the database and wp_ with the prefix of the tables that need to be deleted in batches.

Note that only the drop command can be used like this:

drop table if exists tablename`;

truncate can only be used like this:

truncate table `tp_trade`.`setids`;

Summarize

• When you no longer need the table, use drop;
• When you still want to keep the table but delete all records, use truncate;
• When you want to delete some records, use delete.

This is the end of this article about Mysql deleting data and data tables. For more information about Mysql deleting data and data tables, please search for previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope you will support 123WORDPRESS.COM in the future!