将PHP的session数据存储到数据库中的代码实例

一个开发环境有多个网站，需要使用不同的session，解决方案很多。不过这次也高大上一把

一个开发环境有多个网站，需要使用不同的session，解决方案很多。不过这次也高大上一把，用数据库存，方便以后扩展。

PostgreSQL版 首先是数据库的部分

--drop table php_session
create unlogged table php_session
(
 sess_id varchar(32) primary key,
 modify_time timestamp with time zone not null,
 sess_data varchar(3000) default ''
);

create index concurrently idx_php_session_modify_time on php_session(modify_time);

--set_session(id, data)
create or replace function set_session(varchar, varchar) returns void as $set_session$
 with upsert as (
 update php_session
 set modify_time = current_timestamp, sess_data = $2
 where sess_id = $1
 returning 1
 )
 insert into php_session (sess_id, modify_time, sess_data)
 select $1, current_timestamp, $2
 where not exists (
 select 1 from upsert
 );
$set_session$ language sql;

--get_session(id)
create or replace function get_session(varchar) returns varchar as $get_session$
 select sess_data from php_session where sess_id = $1
$get_session$ language sql;

--del_session
create or replace function del_session(varchar) returns void as $del_session$
 delete from php_session where sess_id = $1
$del_session$ language sql;

--gc_session
create or replace function gc_session() returns void as $del_session$
 delete from php_session where modify_time < current_timestamp - interval '30 days'
$del_session$ language sql;

然后是PHP的部分

<?php

session_set_save_handler(
 function ($savePath, $sessionName) {//open
 return true;
 },
 function () {//close
 return true;
 },
 function ($id) {//read
 $sql = "select get_session($1)";
 $stmt = pg_query_params(SESSION_CONN, $sql, array($id));
 $result = pg_fetch_row($stmt);
 
 return $result[0];
 },
 function ($id, $data) {//write
 $sql = "select set_session($1, $2)";
 pg_query_params(SESSION_CONN, $sql, array($id, $data));
 return true;
 },
 function ($id) {//destroy
 $sql = "select del_session($1)";
 pg_query_params(SESSION_CONN, $sql, array($id, $data));
 return true;
 },
 function ($maxlifetime) {//gc
 //php needn't control the global session gc 
 return true;
 }
);

register_shutdown_function('session_write_close');

?>

然后只要在session_start之前调用这个就可以了

至于SESSION_CONN，那是我定义的一个常量，表示一个指向session数据库的链接而已。

MySQL版 再总结一个针对MySQL的集成更多基础功能的例子： 表结构：

 CREATE TABLE IF NOT EXISTS `sessioninfo` (
 `sid` varchar(255) NOT NULL,
 `value` text NOT NULL,
 `expiration` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 PRIMARY KEY (`sid`)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

session信息存储到数据库的类：

class MySessionHandler implements SessionHandlerInterface {
 
 /**
 * @access private
 * @var object 数据库连接
 */
 private $_dbLink;
 /**
 * @access private
 * @var string 保存session的表名
 */
 Private $_sessionTable;
 /**
 * @access private
 * @var string session名
 */
 private $_sessionName;
 /**
 * @const 过期时间
 */
 const SESSION_EXPIRE = 10;
 
 public function __construct($dbLink, $sessionTable) {
 if(!is_object($dbLink)) {
 return false;
 }
 $this->_dbLink = $dbLink;
 $this->_sessionTable = $sessionTable;
 }
 
 /**
 * 打开
 * @access public
 * @param string $session_save_path 保存session的路径
 * @param string $session_name session名
 * @return integer
 */
 public function open($session_save_path, $session_name) {
 $this->_sessionName = $session_name;
 return 0;
 }
 
 /**
 * 关闭
 * @access public
 * @return integer
 */
 public function close() {
 return 0;
 }
 
 /**
 * 关闭session
 * @access public
 * @param string $session_id session ID
 * @return string
 */
 public function read($session_id) {
 $query = "SELECT value FROM {$this->_sessionTable} WHERE sid = {$session_id} AND UNIX_TIMESTAMP(expiration) + " . self::SESSION_EXPIRE . " > UNIX_TIMESTAMP(NOW())";
 $result = $this->_dbLink->query($query);
 if(!isset($value) || empty($value)) {
 $value = "";
 return $value;
 }
 $this->_dbLink->query("UPDATE {$this->_sessionTable} SET expiration = CURRENT_TIMESTAMP() WHERE sid = {$session_id}");
 $value = $result->fetch_array();
 $result->free();
 return $value['value'];
 }
 
 /**
 * 写入session
 * @access public
 * @param string $session_id session ID
 * @param string $session_data session data
 * @return integer
 */
 public function write($session_id, $session_data) {
 $query = "SELECT value FROM {$this->_sessionTable} WHERE sid = '{$session_id}' AND UNIX_TIMESTAMP(expiration) + " . self::SESSION_EXPIRE . " > UNIX_TIMESTAMP(NOW())";
 $result = $this->_dbLink->query($query);
 $result = $result->fetch_array();
 if(!empty($result)) {
 $result = $this->_dbLink->query("UPDATE {$this->_sessionTable} SET value = {$session_data} WHERE sid = {$session_id}");
 }
 else{
 $result = $this->_dbLink->query("INSERT INTO {$this->_sessionTable} (sid, value) VALUES ('{$session_id}', '{$session_data}')");
 }
 if($result){
 return 0;
 }
 else{
 return 1;
 } 
 }
 
 /**
 * 销魂session
 * @access public
 * @param string $session_id session ID
 * @return integer
 */
 public function destroy($session_id) {
 $result = $this->_dbLink->query("DELETE FROM {$this->_sessionTable} WHERE sid = '{$session_id}'");
 if($result){
 return 0;
 }
 else{
 return 1;
 }
 }
 
 /**
 * 垃圾回收
 * @access public
 * @param string $maxlifetime session 最长生存时间
 * @return integer
 */
 public function gc($maxlifetime) {
 $result = $this->_dbLink->query("DELETE FROM {$this->_sessionTable} WHERE UNIX_TIMESTAMP(expiration) < UNIX_TIMESTAMP(NOW()) - " . self::SESSION_EXPIRE);
 if($result){
 return 0;
 }
 else{
 return 1;
 }
 }
 
}