前面我们提到过,数据库编程中的SQL指令分为四种,最后一种dtl数据事务语句还没有讲到。熟悉数据库的朋友应该知道,数据库是一个并行系统,在系统运行的过程中,不可能同一时刻只有一个用户在访问某个数据,当不同用户对同一个数据进行访问时,系统必须保证数据的同步,这就涉及到事务。
事务用于保证数据的一致性,由一组相关的dml语句组成,该组语句要么全部成功要么全部失败。事务具有acid四大特性:原子性、一致性、隔离性、持久性。在mysqli扩展库中提供了相应的函数支持,用来处理事务。同时,该扩展库还支持预编译技术,用于对多条SQL语句进行批处理。
事务处理
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
| $mysqli=new MySQLi("localhost","root","mysql","test"); if ($mysqli->connect_error) { die("connect failed".$mysqli->connect_error); }
$mysqli->autocommit(false);
$sql1="update account set balance=balance-2 where id=1"; $sql2="update account set balance=balance+2 where id=2";
$res1=$mysqli->query($sql1); $res2=$mysqli->query($sql2);
if ($res1&&$res2) { $mysqli->commit(); } else { $mysqli->rollback(); }
$mysqli->close();
|
stmt预编译
PHP向mysql数据库发送批量相同模板指令时,可以第一次发送先编译好,之后只需要发送数据即可,批处理有两个优点:
- 效率高,节省数据库编译时间开销
- 安全性高,可以防止sql注入攻击
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16
|
class User { public $id; public $name; public $age; public $password; function __construct($name,$age,$password) { $this->name=$name; $this->age=$age; $this->password=$password; } }
|
dml语句预编译处理方式
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24
| $mysqli=new MySQLi("localhost","root","mysql","test");
$u1=new User("n1",22,"p1"); $u2=new User("n2",23,"p2"); $u3=new User("n3",24,"p3"); $user_set=array($u1,$u2,$u3);
$sql_dml="insert into user(name,age,password) values(?,?,?);";
$mysqli_stmt=$mysqli->prepare($sql_dml);
foreach ($user_set as $key => $value) {
$mysqli_stmt->bind_param("sis",$value->name,$value->age,$value->password);
$res=$mysqli_stmt->execute(); if (!$res) { echo "failed".$mysqli_stmt->error; } }
|
dql语句预编译处理方式
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 37 38 39 40
| $mysqli=new MySQLi("localhost","root","mysql","test");
$res_set=array();
$param_set=array(20,23,25);
$sql_dql="select * from user where age>?;";
$mysqli_stmt=$mysqli->prepare($sql_dql);
foreach ($param_set as $key => $value) { $mysqli_stmt->bind_param("i",$value); $mysqli_stmt->bind_result($id,$name,$age,$password); $mysqli_stmt->execute(); $res=array(); while ($mysqli_stmt->fetch()) { $u=new User($name,$age,$password); $u->id=$id; array_push($res, $u); } array_push($res_set, $res); $mysqli_stmt->free_result(); }
$mysqli_stmt->close();
foreach ($res_set as $key => $value) { echo "<br>new result"; foreach ($value as $key1 => $value1) { echo "<br>--".$value1->id.$value1->name.$value1->age.$value1->password; } }
|
mysqli扩展库中对表格信息的访问
1 2 3 4 5 6 7 8 9 10 11 12
| $sql="select * from user;"; $res=$mysqli->query($sql);
echo "<br>--".$res->num_rows.$res->field_count;;
while ($field=$res->fetch_field()) { echo "--".$field->name; echo "--".$field->max_length; }
|