对 Medoo v2.1.12 再封装，让操作更简单。
且独立维护Medoo.php，不再同步官方代码，如有BUG提交ISSUE
在原类Medoo.php上添加
$where['FOR UPDATE'] => TRUE
$where['user_name[FIND_IN_SET]']=>(string)10
$where['user_name[RAW]'] => '[a-z0-9]*'
安装
composer require thefunpower/db_medoo
配置
/**
* 数据库连接
*/
$medoo_db_config['db_name'] = 'dentalbest_erp';
$medoo_db_config['db_host'] = '127.0.0.1';
$medoo_db_config['db_user'] = 'root';
$medoo_db_config['db_pwd'] = '111111';
$medoo_db_config['db_port'] = 3306;
include __DIR__.'/vendor/thefunpower/db_medoo/boot.php';
读库
// 从库读库
$medoo_db_config['read_db_host'] = '127.0.0.1';
$medoo_db_config['read_db_name'] = ['read2','read1'];
$medoo_db_config['read_db_user'] = 'root';
$medoo_db_config['read_db_pwd'] = '111111';
$medoo_db_config['read_db_port'] = 3306;
在使用只读库时
db_active_read();
切回默认数据库
db_active_default();
需要放在事务中
db_for_update($table,$id);
"age[+]" => 1
'user_name[REGEXP]' => '[a-z0-9]*'
'user_name[FIND_IN_SET]'=>(string)10
'user_name[RAW]' => '[a-z0-9]*'
$where = [
//like查寻
'product_num[~]' => 379,
//等于查寻
'product_num' => 3669,
//大于查寻
'id[>]' => 1,
'id[>=]' => 1,
'id[<]' => 1,
'id[<=]' => 1,
];
$where = [];
$where['OR'] = [
'product_num[~]'=>379,
'product_num[>]'=>366,
];
$where['LIMIT'] = 10;
$where['ORDER'] = ['id'=>'DESC'];
//(... AND ...) OR (... AND ...)
"OR #1" => [
"AND #2" => $where,
"AND #3" => $or_where
]
];
//(... OR ...) AND (... OR ...)
"AND #1" => [
"OR #2" => $where,
"OR #3" => $or_where
]
];
字段是datetime类型
$date1 = '2022-11-01';
$date2 = '2022-12-14';
db_between_date($field,$date1,$date2)
$date1 = '2022-11';
$date2 = '2022-12';
db_between_month($field,$date1,$date2
$res = db_get_one("products","*",$where);
$res = db_get_one("products",$where);
$res = db_get("products","*",$where);
$res = db_get("products",$where);
$res = db_pager("products","*",$where);
原生方法将不会触发action
$res = db()->select("products",['id'],[]);
$res = db_get("qr_rule","qr_num",['GROUP'=>'qr_num']);
print_r($res);
db_insert($table, $data = [],$don_run_action = false)
db_update($table, $data = [], $where = [],$don_run_action = false)
db_get_min($table, $join = "*", $column = null, $where = null)
其他一些如取最大值等
db_get_max
db_get_count
db_get_has
db_get_rand
db_get_sum
db_get_avg
db_del($table, $where)
do_action("db_insert.$table.before", $data);
do_action("db_save.$table.before", $data);
其中$data有 id 及 data
do_action("db_insert.$table.after", $action_data);
do_action("db_save.$table.after", $action_data);
数据格式
$action_data = [];
$action_data['id'] = $id;
$action_data['data'] = $data;
do_action("db_update.$table.before", $data);
do_action("db_save.$table.before", $data);
其中$data有 id data where
do_action("db_update.$table.after", $action_data);
do_action("db_save.$table.after", $action_data);
数据格式
$action_data = [];
$action_data['where'] = $where;
$action_data['id'] = $where['id'] ?: '';
$action_data['data'] = $data;
do_action("db_get_one.$table", $v);
do_action("db_del.$table.before", $where);
do_action("db_del.$table.after", $where);
show_tables($table)
get_table_fields($table, $has_key = true)
db_allow($table, $data)
database_tables($name = null, $show_markdown = false)
array_order_by($row,$order,SORT_DESC);
is_json($data)
db_query($sql, $raw = null)
do_action("db_query", $all)
其中$sql为select * from table_name where user_id=:user_id
$raw 为 [':user_id'=>1]
需要inner db支持
db_action(function()use($data)){
});
db_for_update($table,$id)
db_pager_count($nums = null)
$data = db_pager("do_order",
["[><]do_mini_user" => ["uid" => "id"]],
[
"do_order.id",
"do_order.uid",
"user" => [
"do_mini_user.nickName",
"do_mini_user.avatarUrl",
"do_mini_user.openid",
]
],
$where);
$lists = db_get('do_order', [
'count' => 'COUNT(`id`)', // 对应 select COUNT(`id`) as count
'total' => 'SUM(`total_fee`)',
'date' => "FROM_UNIXTIME(`inserttime`, '%Y-%m-%d')"
],$where);
'ORDER'=>['id'=>[1,2]]
调用方式
xa_db_action([
'a'=>function(){
echo "a<br>";
db_insert("config",['title'=>1]);
},
'b'=>function(){
echo "b<br>";
db_insert("config",['title'=>'b']);
//抛出异常时也会回滚
//throw new Exception("错误");
}
]);
$err = db_get_error();
if($err) {
pr($err);
}
其中 a b是数据库连接
配置数据库
new_db([
'db_host'=>"127.0.0.1",
'db_name'=>"test1",
'db_user'=>"root",
'db_pwd'=>"111111",
'db_port'=>"3306",
],'a');
new_db([
'db_host'=>"127.0.0.1",
'db_name'=>"test2",
'db_user'=>"root",
'db_pwd'=>"111111",
'db_port'=>"3306",
],'b');
add_action("db_table.a",function(&$table){
$table = 'a_100';
});
db_struct_table_range_auto('wordpress','my_table',[
'2023-11',
'2023-12',
'2024-01',
'2024-02',
'2024-03',
]);
返回创建分区SQL
db_struct_table_range('my_table',[
'2023-11',
'2023-12',
'2024-01',
],'created_at','p',true);
验证规则
https://github.com/vlucas/valitron
<?php
namespace model;
class user extends \model{
protected $table = 'users';
protected $field = [
'name' => '姓名',
'phone' => '手机号',
'email' => '邮件',
];
protected $validate = [
'required'=>[
'name','phone','email',
],
'email'=>[
['email'],
],
'phonech'=>[
['phone']
],
'unique'=>[
['phone',],
['email',],
]
];
protected $unique_message = [
'手机号已存在',
'邮件已存在',
];
/**
* 写入数据前
*/
public function before_insert(&$data){
parent::before_insert($data);
$data['created_at'] = now();
parent::before_insert($data);
}
}
model事件，注意使用parent::
/**
* 查寻前
*/
public function before_find(&$where){
}
/**
* 查寻后
*/
public function after_find(&$data){
}
/**
* 写入数据前
*/
public function before_insert(&$data){
}
/**
* 写入数据后
*/
public function after_insert($id){
}
/**
* 更新数据前
*/
public function before_update(&$data,$where){
}
/**
* 更新数据后
*/
public function after_update($row_count,$data,$where){
}
/**
* 删除前
*/
public function before_del(&$where)
{
}
/**
* 删除后
*/
public function after_del($where)
{
}
字段映射
protected $field_ln = [
'title' => 'name',
];
name是数据库中真实存在的字段,title是自己定义了字段。
$model->find(['title[~]'=>'test']);
等同于
$model->find(['name[~]'=>'test']);
返回的记录中将同时有name title
model查询
$model->find($id) //返回一条记录 $id是int类型
$model->find(['name'=>'t'],$limit=1) //返回一条记录
$model->find(['name'=>'t']) //返回所有记录
关联定义
class invoice_detail extends \core\sys\model\base
{
protected $table = 'invoice_detail';
protected $has_one = [
'detail_one' => [invoice::class,'invoice_id'],
];
protected $has_many = [
'product_info' => [invoice_product::class,'product_num','product_num',['LIMIT' => 2]]
];
public function after_find(&$data)
{
unset($data['id']);
}
}
class invoice extends \core\sys\model\base
{
protected $table = 'invoice';
protected $has_many = [
'detail' => [invoice_detail::class,'invoice_id','id',['LIMIT' => 2]]
];
}
class invoice_product extends \core\sys\model\base
{
protected $table = 'invoice_products';
}
默认并不会自动查寻关联数据，如果查寻关联数据
$m = new yourmodelname();
$m->relation()->find();
insert
$model->insert($data, $ignore_hook = false)
update
$model->update($data,$where = '', $ignore_hook = false)
pager
$model->pager($join, $columns = null, $where = null, $ignore_hook = false)
sum
$model->sum($filed,$where = '')
count
$model->count($where = '')
delete
$model->del($where = '', $ignore_hook = false)
max
$model->max($filed,$where = '')
min
$model->min($filed,$where = '')
DISTINCT
$res = $m->find([
'select'=>['@title',"name2" => db_raw("COUNT(DISTINCT <title>)"),],
'status'=>1,
],$limit = '' ,true);
select数组中的@title是GROUP BY
生成数据库结构差量SQL
project_base为基础结构，其他的的project_user_*为需要被同步的结构
$sql = create_db_compare_sql([
'db_host' => '127.0.0.1',
'db_name' => 'project_base',
'db_user' => 'root',
'db_pwd' => '111111'
], [
'project_user_',
], $is_like = true);
其中$is_like表示是否是like效果。
在SAAS平台开发时，存在数据库切换的情况。
db_active('default',true);
//这里操作数据库
db_active('main');
//这里操作数据库
//这时会回到default连接
db_active_rollback();
关键函数
db_active($name = 'default',$need_rollback_here = false)
设置表前缀
db_prefix('wp_');
db_prefix('');
获取表前缀
db_prefix();
如果因为设置前缀导致有些表查寻有问题，可用add_action
add_action("db.table",function(&$table){});
add_action('db.err',function($err){
//如果是字符串说明是链接失败，如果是数组说明是SQL错误
});
数组转成tree
$model->array_to_tree($new_list, $pk = 'id', $pid = 'pid', $child = 'children', $root = 0, $my_id = '')
向上取递归
$model->get_tree_up($id, $is_frist = false)
向下递归
$model->get_tree_id($id, $where = [], $get_field = 'id')
$where = [];
$select = [];
$where['GROUP'] = 'company_num';
$select[] = 'customer_name';
$select[] = 'company_num';
$select['total'] = 'COUNT(`total_num`)';
$select['amount'] = 'SUM(`total_price`)';
$data = $this->invoice->pager($select, $where);
GROUP BY 与 ORDER BY使用
$wq = $this->input['wq'];
$date = $this->input['date'];
$date_start = $date[0];
$date_end = $date[1];
$where_string = "";
$query = [];
$where = [];
$select = [];
$select[] = 'customer_name';
$select[] = 'company_num';
$select[] = '@company_num';
$select['total'] = 'COUNT(`total_num`)';
$select['amount'] = 'SUM(`total_price`)';
if($date_start) {
$where['created_at[>=]'] = $date_start . " 00:00:01";
$where_string .= " AND created_at >= :created_at ";
$query[':created_at'] = $date_start . " 00:00:01";
}
if($date_end) {
$where['created_at[<=]'] = $date_end . " 23:59:59";
$where_string .= " AND created_at <= :created_at_1 ";
$query[':created_at_1'] = $date_end . " 23:59:59";
}
if($wq) {
$or['customer_name[~]'] = $wq;
$or['company_num[~]'] = $wq;
$where['OR'] = $or;
$where_string .= " AND (customer_name LIKE :customer_name OR company_num LIKE :company_num) ";
$query[':customer_name'] = "%" . $wq . "%";
$query[':company_num'] = "%" . $wq . "%";
}
//有GROUP BY ORDER BY时COUNT需要自行计算
$sql = "SELECT COUNT(DISTINCT(company_num)) AS total FROM invoice WHERE 1=1 " . $where_string . " LIMIT 1";
$count = db_query($sql, $query);
db_pager_count($count[0]['total']);
$where['ORDER'] = ['amount' => 'DESC'];
$where['GROUP'] = 'company_num';
$new_where = $where;
$new_where['select'] = $select;
$data = $this->invoice->pager($new_where);
$group_by = "product_num,base_name";
$sql = "select count(id) as total from (select * from ".$table." GROUP BY ".$group_by.") as wms";
$res = db_query($sql,[]);
$total = $res[0]['total'];
$all['total'] = $total;
本项目包含的第三方源码和二进制文件之版权信息另行标注。