Home > Article > php教程 > PHP利用MySQL保存session，phpmysqlsession

PHP利用MySQL保存session，phpmysqlsession

WBOYOriginal: 2016-06-13 09:26:32778browse

PHP利用MySQL保存session，phpmysqlsession

实现环境：

PHP 5.4.24<span>
MySQL </span>5.6.19<span>
OS X </span>10.9.4/Apache 2.2.26

一、代码

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

<span>  1</span> <?<span>php
</span><span>  2</span> <span>/*</span>
<span>  3</span> <span> * 连接数据库所需的DNS、用户名、密码等，一般情况不会在代码中进行更改，
</span><span>  4</span> <span> * 所以使用常量的形式，可以避免在函数中引用而需要global。
</span><span>  5</span>  <span>*/</span>
<span>  6</span> <span>define</span>('SESSION_DNS', 'mysql:host=localhost;dbname=db;charset=utf8mb4'<span>);
</span><span>  7</span> <span>define</span>('SESSION_USR', 'usr'<span>);
</span><span>  8</span> <span>define</span>('SESSION_PWD', 'pwd'<span>);
</span><span>  9</span> <span>define</span>('SESSION_MAXLIFETIME', <span>get_cfg_var</span>('session.gc_maxlifetime'<span>));
</span><span> 10</span> 
<span> 11</span> <span>//</span><span>创建PDO连接
</span><span> 12</span> <span>//持久化连接可以提供更好的效率</span>
<span> 13</span> <span>function</span><span> getConnection() {
</span><span> 14</span>     <span>try</span><span> {
</span><span> 15</span>         <span>$conn</span> = <span>new</span> PDO(SESSION_DNS, SESSION_USR, SESSION_PWD, <span>array</span><span>(
</span><span> 16</span>             PDO::ATTR_PERSISTENT => <span>TRUE</span>,
<span> 17</span>             PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
<span> 18</span>             PDO::ATTR_EMULATE_PREPARES => <span>FALSE</span>
<span> 19</span> <span>        ));
</span><span> 20</span>         <span>return</span> <span>$conn</span><span>;
</span><span> 21</span>     } <span>catch</span> (<span>Exception</span> <span>$ex</span><span>) {
</span><span> 22</span> 
<span> 23</span> <span>    }
</span><span> 24</span> <span>}
</span><span> 25</span> 
<span> 26</span> <span>//</span><span>自定义的session的open函数</span>
<span> 27</span> <span>function</span> sessionMysqlOpen(<span>$savePath</span>, <span>$sessionName</span><span>) {
</span><span> 28</span>     <span>return</span> <span>TRUE</span><span>;
</span><span> 29</span> <span>}
</span><span> 30</span> 
<span> 31</span> <span>//</span><span>自定义的session的close函数</span>
<span> 32</span> <span>function</span><span> sessionMysqlClose() {
</span><span> 33</span>     <span>return</span> <span>TRUE</span><span>;
</span><span> 34</span> <span>}
</span><span> 35</span> <span>/*</span>
<span> 36</span> <span> * 由于一般不会把用户提交的数据直接保存到session，所以普通情况不存在注入问题。
</span><span> 37</span> <span> * 且处理session数据的SQL语句也不会多次使用。因此预处理功能的效益无法体现。
</span><span> 38</span> <span> * 所以，实际工程中可以不必教条的使用预处理功能。
</span><span> 39</span>  <span>*/</span>
<span> 40</span> <span>/*</span>
<span> 41</span> <span> * sessionMysqlRead()函数中，首先通过SELECT count(*)来判断sessionID是否存在。
</span><span> 42</span> <span> * 由于MySQL数据库提供SELECT对PDOStatement::rowCount()的支持，
</span><span> 43</span> <span> * 因此，实际的工程中可以直接使用rowCount()进行判断。
</span><span> 44</span>  <span>*/</span>
<span> 45</span> <span>//</span><span>自定义的session的read函数
</span><span> 46</span> <span>//SQL语句中增加了&ldquo;expire > time()&rdquo;判断，用以避免读取过期的session。</span>
<span> 47</span> <span>function</span> sessionMysqlRead(<span>$sessionId</span><span>) {
</span><span> 48</span>     <span>try</span><span> {
</span><span> 49</span>         <span>$dbh</span> =<span> getConnection();
</span><span> 50</span>         <span>$time</span> = <span>time</span><span>();
</span><span> 51</span>         
<span> 52</span>         <span>$sql</span> = 'SELECT count(*) AS `count` FROM session '
<span> 53</span>                 . 'WHERE skey = ? and expire > ?'<span>;
</span><span> 54</span>         <span>$stmt</span> = <span>$dbh</span>->prepare(<span>$sql</span><span>);
</span><span> 55</span>         <span>$stmt</span>->execute(<span>array</span>(<span>$sessionId</span>, <span>$time</span><span>));
</span><span> 56</span>         <span>$data</span> = <span>$stmt</span>->fetch(PDO::FETCH_ASSOC)['count'<span>];
</span><span> 57</span>         <span>if</span> (<span>$data</span> = 0<span>) {
</span><span> 58</span>             <span>return</span> ''<span>;
</span><span> 59</span> <span>        }
</span><span> 60</span>         
<span> 61</span>         <span>$sql</span> = 'SELECT `data` FROM `session` '
<span> 62</span>                 . 'WHERE `skey` = ? and `expire` > ?'<span>;
</span><span> 63</span>         <span>$stmt</span> = <span>$dbh</span>->prepare(<span>$sql</span><span>);
</span><span> 64</span>         <span>$stmt</span>->execute(<span>array</span>(<span>$sessionId</span>, <span>$time</span><span>));
</span><span> 65</span>         <span>$data</span> = <span>$stmt</span>->fetch(PDO::FETCH_ASSOC)['data'<span>];
</span><span> 66</span>         <span>return</span> <span>$data</span><span>;
</span><span> 67</span>     } <span>catch</span> (<span>Exception</span> <span>$e</span><span>) {
</span><span> 68</span>         <span>return</span> ''<span>;
</span><span> 69</span> <span>    }
</span><span> 70</span> <span>}
</span><span> 71</span> 
<span> 72</span> <span>//</span><span>自定义的session的write函数
</span><span> 73</span> <span>//expire字段存储的数据为当前时间+session生命期，当这个值小于time()时表明session失效。</span>
<span> 74</span> <span>function</span> sessionMysqlWrite(<span>$sessionId</span>, <span>$data</span><span>) {
</span><span> 75</span>     <span>try</span><span> {
</span><span> 76</span>         <span>$dbh</span> =<span> getConnection();
</span><span> 77</span>         <span>$expire</span> = <span>time</span>() +<span> SESSION_MAXLIFETIME;
</span><span> 78</span> 
<span> 79</span>         <span>$sql</span> = 'INSERT INTO `session` (`skey`, `data`, `expire`) '
<span> 80</span>                 . 'values (?, ?, ?) '
<span> 81</span>                 . 'ON DUPLICATE KEY UPDATE data = ?, expire = ?'<span>;
</span><span> 82</span>         <span>$stmt</span> = <span>$dbh</span>->prepare(<span>$sql</span><span>);
</span><span> 83</span>         <span>$stmt</span>->execute(<span>array</span>(<span>$sessionId</span>, <span>$data</span>, <span>$expire</span>, <span>$data</span>, <span>$expire</span><span>));
</span><span> 84</span>     } <span>catch</span> (<span>Exception</span> <span>$e</span><span>) {
</span><span> 85</span>         <span>echo</span> <span>$e</span>-><span>getMessage();
</span><span> 86</span> <span>    }
</span><span> 87</span> <span>}
</span><span> 88</span> 
<span> 89</span> <span>//</span><span>自定义的session的destroy函数</span>
<span> 90</span> <span>function</span> sessionMysqlDestroy(<span>$sessionId</span><span>) {
</span><span> 91</span>     <span>try</span><span> {
</span><span> 92</span>         <span>$dbh</span> =<span> getConnection();
</span><span> 93</span> 
<span> 94</span>         <span>$sql</span> = 'DELETE FROM `session` where skey = ?'<span>;
</span><span> 95</span>         <span>$stmt</span> = <span>$dbh</span>->prepare(<span>$sql</span><span>);
</span><span> 96</span>         <span>$stmt</span>->execute(<span>array</span>(<span>$sessionId</span><span>));
</span><span> 97</span>         <span>return</span> <span>TRUE</span><span>;
</span><span> 98</span>     } <span>catch</span> (<span>Exception</span> <span>$e</span><span>) {
</span><span> 99</span>         <span>return</span> <span>FALSE</span><span>;
</span><span>100</span> <span>    }
</span><span>101</span> <span>}
</span><span>102</span> 
<span>103</span> <span>//</span><span>自定义的session的gc函数</span>
<span>104</span> <span>function</span> sessionMysqlGc(<span>$lifetime</span><span>) {
</span><span>105</span>     <span>try</span><span> {
</span><span>106</span>         <span>$dbh</span> =<span> getConnection();
</span><span>107</span> 
<span>108</span>         <span>$sql</span> = 'DELETE FROM `session` WHERE expire < ?'<span>;
</span><span>109</span>         <span>$stmt</span> = <span>$dbh</span>->prepare(<span>$sql</span><span>);
</span><span>110</span>         <span>$stmt</span>->execute(<span>array</span>(<span>time</span><span>()));
</span><span>111</span>         <span>$dbh</span> = <span>NULL</span><span>;
</span><span>112</span>         <span>return</span> <span>TRUE</span><span>;
</span><span>113</span>     } <span>catch</span> (<span>Exception</span> <span>$e</span><span>) {
</span><span>114</span>         <span>return</span> <span>FALSE</span><span>;
</span><span>115</span> <span>    }
</span><span>116</span> <span>}
</span><span>117</span> 
<span>118</span> <span>//</span><span>自定义的session的session id设置函数</span>
<span>119</span> <span>/*</span>
<span>120</span> <span> * 由于在session_start()之前，SID和session_id()均无效，
</span><span>121</span> <span> * 故使用$_GET[session_name()]和$_COOKIE[session_name()]进行检测。
</span><span>122</span> <span> * 如果此两者均为空，则表明session尚未建立，需要为新session设置session id。
</span><span>123</span> <span> * 通过MySQL数据库获取uuid作为session id可以更好的避免session id碰撞。
</span><span>124</span>  <span>*/</span>
<span>125</span> <span>function</span><span> sessionMysqlId() {
</span><span>126</span>     <span>if</span> (filter_input(INPUT_GET, <span>session_name</span>()) == ''<span> and
</span><span>127</span>             filter_input(INPUT_COOKIE, <span>session_name</span>()) == ''<span>) {
</span><span>128</span>         <span>try</span><span> {
</span><span>129</span>             <span>$dbh</span> =<span> getConnection();
</span><span>130</span>             <span>$stmt</span> = <span>$dbh</span>->query('SELECT uuid() AS uuid'<span>);
</span><span>131</span>             <span>$data</span> = <span>$stmt</span>->fetch(PDO::FETCH_ASSOC)['uuid'<span>];
</span><span>132</span>             <span>$data</span> = <span>str_replace</span>('-', '', <span>$data</span><span>);
</span><span>133</span>             <span>session_id</span>(<span>$data</span><span>);
</span><span>134</span>             <span>return</span> <span>TRUE</span><span>;
</span><span>135</span>         } <span>catch</span> (<span>Exception</span> <span>$ex</span><span>) {
</span><span>136</span>             <span>return</span> <span>FALSE</span><span>;
</span><span>137</span> <span>        }
</span><span>138</span>         
<span>139</span> <span>    }
</span><span>140</span> <span>}
</span><span>141</span> 
<span>142</span> <span>//</span><span>session启动函数，包括了session_start()及其之前的所有步骤。</span>
<span>143</span> <span>function</span><span> startSession() {
</span><span>144</span>     <span>session_set_save_handler</span><span>(
</span><span>145</span>             'sessionMysqlOpen',
<span>146</span>             'sessionMysqlClose',
<span>147</span>             'sessionMysqlRead',
<span>148</span>             'sessionMysqlWrite',
<span>149</span>             'sessionMysqlDestroy',
<span>150</span>             'sessionMysqlGc'<span>);
</span><span>151</span>     <span>register_shutdown_function</span>('session_write_close'<span>);
</span><span>152</span> <span>    sessionMysqlId();
</span><span>153</span>     <span>session_start</span><span>();
</span><span>154</span> }

二、简介

三、需求

当用户量非常大，需要多台服务器提供应用的时候，使用MySQL存储会话相对使用会话文件具有一定的优越性。比如具有最小的存储开销，比如可以避免文件共享带来的复杂性，比如可以更好的避免发生碰撞，比如相比会话文件共享具有更好的性能。总体上来说，当访问量剧增的时候，如果使用数据库保存会话带来的问题是线性增长的，那么使用会话文件带来的问题几乎是爆炸性的。好吧，换一个更直白的说法吧：如果您的应用用户量不大，其实让PHP自己处理session就好了，没必要考虑MySQL。

四、参考

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

急PHP用户登录用mysql数据库存储session,并同时且用cookie存储的完整源程序或类

$gb_DBname="charles_friend";//数据库名称
$gb_DBuser="charles_friend";//数据库用户名称
$gb_DBpass="wxyzoui";//数据库密码
$gb_DBHOSTname="localhost";//主机的名称或是IP地址
$SESS_DBH="";
$SESS_LIFE=get_cfg_var("session.gc_maxlifetime");//得到session的最大有效期。
function sess_open($save_path,$session_name){
global $gb_DBHOSTname,$gb_DBname,$gb_DBuser,$gb_DBpass,$SESS_DBH;
if(!$SESS_DBH=mysql_pconnect($gb_DBHOSTname,$gb_DBuser,$gb_DBpass)){
echo "

MySql Error:".mysql_error()."

";
die();
}
if(!mysql_select_db($gb_DBname,$SESS_DBH)){
echo "

MySql Error:".mysql_error()."

";
die();
}
return true;
}
function sess_close(){
return true;
}
function sess_read($key){
global $SESS_DBH,$SESS_LIFE;
$qry="select value from db_session where sesskey = '$key' and expiry > ".time();
$qid=mysql_query($qry,$SESS_DBH);
if(list($value)=mysql_fetch_row($qid)){
return $value;
}
return false;
}
function sess_write($key,$val){
global $SESS_DBH,$SESS_LIFE;
$expiry=time()+$SESS_LIFE;
$value=$val;
$qry="insert into db_session values('$key',$expiry,'$value')";
$qid=mysql_query($qry,$SESS_DBH);
if(!$qid){
$qry="update db_session set expiry=$expiry, value='$value' where sesskey='$key' and expiry >".time();
$qid=mysql_query($qry,$SESS_DBH);
}
return $qid;
} ......余下全文>>

怎使用数据库实现PHP保存SESSION 详细??

希望大家能通过这篇文章介绍的相关方法与技巧，可以全面掌握这一知识。PHP $con =mysql_connection("127.0.0.1","user" , "pass"); mysql_select_db("session"); function open($save_path, $session_name) { return(true); } function close() { return(true); } function read($id) { if($result = mysql_query("select * from session where id='$id'")){ if($row = mysql_felth_row($result )) { return $row["data"]; } } else { return ""; } } function write($id, $sess_data) { if($result = mysql_query("update session set data='$sess_data' where id='$id'")) { return true; } else { return false; } } function destroy($id) { if($result = mysql_query("delete * from session where id='$id'")){ return true; } else { return false; } } function gc($maxlifetime) { return true; } session_set_save_handler("open", "close", "read", "write", "destroy", "gc"); session_start(); // proceed to use sessions normally ?

Statement：

The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn

Previous article：PHP实现利用MySQL保存session的方法，mysqlsessionNext article：PHP中feof()函数实例测试，phpfeof

See more

PHP利用MySQL保存session，phpmysqlsession