<?php
$mysqli = new mysqli("localhost", "my_user", "my_password", "world");
/* check connection */
if (mysqli_connect_errno()) {
printf("Connect failed: %s\n", mysqli_connect_error());
exit();
}
$query = "SELECT CURRENT_USER();";
$query .= "SELECT Name FROM City ORDER BY ID LIMIT 20, 5";
/* execute multi query */
if ($mysqli->multi_query($query)) {
do {
/* store first result set */
if ($result = $mysqli->store_result()) {
while ($row = $result->fetch_row()) {
printf("%s\n", $row[0]);
}
$result->free();
}
/* print divider */
if ($mysqli->more_results()) {
printf("-----------------\n");
}
} while ($mysqli->next_result());
}
/* close connection */
$mysqli->close();
?>
自己的demo:
<?php
/**
练习:使用mysqli的mysqli:multi_query(); 一次性查询并显示①employee; ② words表的英文和中文
*/
//include 'mysqliHelper.php';
/**
function __autoload($class_name){
require_once $class_name.'.php';//这种自动加载类的方式 需要保持 文件名 和类名保持一致 目前是这样
}
header("content-type:text/html;charset=utf-8");
//$mysql = new Mysqlii('localhost','root','tmdqobn','db100') or die ("出错了");;
//$sqls = "select * from employee";
//$sqls.="select * from words";
//$res = $mysql->Multi_query($sqls);
if($mysql->Multi_query($sqls)){
do{
if($result==MySQLi->store_result()){
while($row = $result->fetch_row()){
foreach ($row as $key=>$value){
echo "Key: $key; Value: $value<br />\n";
}
}
}
}while($mysql->GetNext_result());
}
if($res){
while($result = $mysql->GetStore_result()){
while($row = $result->fetch_row()){
foreach ($row as $key=>$value){
echo "Key: $key; Value: $value<br />\n";
}
}
}
}
*/
$mysqli = new MySQLi('localhost','root','tmdqobn','db100') or die ("数据库链接异常请重试");;
$sqls = "select * from employee;";
$sqls.="select * from words";
echo "0";
//如果执行成功,那么至少有一个结果集
if($mysqli->multi_query($sqls)){
echo "1";
do{
if($result=$mysqli->store_result()){//从mysqli里边取出一个结果集 这玩意必然是mysqli result对象
while($row = $result->fetch_row()){
/// var_dump($row);
/**
for($i=0;$i<count($row);$i++){
echo $row[2]+" .... ";
}
*/
foreach ($row as $value){
echo "这是第一种".$value;
}
echo "<br/>";
// 或者 这么写
foreach ($row as $key=>$val){
echo "这是第二种".$val;
}
echo "<br/>";
}
if($mysqli->more_results()){//需要用这个判断是否还有更多结果集,没有更多需要及时处理不然会报一个警告。
printf("-----------------\n\n\n");
}else{
return;
}
}
}while($mysqli->next_result());
}
?>
事物:
mysql默认的数据库引擎是MyISAM,==不支持事务和外键==,也可使用支持事务和外键的==InnoDB==。
InnoDB
mysql> ALTER TABLE account ENGINE=InnoDB;
Query OK, 2 rows affected (0.61 sec)
Records: 2 Duplicates: 0 Warnings: 0
如下已经改为innodb引擎
mysql> show create table account;
+---------+-------------------------------------------------------------------
-----------------------------------------------------------------------+
| Table | Create Table
|
+---------+-------------------------------------------------------------------
-----------------------------------------------------------------------+
| account | CREATE TABLE `account` (
`id` int(11) NOT NULL,
`blance` float DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+---------+-------------------------------------------------------------------
-----------------------------------------------------------------------+
1 row in set (0.00 sec)
具体事物控制执行代码:
<?php
header("content-type:text/html;charset=utf-8");
$mysqli = new MySQLi('localhost','root','tmdqobn','db100');
if($mysqli->connect_error){
die("数据库连接出错".$mysqli->connect_error);
}
$mysqli->autocommit(0);//先将自动提交改为false,代码写完后手动执行提交。
$sql1 = "update account set blance=blance-2 where id=1";
$sql2 = "update account2 set blance=blance+2 where id=2";
$b1 = $mysqli->query($sql1) /**or die ($mysqli-error)*/;
$b2 = $mysqli->query($sql2) /**or die ($mysqli-error)*/;
//echo "$b1"."$b1";
if(!$b1||!$b2){
echo "失败回滚";
$mysqli->rollback();
}else{
echo "成功";
$mysqli->commit();//成功的话手动调用执行提交
}
//事物往往是针对增删改来执行的。
$mysqli->close();
//如上代码加入$sql2 写错了 那么会出现 $sql1执行成功减掉2块钱 但是 $sql2没成功加上这个钱。这种场景在涉及到钱或者积分的地方都算是错误代码
?>
数据库效果:
//没有执行php代码
mysql> select * from account;
+----+--------+
| id | blance |
+----+--------+
| 1 | 100 |
| 2 | 80 |
+----+--------+
2 rows in set (0.00 sec)
//php成功执行无错
mysql> select * from account;
+----+--------+
| id | blance |
+----+--------+
| 1 | 98 |
| 2 | 82 |
+----+--------+
2 rows in set (0.00 sec)
//php执行任一语句出错 查询效果不变
mysql> select * from account;
+----+--------+
| id | blance |
+----+--------+
| 1 | 98 |
| 2 | 82 |
+----+--------+
2 rows in set (0.00 sec)
/**
在mysql控制台可以使用事物才进行操作,具体步骤如下
1、开启一个事物:
start transaction
2、做保存点
savepoint 保存点名称
3、个人做的操作
。。。。。
4、如果第三步没有错误。那么可以执行commit提交,如果觉得有问题那么就回滚 rollback to 保存点名称;
*/
//实际操作代码如下 事物回滚代码mysql控制台操作:
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> savepoint a;
Query OK, 0 rows affected (0.05 sec)
mysql> delete from account where id=1;
Query OK, 1 row affected (0.04 sec)
mysql> select * from account;
+----+--------+
| id | blance |
+----+--------+
| 2 | 90 |
| 3 | 400 |
| 4 | 500 |
+----+--------+
3 rows in set (0.00 sec)
mysql> rollback to a;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from account;
+----+--------+
| id | blance |
+----+--------+
| 1 | 100 |
| 2 | 90 |
| 3 | 400 |
| 4 | 500 |
+----+--------+
4 rows in set (0.00 sec)
事物的特点 acid;