业务场景订单表orders,根据年以及客户id实现水平分表
控制器
//添加数据
public function insert($params){
$OrderCard=new OrderCard;
$res=$OrderCard->addOrder($params);
dd($res);
}
//查询数据
public function find(){
$OrderCard=new OrderCard;
$res=$OrderCard->getOrdersByConditions();
dd($res);
}
模型层
protected $pk = 'id';
protected $name='orders';
// 获取订单表分表名
private function getTableName($customerId)
{
return 'orders' . '_' . date('Y') . '_' .$customerId ;
}
// 检查分表是否存在,如果不存在则创建
private function checkAndCreateTable($tableName)
{
if (!Db::query("SHOW TABLES LIKE '$tableName'")) {
// 如果表不存在,创建分表
Db::execute("CREATE TABLE $tableName(
id INT PRIMARY KEY AUTO_INCREMENT,
clientId INT,
rechargeTarget
)");
}
}
// 添加订单
public function addOrder($data)
{
$customerId = $data['clientId'];
$tableName = $this->getTableName($customerId);
// 检查分表是否存在,如果不存在则创建
$this->checkAndCreateTable($tableName);
return Db::table($tableName)->insert($data);
}
// 根据客户ID和年份查询订单
public function getOrdersByCustomerAndYear($customerId)
{
$tableName = $this->getTableName($customerId);
return Db::table('orders')->where('rechargeTarget', $customerId)->select();
}
// 根据客户ID和年份查询订单,以查询rechargeTarget=111为例
public function getOrdersByConditions()
{
// 获取数据库连接
$db = Db::connect();
// 获取数据库名
$databaseName = $db->getConfig('database');
$data=[];
// 查询所有表名以 "order" 为前缀的数据表信息
// 查询所有前缀为 "order" 的表
$sql = "SELECT table_name FROM information_schema.tables WHERE table_schema='{$databaseName}' AND table_name LIKE 'orders%'";
$tables = $db->query($sql);
// 处理查询结果
foreach ($tables as $table) {
//$tableName = $table['table_name'];
$tableName = current($table);//用于返回数组中的当前元素的值
// 查询每个表中满足条件的数据
$result = $db->table($tableName)->where('rechargeTarget', '111')->select()->toArray();
// 处理查询结果
foreach ($result as $item=>$value) {
$data[]=$value;
}
}
return $data;
}