社群 學習 工具庫 休閒
搜尋
繁体中文
首頁 > php教程 > php手册 > 主體

PHP利用MySQL保存session，phpmysqlsession

WBOY
發布： 2016-06-13 09:26:32
原創
811 人瀏覽過

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 ?
     

    • 相關標籤：
    mysql資料庫
    來源：php.cn
    上一篇：PHP实现利用MySQL保存session的方法，mysqlsession 下一篇：PHP中feof()函数实例测试，phpfeof
    本網站聲明
    本文內容由網友自願投稿，版權歸原作者所有。本站不承擔相應的法律責任。如發現涉嫌抄襲或侵權的內容，請聯絡admin@php.cn
    作者最新文章
    最新問題
    匹配單字的正規表示式 我有一個腳本，我試圖將新的職位名稱與資料庫中的現有職位名稱進行配對。 SELECTa.titleASJobTitle,j.DescriptionASMatchedJobTitle,...
    來自於 2024-04-06 21:24:04
    0
    1
    606
    使用MySQL SQL查詢計算另一張表中欄位的總和 我有一個這樣的模式：具有屬性“user_id”和“username”的用戶表以及具有屬性“customer_id”（user_id的FK）和“finalPrice”的訂單表資料庫架...
    來自於 2024-04-06 19:39:29
    0
    1
    441
    MySQL：'從......中選擇文字作為<此處變數或子查詢>” 我有下表，其中包含以下數據：id文本語言1德語文本德語2英文文本英語我想要的是獲得以下格式的結果：german="deutschertext"english=&...
    來自於 2024-04-06 19:25:54
    0
    2
    529
    MySQL中如何進行分組和計數？ 我正在嘗試編寫查詢，提取發送給朋友的未刪除訊息的總數。這是表格結構的螢幕截圖：我想要實現的輸出結果如下：idmessages_count----------------------...
    來自於 2024-04-06 18:30:17
    0
    1
    353
    MySQL從多個表中取得數據 我有一個eg_design表，其中包含以下幾列：和eg_domains表，其中包含以下列：和eg_fonts表，其中包含以下幾列：現在，基於$domain_id我想獲取eg_des...
    來自於 2024-04-06 18:42:44
    0
    2
    479
    相關專題
    更多>
    熱門推薦
    熱門教學
    更多>
    相關教學
    熱門推薦
    最新課程
    最新下載
    更多>
    網站特效
    網站源碼
    網站素材
    前端模板