The realization of PHP save session with MySQL and the example code _php skill

Implementing the Environment:

PHP 5.4.24
MySQL 5.6.19
OS X 10.9.4/apache 2.2.26

One, code

CREATE TABLE ' Session ' (
' skey ' char (+) CHARACTER SET ASCII not NULL,
' data ' text COLLATE utf8mb4_bin,
' Expir E ' int (one) not NULL,
PRIMARY key (' Skey '),
key ' Index_session_expire ' (' expire ') USING btree
) Engine=myisam DEFAULT CHARSET=UTF8MB4 Collate=utf8mb4_bin;

<?php/* The DNS, username, password, etc. required to connect to the database are generally not changed in code, * so using the form of constants avoids the need for global references in functions.
* * Define (' Session_dns ', ' mysql:host=localhost;dbname=db;charset=utf8mb4 ');
Define (' session_usr ', ' USR ');
Define (' Session_pwd ', ' PWD ');

Define (' Session_maxlifetime ', Get_cfg_var (' session.gc_maxlifetime '));
Creating a PDO connection//persistent connection can provide better efficiency function getconnection () {try {$conn = new PDO (Session_dns, SESSION_USR, Session_pwd, Array ( Pdo::attr_persistent => TRUE, Pdo::attr_errmode => pdo::errmode_exception, Pdo::attr_emulate_prepares =>
FALSE));
return $conn;

The Open function Sessionmysqlopen ($savePath, $sessionName) {return TRUE;} for a custom session of catch (Exception $ex) {}}}
The Close function function Sessionmysqlclose () of a custom session is {return TRUE;}/* The general situation does not have an injection problem because the data submitted by the user is generally not saved directly to the session. * and SQL statements that process session data are not used more than once.
Therefore, the effectiveness of the pretreatment function can not be reflected.
* Therefore, the actual project can not be dogmatic use of the preprocessing function.
In the */* Sessionmysqlread () function, first determine whether the SessionID exists by select COUNT (*). * Since the MySQL database provides select support for Pdostatement::rowcount (), * Therefore, the actual project can be directly used rowcount() to make a judgment.
///////The Read function of the custom session adds "Expire > Time ()" to the//sql statement to avoid reading expired sessions.

function Sessionmysqlread ($sessionId) {try {$dbh = getconnection (); $time = time (); $sql = ' SELECT count (*) as ' count ' from session '. ' WHERE Skey =?
and expire >? ';
$stmt = $dbh->prepare ($sql);
$stmt->execute (Array ($sessionId, $time));
$data = $stmt->fetch (PDO::FETCH_ASSOC) [' Count '];

if ($data = 0) {return ';} $sql = ' SELECT ' data ' from ' Session '. ' WHERE ' skey ' =?
and ' expire ' > ';
$stmt = $dbh->prepare ($sql);
$stmt->execute (Array ($sessionId, $time));
$data = $stmt->fetch (PDO::FETCH_ASSOC) [' Data '];
return $data;

catch (Exception $e) {return ';}}
The Write function for a custom session//expire the data stored in the field as the current time +session lifetime, indicating that the session is invalid when the value is less than ().

function Sessionmysqlwrite ($sessionId, $data) {try {$dbh = getconnection (); $expire = time () + session_maxlifetime; $sql = ' INSERT into ' session ' (' Skey ', ' data ', ' expire ') '. ' VALUES (?,?,?) '
. ' On DUPLICATE KEY UPDATE data =?expire =? ';
$stmt = $dbh->prepare ($sql);
$stmt->execute (Array ($sessionId, $data, $expire, $data, $expire));

The catch (Exception $e) {echo $e->getmessage ();}}

The custom session's Destroy function Sessionmysqldestroy ($sessionId) {try {$dbh = getconnection ();
$sql = ' DELETE from ' session ' where skey =? ';
$stmt = $dbh->prepare ($sql);
$stmt->execute (Array ($sessionId));
return TRUE;

catch (Exception $e) {return FALSE;}}

A custom session's GC function SESSIONMYSQLGC ($lifetime) {try {$dbh = getconnection ();
$sql = ' DELETE from ' session ' WHERE expire < ';
$stmt = $dbh->prepare ($sql);
$stmt->execute Array (time ());
$DBH = NULL;
return TRUE;

catch (Exception $e) {return FALSE;}} The session ID setting function for a custom session/* Because the SID and Session_id () are invalid before session_start (), use $_get[session_name ()] and $_cookie[
Session_name ()] for testing.
* If both are empty, the session is not established and the session ID needs to be set for the new session.
* Obtaining a UUID from the MySQL database as the session ID is a better way to avoid session ID collisions. */function Sessionmysqlid () {if (filter_input(Input_get, Session_name ()) = = ' and Filter_input (Input_cookie, session_name ()) = = ') {try {$dbh = getconnection (); $s
TMT = $dbh->query (' SELECT uuid () as UUID ');
$data = $stmt->fetch (PDO::FETCH_ASSOC) [' uuid '];
$data = Str_replace ('-', ', ', $data);
session_id ($data);
return TRUE;

catch (Exception $ex) {return FALSE;}
}//session the start function, including session_start () and all previous steps. function Startsession () {session_set_save_handler (' sessionmysqlopen ', ' sessionmysqlclose ', ' sessionmysqlread ', '
Sessionmysqlwrite ', ' Sessionmysqldestroy ', ' SESSIONMYSQLGC ');
Register_shutdown_function (' Session_write_close ');
Sessionmysqlid ();
Session_Start (); }

Ii. Introduction

Using MySQL to save session, you need to save three key data: The Sessions ID, the duration data, and the period.
Given the way the session is used, there is no need to use the InnoDB engine, and the MyISAM engine can achieve better performance. If the environment allows, you can try using the memory engine.
The column that holds the session data, you can use the UTF8 or utf8mb4 character set if necessary, and the column that holds the session ID is not necessary, generally using the ASCII character set, you can save the storage cost.
The columns that save the session life cycle can be designed to suit the needs of the project. such as datetime type, timestamp type, int type. The session build time or expiration time can be saved for datetime, int types.
If necessary, you can extend the columns of the session table and modify the read and write functions to support (maintain) related columns to save information such as user names.
The current version, as long as you register a custom session maintenance function through Session_set_save_handler, you do not need to use the Session_module_name (' user ') function before it.
When the read function gets the data and returns, PHP automatically deserializes it, and don't make any changes to the data in general.
The date parameter that PHP passes to the Write function is the serialized session data, which can be saved directly, and do not make changes to the data in general.
According to the logic of this code, the PHP configuration options for session lifetime settings are no longer valid, this value can be maintained on its own, does not necessarily need to be acquired through Get_cfg_var.
Sessionmysqlid () function is to avoid large users, multiple Web servers in the case of collisions, generally, PHP automatically generated session ID can meet user requirements.
No more.

Third, demand

When users are very large and require multiple servers to provide applications, using a MySQL storage session is a relatively advantageous use of session files. For example, there is minimal storage overhead, such as avoiding the complexity of file sharing, such as better avoiding collisions, for example, better performance than session file sharing. In general, the problem with session files is almost explosive when the number of visits increases linearly, and the problem with using a database save session is linear. Well, to put it more bluntly: if your application user is not large, in fact, let PHP deal with the session itself is good, do not need to consider MySQL.