PHP MySQLi/PDO_MySQL/PDO_SQLite CRUD(增查改删)
博客专区 > eechen 的博客 > 博客详情
PHP MySQLi/PDO_MySQL/PDO_SQLite CRUD(增查改删)
eechen 发表于2年前
PHP MySQLi/PDO_MySQL/PDO_SQLite CRUD(增查改删)
  • 发表于 2年前
  • 阅读 1277
  • 收藏 8
  • 点赞 3
  • 评论 8

腾讯云 十分钟定制你的第一个小程序>>>   

摘要: Web应用说到底还不是数据库增删改查,PHP完全能够胜任,JAVAer少在PHPer面前装高大上.

PHP MySQLi:

http://php.net/mysqli

<?php

function db() {
	global $app;
	static $db; //1个请求内多个函数共用1个连接.
	if ($db) {
		return $db;
	} else {
		$db = @new mysqli(
			$app['db_host'], 
			$app['db_user'], 
			$app['db_pass'], 
			$app['db_name'], 
			$app['db_port']
		);
	}
	if ($db->connect_errno) {
		echo $db->connect_error;
		exit();
	}
	$db->set_charset('utf8');
	return $db;
}

function insert($title = '', $content = '') {
	global $app;
	$db = db();
	$stmt = $db->prepare('INSERT INTO posts (post_title, post_content) VALUES (?, ?)');
	$stmt->bind_param('ss', $title, $content);
	$stmt->execute();
	//插入失败,或者没有AUTO_INCREMENT字段,或者不是INSERT语句,insert_id为0.
	return ($stmt->affected_rows !== 0 && $stmt->affected_rows !== -1) ? 
		array(true,  'insert_id' => $stmt->insert_id) : 
		array(false, 'insert_id' => $stmt->insert_id);
}

function select($id = '') {
	global $app;
	$db = db();
	if (!empty($id)) {
		return $db->query('SELECT * FROM posts WHERE id = '.intval($id))->fetch_all(MYSQLI_ASSOC);
	} else {
		return $db->query('SELECT * FROM posts')->fetch_all(MYSQLI_ASSOC);
	}
}

function select_v2($id = '') {
	global $app;
	$db = db();
	if (!empty($id)) {
		$stmt = $db->prepare('SELECT * FROM posts WHERE id = ?');
		$stmt->bind_param('i', $id);
	} else {
		$stmt = $db->prepare('SELECT * FROM posts');
	}
	$stmt->execute();
	//get_result和fetch_all需要mysqlnd支持,PHP从5.4开始内置mysqlnd.
	return $stmt->get_result()->fetch_all(MYSQLI_ASSOC);
}

function update($id, $title = '', $content = '') {
	global $app;
	$db = db();
	//var_export($db->query('SELECT @@autocommit')->fetch_all(MYSQLI_ASSOC)); exit(); //返回1可见MySQL默认会自动提交事务.
	$db->query('SET AUTOCOMMIT=0');  //$db->autocommit(false);
	//注意,InnoDB引擎会把写操作(insert/update/delete)当做事务来处理.
	//所以commit提交事务后autocommit记得重新设为true,否则delete等这些写操作因为没有手动commit会自动回滚.
	$db->query('START TRANSACTION'); //$db->begin_transaction(); PHP从5.5开始才有这个函数.
	$stmt = $db->prepare('UPDATE posts SET post_title = ?, post_content = ? WHERE id = ?');
	$stmt->bind_param('ssi', $title, $content, $id);
	$stmt->execute();
	$db->query('COMMIT'); //$db->commit();
	//$db->query('ROLLBACK'); //$db->rollback();
	$db->query('SET AUTOCOMMIT=1'); //commit提交事务后autocommit记得重新设为true.
	//UPDATE时,如果更新的内容跟原来的内容一样,affected_rows也会返回0.
	return ($stmt->affected_rows !== 0 && $stmt->affected_rows !== -1) ? true : false;
	//affected_rows:
	//返回-1表示查询出错.
	//返回0表示没有数据被修改.
	//返回1表示有1条数据被修改.
	//如果返回的值大于PHP_INT_MAX,这时affected_rows的类型是一个字符串.
}

function delete($id) {
	global $app;
	$db = db();
	$db->query('DELETE FROM posts WHERE id = '.intval($id));
	return ($db->affected_rows !== 0 && $db->affected_rows !== -1) ? true : false;
}

function delete_v2($id) {
	global $app;
	$db = db();
	$stmt = $db->prepare('DELETE FROM posts WHERE id = ?');
	$stmt->bind_param('i', $id);
	$stmt->execute();
	return ($stmt->affected_rows !== 0 && $stmt->affected_rows !== -1) ? true : false;
}

header('Content-Type: text/plain; charset=utf-8');

//mysql -uroot -p -e "CREATE DATABASE IF NOT EXISTS tuxbase DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;GRANT ALL PRIVILEGES ON tuxbase.* TO tux@127.0.0.1 IDENTIFIED BY '913dab0c6788bb8f0';FLUSH PRIVILEGES;"
$app = array(
	'db_host' => '127.0.0.1',
	'db_user' => 'tux',
	'db_pass' => '913dab0c6788bb8f0',
	'db_name' => 'tuxbase',
	'db_port' => 3306
);

$table = "CREATE TABLE IF NOT EXISTS posts (
	id           int(10) unsigned NOT NULL AUTO_INCREMENT,
	post_title   varchar(255)     NOT NULL DEFAULT '',
	post_content text             NOT NULL DEFAULT '',
	PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8;";

db()->query('DROP TABLE IF EXISTS posts;') or exit();
db()->query($table) or exit();

echo "var_export(insert('标题1', '内容1'));\n";
var_export(insert('标题1', '内容1'));
echo "\n\n";

echo "var_export(insert('标题2', '内容2'));\n";
var_export(insert('标题2', '内容2'));
echo "\n\n";

echo "var_export(select());\n";
var_export(select());
echo "\n\n";

echo "var_export(update(2, '标题2_更新','内容2_更新'));\n";
var_export(update(2, '标题2_更新','内容2_更新'));
echo "\n\n";

echo "var_export(select(2));\n";
var_export(select(2));
echo "\n\n";

echo "var_export(delete(2));\n";
var_export(delete(2));
echo "\n\n";

echo "var_export(select());\n";
var_export(select());
echo "\n\n";

PHP PDO_MySQL:

http://php.net/pdo

<?php

function db() {
	global $app;
	static $db;
	if ($db) {
		return $db;
	} else {
		/* MySQL */
		try {
			$dsn = "mysql:dbname=$app[db_name];host=$app[db_host];port=$app[db_port];charset=utf8";
			$db = new PDO($dsn, $app['db_user'], $app['db_pass'], array(
				PDO::ATTR_PERSISTENT => false,
				PDO::ATTR_EMULATE_PREPARES => false,
				PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8'
			));
		} catch (PDOException $e) {
			echo $e->getMessage();
			exit();
		}
		
		/* SQLite 
		try {
			$db = new PDO('sqlite:'.dirname(__FILE__).'/data.db3');
		} catch (PDOException $e) {
			echo $e->getMessage();
			exit();
		}
		*/
	}
	return $db;
}

function insert($title = '', $content = '') {
	global $app;
	$db = db();
	$stmt = $db->prepare('INSERT INTO posts (post_title, post_content) VALUES (?, ?)');
	$stmt->bindParam(1, $title,   PDO::PARAM_STR);
	$stmt->bindParam(2, $content, PDO::PARAM_STR);
	$stmt->execute();
	//插入失败,lastInsertId为0.
	return ($stmt->rowCount() !== 0) ? 
		array(true,  'lastInsertId' => $db->lastInsertId()) : 
		array(false, 'lastInsertId' => $db->lastInsertId());
}

function select($id = '') {
	global $app;
	$db = db();
	if (!empty($id)) {
		return $db->query('SELECT * FROM posts WHERE id = '.intval($id))->fetchAll(PDO::FETCH_ASSOC);
	} else {
		return $db->query('SELECT * FROM posts')->fetchAll(PDO::FETCH_ASSOC);
	}
}

function select_v2($id = '') {
	global $app;
	$db = db();
	if (!empty($id)) {
		$stmt = $db->prepare('SELECT * FROM posts WHERE id = ?');
		$stmt->bindParam(1, $id, PDO::PARAM_INT);
	} else {
		$stmt = $db->prepare('SELECT * FROM posts');
	}
	$stmt->execute();
	return $stmt->fetchAll(PDO::FETCH_ASSOC);
}

function update($id, $title = '', $content = '') {
	global $app;
	$db = db();
	
	//echo PDO::ATTR_AUTOCOMMIT; //返回0可见PDO默认禁用自动提交事务.
	//echo $db->getAttribute(PDO::ATTR_AUTOCOMMIT); exit(); //返回1可见MySQL默认会自动提交事务.
	
	//SQLite不支持设置PDO::ATTR_AUTOCOMMIT:
	//SQLite: Uncaught exception 'PDOException' with message 'The auto-commit mode cannot be changed for this driver'
	$db->setAttribute(PDO::ATTR_AUTOCOMMIT, false);
	$db->beginTransaction();

	//方法1(问号占位符)
	$stmt = $db->prepare('UPDATE posts SET post_title = ?, post_content = ? WHERE id = ?');
	$stmt->execute(array($title,$content,$id)); //所有值视作PDO::PARAM_STR处理
	
	//方法1(命名占位符)
	//$stmt = $db->prepare('UPDATE posts SET post_title = :title, post_content = :content WHERE id = :id');
	//$stmt->execute(array(':title' => $title,':content' => $content,':id' => $id)); //所有值视作PDO::PARAM_STR处理
	
	//方法2(问号占位符)
	//$stmt = $db->prepare('UPDATE posts SET post_title = ?, post_content = ? WHERE id = ?');
	//$stmt->bindParam(1, $title,   PDO::PARAM_STR);
	//$stmt->bindParam(2, $content, PDO::PARAM_STR);
	//$stmt->bindParam(3, $id,      PDO::PARAM_INT);
	//$stmt->execute();
	
	//方法2(命名占位符)
	//$stmt = $db->prepare('UPDATE posts SET post_title = :title, post_content = :content WHERE id = :id');
	//$stmt->bindParam(':title',   $title,   PDO::PARAM_STR);
	//$stmt->bindParam(':content', $content, PDO::PARAM_STR);
	//$stmt->bindParam(':id',      $id,      PDO::PARAM_INT);
	//$stmt->execute();
	
	//MySQLi只有一种写法(MySQLi不支持命名占位符)
	//$stmt = $db->prepare('UPDATE posts SET post_title = ?, post_content = ? WHERE id = ?');
	//$stmt->bind_param('ssi', $title, $content, $id);
	
	echo 'sleep(3);'."\n";
	sleep(3);
	
	$db->commit();
	$db->setAttribute(PDO::ATTR_AUTOCOMMIT, true); //commit提交事务后autocommit记得重新设为true
	return ($stmt->rowCount() !== 0) ? true : false;
}

function delete($id) {
	global $app;
	$db = db();
	return ($db->query('DELETE FROM posts WHERE id = '.intval($id))->rowCount() !== 0) ? true : false;
}

function delete_v2($id) {
	global $app;
	$db = db();
	$stmt = $db->prepare('DELETE FROM posts WHERE id = ?');
	$stmt->bindParam(1, $id, PDO::PARAM_INT);
	$stmt->execute();
	return ($stmt->rowCount() !== 0) ? true : false;
}

header('Content-Type: text/plain; charset=utf-8');

$app = array(
	'db_host' => '127.0.0.1',
	'db_user' => 'tux',
	'db_pass' => '913dab0c6788bb8f0',
	'db_name' => 'tuxbase',
	'db_port' => 3306
);

$mysql = "CREATE TABLE IF NOT EXISTS posts (
	id           int(10) unsigned NOT NULL AUTO_INCREMENT,
	post_title   varchar(255)     NOT NULL DEFAULT '',
	post_content text             NOT NULL DEFAULT '',
	PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8;";

$sqlite = "CREATE TABLE IF NOT EXISTS posts (
	id           INTEGER PRIMARY KEY,
	post_title   VARCHAR(255) NOT NULL,
	post_content TEXT         NOT NULL
)";

db()->query('DROP TABLE IF EXISTS posts;') or exit();
db()->query($mysql) or exit();

//并发时,SQLite在insert时因为库文件被其他请求锁住而导致阻塞
echo "var_export(insert('标题1', '内容1'));\n";
var_export(insert('标题1', '内容1'));
echo "\n\n";

echo "var_export(insert('标题2', '内容2'));\n";
var_export(insert('标题2', '内容2'));
echo "\n\n";

echo "var_export(select());\n";
var_export(select());
echo "\n\n";

echo "var_export(update(2, '标题2_更新','内容2_更新'));\n";
var_export(update(2, '标题2_更新','内容2_更新'));
echo "\n\n";

echo "var_export(select(2));\n";
var_export(select(2));
echo "\n\n";

echo "var_export(delete(2));\n";
var_export(delete(2));
echo "\n\n";

echo "var_export(select());\n";
var_export(select());
echo "\n\n";

PHP PDO_SQLite:

<?php

function db() {
	global $app;
	static $db;
	if ($db) {
		return $db;
	} else {
		/* MySQL 
		try {
			$dsn = "mysql:dbname=$app[db_name];host=$app[db_host];port=$app[db_port];charset=utf8";
			$db = new PDO($dsn, $app['db_user'], $app['db_pass'], array(
				PDO::ATTR_PERSISTENT => false,
				PDO::ATTR_EMULATE_PREPARES => false,
				PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8'
			));
		} catch (PDOException $e) {
			echo $e->getMessage();
			exit();
		}
		*/
		/* SQLite */
		try {
			$db = new PDO('sqlite:'.dirname(__FILE__).'/data.db3');
		} catch (PDOException $e) {
			echo $e->getMessage();
			exit();
		}
		
	}
	return $db;
}

function insert($title = '', $content = '') {
	global $app;
	$db = db();
	$stmt = $db->prepare('INSERT INTO posts (post_title, post_content) VALUES (?, ?)');
	$stmt->bindParam(1, $title,   PDO::PARAM_STR);
	$stmt->bindParam(2, $content, PDO::PARAM_STR);
	$stmt->execute();
	return ($stmt->rowCount() !== 0) ? 
		array(true,  'lastInsertId' => $db->lastInsertId()) : 
		array(false, 'lastInsertId' => $db->lastInsertId());
}

function select($id = '') {
	global $app;
	$db = db();
	if (!empty($id)) {
		return $db->query('SELECT * FROM posts WHERE id = '.intval($id))->fetchAll(PDO::FETCH_ASSOC);
	} else {
		return $db->query('SELECT * FROM posts')->fetchAll(PDO::FETCH_ASSOC);
	}
}

function select_v2($id = '') {
	global $app;
	$db = db();
	if (!empty($id)) {
		$stmt = $db->prepare('SELECT * FROM posts WHERE id = ?');
		$stmt->bindParam(1, $id, PDO::PARAM_INT);
	} else {
		$stmt = $db->prepare('SELECT * FROM posts');
	}
	$stmt->execute();
	return $stmt->fetchAll(PDO::FETCH_ASSOC);
}

function update($id, $title = '', $content = '') {
	global $app;
	$db = db();
	
	//echo PDO::ATTR_AUTOCOMMIT; //返回0可见PDO默认禁用自动提交事务.
	//echo $db->getAttribute(PDO::ATTR_AUTOCOMMIT); exit(); //返回1可见MySQL默认会自动提交事务.
	
	//SQLite不支持设置PDO::ATTR_AUTOCOMMIT:
	//SQLite: Uncaught exception 'PDOException' with message 'The auto-commit mode cannot be changed for this driver'
	//$db->setAttribute(PDO::ATTR_AUTOCOMMIT, false);
	$db->beginTransaction();
	$stmt = $db->prepare('UPDATE posts SET post_title = ?, post_content = ? WHERE id = ?');
	
	$stmt->execute(array($title,$content,$id)); //所有值视作PDO::PARAM_STR处理
	//$stmt->execute(array(':title' => $title,':content' => $content,':id' => $id));
	//$stmt->bind_param('ssi', $title, $content, $id); //对比mysqli
	
	echo 'sleep(3);'."\n";
	sleep(3);
	
	$db->commit();
	//$db->setAttribute(PDO::ATTR_AUTOCOMMIT, true); //commit提交事务后autocommit记得重新设为true
	return ($stmt->rowCount() !== 0) ? true : false;
}

function delete($id) {
	global $app;
	$db = db();
	return ($db->query('DELETE FROM posts WHERE id = '.intval($id))->rowCount() !== 0) ? true : false;
}

function delete_v2($id) {
	global $app;
	$db = db();
	$stmt = $db->prepare('DELETE FROM posts WHERE id = ?');
	$stmt->bindParam(1, $id, PDO::PARAM_INT);
	$stmt->execute();
	return ($stmt->rowCount() !== 0) ? true : false;
}

header('Content-Type: text/plain; charset=utf-8');

$app = array(
	'db_host' => '127.0.0.1',
	'db_user' => 'tux',
	'db_pass' => '913dab0c6788bb8f0',
	'db_name' => 'tuxbase',
	'db_port' => 3306
);

$mysql = "CREATE TABLE IF NOT EXISTS posts (
	id           int(10) unsigned NOT NULL AUTO_INCREMENT,
	post_title   varchar(255)     NOT NULL DEFAULT '',
	post_content text             NOT NULL DEFAULT '',
	PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8;";

$sqlite = "CREATE TABLE IF NOT EXISTS posts (
	id           INTEGER PRIMARY KEY,
	post_title   VARCHAR(255) NOT NULL,
	post_content TEXT         NOT NULL
)";

db()->query('DROP TABLE IF EXISTS posts;') or exit();
db()->query($sqlite) or exit();

//并发时,SQLite在insert时因为库文件被其他请求锁住而导致阻塞
echo "var_export(insert('标题1', '内容1'));\n";
var_export(insert('标题1', '内容1'));
echo "\n\n";

echo "var_export(insert('标题2', '内容2'));\n";
var_export(insert('标题2', '内容2'));
echo "\n\n";

echo "var_export(select());\n";
var_export(select());
echo "\n\n";

echo "var_export(update(2, '标题2_更新','内容2_更新'));\n";
var_export(update(2, '标题2_更新','内容2_更新'));
echo "\n\n";

echo "var_export(select(2));\n";
var_export(select(2));
echo "\n\n";

echo "var_export(delete(2));\n";
var_export(delete(2));
echo "\n\n";

echo "var_export(select());\n";
var_export(select());
echo "\n\n";

 

共有 人打赏支持
eechen
粉丝 917
博文 106
码字总数 55593
作品 1
评论 (8)
彩虹糖tang
你这代码质量确实不咋滴啊,第一个函数就不少槽点(感谢在osc社区为推广php做出的莫大努力)
游走的鱼
彩虹糖tang 。。。。
eechen

引用来自“彩虹糖tang”的评论

你这代码质量确实不咋滴啊,第一个函数就不少槽点(感谢在osc社区为推广php做出的莫大努力)
那就请你写一篇更咋滴的博文让大家学习学习你的写法嘛,看看是你的XX模式好用还是我的全局变量好用.
陨落人间
@eechen , 大神代码,果断差评,$app,居然用全局 global变量,$db连接没有使用单例模式,

$db居然还用mysql函数,还用了@符号
陨落人间
而且如果是连接几个不同数据库,连工厂模式也不用,。。照上面的兄弟说的,槽点一堆。@彩虹糖tang , @eechen
eechen
@陨落人间
连mysql和mysqli两套函数都分不清的人就不要自以为是了.http://php.net/manual/zh/mysqli.overview.php 官方文档的建议: mysqli(首选) pdo_mysql(建议) mysql(不建议) 还有phpMyAdmin用的也是mysqli操作MySQL.对于MySQL而言,mysqli远比PDO合适.

全局数组多么方便的东西,用来传递参数再合适不过,去特么狗屁的设计模式.Discuz!的全局数组$_G你不知道?哈哈.一个static静态变量就已经能够避免多次connect数据库,就是这么简单.不服,写一篇博文来PK呀,别特么光说不练眼高手低呀.哈哈.
qycms_cn
@ eechen +1024
×
eechen
如果觉得我的文章对您有用,请随意打赏。您的支持将鼓励我继续创作!
* 金额(元)
¥1 ¥5 ¥10 ¥20 其他金额
打赏人
留言
* 支付类型
微信扫码支付
打赏金额:
已支付成功
打赏金额: