Recent Posts
- (09/10) Fixing Warning: the ECDSA host key for 'github.com' differs from the key for the IP addressTAGS:Web Server Admin
- (12/26) CakePHP 3 - Getting List of Column Definitions from a Table (like schema())TAGS:CakephpCake3
- (09/14) Change Order of Loaded Behaviors in CakePHP 3TAGS:Cake3CakephpWeb ProgrammingPhp
- (05/29) CakePHP 3 - Accept JSON Header Only Working When Debug FalseTAGS:Web ProgrammingCakephpCake3
- (05/23) Remove All Events from Google Calendar (Reset Calendar)TAGS:Web ProgrammingPhp
- (11/08) Google Tag Manager (GTM) Not Firing Default PageView EventTAGS:Web ProgrammingJavascriptGoogle Tag Manager
- (10/13) In PHP, how do you get __toString() Magic Method Result without calling echo?TAGS:CakephpCake3Cakephp 13PhpWeb Programming
- (11/14) Getting output from shell_exec() at all timesTAGS:Web ProgrammingWeb Server Admin
Subscribe to my feed
MainelyDesign.com Blog
Difference Between Truncate and Empty (Delete From) in Mysql
Posted on 06/23/2010 at 02:30 pm by Kevin Wentworth
Viewed 33,061 times | 1 comment
I use Navicat for administering all of my MySQL databases. It has a command that I always use- empty table. I noticed today that all along there has been a command right below it for truncate table. Hmmm. I figured this would result in the same outcome and wondered what the difference is between emptying a mysql table and truncating the same table.
Use TRUNCATE TABLE to Start Fresh
After doing some research I found out that when you truncate a MySQL table, you are actually dropping the database table and re-creating the table from its SQL create statement. This has one major implication, besides being faster (among other things), it means that all of your autoincrement fields will start over at 1. I really like that outcome- nice and tidy.
Use DELETE FROM TABLE to Not Re-Use IDs
What I usually do (at least in CakePHP) is a deleteAll, which executes a DELETE FROM mysql_table_name which clears out all of the rows in a table. The major difference- MySQL goes row by row, deleting each row, resulting in a performance hit vs. truncating the same data. The consequence of only deleting data and not re-creating the table, is that your autoincrement value will remain unchanged.
I confess, the ID thing isn't that much of a big deal. I guess I must really love sequentially numbered database tables.
Cheers,
-Kevin Wentworth
Tags for Difference Between Truncate and Empty (Delete From) in Mysql
Mysql | Database | Web Server Admin | Usage | Software | Truncate
Comments for this Posting
Sorry, comments are closed for this posting.
Please Email Kevin if you have any questions. Thanks!
Posted by photo recovery
on 31/7/10
Be careful with delete and replace:
If you run a replace on existing keys from the table A and table B references C with a forgein key constraint ON DELETE CASCADE, then table A will be updated - but table B will be emptied due to the DELETE before INSERT.