PHP 的MySQL连接当用PHP访问数据库时，除了PHP自带的数据库驱动，我们一般还有两种比较好的选择：PDO和MySQLi。注意：自PHP5.5开始，传统的mysql扩展已经废弃，只能使用PHP提
PHP 的MySQL连接
当用PHP访问数据库时，除了PHP自带的数据库驱动，我们一般还有两种比较好的选择：PDO和MySQLi。
注意：自PHP5.5开始，传统的mysql扩展已经废弃，只能使用PHP提供的MySQLi扩展或PDO扩展
PDO和MySQLi区别
| PDO | MySQLi | |
|---|---|---|
| Database support | 12 different drivers | MySQL only |
| API | OOP | OOP + procedural |
| Connection | Easy | Easy |
| Named parameters | Yes | No |
| Object mapping | Yes | Yes |
| Prepared statements (client side) | Yes | No |
| Performance | Fast | Fast |
| Stored procedures | Yes | Yes |
如果你的项目需要在多种数据库中切换，建议使用 PDO;若选用MySQLi,在多种数据库中切换时候，你需要重新编写所有代码。
MySQLi专门针对MySQL设计的，所以MySQLi相对于PDO性能稍微好一些。
PHP 连接MySQL
MySQLi - 面向对象的连接方法
<?php$servername = "localhost";$username = "username";$password = "password";// 创建连接$conn = new mysqli($servername, $username, $password);// 检测连接if ($conn->connect_error) { die("连接失败: " . $conn->connect_error);}echo "连接成功";?>MySQLi - 面向过程的连接方法
<?php$servername = "localhost";$username = "username";$password = "password";// 创建连接$conn = mysqli_connect($servername, $username, $password);// 检测连接if (!$conn) { die("Connection failed: " . mysqli_connect_error());}echo "连接成功";?PDO 的连接方法
<?php$servername = "localhost";$username = "username";$password = "password";try { $conn = new PDO("mysql:host=$servername;dbname=myDB", $username, $password); echo "连接成功";}catch(PDOException $e){ echo $e->getMessage();}?>关闭连接
连接在脚本执行完后会自动关闭。可以使用以下代码来关闭连接：
MySQLi - 面向对象的关闭连接方法
$conn->close();MySQLi - 面向过程的关闭连接方法
mysqli_close($conn);PDO关闭连接的方法
$conn = null;创建数据库
使用 MySQLi 和 PDO 创建 MySQL 数据库
使用CREATE DATABASE语句创建数据库
MySQLi - 使用面向对象的方式创建数据库
<?php$servername = "localhost";$username = "username";$password = "password";// 创建连接$conn = new mysqli($servername, $username, $password);// 检测连接if ($conn->connect_error) { die("连接失败: " . $conn->connect_error);}// 创建数据库$sql = "CREATE DATABASE myDB";if ($conn->query($sql) === TRUE) { echo "数据库创建成功";} else { echo "Error creating database: " . $conn->error;}$conn->close();?>**MySQLi-使用面向过程的方式创建数据库 **
<?php$servername = "localhost";$username = "username";$password = "password";// 创建连接$conn = mysqli_connect($servername, $username, $password);// 检测连接if (!$conn) { die("连接失败: " . mysqli_connect_error());}// 创建数据库$sql = "CREATE DATABASE myDB";if (mysqli_query($conn, $sql)) { echo "数据库创建成功";} else { echo "Error creating database: " . mysqli_error($conn);}mysqli_close($conn);?>PDO 创建数据库
<?php$servername = "localhost";$username = "username";$password = "password";try { $conn = new PDO("mysql:host=$servername;dbname=myDB", $username, $password); // 设置 PDO 错误模式为异常 $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); $sql = "CREATE DATABASE myDBPDO"; // 使用 exec() ，因为没有结果返回 $conn->exec($sql); echo "数据库创建成功<br>";}catch(PDOException $e){ echo $sql . "<br>" . $e->getMessage();}$conn = null;?>创建数据表
使用CREATE TABLE语句创建数据表
我们将创建一个名为 "MyGuests" 的表，有 5 个列： "id", "firstname", "lastname", "email" 和 "reg_date":
CREATE TABLE MyGuests (id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,firstname VARCHAR(30) NOT NULL,lastname VARCHAR(30) NOT NULL,email VARCHAR(50),reg_date TIMESTAMP)MySQLi - 使用面向对象的方式创建数据表
<?php$servername = "localhost";$username = "username";$password = "password";$dbname = "myDB";// 创建连接$conn = new mysqli($servername, $username, $password, $dbname);// 检测连接if ($conn->connect_error) { die("连接失败: " . $conn->connect_error);}// 使用 sql 创建数据表$sql = "CREATE TABLE MyGuests ( id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY, firstname VARCHAR(30) NOT NULL, lastname VARCHAR(30) NOT NULL, email VARCHAR(50), reg_date TIMESTAMP )"; if ($conn->query($sql) === TRUE) { echo "Table MyGuests created successfully";} else { echo "创建数据表错误: " . $conn->error;}$conn->close();?>MySQLi - 使用面向过程的方式创建数据表
<?php$servername = "localhost";$username = "username";$password = "password";$dbname = "myDB";// 创建连接$conn = mysqli_connect($servername, $username, $password, $dbname);// 检测连接if (!$conn) { die("连接失败: " . mysqli_connect_error());}// 使用 sql 创建数据表$sql = "CREATE TABLE MyGuests ( id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY, firstname VARCHAR(30) NOT NULL, lastname VARCHAR(30) NOT NULL, email VARCHAR(50), reg_date TIMESTAMP )";if (mysqli_query($conn, $sql)) { echo "数据表 MyGuests 创建成功";} else { echo "创建数据表错误: " . mysqli_error($conn);}mysqli_close($conn);?>PDO 创建数据表
<?php$servername = "localhost";$username = "username";$password = "password";$dbname = "myDBPDO";try { $conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password); // 设置 PDO 错误模式，用于抛出异常 $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); // 使用 sql 创建数据表 $sql = "CREATE TABLE MyGuests ( id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY, firstname VARCHAR(30) NOT NULL, lastname VARCHAR(30) NOT NULL, email VARCHAR(50), reg_date TIMESTAMP )"; // 使用 exec() ，没有结果返回 $conn->exec($sql);echo "数据表 MyGuests 创建成功";}catch(PDOException $e){ echo $sql . "<br>" . $e->getMessage();}$conn = null;?>插入数据
插入语句
INSERT INTO table_name (column1, column2, column3,...)VALUES (value1, value2, value3,...)MySQLi - 使用面向对象方式插入数据
<?php$servername = "localhost";$username = "username";$password = "password";$dbname = "myDB";// 创建连接$conn = new mysqli($servername, $username, $password, $dbname);// 检测连接if ($conn->connect_error) { die("连接失败: " . $conn->connect_error);}$sql = "INSERT INTO MyGuests (firstname, lastname, email) VALUES ('John', 'Doe', '[email protected]')"; if ($conn->query($sql) === TRUE) { echo "新记录插入成功";} else { echo "Error: " . $sql . "<br>" . $conn->error;}$conn->close();?>MySQLi - 使用面向过程方式插入数据
<?php$servername = "localhost";$username = "username";$password = "password";$dbname = "myDB";// 创建连接$conn = mysqli_connect($servername, $username, $password, $dbname);// 检测连接if (!$conn) { die("Connection failed: " . mysqli_connect_error());}$sql = "INSERT INTO MyGuests (firstname, lastname, email) VALUES ('John', 'Doe', '[email protected]')";if (mysqli_query($conn, $sql)) { echo "新记录插入成功";} else { echo "Error: " . $sql . "<br>" . mysqli_error($conn);}mysqli_close($conn);?>POD 插入数据
<?php$servername = "localhost";$username = "username";$password = "password";$dbname = "myDBPDO";try { $conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password); // 设置 PDO 错误模式，用于抛出异常 $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); $sql = "INSERT INTO MyGuests (firstname, lastname, email) VALUES ('John', 'Doe', '[email protected]')"; // 使用 exec() ，没有结果返回 $conn->exec($sql); echo "新记录插入成功";}catch(PDOException $e) { echo $sql . "<br>" . $e->getMessage();}$conn = null;?>插入多条数据
MySQLi - 使用面向对象方式插入多条数据
<?php$servername = "localhost";$username = "username";$password = "password";$dbname = "myDB";// 创建链接$conn = new mysqli($servername, $username, $password, $dbname);// 检查链接if ($conn->connect_error) { die("连接失败: " . $conn->connect_error);}$sql = "INSERT INTO MyGuests (firstname, lastname, email) VALUES ('John', 'Doe', '[email protected]');";$sql .= "INSERT INTO MyGuests (firstname, lastname, email) VALUES ('Mary', 'Moe', '[email protected]');";$sql .= "INSERT INTO MyGuests (firstname, lastname, email) VALUES ('Julie', 'Dooley', '[email protected]')"; if ($conn->multi_query($sql) === TRUE) { echo "新记录插入成功";} else { echo "Error: " . $sql . "<br>" . $conn->error;} $conn->close();?>MySQLi - 使用面向过程方式插入多条数据
<?php$servername = "localhost";$username = "username";$password = "password";$dbname = "myDB";// 创建链接$conn = mysqli_connect($servername, $username, $password, $dbname);// 检查链接if (!$conn) { die("连接失败: " . mysqli_connect_error());}$sql = "INSERT INTO MyGuests (firstname, lastname, email) VALUES ('John', 'Doe', '[email protected]');";$sql .= "INSERT INTO MyGuests (firstname, lastname, email) VALUES ('Mary', 'Moe', '[email protected]');";$sql .= "INSERT INTO MyGuests (firstname, lastname, email) VALUES ('Julie', 'Dooley', '[email protected]')";if (mysqli_multi_query($conn, $sql)) { echo "新记录插入成功";} else { echo "Error: " . $sql . "<br>" . mysqli_error($conn);}mysqli_close($conn);?>PDO 插入多条数据
<?php$servername = "localhost";$username = "username";$password = "password";$dbname = "myDBPDO";try { $conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password); // set the PDO error mode to exception $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); // 开始事务 $conn->beginTransaction(); // SQL 语句 $conn->exec("INSERT INTO MyGuests (firstname, lastname, email) VALUES ('John', 'Doe', '[email protected]')"); $conn->exec("INSERT INTO MyGuests (firstname, lastname, email) VALUES ('Mary', 'Moe', '[email protected]')"); $conn->exec("INSERT INTO MyGuests (firstname, lastname, email) VALUES ('Julie', 'Dooley', '[email protected]')"); // 提交事务 $conn->commit(); echo "新记录插入成功"; }catch(PDOException $e){ // 如果执行失败回滚 $conn->rollback(); echo $sql . "<br>" . $e->getMessage();}$conn = null;?>使用预处理语句插入多条数据
<?php$servername = "localhost";$username = "username";$password = "password";$dbname = "myDB";// 创建连接$conn = new mysqli($servername, $username, $password, $dbname);// 检测连接if ($conn->connect_error) { die("连接失败: " . $conn->connect_error);} else { $sql = "INSERT INTO MyGuests VALUES(?, ?, ?)"; // 为 mysqli_stmt_prepare() 初始化statement 对象 $stmt = mysqli_stmt_init($conn); //预处理语句 if (mysqli_stmt_prepare($stmt, $sql)) { // 绑定参数 mysqli_stmt_bind_param($stmt, 'sss', $firstname, $lastname, $email); // 设置参数并执行 $firstname = 'John'; $lastname = 'Doe'; $email = '[email protected]'; mysqli_stmt_execute($stmt); $firstname = 'Mary'; $lastname = 'Moe'; $email = '[email protected]'; mysqli_stmt_execute($stmt); $firstname = 'Julie'; $lastname = 'Dooley'; $email = '[email protected]'; mysqli_stmt_execute($stmt); }}?>参数绑定
mysqli_stmt_bind_param($stmt, 'sss', $firstname, $lastname, $email);该函数绑定参数查询并将参数传递给数据库。第二个参数是 "sss" 。以下列表展示了参数的类型。
- i - 整数
- d - 双精度浮点数
- s - 字符串
- b - 布尔值
预处理语句
预处理：创建 SQL 语句模板并发送到数据库。预留的值使用参数 '?' 标记 。数据库解析，编译，对SQL语句模板执行查询优化，并存储结果不输出。最后，将应用绑定的值传递给参数（"?" 标记），数据库执行语句
相对直接执行SQL语句，预处理的优点：
- 预处理语句大大减少了分析时间，只做了一次查询（虽然语句多次执行）。
- 绑定参数减少了服务器带宽，你只需要发送查询的参数，而不是整个语句。
- 预处理语句针对SQL注入是非常有用的，因为参数值发送后使用不同的协议，保证了数据的合法性。
MySQLi 使用预处理语句
<?php$servername = "localhost";$username = "username";$password = "password";$dbname = "myDB";// 创建连接$conn = new mysqli($servername, $username, $password, $dbname);// 检测连接if ($conn->connect_error) { die("连接失败: " . $conn->connect_error);}// 预处理及绑定$stmt = $conn->prepare("INSERT INTO MyGuests (firstname, lastname, email) VALUES(?, ?, ?)");$stmt->bind_param("sss", $firstname, $lastname, $email);// 设置参数并执行$firstname = "John";$lastname = "Doe";$email = "[email protected]";$stmt->execute();$firstname = "Mary";$lastname = "Moe";$email = "[email protected]";$stmt->execute();$firstname = "Julie";$lastname = "Dooley";$email = "[email protected]";$stmt->execute();echo "新记录插入成功";$stmt->close();$conn->close();?>PDO 使用预处理语句
<?php$servername = "localhost";$username = "username";$password = "password";$dbname = "myDBPDO";try { $conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password); // 设置 PDO 错误模式为异常 $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); // 预处理 SQL 并绑定参数 $stmt = $conn->prepare("INSERT INTO MyGuests (firstname, lastname, email) VALUES (:firstname, :lastname, :email)"); $stmt->bindParam(':firstname', $firstname); $stmt->bindParam(':lastname', $lastname); $stmt->bindParam(':email', $email); // 插入行 $firstname = "John"; $lastname = "Doe"; $email = "[email protected]"; $stmt->execute(); // 插入其他行 $firstname = "Mary"; $lastname = "Moe"; $email = "[email protected]"; $stmt->execute(); // 插入其他行 $firstname = "Julie"; $lastname = "Dooley"; $email = "[email protected]"; $stmt->execute(); echo "新记录插入成功"; }catch(PDOException $e) { echo $sql . "<br>" . $e->getMessage();}$conn = null;?>查询数据
MySQLi -使用面向对象方式查询数据
<?php$servername = "localhost";$username = "username";$password = "password";$dbname = "myDB";// 创建连接$conn = new mysqli($servername, $username, $password, $dbname);// 检测连接if ($conn->connect_error) { die("连接失败: " . $conn->connect_error);} $sql = "SELECT id, firstname, lastname FROM MyGuests";$result = $conn->query($sql);if ($result->num_rows > 0) { // 输出每行数据 while($row = $result->fetch_assoc()) { echo "<br> id: ". $row["id"]. " - Name: ". $row["firstname"]. " " . $row["lastname"]; }} else { echo "0 个结果";}$conn->close();?>PDO 查询数据
<?phpecho "<table style='border: solid 1px black;'>";echo "<tr><th>Id</th><th>Firstname</th><th>Lastname</th><th>Email</th><th>Reg date</th> </tr>";class TableRows extends RecursiveIteratorIterator { function __construct($it) { parent::__construct($it, self::LEAVES_ONLY); } function current() { return "<td style='width: 150px; border: 1px solid black;'>" . parent::current(). "</td>"; } function beginChildren() { echo "<tr>"; } function endChildren() { echo "</tr>" . "/n"; } }$servername = "localhost";$username = "username";$password = "password";$dbname = "myDBPDO";try { $conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password); $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); $stmt = $conn->prepare("SELECT * FROM MyGuests"); $stmt->execute(); // 设置结果集为关联数组 $result = $stmt->setFetchMode(PDO::FETCH_ASSOC); foreach(new TableRows(new RecursiveArrayIterator($stmt->fetchAll())) as $k=>$v) { echo $v; } $dsn = null; }catch(PDOException $e){ echo "Error: " . $e->getMessage();}$conn = null;echo "</table>";?>使用 WHERE 子句
WHERE 子句用于提取满足指定标准的的记录。
语法：
SELECT column_name(s)FROM table_nameWHERE column_name operator value例子：
<?php$con=mysqli_connect("localhost","username","password","database");// 检测连接if (mysqli_connect_errno()){ echo "连接失败: " . mysqli_connect_error();}$sql = "SELECT * FROM Persons WHERE FirstName='Peter'";$result = mysqli_query($con,$sql);while($row = mysqli_fetch_array($result)) { echo $row['FirstName'] . " " . $row['LastName']; echo "<br>";}?>ORDER BY 关键词
ORDER BY 关键词用于对记录集中的数据进行排序，默认为升序。
语法：
SELECT column_name(s)FROM table_nameORDER BY column_name(s) ASC|DESC例子：
<?php$con=mysqli_connect("localhost","username","password","database");// 检测连接if (mysqli_connect_errno()){ echo "连接失败: " . mysqli_connect_error();}$sql = "SELECT * FROM Persons ORDER BY age";$result = mysqli_query($con,$sql);while($row = mysqli_fetch_array($result)){ echo $row['FirstName']; echo " " . $row['LastName']; echo " " . $row['Age']; echo "<br>";}mysqli_close($con);?>根据两列进行排序
可以根据多个列进行排序.
当按照多个列进行排序时，只有第一列的值相同时才使用第二列：
SELECT column_name(s)FROM table_nameORDER BY column1, column2Update 更新数据
UPDATE 语句用于更新数据库表中已存在的记录。
语法：
UPDATE table_nameSET column1=value, column2=value2,...WHERE some_column=some_value注意：如果省去 WHERE 子句，所有的记录都会被更新！
假设已经创建了“Person表”：
| FirstName | LastName | Age |
|---|---|---|
| Peter | Griffin | 35 |
| Glenn | Quagmire | 33 |
对Person表更新
<?php$con=mysqli_connect("localhost","username","password","database");// 检测连接if (mysqli_connect_errno()){ echo "连接失败: " . mysqli_connect_error();}$sql = "UPDATE Persons SET Age=36 WHERE FirstName='Peter' AND LastName='Griffin'";mysqli_query($con,$sql);mysqli_close($con);?>更新后：
| FirstName | LastName | Age |
|---|---|---|
| Peter | Griffin | 36 |
| Glenn | Quagmire | 33 |
Delete 删除数据
DELETE 语句用于从数据库表中删除行。
语法：
DELETE FROM table_nameWHERE some_column = some_value注意：如果省去 WHERE 子句，所有的记录都会被删除！
假设有表Person
| FirstName | LastName | Age |
|---|---|---|
| Peter | Griffin | 35 |
| Glenn | Quagmire | 33 |
下面的例子删除 "Persons" 表中所有 LastName='Griffin' 的记录：
<?php$con=mysqli_connect("localhost","username","password","database");// 检测连接if (mysqli_connect_errno()){ echo "连接失败: " . mysqli_connect_error();}$sql = "DELETE FROM Persons WHERE LastName='Griffin'";mysqli_query($con,$sql);mysqli_close($con);?>删除数据后：
| FirstName | LastName | Age |
|---|---|---|
| Glenn | Quagmire | 33 |
参考
- 数据库扩展
- MySQL Drivers and Plugins
- PDO和MySQLi区别与选择?
- PHP数据库
