Truncate all tables in Mysql
Today I will show you how to truncate all tables in Mysql. It is used when you want to delete the entire table TRUNCATE TABLE. TRUNCATEIt is a type of DML statement, which means it cannot be rolled back once it is committed.
There are two ways to truncate all tables in MySQL.
Truncate all tables in Mysql from command line
mysql mydb -Nse 'show tables' | while read table; do mysql mydb -e "truncate table $table"; done
Let's see what this code does in general: mysql mydatabase -Nse 'show tables'Print all the tables in the mydb database one by one. while read tablePerform a loop for each table in the database.
mysql db -e "truncate table $table"Executes a truncation query for each $table variable value.
Use truncation script for all tables
CREATE TABLE table_name (table_id INT);
CREATE TABLE table_name1 (table_id INT);
insert into table_name values (1);
insert into table_name values (2);
insert into table_name1 values (34);
insert into table_name1 values (35);
SELECT Concat('TRUNCATE TABLE ', table_name, ';')
FROM INFORMATION_SCHEMA.TABLES
Let's check if the table is now empty:
SELECT * FROM table_name
Output:
Empty set (0.01 sec)
Note that if your tables have foreign keys referencing each other, you will need to make sure foreign keys are disabled before executing this script.
SET FOREIGN_KEY_CHECKS=0;
After you truncate your table you can enable it.
SET FOREIGN_KEY_CHECKS=1;
For reprinting, please send an email to 1244347461@qq.com for approval. After obtaining the author's consent, kindly include the source as a link.
Related Articles
How to select from multiple tables in MySQL
Publish Date：2025/04/25 Views：57 Category：MySQL
-
This article explains how to use MySQL to query from multiple tables in one script SELECT . Let's demonstrate a situation: SELECT name , price, details, type , FROM food, food_order WHERE breakfast.id = 'breakfast_id' Now, let's imagine FRO
Creating a table from CSV in MySQL
Publish Date：2025/04/25 Views：114 Category：MySQL
-
In this article, we aim to understand how to create a table from CSV in MySQL database. Businesses and organizations must quickly generate tables from large amounts of data. These organizations typically have large CSV files with large amou
Creating a Temporary Table in MySQL
Publish Date：2025/04/25 Views：183 Category：MySQL
-
In this article, we aim to explore different ways to create temporary tables in MySQL. One of the main features of temporary tables is that it helps in storing temporary data. This feature is enabled in MySQL 3.23 and later versions. These
Get column names in MySQL
Publish Date：2025/04/25 Views：109 Category：MySQL
-
In this article, we aim to explore how to get the column names of a specific table in MySQL database. Often, when working with data in MySQL, we tend to forget the column names of a particular table in the database and the data types of dif
Get the version in MySQL
Publish Date：2025/04/24 Views：170 Category：MySQL
-
In this article, we will learn how to get the current version of MySQL on Windows systems. It is necessary for businesses to maintain versions of different tools and software used to run their business in order to keep systems compatible. T
Full Join in MySQL
Publish Date：2025/04/24 Views：71 Category：MySQL
-
This article aims to explore how to perform a full join or full outer join in MySQL. Full outer join is used to merge or combine the entire data from two separate tables. For example, suppose we have two tables named student_id and student_
Grouping by month in MySQL
Publish Date：2025/04/24 Views：166 Category：MySQL
-
In this article, we will learn how to group values by month in MySQL database. Businesses and organizations have to find user or customer data based on purchase or usage trends over a few months. If a particular business achieves its
Enabling the slow query log in MySQL
Publish Date：2025/04/24 Views：177 Category：MySQL
-
Today, we will enable MySQL in MySQL using MySQL shell on Windows and Ubuntu 20.04 slow_query_log . For this tutorial, we are using MySQL version 8.0 and Ubuntu 20.04. MySQL slow_query_log MySQL slow_query_log contains SQL statements that t
Update multiple tables in MySQL with one query
Publish Date：2025/04/24 Views：66 Category：MySQL
-
In some cases, users want to update logically related tables at the same time. These logically related tables are linked to each other through some attributes. Advantages of updating multiple tables in one MySQL query Similar attributes in
