Created
December 27, 2013 01:37
-
-
Save paulferrett/8141241 to your computer and use it in GitHub Desktop.
Stored procedure to allow a efficient truncating of InnoDB tables
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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 |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
There is a TAB in line 7, which makes mysql client go crazy!!!