README

Mysql 操作便捷类，是对 mysqli 模块的简化封装，实现了

• 占位符功能
• 首次查询时自动连接
• 方便的数组选择

安装

composer require kea255/dbi

将您的连接数据常量添加到项目配置文件中

const dbhost = 'localhost';
const dbuser = 'username';
const dbpass = 'password';
const dbname = 'databaseName';

使用占位符

简单标量数据，占位符：?

$rows = DBi::select('SELECT * FROM tbl WHERE a=? AND b=? AND c=?', 1, 'test', null);

将执行查询

SELECT * FROM tbl WHERE a=1 AND b='test' AND c=NULL

数组，占位符：?a

$rows = DBi::select('SELECT * FROM tbl WHERE date IN(?a)', ['2006-03-02', '2012-01-02', '2022-05-01']);

将执行查询

SELECT * FROM tbl WHERE date IN('2006-03-02', '2012-01-02', '2022-05-01')

UPDATE 类型查询的关联数组

DBi::query('UPDATE tbl SET ?a', ['id'=>10, 'date'=>"2006-03-02"]);

将执行查询

UPDATE tbl SET `id`='10', `date`='2006-03-02'

INSERT 查询示例

$data = ['id' => 101, 'name' => 'Rabbit', 'age' => 30];
DBi::query('INSERT INTO table(?#) VALUES(?a)', array_keys($data), array_values($data));

将执行查询

INSERT INTO table(`id`, `name`, `age`) VALUES(101, 'Rabbit', 30)

INSERT ON DUPLICATE KEY UPDATE 查询示例

$data = ['id' => 101, 'name' => 'Rabbit', 'age' => 30];
DBi::query('INSERT INTO table(?#) VALUES(?a) ON DUPLICATE KEY UPDATE ?a', array_keys($data), array_values($data), $data);

将执行查询

INSERT INTO table(`id`, `name`, `age`) VALUES(101, 'Rabbit', 30) ON DUPLICATE KEY UPDATE `id`='101', `name`='Rabbit', `age`='30'

使用选择

选择全部结果：select()

$rows = DBi::select('SELECT Name, CountryCode FROM City');
foreach($rows as $row){
    printf("%s (%s)\n", $row["Name"], $row["CountryCode"]);
}

选择行：selectRow()

$row = DBi::selectRow('SELECT Name, CountryCode FROM City LIMIT 1');
printf("%s (%s)\n", $row["Name"], $row["CountryCode"]);

选择列：selectCol()

$names = DBi::selectCol('SELECT Name FROM City');
foreach($names as $name){
    printf("%s\n", $name);
}

选择单元格：selectCell()

$name = DBi::selectCell('SELECT Name FROM City WHERE CountryCode=?', 'RU');
printf("%s\n", $name);

可以使用临时表

DBi::query('CREATE TEMPORARY TABLE t1 SELECT Name, CountryCode FROM City');
$rows = DBi::select('SELECT * FROM t1');
foreach($rows as $row){
    printf("%s (%s)\n", $row["Name"], $row["CountryCode"]);
}

节省大结果集的内存

可以逐行获取结果，而不必将整个选择加载到内存中

$result = DBi::query('SELECT Name, CountryCode FROM City');
while($row = $result->fetch_assoc()){
	printf("%s (%s)\n", $row["Name"], $row["CountryCode"]);
}
$result->free();

查询日志记录

DBi::$query_log_file = __DIR__.'/db_log.txt';

错误时不停止脚本执行

DBi::$skip_error = true;

获取 INSERT, UPDATE, REPLACE 或 DELETE 查询影响的行数。

echo DBi::query_cnt('UPDATE table SET dat=NOW() WHERE id IN(13,17)');

事务

循环中加速大量行更新示例

$cnt = 0; 
$query_in_trans = 10000;
DBi::transaction();
while($reader->read()){
	$cnt++;
	if($cnt % $query_in_trans == 0){ 
		DBi::commit(); 
		DBi::transaction(); 
	}
	
	DBi::query("UPDATE `table` SET ?a WHERE id=?", $data, $id);
}
DBi::commit();