Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Difference between TRUNCATE and DELETE
-- this was written for mysql to show how TRUNCATE does more than just DELETE all the rows
-- make sure you have a `test` database and a user with permissions to CREATE tables
DROP TABLE `test`.`test`;
CREATE TABLE `test`.`test`
(
`id` INT(11) PRIMARY KEY AUTO_INCREMENT NOT NULL,
`name` VARCHAR(32) NOT NULL
);
INSERT INTO `test`.`test` (`name`) VALUES ("pete"), ("luke");
-- this will give you 2
SELECT MAX(`id`) FROM `test`.`test`;
-- this will delete everything, the table is empty
DELETE FROM `test`.`test` WHERE 1;
INSERT INTO `test`.`test` (`name`) VALUES ("nathan"), ("kyle");
-- this will give you 4, the id keeps incrementing
SELECT MAX(`id`) FROM `test`.`test`;
TRUNCATE TABLE `test`.`test`;
INSERT INTO `test`.`test` (`name`) VALUES ("pete"), ("luke");
-- this will give you 2 gain, because truncate empties everything and resets the table
SELECT MAX(`id`) FROM `test`.`test`;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.