Method of Saving session with MySQL by PHP

session is a variable used to save user information in the server of PHP programming, which has very wide application value. In this paper, the method of saving session by MySQL is described with an example. Share it with you for your reference. The specific steps are as follows:

The implementation environment of this example is:

PHP 5.4.24
MySQL 5.6.19
OS X 10.9.4/Apache 2.2.26

1. Code section

1. SQL statement:

CREATE TABLE `session` (
 `skey` char(32) CHARACTER SET ascii NOT NULL,
 `data` text COLLATE utf8mb4_bin,
 `expire` int(11) NOT NULL,
 PRIMARY KEY (`skey`),
 KEY `index_session_expire` (`expire`) USING BTREE
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

2. PHP partial code:

<?php
/*
 *  Required to connect to the database DNS , user name, password, etc., 1 Generally, changes will not be made in the code, 
 *  Therefore, using the form of constants can avoid the need to reference in functions global . 
 */
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'));

// Create PDO Connect 
// Persistent connections 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;
  } catch (Exception $ex) {

  }
}

// Custom session Adj. open Function 
function sessionMysqlOpen($savePath, $sessionName) {
  return TRUE;
}

// Custom session Adj. close Function 
function sessionMysqlClose() {
  return TRUE;
}
/*
 *  Due to 1 The data submitted by the user will not be saved directly to session Therefore, there is no injection problem in normal cases. 
 *  And deal with session Data SQL Statement is not used multiple times. Therefore, the benefit of pretreatment function cannot be reflected. 
 *  Therefore, it is unnecessary to use the preprocessing function dogmatically in practical engineering. 
 */
/*
 * sessionMysqlRead() Function, first pass the SELECT count(*) To judge sessionID Whether it exists. 
 *  Due to MySQL Database provision SELECT Right PDOStatement::rowCount() Support, 
 *  Therefore, it can be directly used in actual engineering rowCount() Make a judgment. 
 */
// Custom session Adj. read Function 
//SQL Statement has been added to the " expire > time() "Judgment to avoid reading expired session . 
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 '';
  }
}

// Custom session Adj. write Function 
//expire The data stored in the field is the current time +session Lifetime, when this value is less than time() Time indication session Failure. 
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));
  } catch (Exception $e) {
    echo $e->getMessage();
  }
}

// Custom session Adj. destroy Function 
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;
  }
}

// Custom session Adj. gc Function 
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;
  }
}

// Custom session Adj. session id Set function 
/*
 *  Because in session_start() Before, SID And session_id() Are invalid, 
 *  So use $_GET[session_name()] And $_COOKIE[session_name()] Test. 
 *  If both are empty, it indicates that session Not yet established, needs to be new session Settings session id . 
 *  Pass MySQL Database acquisition uuid As session id It can be better avoided session id Collision. 
 */
function sessionMysqlId() {
  if (filter_input(INPUT_GET, session_name()) == '' and
      filter_input(INPUT_COOKIE, session_name()) == '') {
    try {
      $dbh = getConnection();
      $stmt = $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 Startup function, including session_start() And all the steps before it. 
function startSession() {
  session_set_save_handler(
      'sessionMysqlOpen',
      'sessionMysqlClose',
      'sessionMysqlRead',
      'sessionMysqlWrite',
      'sessionMysqlDestroy',
      'sessionMysqlGc');
  register_shutdown_function('session_write_close');
  sessionMysqlId();
  session_start();
}

2. Introduction

1. To save session with MySQL, three key data need to be saved: session id, session data and session life cycle.

2. Given the way the session is used, there is no need to use the InnoDB engine. The MyISAM engine can achieve better performance. If the environment permits, you can try to use the MEMORY engine.

3. Columns that hold session data, using the utf8 or utf8mb4 character set if necessary; It is not necessary to save the columns of session and id. In 1 case, it is OK to use ascii character set, which can save the storage cost.

4. Columns that hold the session lifecycle can be designed according to engineering needs. For example, datetime type, timestamp type and int type. For datetime, the int type can hold the session generation time or expiration time.

5. If necessary, extend the columns of the session table and modify the read and write functions to support (maintain) related columns to hold information such as user names.

6. In the current version, it is only necessary to register the custom session maintenance function through session_set_save_handler, without using the session_module_name ('user') function before it.

7. When the read function gets the data and returns, PHP will automatically deserialize it. Please do not change the data in general.

8. The date parameter passed by PHP to write function is the serialized session data, which can be saved directly. Please do not change the data in general.

9. According to the logic of this code, the setting of session lifetime of PHP configuration option is no longer valid. This value can be maintained by itself, and it must be obtained through get_cfg_var.

10. The sessionMysqlId () function is used to avoid collision in the case of large number of users and multiple Web servers. In general, session id automatically generated by PHP can meet user requirements.

3. Needs

When the number of users is very large and multiple servers are needed to provide applications, using MySQL to store sessions has a definite advantage over using session files. For example, it has minimal storage overhead, such as avoiding the complexity brought by file sharing, such as better avoiding collisions, and better performance than session file sharing. In general, if the problems associated with using a database to hold a session grow linearly when traffic increases dramatically, then the problems associated with using a session file are almost explosive. Ok, let's put it in a more straightforward way: If you don't have a large number of application users, it's good to let PHP handle session by itself, so there is no need to consider MySQL.

4. Reference function and concept section:

1 http://cn2.php.net/manual/zh/function.session-set-save-handler.php
2 http://cn2.php.net/manual/zh/session.idpassing.php
3 http://cn2.php.net/manual/zh/pdo.connections.php
4 http://cn2.php.net/manual/zh/pdo.prepared-statements.php
5 http://dev.mysql.com/doc/refman/5.1/zh/sql-syntax.html#insert

I hope the examples described in this paper are helpful to everyone's PHP programming.