A light implementation of Session table in Mysql 5.6 using memory engine


/ Published in: SQL
Save to your folder(s)

caching session variables in Mysql using memory engine for fast client response.
note: please test before use, then use at your own risk.


Copy this code and paste it in your HTML
  1. /** @author prgrmmr.aben [at] gmail (dot) com
  2. * http://fivesnippets.blogspot.com/2014/08/a-very-light-implementation-of-session.html
  3. * please give back a small donation if you find
  4. * this little educational snippet of code useful
  5. **/
  6. CREATE TABLE IF NOT EXISTS `session` (
  7. `id` INT(11) NOT NULL AUTO_INCREMENT,
  8. /*`refUser` int(11) DEFAULT NULL COMMENT 'references user number',*/
  9. `IP` INT(39) DEFAULT NULL,
  10. `creation` datetime NOT NULL TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT 'creation time',
  11. `expiry` datetime DEFAULT NULL COMMENT 'expiry time',
  12. `secretToken` VARCHAR(10) NOT NULL,
  13. `type` enum('guest','client') NOT NULL DEFAULT 'guest',
  14. PRIMARY KEY (`id`),
  15. ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='don''t need to be a real session in memory' AUTO_INCREMENT=1 ;
  16. CREATE TABLE cachedSession ENGINE=MEMORY SELECT * FROM SESSION;
  17. CREATE DEFINER=`root`@`localhost` PROCEDURE `cacheSessions`()
  18. NO SQL
  19. BEGIN
  20. DELETE FROM cachedsession WHERE 1;
  21. INSERT INTO cachedsession SELECT * FROM SESSION WHERE `expiry`<NOW();
  22. SELECT ROW_COUNT();
  23. END$$
  24.  
  25. CREATE DEFINER=`root`@`localhost` PROCEDURE `cleanSession`()
  26. NO SQL
  27. COMMENT 'clean sessions expired @hours ago'
  28. BEGIN
  29. DELETE FROM `session` WHERE TIMESTAMPDIFF(MINUTE, expiry, NOW())>0;
  30. SELECT ROW_COUNT();
  31. END$$

Report this snippet


Comments

RSS Icon Subscribe to comments

You need to login to post a comment.