Using MySQL-Based Web Session Management - MySQL - page 740

CREATE TABLE php_session
(
id CHAR ( 32 ) NOT NULL ,
data LONGBLOB ,
update_time TIMESTAMP NOT NULL ,
PRIMARY KEY ( id ),
INDEX ( update_time )
);
The id column holds session identifiers, which are unique 32-character MD5 values.
The data column holds session information. PHP serializes session data into a string
before storing it, so php_session needs only a large generic string column to hold the
resulting serialized value. The update_time column is a TIMESTAMP , so MySQL updates
it automatically whenever a session record is updated. This column is not required by
PHP, but it's useful for implementing a garbage collection policy based on each session's
most recent update time.
A small number of statements suffice to manage the contents of the php_session table
as we have defined it:
• To retrieve a session's data, use a simple SELECT based on the session identifier:
SELECT data FROM php_session WHERE id = ' sess_id ' ;
• To write session data, a REPLACE updates an existing row, or creates a new one if no
such row exists:
REPLACE INTO php_session ( id , data ) VALUES ( ' sess_id ',' sess_data ' );
REPLACE also updates the timestamp in the row when creating or updating a row,
which is important for garbage collection.
Some storage manager implementations use a combination of INSERT and a fallback
to UPDATE if the INSERT fails because a row with the given session ID already exists
(or an UPDATE with a fallback to INSERT if the UPDATE fails because a row with the
ID does not exist). In MySQL, REPLACE performs the required task with a single
statement.
• To destroy a session, delete the corresponding row:
DELETE FROM php_session WHERE id = ' sess_id ' ;
• To perform garbage collection, remove old rows. The following statement deletes
rows that have a timestamp value more than sess_life seconds old:
DELETE FROM php_session
WHERE update_time < NOW () - INTERVAL sess_life SECOND ;
The PHP session manager supplies the value of sess_life when it invokes the
garbage collection routine. (The table definition for php_session indexes up
date_time to make DELETE statements faster.)