Rumah > Artikel > pembangunan bahagian belakang > PHP利用MySQL保存session,phpmysqlsession_PHP教程
实现环境:
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语句中增加了“expire > time()”判断,用以避免读取过期的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
$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 "
希望大家能通过这篇文章介绍的相关方法与技巧,可以全面掌握这一知识。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 ?