种易犯的PHP数据库错误包括数据库模式设计数据库访问和使用数据库的业务逻辑代码以及它们的解决方案
如果只有一种方式使用数据库是正确的……
您可以用很多的方式创建数据库设计数据库访问和基于数据库的PHP业务逻辑代码但最终一般以错误告终本文说明了数据库设计和访问数据库的PHP代码中出现的五个常见问题以及在遇到这些问题时如何修复它们
问题直接使用MySQL
一个常见问题是较老的PHP代码直接使用mysql_函数来访问数据库清单展示了如何直接访问数据库
清单access/getphp
＜?php
functionget_user_id($name)
{
$db=mysql_connect(’localhost’’root’’passWord’);
mysql_select_db(’users’);
$res=mysql_query("SELECTidFROMusersWHERElogin=’"$name"’");
while($row=mysql_fetch_array($res)){$id=$row[];}
return$id;
}
var_dump(get_user_id(’jack’));
?＞
注意使用了mysql_connect函数来访问数据库还要注意查询其中使用字符串连接来向查询添加$name参数
该技术有两个很好的替代方案PEARDB模块和PHPDataObjects(PDO)类两者都从特定数据库选择提供抽象因此您的代码无需太多调整就可以在IBM?DB?MySQLPostgreSQL或者您想要连接到的任何其他数据库上运行
使用PEARDB模块和PDO抽象层的另一个价值在于您可以在SQL语句中使用?操作符这样做可使SQL更加易于维护且可使您的应用程序免受SQL注入攻击
使用PEARDB的替代代码如下所示
清单Access/get_goodphp
＜?php
require_once("DBphp");
functionget_user_id($name)
{
$dsn=’mysql://root:password@localhost/users’;
$db=&DB::Connect($dsnarray());
if(PEAR::isError($db)){die($db＞getMessage());}
$res=$db＞query(’SELECTidFROMusersWHERElogin=?’array($name));
$id=null;
while($res＞fetchInto($row)){$id=$row[];}
return$id;
}
var_dump(get_user_id(’jack’));
?＞
注意所有直接用到MySQL的地方都消除了只有$dsn中的数据库连接字符串除外此外我们通过?操作符在SQL中使用$name变量然后查询的数据通过query()方法末尾的array被发送进来
问题不使用自动增量功能
与大多数现代数据库一样MySQL能够在每记录的基础上创建自动增量惟一标识符除此之外我们仍然会看到这样的代码即首先运行一个SELECT语句来找到最大的id然后将该id增并找到一个新记录清单展示了一个示例坏模式
清单Badidsql
DROPTABLEIFEXISTSusers;
CREATETABLEusers(
idMEDIUMINT
loginTEXT
passwordTEXT
);
INSERTINTOusersVALUES(’jack’’pass’);
INSERTINTOusersVALUES(’joan’’pass’);
INSERTINTOusersVALUES(’jane’’pass’);
这里的id字段被简单地指定为整数所以尽管它应该是惟一的我们还是可以添加任何值如CREATE语句后面的几个INSERT语句中所示清单展示了将用户添加到这种类型的模式的PHP代码
清单Add_userphp
＜?php
require_once("DBphp");
functionadd_user($name$pass)
{
$rows=array();
$dsn=’mysql://root:password@localhost/bad_badid’;
$db=&DB::Connect($dsnarray());
if(PEAR::isError($db)){die($db＞getMessage());}
$res=$db＞query("SELECTmax(id)FROMusers");
$id=null;
while($res＞fetchInto($row)){$id=$row[];}
$id+=;
$sth=$db＞PRepare("INSERTINTOusersVALUES(???)");
$db＞execute($stharray($id$name$pass));
return$id;
}
$id=add_user(’jerry’’pass’);
var_dump($id);
?＞
add_userphp中的代码首先执行一个查询以找到id的最大值然后文件以id值加运行一个INSERT语句该代码在负载很重的服务器上会在竞态条件中失败另外它也效率低下
那么替代方案是什么呢？使用MySQL中的自动增量特性来自动地为每个插入创建惟一的ID更新后的模式如下所示
清单Goodidphp
DROPTABLEIFEXISTSusers;
CREATETABLEusers(
idMEDIUMINTNOTNULLAUTO_INCREMENT
loginTEXTNOTNULL
passwordTEXTNOTNULL
PRIMARYKEY(id)
);
INSERTINTOusersVALUES(null’jack’’pass’);
INSERTINTOusersVALUES(null’joan’’pass’);
INSERTINTOusersVALUES(null’jane’’pass’);
我们添加了NOTNULL标志来指示字段必须不能为空我们还添加了AUTO_INCREMENT标志来指示字段是自动增量的添加PRIMARYKEY标志来指示那个字段是一个id这些更改加快了速度清单展示了更新后的PHP代码即将用户插入表中
清单Add_user_goodphp
＜?php
require_once("DBphp");
functionadd_user($name$pass)
{
$dsn=’mysql://root:password@localhost/good_genid’;
$db=&DB::Connect($dsnarray());
if(PEAR::isError($db)){die($db＞getMessage());}
$sth=$db＞prepare("INSERTINTOusersVALUES(null??)");
$db＞execute($stharray($name$pass));
$res=$db＞query("SELECTlast_insert_id()");
$id=null;
while($res＞fetchInto($row)){$id=$row[];}
return$id;
}
$id=add_user(’jerry’’pass’);
var_dump($id);
?＞
现在我不是获得最大的id值而是直接使用INSERT语句来插入数据然后使用SELECT语句来检索最后插入的记录的id该代码比最初的版本及其相关模式要简单得多且效率更高
问题使用多个数据库
偶尔我们会看到一个应用程序中每个表都在一个单独的数据库中在非常大的数据库中这样做是合理的但是对于一般的应用程序则不需要这种级别的分割此外不能跨数据库执行关系查询这会影响使用关系数据库的整体思想更不用说跨多个数据库管理表会更困难了那么多个数据库应该是什么样的呢？首先您需要一些数据清单展示了分成个文件的这样的数据
清单数据库文件
Filessql:
CREATETABLEfiles(
idMEDIUMINT
user_idMEDIUMINT
nameTEXT
pathTEXT
);
Load_filessql:
INSERTINTOfilesVALUES(’testjpg’’files/testjpg’);
INSERTINTOfilesVALUES(’testjpg’’files/testjpg’);
Userssql:
DROPTABLEIFEXISTSusers;
CREATETABLEusers(
idMEDIUMINT
loginTEXT
passwordTEXT
);
Load_userssql:
INSERTINTOusersVALUES(’jack’’pass’);
INSERTINTOusersVALUES(’jon’’pass’);
在这些文件的多数据库版本中您应该将SQL语句加载到一个数据库中然后将usersSQL语句加载到另一个数据库中用于在数据库中查询与某个特定用户相关联的文件的PHP代码如下所示
清单Getfilesphp
＜?php
require_once("DBphp");
functionget_user($name)
{
$dsn=’mysql://root:password@localhost/bad_multi’;
$db=&DB::Connect($dsnarray());
if(PEAR::isError($db)){die($db＞getMessage());}
$res=$db＞query("SELECTidFROMusersWHERElogin=?"array($name));
$uid=null;
while($res＞fetchInto($row)){$uid=$row[];}
return$uid;
}
functionget_files($name)
{
$uid=get_user($name);
$rows=array();
$dsn=’mysql://root:password@localhost/bad_multi’;
$db=&DB::Connect($dsnarray());
if(PEAR::isError($db)){die($db＞getMessage());}
$res=$db＞query("SELECT*FROMfilesWHEREuser_id=?"array($uid));
while($res＞fetchInto($row)){$rows[]=$row;}
return$rows;
}
$files=get_files(’jack’);
var_dump($files);
?＞
get_user函数连接到包含用户表的数据库并检索给定用户的IDget_files函数连接到文件表并检索与给定用户相关联的文件行
做所有这些事情的一个更好办法是将数据加载到一个数据库中然后执行查询比如下面的查询
清单Getfiles_goodphp
＜?php
require_once("DBphp");
functionget_files($name)
{
$rows=array();
$dsn=’mysql://root:password@localhost/good_multi’;
$db=&DB::Connect($dsnarray());
if(PEAR::isError($db)){die($db＞getMessage());}
$res=$db＞query("SELECTfiles*FROMusersfilesWHERE
userslogin=?ANDusersid=filesuser_id"
array($name));
while($res＞fetchInto($row)){$rows[]=$row;}
return$rows;
}
$files=get_files(’jack’);
var_dump($files);
?＞
该代码不仅更短而且也更容易理解和高效我们不是执行两个查询而是执行一个查询
尽管该问题听起来有些牵强但是在实践中我们通常总结出所有的表应该在同一个数据库中除非有非常迫不得已的理由 问题不使用关系
关系数据库不同于编程语言它们不具有数组类型相反它们使用表之间的关系来创建对象之间的一到多结构这与数组具有相同的效果我在应用程序中看到的一个问题是工程师试图将数据库当作编程语言来使用即通过使用具有逗号分隔的标识符的文本字符串来创建数组请看下面的模式
清单Badsql
DROPTABLEIFEXISTSfiles;
CREATETABLEfiles(
idMEDIUMINT
nameTEXT
pathTEXT
);
DROPTABLEIFEXISTSusers;
CREATETABLEusers(
idMEDIUMINT
loginTEXT
passwordTEXT
filesTEXT
);
INSERTINTOfilesVALUES(’testjpg’’media/testjpg’);
INSERTINTOfilesVALUES(’testjpg’’media/testjpg’);
INSERTINTOusersVALUES(’jack’’pass’’’);
系统中的一个用户可以具有多个文件在编程语言中应该使用数组来表示与一个用户相关联的文件在本例中程序员选择创建一个files字段其中包含一个由逗号分隔的文件id列表要得到一个特定用户的所有文件的列表程序员必须首先从用户表中读取行然后解析文件的文本并为每个文件运行一个单独的SELECT语句该代码如下所示
清单Getphp
＜?php
require_once("DBphp");
functionget_files($name)
{
$dsn=’mysql://root:password@localhost/bad_norel’;
$db=&DB::Connect($dsnarray());
if(PEAR::isError($db)){die($db＞getMessage());}
$res=$db＞query("SELECTfilesFROMusersWHERElogin=?"array($name));
$files=null;
while($res＞fetchInto($row)){$files=$row[];}
$rows=array();
foreach(split(’’$files)as$file)
{
$res=$db＞query("SELECT*FROMfilesWHEREid=?"
array($file));
while($res＞fetchInto($row)){$rows[]=$row;}
}
return$rows;
}
$files=get_files(’jack’);
var_dump($files);
?＞
该技术很慢难以维护且没有很好地利用数据库惟一的解决方案是重新架构模式以将其转换回到传统的关系形式如下所示
清单Goodsql
DROPTABLEIFEXISTSfiles;
CREATETABLEfiles(
idMEDIUMINT
user_idMEDIUMINT
nameTEXT
pathTEXT
);
DROPTABLEIFEXISTSusers;
CREATETABLEusers(
idMEDIUMINT
loginTEXT
passwordTEXT
);
INSERTINTOusersVALUES(’jack’’pass’);
INSERTINTOfilesVALUES(’testjpg’’media/testjpg’);
INSERTINTOfilesVALUES(’testjpg’’media/testjpg’);
这里每个文件都通过user_id函数与文件表中的用户相关这可能与任何将多个文件看成数组的人的思想相反当然数组不引用其包含的对象——事实上反之亦然但是在关系数据库中工作原理就是这样的并且查询也因此要快速且简单得多清单展示了相应的PHP代码
清单Get_goodphp
＜?php
require_once("DBphp");
functionget_files($name)
{
$dsn=’mysql://root:password@localhost/good_rel’;
$db=&DB::Connect($dsnarray());
if(PEAR::isError($db)){die($db＞getMessage());}
$rows=array();
$res=$db＞query("SELECTfiles*FROMusersfilesWHEREuserslogin=?
ANDusersid=filesuser_id"array($name));
while($res＞fetchInto($row)){$rows[]=$row;}
return$rows;
}
$files=get_files(’jack’);
var_dump($files);
?＞
这里我们对数据库进行一次查询以获得所有的行代码不复杂并且它将数据库作为其原有的用途使用
问题n+模式
我真不知有多少次看到过这样的大型应用程序其中的代码首先检索一些实体（比如说客户）然后来回地一个一个地检索它们以得到每个实体的详细信息我们将其称为n+模式因为查询要执行这么多次——一次查询检索所有实体的列表然后对于n个实体中的每一个执行一次查询当n=时这还不成其为问题但是当n=或n=时呢？然后肯定会出现低效率问题清单展示了这种模式的一个例子
清单Schemasql
DROPTABLEIFEXISTSauthors;
CREATETABLEauthors(
idMEDIUMINTNOTNULLAUTO_INCREMENT
nameTEXTNOTNULL
PRIMARYKEY(id)
);
DROPTABLEIFEXISTSbooks;
CREATETABLEbooks(
idMEDIUMINTNOTNULLAUTO_INCREMENT
author_idMEDIUMINTNOTNULL
nameTEXTNOTNULL
PRIMARYKEY(id)
);
INSERTINTOauthorsVALUES(null’JackHerrington’);
INSERTINTOauthorsVALUES(null’DaveThomas’);
INSERTINTObooksVALUES(null’CodeGenerationinAction’);
INSERTINTObooksVALUES(null’PodcastingHacks’);
INSERTINTObooksVALUES(null’PHPHacks’);
INSERTINTObooksVALUES(null’PragmaticProgrammer’);
INSERTINTObooksVALUES(null’RubyonRails’);
INSERTINTObooksVALUES(null’ProgrammingRuby’);
该模式是可靠的其中没有任何错误问题在于访问数据库以找到一个给定作者的所有书籍的代码中如下所示
清单Getphp
＜?php
require_once(’DBphp’);
$dsn=’mysql://root:password@localhost/good_books’;
$db=&DB::Connect($dsnarray());
if(PEAR::isError($db)){die($db＞getMessage());}
functionget_author_id($name)
{
global$db;
$res=$db＞query("SELECTidFROMauthorsWHEREname=?"array($name));
$id=null;
while($res＞fetchInto($row)){$id=$row[];}
return$id;
}
functionget_books($id)
{
global$db;
$res=$db＞query("SELECTidFROMbooksWHEREauthor_id=?"array($id));
$ids=array();
while($res＞fetchInto($row)){$ids[]=$row[];}
return$ids;
}
functionget_book($id)
{
global$db;
$res=$db＞query("SELECT*FROMbooksWHEREid=?"array($id));
while($res＞fetchInto($row)){return$row;}
returnnull;
}
$author_id=get_author_id(’JackHerrington’);
$books=get_books($author_id);
foreach($booksas$book_id){
$book=get_book($book_id);
var_dump($book);
}
?＞
如果您看看下面的代码您可能会想“嘿这才是真正的清楚明了”首先得到作者id然后得到书籍列表然后得到有关每本书的信息的确它很清楚明了但是其高效吗？回答是否定的看看只是检索JackHerrington的书籍时要执行多少次查询一次获得id另一次获得书籍列表然后每本书执行一次查询三本书要执行五次查询！
解决方案是用一个函数来执行大量的查询如下所示
清单Get_goodphp
＜?php
require_once(’DBphp’);
$dsn=’mysql://root:password@localhost/good_books’;
$db=&DB::Connect($dsnarray());
if(PEAR::isError($db)){die($db＞getMessage());}
functionget_books($name)
{
global$db;
$res=$db＞query("SELECTbooks*FROMauthorsbooksWHEREbooksauthor_id=authorsidANDauthorsname=?"
array($name));
$rows=array();
while($res＞fetchInto($row)){$rows[]=$row;}
return$rows;
}
$books=get_books(’JackHerrington’);
var_dump($books);
?＞
现在检索列表需要一个快速单个的查询这意味着我将很可能必须具有几个这些类型的具有不同参数的方法但是实在是没有选择如果您想要具有一个扩展的PHP应用程序那么必须有效地使用数据库这意味着更智能的查询
本例的问题是它有点太清晰了通常来说这些类型的n+或n*n问题要微妙得多并且它们只有在数据库管理员在系统具有性能问题时在系统上运行查询剖析器时才会出现
