PHP还是比较常用的,于是我研究了一下PHP数据库文件,在这里拿出来和大家分享一下,希望对大家有用。我们会看到一个应用程序中,每个表都在一个单独的数据库中。在非常大的数据库中这样做是合理的,但是对于一般的应用程序,则不需要这种级别的分割。此外,不能跨数据库执行关系查询,这会影响使用关系数据库的整体思想,更不用说跨多个数据库管理表会更困难了。 那么,多个数据库应该是什么样的呢?首先,您需要一些数据。清单 1 展示了分成 4 个文件的这样的数据。
清单 1. PHP数据库文件
<ol class="dp-xml"> <li class="alt"><span><span>Files.sql: </span></span></li> <li class=""><span>CREATE TABLE files ( </span></li> <li class="alt"><span>id MEDIUMINT, </span></li> <li class=""><span>user_id MEDIUMINT, </span></li> <li class="alt"><span>name TEXT, </span></li> <li class=""><span>path TEXT </span></li> <li class="alt"><span>); </span></li> <li class=""><span> </span></li> <li class="alt"><span>Load_files.sql: </span></li> <li class=""><span>INSERT INTO files VALUES ( 1, 1, 'test1.jpg', 'files/test1.jpg' ); </span></li> <li class="alt"><span>INSERT INTO files VALUES ( 2, 1, 'test2.jpg', 'files/test2.jpg' ); </span></li> <li class=""><span> </span></li> <li class="alt"><span>Users.sql: </span></li> <li class=""><span>DROP TABLE IF EXISTS users; </span></li> <li class="alt"><span>CREATE TABLE users ( </span></li> <li class=""><span>id MEDIUMINT, </span></li> <li class="alt"><span>login TEXT, </span></li> <li class=""><span>password TEXT </span></li> <li class="alt"><span>); </span></li> <li class=""><span> </span></li> <li class="alt"><span>Load_users.sql: </span></li> <li class=""><span>INSERT INTO users VALUES ( 1, 'jack', 'pass' ); </span></li> <li class="alt"><span>INSERT INTO users VALUES ( 2, 'jon', 'pass' ); </span></li> </ol>
清单 2. PHP数据库文件Getfiles.php
<ol class="dp-xml"> <li class="alt"><span><span><?php </span></span></li> <li class=""><span>require_once("DB.php"); </span></li> <li class="alt"><span> </span></li> <li class=""><span>function get_user( $name ) </span></li> <li class="alt"><span>{ </span></li> <li class=""> <span>$</span><span class="attribute"><font color="#ff0000">dsn</font></span><span> = </span><span class="attribute-value"><font color="#0000ff">'mysql://root:password@localhost/bad_multi1'</font></span><span>; </span> </li> <li class="alt"> <span>$</span><span class="attribute"><font color="#ff0000">db</font></span><span> =& DB::Connect( $dsn, array() ); </span> </li> <li class=""><span>if (PEAR::isError($db)) { die($db->getMessage()); } </span></li> <li class="alt"><span> </span></li> <li class=""> <span>$</span><span class="attribute"><font color="#ff0000">res</font></span><span> = $db->query( "SELECT id FROM users WHERE </span><span class="attribute"><font color="#ff0000">login</font></span><span>=?",array( $name ) ); </span> </li> <li class="alt"> <span>$</span><span class="attribute"><font color="#ff0000">uid</font></span><span> = </span><span class="attribute-value"><font color="#0000ff">null</font></span><span>; </span> </li> <li class=""> <span>while( $res->fetchInto( $row ) ) { $</span><span class="attribute"><font color="#ff0000">uid</font></span><span> = $row[0]; } </span> </li> <li class="alt"><span> </span></li> <li class=""><span>return $uid; </span></li> <li class="alt"><span>} </span></li> <li class=""><span> </span></li> <li class="alt"><span>function get_files( $name ) </span></li> <li class=""><span>{ </span></li> <li class="alt"> <span>$</span><span class="attribute"><font color="#ff0000">uid</font></span><span> = </span><span class="attribute-value"><font color="#0000ff">get_user</font></span><span>( $name ); </span> </li> <li class=""><span> </span></li> <li class="alt"> <span>$</span><span class="attribute"><font color="#ff0000">rows</font></span><span> = </span><span class="attribute-value"><font color="#0000ff">array</font></span><span>(); </span> </li> <li class=""><span> </span></li> <li class="alt"> <span>$</span><span class="attribute"><font color="#ff0000">dsn</font></span><span> = </span><span class="attribute-value"><font color="#0000ff">'mysql://root:password@localhost/bad_multi2'</font></span><span>; </span> </li> <li class=""> <span>$</span><span class="attribute"><font color="#ff0000">db</font></span><span> =& DB::Connect( $dsn, array() ); </span> </li> <li class="alt"><span>if (PEAR::isError($db)) { die($db->getMessage()); } </span></li> <li class=""><span> </span></li> <li class="alt"> <span>$</span><span class="attribute"><font color="#ff0000">res</font></span><span> = $db->query( "SELECT * FROM files WHERE </span><span class="attribute"><font color="#ff0000">user_id</font></span><span>=?",array( $uid ) ); </span> </li> <li class=""><span>while( $res->fetchInto( $row ) ) { $rows[] = $row; } </span></li> <li class="alt"><span>return $rows; </span></li> <li class=""><span>} </span></li> <li class="alt"><span> </span></li> <li class=""> <span>$</span><span class="attribute"><font color="#ff0000">files</font></span><span> = </span><span class="attribute-value"><font color="#0000ff">get_files</font></span><span>( 'jack' ); </span> </li> <li class="alt"><span> </span></li> <li class=""><span>var_dump( $files ); </span></li> <li class="alt"><span>?> </span></li> </ol>
清单 3. Getfiles_good.php
<ol class="dp-xml"> <li class="alt"><span><span><?php </span></span></li> <li class=""><span>require_once("DB.php"); </span></li> <li class="alt"><span> </span></li> <li class=""><span>function get_files( $name ) </span></li> <li class="alt"><span>{ </span></li> <li class=""> <span>$</span><span class="attribute">rows</span><span> = </span><span class="attribute-value">array</span><span>(); </span> </li> <li class="alt"><span> </span></li> <li class=""> <span>$</span><span class="attribute">dsn</span><span> = </span><span class="attribute-value">'mysql://root:password@localhost/good_multi'</span><span>; </span> </li> <li class="alt"> <span>$</span><span class="attribute">db</span><span> =& DB::Connect( $dsn, array() ); </span> </li> <li class=""><span>if (PEAR::isError($db)) { die($db->getMessage()); } </span></li> <li class="alt"><span> </span></li> <li class=""> <span>$</span><span class="attribute">res</span><span> = $db->query("SELECT files.* FROM users, files WHERE </span> </li> <li class="alt"> <span></span><span class="attribute">users.login</span><span>=? AND </span><span class="attribute">users.id</span><span>=</span><span class="attribute-value">files</span><span>.user_id", </span> </li> <li class=""><span>array( $name ) ); </span></li> <li class="alt"><span>while( $res->fetchInto( $row ) ) { $rows[] = $row; } </span></li> <li class=""><span> </span></li> <li class="alt"><span>return $rows; </span></li> <li class=""><span>} </span></li> <li class="alt"><span> </span></li> <li class=""> <span>$</span><span class="attribute">files</span><span> = </span><span class="attribute-value">get_files</span><span>( 'jack' ); </span> </li> <li class="alt"><span> </span></li> <li class=""><span>var_dump( $files ); </span></li> <li class="alt"><span>?> </span></li> </ol>
该代码不仅更短,而且也更容易理解和高效。我们不是执行两个查询,而是执行一个查询。尽管该问题听起来有些牵强,但是在实践中我们通常总结出所有的表应该在同一个数据库中,除非有非常迫不得已的理由。