PHP连接MySQL的几种方式及推荐

发表于 更新于 分类于
PHP连接MySQL的几种方式及推荐

前言

可参考文档：

https://www.runoob.com/php/php-mysql-intro.html

本文使用的是PHP7.3版本。

面向对象/面向过程/PDO

最先有的是面向过程的方式，后来学习其他语言添加的面向对象，而PDO是面向对象方式对所有数据库的一种封装。

PDO是PHP数据对象,PHP Data Object的缩写。

统一API能操作各种数据库，这样切换数据库不用修改代码了。

MySQLi和PDO API是在MySQL 4.1版本之后引入的，因此只有MySQL 5.0及以上版本才支持这两种API。而PHP7对MySQLi和PDO API的支持是基于这两种API的实现的，因此只有MySQL 5.5及以上版本的API才能与PHP7兼容。

结论

推荐使用PDO。

PDO方式

查询列表

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
<?php
$dbms = 'mysql'; //数据库类型
$host = 'localhost'; //数据库主机名
$dbName = 'zdb'; //使用的数据库
$user = 'root'; //数据库连接用户名
$pass = 'root'; //对应的密码
$dsn = "$dbms:host=$host;dbname=$dbName";

$userlist = array();
try {
  $dbh = new PDO($dsn, $user, $pass); //初始化一个PDO对象
  foreach ($dbh->query('SELECT * from t_user limit 10') as $row) {
    array_push($userlist, $row);
  }
  $dbh = null;

} catch (PDOException $e) {
  die("Error!: " . $e->getMessage() . "<br/>");
}
echo json_encode($userlist);
?>

插入单条数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
<?php
$dbms = 'mysql'; //数据库类型
$host = 'localhost'; //数据库主机名
$dbName = 'zdb'; //使用的数据库
$user = 'root'; //数据库连接用户名
$pass = 'root'; //对应的密码
$dsn = "$dbms:host=$host;dbname=$dbName";

try {
  $conn = new PDO($dsn, $user, $pass);
  // 设置 PDO 错误模式，用于抛出异常
  $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
  $sql = "INSERT INTO t_user (name, age) VALUES ('John', 15)";
  $conn->exec($sql);
  echo "新记录插入成功";
} catch (PDOException $e) {
  echo $sql . "<br>" . $e->getMessage();
}
$conn = null;
?>

插入多条数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
<?php
$servername = "localhost";
$username = "root";
$password = "root";
$dbname = "zdb";

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 t_user (name, age) VALUES ('John', 10);");
  $conn->exec("INSERT INTO t_user (name, age) VALUES ('Mary', 20);");
  $conn->exec("INSERT INTO t_user (name, age) VALUES ('Julie', 30)");

  // 提交事务
  $conn->commit();
  echo "新记录插入成功";
} catch (PDOException $e) {
  // 如果执行失败回滚
  $conn->rollback();
  echo $sql . "<br>" . $e->getMessage();
}

$conn = null;
?>

插入数据-预处理

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
<?php
$servername = "localhost";
$username = "root";
$password = "root";
$dbname = "zdb";

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 t_user (name, age) VALUES (:name, :age)");
  $stmt->bindParam(':name', $name);
  $stmt->bindParam(':age', $age);

  // 设置参数并执行
  $name = "John";
  $age = 12;
  $stmt->execute();

  $name = "Mary";
  $age = 14;
  $stmt->execute();

  $name = "Julie";
  $age = 21;
  $stmt->execute();

  echo "新记录插入成功";
} catch (PDOException $e) {
  echo "Error: " . $e->getMessage();
}
$conn = null;
?>

删除数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
<?php
$dbms = 'mysql'; //数据库类型
$host = 'localhost'; //数据库主机名
$dbName = 'zdb'; //使用的数据库
$user = 'root'; //数据库连接用户名
$pass = 'root'; //对应的密码
$dsn = "$dbms:host=$host;dbname=$dbName";

try {
  $conn = new PDO($dsn, $user, $pass);
  // 设置 PDO 错误模式，用于抛出异常
  $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
  $id = 9;
  $sql = "DELETE FROM t_user where id=$id";
  $conn->exec($sql);
  echo "删除成功";
} catch (PDOException $e) {
  echo $sql . "<br>" . $e->getMessage();
}
$conn = null;
?>

更新数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
<?php
$dbms = 'mysql'; //数据库类型
$host = 'localhost'; //数据库主机名
$dbName = 'zdb'; //使用的数据库
$user = 'root'; //数据库连接用户名
$pass = 'root'; //对应的密码
$dsn = "$dbms:host=$host;dbname=$dbName";

try {
  $conn = new PDO($dsn, $user, $pass);
  // 设置 PDO 错误模式，用于抛出异常
  $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
  $id = 12;
  $sql = "update t_user set name='小明' where id=$id";
  $conn->exec($sql);
  echo "更新成功";
} catch (PDOException $e) {
  echo $sql . "<br>" . $e->getMessage();
}
$conn = null;
?>

兼容MySQL8

mysql8默认php pdo无法连接问题

会报两个错误

错误1

PDO::__construct(): Server sent charset (255) unknown to the client. Please, report to the developers

错误2

PDO::__construct(): The server requested authentication method unknown to the client

错误1的原因是编码不支持

错误2的原因是mysql8默认的使用密码认证方式不一样

mysql8.0默认使用caching_sha2_password，但是之前版本都是使用mysql_native_password。

查询

1
select user,host,plugin from mysql.user;

结果：

1147690-20190717165218761-1176368061

现在我们要改写mysql的默认密码认证方式。

改配置

/etc/mysql/my.cnf

1
2
3
4
5
6
7
8
9
10
[client]
default-character-set=utf8

[mysql]
default-character-set=utf8

[mysqld]
default_authentication_plugin=mysql_native_password
collation-server = utf8_unicode_ci
character-set-server = utf8

重启mysql

1
service mysqld restart

改密码

1
2
ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY'root';
flush privileges;

重启mysql

1
service mysqld restart

面向对象方式

创建数据库

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
<?php
$servername = "localhost";
$username = "root";
$password = "root";

// 创建连接
$conn = new mysqli($servername, $username, $password);
// 检测连接
if ($conn->connect_error) {
  die("连接失败: " . $conn->connect_error);
}

// 创建数据库
$sql = "CREATE DATABASE zdb";
if ($conn->query($sql) === TRUE) {
  echo "数据库创建成功";
} else {
  echo "Error creating database: " . $conn->error;
}

$conn->close();
?>

创建表

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
<?php
$servername = "localhost";
$username = "root";
$password = "root";
$dbname = "zdb";

// 创建连接
$conn = new mysqli($servername, $username, $password, $dbname);
// 检测连接
if ($conn->connect_error) {
  die("连接失败: " . $conn->connect_error);
}

// 使用 sql 创建数据表
$sql = "CREATE TABLE t_user2 (
id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY, 
name VARCHAR(30) NOT NULL,
age INTEGER(30) NOT NULL,
regdate TIMESTAMP
)";

if ($conn->query($sql) === TRUE) {
  echo "创建表成功";
} else {
  echo "创建数据表错误: " . $conn->error;
}

$conn->close();
?>

查询列表

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
<?php
$servername = "localhost";
$username = "root";
$password = "root";
$dbname = "zdb";

// 创建连接
$conn = new mysqli($servername, $username, $password, $dbname);
if ($conn->connect_error) {
  die("连接失败: " . $conn->connect_error);
}

$sql = "SELECT id, name, age FROM t_user";
$result = $conn->query($sql);
$userlist = array();
if ($result->num_rows > 0) {
  // 输出数据
  while ($row = $result->fetch_assoc()) {
    array_push($userlist, $row);
  }
}
$conn->close();

echo json_encode($userlist);
?>

插入单条数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
<?php
$servername = "localhost";
$username = "root";
$password = "root";
$dbname = "zdb";

// 创建连接
$conn = new mysqli($servername, $username, $password, $dbname);
// 检测连接
if ($conn->connect_error) {
  die("连接失败: " . $conn->connect_error);
}

$sql = "INSERT INTO t_user (name, age) VALUES ('John', 33)";

if ($conn->query($sql) === TRUE) {
  echo "新记录插入成功";
} else {
  echo "Error: " . $sql . "<br>" . $conn->error;
}

$conn->close();
?>

插入多条数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
<?php
$servername = "localhost";
$username = "root";
$password = "root";
$dbname = "zdb";

// 创建链接
$conn = new mysqli($servername, $username, $password, $dbname);
// 检查链接
if ($conn->connect_error) {
  die("连接失败: " . $conn->connect_error);
}

$sql = "INSERT INTO t_user (name, age) VALUES ('John', 10);";
$sql .= "INSERT INTO t_user (name, age) VALUES ('Mary', 20);";
$sql .= "INSERT INTO t_user (name, age) VALUES ('Julie', 30)";

if ($conn->multi_query($sql) === TRUE) {
  echo "新记录插入成功";
} else {
  echo "Error: " . $sql . "<br>" . $conn->error;
}

$conn->close();
?>

插入数据-预处理

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
<?php
$servername = "localhost";
$username = "root";
$password = "root";
$dbname = "zdb";

// 创建连接
$conn = new mysqli($servername, $username, $password, $dbname);

// 检测连接
if ($conn->connect_error) {
  die("连接失败: " . $conn->connect_error);
}

// 预处理及绑定
$stmt = $conn->prepare("INSERT INTO t_user (name, age) VALUES (?, ?)");
$stmt->bind_param("si", $name, $age);

// 设置参数并执行
$name = "John";
$age = 12;
$stmt->execute();

$name = "Mary";
$age = 14;
$stmt->execute();

$name = "Julie";
$age = 21;
$stmt->execute();

echo "新记录插入成功";

$stmt->close();
$conn->close();
?>

接下来，让我们来看下 bind_param() 函数：

1
$stmt->bind_param("si", $name, $age);

该函数绑定了 SQL 的参数，且告诉数据库参数的值。 "si" 参数列处理其余参数的数据类型。s 字符告诉数据库该参数为字符串。

参数有以下四种类型:

  • i - integer（整型）
  • d - double（双精度浮点型）
  • s - string（字符串）
  • b - BLOB（binary large object:二进制大对象）

每个参数都需要指定类型。

删除数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
<?php
$servername = "localhost";
$username = "root";
$password = "root";
$dbname = "zdb";

// 创建连接
$conn = new mysqli($servername, $username, $password, $dbname);
// 检测连接
if ($conn->connect_error) {
  die("连接失败: " . $conn->connect_error);
}

$id = 9;
$sql = "DELETE FROM t_user where id=$id";

if ($conn->query($sql)) {
  echo "删除成功";
} else {
  echo "Error: " . $sql . "<br>" . $conn->error;
}

$conn->close();
?>

更新数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
<?php
$servername = "localhost";
$username = "root";
$password = "root";
$dbname = "zdb";

// 创建连接
$conn = new mysqli($servername, $username, $password, $dbname);
// 检测连接
if ($conn->connect_error) {
  die("连接失败: " . $conn->connect_error);
}

$id = 12;
$sql = "update t_user set name='小明' where id=$id";

if ($conn->query($sql)) {
  echo "更新成功";
} else {
  echo "Error: " . $sql . "<br>" . $conn->error;
}

$conn->close();
?>

面向过程

查询列表

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
<?php
//1、连接数据库
$link = mysqli_connect('127.0.0.1:3306', 'root', 'root');
// var_dump($link);
//2、判断数据库是否连接成功
if (!$link) {
  exit('连接数据库失败');
}
//3、设置字符集
mysqli_set_charset($link, 'utf8');
//4、选择数据库
mysqli_select_db($link, 'zdb');
//5、准备sql语句
$sql = "select * from t_user";
//6、发送sql语句
$res = mysqli_query($link, $sql);
//7、处理结果集
$userlist = array();
while ($result = mysqli_fetch_assoc($res)) {
  array_push($userlist, $result);
}
//8、关闭数据库
mysqli_close($link);
echo json_encode($userlist);
?>