PHP code example of baagee / php-mysql
1. Go to this page and download the library: Download baagee/php-mysql library . Choose the download type require .
2. Extract the ZIP file and open the index.php.
3. Add this code to the index.php.
<?php
require_once('vendor/autoload.php');
/* Start to develop here. Best regards https://php-download.com/ */
baagee / php-mysql example snippets
// 引入配置文件
$config = MySQL\DBConfig::init($config);
// 获取DB实例
$db = \BaAGee\MySQL\DB::getInstance();
$db1 = \BaAGee\MySQL\DB::getInstance();
var_dump($db === $db1);
/*插入测试*/
$sql = 'INSERT INTO student_score
(id,student_name,student_id,english,chinese,math,history,biology,create_time,class_id,sex,age) values
(null ,:student_name,:student_id,:english,:chinese,:math,:history,:biology,:create_time,:class_id,:sex,:age)';
for ($i = 0; $i < 5; $i++) {
$res = $db->execute($sql, createStudentScoreRow());
var_dump($res);
var_dump($db1->getLastInsertId());
var_dump(\BaAGee\MySQL\SqlRecorder::getLastSql());
}
/*查询测试*/
$list = $db->query('select * from student_score where id >? order by id desc limit 2', [mt_rand(10, 100)]);
var_dump($list);
var_dump(\BaAGee\MySQL\SqlRecorder::getLastSql());
// 当一次查询数据量大时可以使用yield 返回生成器
$list = $db->yieldQuery('select * from student_score where id>:id', ['id' => 0]);
var_dump($list);
foreach ($list as $i => $item) {
var_dump($item);
}
var_dump(\BaAGee\MySQL\SqlRecorder::getLastSql());
/*测试事务1*/
$db->beginTransaction();
try {
transactionTest($db);
$db->commit();
echo '事务成功' . PHP_EOL;
} catch (Exception $e) {
echo "事务Error:" . $e->getMessage() . PHP_EOL;
$db->rollback();
}
/*测试事务2*/
$res = \BaAGee\MySQL\DB::transaction('transactionTest', [$db]);
var_dump('测试事务2 结果:' . ($res ? 'ok' : 'error'));
function transactionTest(\BaAGee\MySQL\DB $db)
{
$sql = 'INSERT INTO article(id,user_id,title,content,tag,create_time) values
(null ,:user_id,:title,:content,:tag,:create_time)';
$userData = createArticleRow();
$db->execute($sql, $userData);
var_dump(\BaAGee\MySQL\SqlRecorder::getLastSql());
$sql = 'update student_score set english=? where id = ?';
$updateData = [mt_rand(30, 100), 330];
// throw new Exception('发生失误');
$db->execute($sql, $updateData);
var_dump(\BaAGee\MySQL\SqlRecorder::getLastSql());
return true;
}
var_dump($db->getLastPrepareSql());
var_dump($db->getLastPrepareData());
echo 'OVER' . PHP_EOL;
use BaAGee\MySQL\SimpleTable;
$config = $config);
$builder = SimpleTable::getInstance('student_score');
/*插入测试*/
$res = $builder->insert(createStudentScoreRow(), true);
var_dump(\BaAGee\MySQL\SqlRecorder::getLastSql());
var_dump($res);
/*批量插入测试*/
$rows = [];
for ($i = 0; $i < 3; $i++) {
$rows[] = createStudentScoreRow();
}
$res = $builder->insert($rows, true);
var_dump(\BaAGee\MySQL\DB::getLastSql());
var_dump($res);
/*查询测试 多条件嵌套*/
$res = $builder->fields([
'student_name', '`student_id`', 'chinese', 'english', 'math', 'biology', 'history', 'class_id', 'age', 'sex'
])->where([
[
'history' => ['>', '60'],
'or',
'class_id' => ['in', [1, 2, 3, 4]]
],
'or',
[
'sex' => ['=', 0],
'age' => ['<', 19],
'or',
[
'sex' => ['=', 0],
'age' => ['<', 19],
'or',
[
'sex' => ['=', 0],
'age' => ['<', 19],
'or',
[
'sex' => ['=', 0],
'age' => ['<', 19]
]
]
]
]
])->orWhere([
'age' => ['=', 18]
])->orderBy(['id' => 'desc'])->limit(0, 2)->groupBy('student_name')->lockInShareMode()->select(false);
var_dump(\BaAGee\MySQL\DB::getLastSql());
// var_dump($res);
$res = $builder->fields([
'avg(chinese)', 'class_id', 'min(`age`)', 'max(math)', 'sum(biology)', 'count(student_id)'
])->where(['id' => ['>', mt_rand(300, 590)]])->groupBy('class_id')->orderBy(['class_id' => 'desc'])->limit(0, 7)->select();
var_dump(\BaAGee\MySQL\DB::getLastSql());
// var_dump($res);
$res = $builder->fields([
'student_name', 'math', 'english', '`class_id` as cid'
])->where([
'class_id' => ['between', [1, 5]],
'sex' => ['=', 1],
])->orWhere([
'math' => ['>', 60],
'english' => ['<', 60],
'or',
(new Expression('id % 2 = 0'))
])->having(['`cid`' => ['>', 3]])->orHaving([
'cid' => ['<', 2],
// 'or',
'math' => ['>', 60]
])->limit(0, 2)->orderBy(['age' => 'desc', 'student_id' => 'asc'])
->groupBy('student_id')->groupBy('math')->lockInShareMode()->select();
var_dump(\BaAGee\MySQL\DB::getLastSql());
// var_dump($res);
// die;
/*更新测试*/
$res = $builder->where(['id' => ['=', mt_rand(300, 590)]])->update(['student_name' => '哈哈哈' . mt_rand(0, 99)]);
var_dump(\BaAGee\MySQL\DB::getLastSql());
var_dump($res);
// 递增递减
$res=$builder->where([
'id' => ['=', mt_rand(390, 600)]
])->update([
// 使用表达式
'english' => (new Expression('english + 1')),
'math' => (new Expression('math - 1')),
]);
/*删除测试*/
$res = $builder->where(['id' => ['=', mt_rand(300, 590)]])->delete();
var_dump(\BaAGee\MySQL\DB::getLastSql());
var_dump($res);
// 查询
$res = $builder->where(['id' => ['=', mt_rand(300, 590)]])->fields(['distinct `age`', 'sex'])->select();
var_dump(\BaAGee\MySQL\DB::getLastSql());
var_dump($res);
$article = SimpleTable::getInstance('article');
$res = $article->insert(createArticleRow());
var_dump(\BaAGee\MySQL\DB::getLastSql());
var_dump($res);
//
// $article->where(['id' => ['>', 20]]);
// var_dump($article);
// $article = SimpleTable::getInstance('article');
// var_dump($article);
// 关联查询
$studentScoreObj = SimpleTable::getInstance('student_score');
$studentScoreList2 = $studentScoreObj->limit(3)->hasOne('class_id', 'class_group.id', ['name', 'create_time'], [], function (&$v) {
$v['create_time'] = explode(' ', $v['create_time'])[0];
})->hasMany('student_id', 'article.user_id', ['tag'],
[
new \BaAGee\MySQL\Expression('id %2= 0'),
])->select();
use BaAGee\MySQL\SimpleTable;
$config = t($config);
$student = $builder = SimpleTable::getInstance('student_score');
$resList = $student->whereEqual('sex', 1)
->whereGt('chinese', '60')
->whereIn('class_id', [1, 2, 3, 4, 5, 6, 7])
->whereNotIn('age', [16, 17], false)
->whereBetween('math', 60, 99)
->whereNotBetween('history', 0, 60, false)
->whereGte('english', 60)
->whereLt('biology', 90, false)
->whereLte('id', 3000)
->whereLike('student_name', '槽%', false)
->whereNotLike('student_name', '%骆%')
->whereNotEqual('is_delete', 1)
//having
->havingEqual('sex', 1)
->havingGt('chinese', '70', false)
->havingIn('class_id', [1, 2, 3, 4])
->havingNotIn('age', [16, 17, 18], false)
->havingBetween('math', 60, 90)
->havingNotBetween('history', 20, 60, false)
->havingGte('english', 70, false)
->havingLt('biology', 90)
->havingLte('id', 2000)
->havingLike('student_name', '槽%', false)
->havingNotLike('student_name', '%骆%')
->havingNotEqual('is_delete', 1)
->select();
// var_dump($resList);
print_r(\BaAGee\MySQL\SqlRecorder::getLastSql()['fullSql'] . PHP_EOL);
$resList = $student->whereEqual('sex', 1, false)
->whereGt('chinese', '60', false)
->whereIn('class_id', [1, 2, 3, 4, 5, 6, 7], false)
->whereNotIn('age', [16, 17], true)
->whereBetween('math', 60, 99, false)
->whereNotBetween('history', 0, 60, true)
->whereGte('english', 60, false)
->whereLt('biology', 90, true)
->whereLte('id', 3000, false)
->whereLike('student_name', '槽%', true)
->whereNotLike('student_name', '%骆%', false)
->whereNotEqual('is_delete', 1, false)
//having
->havingEqual('sex', 1, false)
->havingGt('chinese', '70', true)
->havingIn('class_id', [1, 2, 3, 4], false)
->havingNotIn('age', [16, 17, 18], true)
->havingBetween('math', 60, 90, false)
->havingNotBetween('history', 20, 60, true)
->havingGte('english', 70, true)
->havingLt('biology', 90, false)
->havingLte('id', 2000, false)
->havingLike('student_name', '槽%', true)
->havingNotLike('student_name', '%骆%', false)
->havingNotEqual('is_delete', 1, false)
->select();
// var_dump($resList);
print_r(\BaAGee\MySQL\SqlRecorder::getLastSql()['fullSql'] . PHP_EOL);
ini_set('display_errors', 1);
$st = microtime(true);
$len) {
$string = 'qazwsxedcrfvtgbyhnujmikolp0129384756';
$count = strlen($string) - 1;
$return = '';
for ($i = 0; $i < $len; $i++) {
$return .= $string{mt_rand(0, $count)};
}
return $return;
};
$config = rtTime']) * 1000;
$log = sprintf("success[%s] cost[%s]ms connectTime[%s]ms [SQL] %s" . PHP_EOL, $params['sqlInfo']['success'] ? 'ok' : 'no',
$time, $cTime, $params['sqlInfo']['fullSql']);
echo $log;
// die;
});
/*插入测试*/
$student = \BaAGee\MySQL\FasterTable::getInstance('student_score');
$student->insert(createStudentScoreRow(), false);
$r = createStudentScoreRow();
$r['id'] = mt_rand(3000, 3100);
// 主键/唯一索引冲突支持自动更新
$student->insert($r, true, [
'english' => new \BaAGee\MySQL\Expression('Values(english)'),
'math' => new \BaAGee\MySQL\Expression('Values(math)'),
'age' => new \BaAGee\MySQL\Expression('Values(age)'),
'update_time' => time(),
]);
var_dump(SqlRecorder::getLastSql());
// die;
$rows = [];
for ($i = 0; $i <= 2; $i++) {
$rows[] = createStudentScoreRow();
}
$student->insert($rows, false);
$student->replace(createStudentScoreRow());
$rows = [];
for ($i = 0; $i <= 2; $i++) {
$rows[] = createStudentScoreRow();
}
$student->replace($rows);
/*删除测试*/
$student->delete(['id' => ['=', mt_rand(2700, 2999)]]);
/*修改测试*/
$student->update(['student_name' => createStudentName()], ['id' => ['=', mt_rand(3000, 3300)]]);
$student->increment('english', ['id' => ['=', mt_rand(3000, 3300)]]);
$student->increment('english', ['id' => ['=', mt_rand(3000, 3300)]], 2);
$student->decrement('english', ['id' => ['=', mt_rand(3000, 3300)]]);
$student->decrement('english', ['id' => ['=', mt_rand(3000, 3300)]], 2);
/*查询测试*/
$res = $student->findRows(['id' => ['=', mt_rand(2900, 3300)]]);
var_dump($res);
$res = $student->findRow(['id' => ['=', mt_rand(2900, 3300)]]);
var_dump($res);
$res = $student->findColumn('student_name', ['id' => ['=', mt_rand(2900, 3300)]]);
var_dump($res);
$res = $student->findValue('student_name', ['id' => ['=', mt_rand(2900, 3300)]]);
var_dump($res);
$res = $student->yieldRows(['chinese' => ['=', mt_rand(90, 99)]]);
foreach ($res as $re) {
var_dump($re);
}
$res = $student->yieldColumn('student_name', ['chinese' => ['=', mt_rand(90, 99)]]);
foreach ($res as $re) {
var_dump($re);
}
$res = $student->exists(['id' => ['=', mt_rand(3000, 3100)]]);
var_dump($res);
$res = $student->findRows(['english' => ['=', mt_rand(90, 100)]], ['*'], ['id' => 'desc'], 10, 10);
var_dump($res);
/*聚合查询测试*/
$res = $student->count(['is_delete' => ['=', 0]], ['1'], ['class_id', 'sex'], ['class_id' => 'asc', 'sex' => 'desc']);
var_dump($res);
$res = $student->sum(['is_delete' => ['=', 0]], ['english', 'math', 'history'], ['class_id', 'sex'], ['class_id' => 'asc', 'sex' => 'desc']);
var_dump($res);
$res = $student->avg(['is_delete' => ['=', 0]], ['english', 'history'], ['class_id', 'sex'], ['class_id' => 'asc', 'sex' => 'desc']);
// var_dump($res);
$res = $student->min(['is_delete' => ['=', 0]], ['english', 'biology'], ['class_id', 'sex'], ['class_id' => 'asc', 'sex' => 'desc']);
foreach ($res as $re) {
var_dump($re);
}
// var_dump($res);
$res = $student->max(['is_delete' => ['=', 0]], ['english', 'math'], ['class_id', 'sex'], ['class_id' => 'asc', 'sex' => 'desc']);
// var_dump($res);
$res = $student->complex(['is_delete' => ['=', 0]], [
'sum' => ['chinese', 'math', 'history', 'biology', 'age'],
'min' => ['chinese', 'math', 'history', 'biology', 'age'],
'max' => ['chinese', 'math', 'history', 'biology', 'age'],
'avg' => ['chinese', 'math', 'history', 'biology', 'age'],
'count' => '1'
], ['class_id', 'sex'], ['class_id' => 'asc', 'sex' => 'desc']);
var_dump($res);
echo ((microtime(true) - $st) * 1000) . PHP_EOL;
use BaAGee\MySQL\SimpleTable;
use BaAGee\MySQL\Expression;
$config = 置
\BaAGee\MySQL\DBConfig::addConfig($config, 'test1');//加入新配置
\BaAGee\MySQL\DBConfig::addConfig($config, 'test2');// 加入新配置
$names = [\BaAGee\MySQL\DBConfig::getCurrentName(), 'test1', 'test2'];// 当前所有配置名
foreach ($names as $name) {
\BaAGee\MySQL\DBConfig::switchTo($name);//切换到其中一个配置
echo '切换到:' . $name . PHP_EOL;
$builder = SimpleTable::getInstance('student_score');
/*插入测试*/
$res = $builder->insert(createStudentScoreRow(), true);
var_dump(\BaAGee\MySQL\DB::getLastSql());
var_dump($res);
/*批量插入测试*/
$rows = [];
for ($i = 0; $i < 3; $i++) {
$rows[] = createStudentScoreRow();
}
$res = $builder->insert($rows, true);
var_dump(\BaAGee\MySQL\DB::getLastSql());
var_dump($res);
/*查询测试*/
$res = $builder->fields([
'id', 'student_name', 'student_id', 'chinese', 'english', 'math', 'biology', 'history', 'class_id', 'age', 'sex'
])->where([
'history' => ['>', '60'],
'class_id' => ['in', [1, 2, 3, 4]],
'or',
(new Expression('id % 2 = 0'))
])->where([
'age' => ['=', 18]
])->orderBy(['id' => 'desc'])->limit(0, 2)->groupBy('student_name')->lockInShareMode()->select(false);
var_dump(\BaAGee\MySQL\SqlRecorder::getLastSql());
// var_dump($res);
// die;
// 强制使用索引
$res = $res = $builder->forceIndex('student_score_student_id_index', 'student_score_student_name_index')->where(
['student_id' => ['=', 1565246274451]]
)->select();
var_dump($res);
var_dump(\BaAGee\MySQL\SqlRecorder::getLastSql());
// die;
}