Skip to content

Instantly share code, notes, and snippets.

@paulferrett
Created December 27, 2013 01:37
Show Gist options
  • Save paulferrett/8141241 to your computer and use it in GitHub Desktop.
Save paulferrett/8141241 to your computer and use it in GitHub Desktop.
Stored procedure to allow a efficient truncating of InnoDB tables
delimiter //
DROP PROCEDURE IF EXISTS INNODB_TRUNCATE//
CREATE PROCEDURE INNODB_TRUNCATE(IN tbl CHAR(128))
BEGIN
SET @tbl_tmp = CONCAT(tbl, '_idbtrunc_tmp');
SET @tbl = tbl;
SET @v = CONCAT("DROP TABLE IF EXISTS ", @tbl_tmp);
PREPARE drop_tmp FROM @v;
EXECUTE drop_tmp;
SET @v = CONCAT("CREATE TABLE ", @tbl_tmp, " LIKE ", @tbl);
PREPARE create_tmp FROM @v;
EXECUTE create_tmp;
SET @v = CONCAT("DROP TABLE ", @tbl);
PREPARE drop_tbl FROM @v;
EXECUTE drop_tbl;
SET @v = CONCAT("RENAME TABLE ", @tbl_tmp, " TO ", @tbl);
PREPARE rename_tmp FROM @v;
EXECUTE rename_tmp;
END;
//
delimiter ;
-- Example Usage:
-- $ CALL INNODB_TRUNCATE('my_innodb_table');
-- Affected rows: 0
-- Time: 0.218ms
@trickkiste
Copy link

There is a TAB in line 7, which makes mysql client go crazy!!!

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment