引见
运用mysql_扩大的日子已过去了,由于自PHP 5.5以来它的要领已被弃用,而且从PHP 7最先被删除。然则互联网上依然充溢着大批的旧教程,初学者只需复制/粘贴并在同享主机平台上运用旧版本的PHP即可。
假如你在PHP中运用MySQL或MariaDB,那末你如今可以挑选MySQLi或PDO。前者只是一个革新版本,支撑历程化和OOP,并增加了预处置惩罚语句,而后者是一个笼统层,许可你为它支撑的一切12个数据库驱动顺序运用一致的API。只管MySQL是PHP天下中最盛行的数据库。
理论上,我们不须要为存在的每种数据库范例都供应特定于供应商的API,由于只运用一种数据库要简朴许多。虽然对此一定有许多原理,但题目是PDO_MYSQL并没有MySQLi所具有的一切最新和最先进的特征。老实说,我不明白为何会如许,由于这将完整消弭运用特定于供应商的API的任何理由。只管如此,我想大多数人并不须要这些分外的功用,但一定有一些人须要。
PDO的上风
1.有用的猎取体式格局
2.许可直接将变量和值传递给execute
3.可以自动检测变量范例(现实发作的状况是,发送到服务器时,一切内容都被视为字符串,但被转换为准确的范例。这在预处置惩罚语句中100%有用,但在某些边沿状况下无效,比方在模仿形式中。)
4.供应一个选项,运用预处置惩罚语句自动缓冲效果
5.定名参数(只管在PDO中封闭模仿形式是无用的,由于你只能运用雷同的称号一次)
MySQLi上风
1.异步查询
2.猎取有关受影响行的更多信息的才能,比方更新具有雷同值的行(可以在PDO中作为组织函数设置完成,今后没法变动)
3.准确的数据库封闭要领
4.一次多个查询(假如在PDO中翻开模仿形式,则可以)
5.运用耐久衔接自动消灭
代码的差别
PDO和MySQLi非常类似,然则在语法上略有差别。MySQLi遵照旧式的PHP snake_case商定,而PDO运用camelCase。另外,MySQLi的要领被用作对象属性,而PDO对函数运用传统语法。
PDO和MySQLi都要求你运用两个零丁的要领来运用预处置惩罚语句,从而使事变变得复杂。不过PDO消弭了运用专用绑定函数的须要。
比方,在特定于供应商的PostgreSQL API中,你可以如许做。
下面是一个示例,演示怎样实行一个“non-prepared”查询来猎取一个包括MySQLi和PDO的关联数组,以供参考。
$arr = $mysqli->query("SELECT * FROM myTable")->fetch_all(MYSQLI_ASSOC);
$arr = $pdo->query("SELECT * FROM myTable")->fetchAll(PDO::FETCH_ASSOC);
现实上,最好的要领是运用包装器、查询生成器或ORM。虽然PDO可以直接将值绑定到execute中,但它依然不是抱负的。在我建立的类中,你可以链接一切挪用,同时传入值作为参数参数绑定。
$arr = $mysqli->query("SELECT * FROM myTable WHERE id > ?", [12])->fetchAll('assoc');
如今,在变量中以更简约的体式格局存储了全部关联数组。
建立一个新的数据库衔接
PDO
$dsn = "mysql:host=localhost;dbname=myDatabase;charset=utf8mb4";$options = [ PDO::ATTR_EMULATE_PREPARES => false, // turn off emulation mode for "real" prepared statements PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, //turn on errors in the form of exceptions PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC, //make the default fetch be an associative array];try { $pdo = new PDO($dsn, "username", "password", $options);} catch (Exception $e) { error_log($e->getMessage()); exit('Something weird happened'); //something a user can understand}
MySQLi
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);try { $mysqli = new mysqli("localhost", "username", "password", "databaseName"); $mysqli->set_charset("utf8mb4");} catch(Exception $e) { error_log($e->getMessage()); exit('Error connecting to database'); //Should be a message a typical user could understand}
插进去、更新、删除
PDO
$stmt = $pdo->prepare("INSERT INTO myTable (name, age) VALUES (?, ?)"); $stmt->execute([$_POST['name'], 29]); $stmt = null;
MySQLi
$stmt = $mysqli->prepare("UPDATE myTable SET name = ? WHERE id = ?"); $stmt->bind_param("si", $_POST['name'], $_SESSION['id']); $stmt->execute(); $stmt->close();
注重,运用PDO可以将prepare()和execute()链接起来。
猎取受影响的行数
PDO
$stmt->rowCount();
MySQLi
$stmt->affected_rows;
插进去最新的主键
注重,这两种要领都运用connection变量,而不是$stmt。
PDO
$pdo->lastInsertId();
MySQLi
$mysqli->insert_id;
获得婚配的行
PDO
在PDO中,完成这一点的唯一要领是将其设置为一个衔接选项,以变动rowCount()的行动。这意味着rowCount()将为全部数据库衔接返回婚配的行或变动的行,但不能同时返回二者。
$options = [ PDO::MYSQL_ATTR_FOUND_ROWS => true];
MySQLi
$mysqli->info;
这将输出全部字符串的信息,如:
Rows matched: 1 Changed: 0 Warnings: 0
可以如许做
preg_match_all('/(\S[^:]+): (\d+)/', $mysqli->info, $matches); $infoArr = array_combine ($matches[1], $matches[2]); var_export($infoArr);
如今可以很容易地接见这些值。注重,该值是一个字符串,因而你可以将一切值转换为int,===可以运用,也可以严厉搜检==。
['Rows matched' => '1', 'Changed' => '0', 'Warnings' => '0']
抓取
猎取关联数组
PDO
$stmt = $pdo->prepare("SELECT * FROM myTable WHERE id <= ?"); $stmt->execute([5]); $arr = $stmt->fetchAll(PDO::FETCH_ASSOC); if(!$arr) exit('No rows'); var_export($arr); $stmt = null;
MySQLi
$stmt = $mysqli->prepare("SELECT id, name, age FROM myTable WHERE name = ?"); $stmt->bind_param("s", $_POST['name']); $stmt->execute(); $arr = $stmt->get_result()->fetch_all(MYSQLI_ASSOC); if(!$arr) exit('No rows'); var_export($arr); $stmt->close();
猎取单行
PDO
$stmt = $pdo->prepare("SELECT id, name, age FROM myTable WHERE name = ?"); $stmt->execute([$_POST['name']]); $arr = $stmt->fetch(PDO::FETCH_ASSOC); if(!$arr) exit('No rows'); var_export($arr); $stmt = null;
MySQLi
$stmt = $mysqli->prepare("SELECT id, name, age FROM myTable WHERE name = ?"); $stmt->bind_param("s", $_POST['name']); $stmt->execute(); $arr = $stmt->get_result()->fetch_assoc(); if(!$arr) exit('No rows'); var_export($arr); $stmt->close();
猎取单个值(标量)
PDO
$stmt = $pdo->prepare("SELECT id, name, age FROM myTable WHERE name = ?"); $stmt->execute([$_POST['name']]); $arr = $stmt->fetch(PDO::FETCH_COLUMN); if(!$arr) exit('No rows'); var_export($arr); $stmt = null;
MySQLi
$stmt = $mysqli->prepare("SELECT id, name, age FROM myTable WHERE name = ?"); $stmt->bind_param("s", $_POST['name']); $stmt->execute(); $arr = $stmt->get_result()->fetch_row()[0]; if(!$arr) exit('No rows'); var_export($arr); $stmt->close();
猎取对象数组
PDO
class myClass {} $stmt = $pdo->prepare("SELECT name, age, weight FROM myTable WHERE name = ?"); $stmt->execute(['Joe']); $arr = $stmt->fetchAll(PDO::FETCH_CLASS, 'myClass'); if(!$arr) exit('No rows'); var_export($arr); $stmt = null;
MySQLi
class myClass {} $arr = []; $stmt = $mysqli->prepare("SELECT id, name, age FROM myTable WHERE id = ?"); $stmt->bind_param("s", $_SESSION['id']); $stmt->execute(); $result = $stmt->get_result(); while($row = $result->fetch_object('myClass')) { $arr[] = $row; } if(!$arr) exit('No rows'); var_export($arr); $stmt->close();
正如你所看到的,PDO在这里非常精彩。MySQLi没有像$mysqli_result->fetch_all(MYSQLI_OBJ)如许的东西。PDO以至更进一步,经由过程运用fetchAll(PDO::FETCH_CLASS | PDO::FETCH_PROPS_LATE, 'myClass')对它举行位元化,以处置惩罚在类组织函数以后挪用它的默许行动。可以在MySQLi中复制这类行动,然则它依赖于省略组织函数,和把戏要领 _set(),或许只在组织函数中设置它(假如它不即是默许值)。
PDO
$search = "%{$_POST['search']}%"; $stmt = $pdo->prepare("SELECT id, name, age FROM myTable WHERE name LIKE ?"); $stmt->execute([$search]); $arr = $stmt->fetchAll(); if(!$arr) exit('No rows'); var_export($arr); $stmt = null; Copy
MySQLi
$search = "%{$_POST['search']}%"; $stmt = $mysqli->prepare("SELECT id, name, age FROM myTable WHERE name LIKE ?"); $stmt->bind_param("s", $search); $stmt->execute(); $arr = $stmt->get_result()->fetch_all(MYSQLI_ASSOC); if(!$arr) exit('No rows'); var_export($arr); $stmt->close();
猎取形式
到目前为止,这是我最喜欢的PDO特征。PDO中的猎取形式非常有用,而MySQLi还没有增加它们。
猎取键/值对
PDO
$stmt = $pdo->prepare("SELECT event_name, location FROM events WHERE id < ?"); $stmt->execute([25]); $arr = $stmt->fetchAll(PDO::FETCH_KEY_PAIR); if(!$arr) exit('No rows'); var_export($arr); $stmt = null; Copy
MySQLi
$arr = []; $id = 25; $stmt = $con->prepare("SELECT event_name, location FROM events WHERE id < ?"); $stmt->bind_param("i", $id); $stmt->execute(); $result = $stmt->get_result(); while($row = $result->fetch_row()) { $arr[$row[0]] = $row[1]; } if(!$arr) exit('No rows'); var_export($arr); $stmt->close();
输出:
['Cool Event' => 'Seattle', 'Fun Event' => 'Dallas', 'Boring Event' => 'Chicago']
猎取组列
PDO
$stmt = $pdo->prepare("SELECT hair_color, name FROM myTable WHERE id < ?"); $stmt->execute([10]); $arr = $stmt->fetchAll(PDO::FETCH_GROUP | PDO::FETCH_COLUMN); if(!$arr) exit('No rows'); var_export($arr); $stmt = null; Copy
MySQLi
$arr = []; $id = 10; $stmt = $con->prepare("SELECT hair_color, name FROM myTable WHERE id < ?"); $stmt->bind_param("i", $id); $stmt->execute(); $result = $stmt->get_result(); while($row = $result->fetch_row()) { $arr[$row[0]][] = $row[1]; } if(!$arr) exit('No rows'); var_export($arr); $stmt->close(); Copy
输出:
[ 'blonde' => ['Patrick', 'Olivia'], 'brunette' => ['Kyle', 'Ricky'], 'red' => ['Jordan', 'Eric'] ]
猎取键/值对数组
PDO
$stmt = $pdo->prepare("SELECT id, max_bench, max_squat FROM myTable WHERE weight < ?"); $stmt->execute([200]); $arr = $stmt->fetchAll(PDO::FETCH_UNIQUE); if(!$arr) exit('No rows'); var_export($arr); $stmt = null; Copy
MySQLi
$arr = []; $weight = 200; $stmt = $con->prepare("SELECT id, max_bench, max_squat FROM myTable WHERE weight < ?"); $stmt->bind_param("i", $weight); $stmt->execute(); $result = $stmt->get_result(); $firstColName = $result->fetch_field_direct(0)->name; while($row = $stmtResult->fetch_assoc()) { $firstColVal = $row[$firstColName]; unset($row[$firstColName]); $arr[$firstColVal] = $row; } if(!$arr) exit('No rows'); var_export($arr); $stmt->close(); Copy
输出:
[ 17 => ['max_bench' => 230, 'max_squat' => 175], 84 => ['max_bench' => 195, 'max_squat' => 235], 136 => ['max_bench' => 135, 'max_squat' => 285] ]
猎取组
PDO
$stmt = $pdo->prepare("SELECT hair_color, name, age FROM myTable WHERE id < ?"); $stmt->execute([12]); $arr = $stmt->fetchAll(PDO::FETCH_GROUP); if(!$arr) exit('No rows'); var_export($arr); $stmt = null; Copy
MySQLi
$arr = []; $id = 12; $stmt = $con->prepare("SELECT hair_color, name, age FROM myTable WHERE id < ?"); $stmt->bind_param("i", $id); $stmt->execute(); $result = $stmt->get_result(); $firstColName = $result->fetch_field_direct(0)->name; while($row = $stmtResult->fetch_assoc()) { $firstColVal = $row[$firstColName]; unset($row[$firstColName]); $arr[$firstColVal][] = $row; } if(!$arr) exit('No rows'); var_export($arr); $stmt->close(); Copy
输出:
[ 'blonde' => [ ['name' => 'Patrick', 'age' => 22], ['name' => 'Olivia', 'age' => 18] ], 'brunette' => [ ['name' => 'Kyle', 'age'=> 25], ['name' => 'Ricky', 'age' => 34] ], 'red' => [ ['name' => 'Jordan', 'age' => 17], ['name' => 'Eric', 'age' => 52] ] ]
在数组中的位置
PDO
$inArr = [1, 3, 5]; $clause = implode(',', array_fill(0, count($inArr), '?')); $stmt = $pdo->prepare("SELECT * FROM myTable WHERE id IN ($clause)"); $stmt->execute($inArr); $resArr = $stmt->fetchAll(); if(!$resArr) exit('No rows'); var_export($resArr); $stmt = null; Copy
MySQLi
$inArr = [12, 23, 44]; $clause = implode(',', array_fill(0, count($inArr), '?')); / $types = str_repeat('i', count($inArr)); / $stmt = $mysqli->prepare("SELECT id, name FROM myTable WHERE id IN ($clause)"); $stmt->bind_param($types, ...$inArr); $stmt->execute(); $resArr = $stmt->get_result()->fetch_all(MYSQLI_ASSOC); if(!$resArr) exit('No rows'); var_export($resArr); $stmt->close();
与其他占位符一同分列的位置
PDO
$inArr = [1, 3, 5]; $clause = implode(',', array_fill(0, count($inArr), '?')); $stmt = $pdo->prepare("SELECT * FROM myTable WHERE id IN ($clause) AND id < ?"); $fullArr = array_merge($inArr, [5]); $stmt->execute($fullArr); $resArr = $stmt->fetchAll(); if(!$resArr) exit('No rows'); var_export($resArr); $stmt = null; Copy
MySQLi
$inArr = [12, 23, 44]; $clause = implode(',', array_fill(0, count($inArr), '?')); $types = str_repeat('i', count($inArr)); $types .= 'i'; //add 1 more int type $fullArr = array_merge($inArr, [26]); $stmt = $mysqli->prepare("SELECT id, name FROM myTable WHERE id IN ($clause) AND age > ?"); $stmt->bind_param($types, ...$fullArr); $stmt->execute(); $resArr = $stmt->get_result()->fetch_all(MYSQLI_ASSOC); if(!$resArr) exit('No rows'); var_export($resArr); $stmt->close();
生意业务
PDO
try { $pdo->beginTransaction(); $stmt1 = $pdo->prepare("INSERT INTO myTable (name, state) VALUES (?, ?)"); $stmt2 = $pdo->prepare("UPDATE myTable SET age = ? WHERE id = ?"); if(!$stmt1->execute(['Rick', 'NY'])) throw new Exception('Stmt 1 Failed'); else if(!$stmt2->execute([27, 139])) throw new Exception('Stmt 2 Failed'); $stmt1 = null; $stmt2 = null; $pdo->commit(); } catch(Exception $e) { $pdo->rollback(); throw $e; }
MySQLi
try { $mysqli->autocommit(FALSE); $stmt1 = $mysqli->prepare("INSERT INTO myTable (name, age) VALUES (?, ?)"); $stmt2 = $mysqli->prepare("UPDATE myTable SET name = ? WHERE id = ?"); $stmt1->bind_param("si", $_POST['name'], $_POST['age']); $stmt2->bind_param("si", $_POST['name'], $_SESSION['id']); $stmt1->execute(); $stmt2->execute(); $stmt1->close(); $stmt2->close(); $mysqli->autocommit(TRUE); } catch(Exception $e) { $mysqli->rollback(); throw $e; }
MySQLi有一个题目,然则解决方案是运用全局处置惩罚顺序将毛病转换为非常。
定名为Paramters
$stmt = $pdo->prepare("UPDATE myTable SET name = :name WHERE id = :id"); $stmt->execute([':name' => 'David', ':id' => 3]); $stmt = null;
相干引荐:《mysql教程》《PHP教程》
以上就是PDO与MySQLi:PHP数据库API之战的细致内容,更多请关注ki4网别的相干文章!