Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

empty a table -  MySQL50

Posted on 2006-07-02
4
Medium Priority
?
415 Views
Last Modified: 2012-05-05
using MySQL 5.0    - - >

what is the easiest way to empty a table?   iow ~  to delete all the data that the table contains...     i'm not talking about dropping the table completely...   rather, just to empty (clear) all the data from it.

is there a way to to this with a "right"-click on the tablename after right-clicking and going to "Edit Table Data" ?    if not,  what is the SQL statement because   DELETE * FROM 'database'.'tablename'   doesn't seem to really empty the table..  cause when i go back to the MySQL Administrator and hit Refresh..  the data in the table still exists.      ??

Thanks.

0
Comment
Question by:lblinc
  • 2
4 Comments
 
LVL 35

Accepted Solution

by:
Raynard7 earned 600 total points
ID: 17028242
truncate table `schemaName`.`tableName`;

This deletes the data and re-creates the index; this is the fastest and best method
0
 
LVL 35

Expert Comment

by:Raynard7
ID: 17028250
Delect * should work - unless you are using transactions; where you will need to commit them.

truncate keeps the table structure but removes all the data within.
0
 
LVL 4

Assisted Solution

by:JeePeeTee
JeePeeTee earned 400 total points
ID: 17036481
Truncate isn't transactional and can't be rolled-back when you have referantial constraints to this table and truncate them, your constaints will be removed.

 Delete from table is transactional. You can limit your rollback size by ussing delete from table where limit <your size>
0
 

Author Comment

by:lblinc
ID: 17038995
this worked...   thanks.

TRUNCATE `schemaName`.`tableName`;
0

Featured Post

[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
Backups and Disaster RecoveryIn this post, we’ll look at strategies for backups and disaster recovery.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses
Course of the Month10 days, 22 hours left to enroll

571 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question