Difference between TRUNCATE and DELETE

truncate-vs-delete.sql

-- 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`;