模型列表自动Where

if (!function_exists('model_where')) {
    function model_where(&$model, $where)
    {
        if (empty($where)) {
            return $model;
        }
        // 使用表结构分析, 自动将传进来的参数根据分析的表结构来决定查询条件
        $tableInfo = $model::DB()->select('SHOW FULL FIELDS FROM `' . $model->getTable() . '`');
        $tableInfo = object_to_array($tableInfo);
        $tableInfo = array_column_to_key($tableInfo, 'Field');
        $model = $model::query();
        foreach ($tableInfo as $key => $v) {
            if (strpos($tableInfo[$key]['Type'], 'int') !== false) {
                if (array_key_exists($key, $where) && is_numeric($where[$key])) {
                    $model = $model->where($key, intval($where[$key]));
                }
            } else if (strpos($tableInfo[$key]['Type'], 'char') !== false) {
                if (!empty($where[$key])) {
                    $model = $model->where($key, 'like', '%' . $where[$key] . '%');
                }
            } else if (
                strpos($tableInfo[$key]['Type'], 'decimal') !== false
                || strpos($tableInfo[$key]['Type'], 'timestamp') !== false
            ) {
                if (array_key_exists($key . '_from', $where) && $where[$key . '_from'] !== '') {
                    $model = $model->where($key, '>=', $where[$key . '_from']);
                }
                if (array_key_exists($key . '_to', $where) && $where[$key . '_to'] !== '') {
                    $model = $model->where($key, '<', $where[$key . '_to']);
                }
                if (array_key_exists($key, $where) && $where[$key] !== '') {
                    $model = $model->where($key, $where[$key]);
                }
            } else if (strpos($tableInfo[$key]['Type'], 'enum') !== false) {
                if (array_key_exists($key, $where)) {
                    $model = $model->where($key, $where[$key]);
                }
            }
        }
    }
}

• 代码层使用示例

$where = RequestHelper::get();
$query = new AbcdModel()
model_where($query,$where);
return ResponseHelper::paginator($query->paginate(RequestHelper::getPerPage()), new \App\Transformers\AbcdModelTransformer());

限制不重复写入

if (!function_exists('sql_insert_once')) {
    // 写入限制, 确保写入只会被写入一次
    function model_sql_insert_once($table, $array, $existsSql = '')
    {
        $sql0 = [];
        $sql1 = [];
        foreach ($array as $kq => $kv) {
            $sql0[] = '`' . $kq . '`';
            if (is_null($kv)) {
                $sql1[] = 'NULL';
            } else {
                $sql1[] = '\'' . addslashes($kv) . '\'';
            }
        }
        // SELECT * FROM `' . $table . '` WHERE AND created_at>NOW()-INTERVAL 5 SECOND
        return ' INSERT INTO `' . $table . '`(' . implode(',', $sql0) . ') 
 SELECT ' . implode(',', $sql1) . ' FROM DUAL WHERE NOT EXISTS(' . $existsSql . ') ';

        /*
        DB::insert(DB::raw($tmpSql));
        $paymentParentId = DB::getPdo()->lastInsertId();
        if (empty($paymentParentId)) {
            error_responses('支付中 ...');
        }
        */

    }
}

• 使用demo

$sql = model_sql_insert_once('course_orders', $courseOrderHeader, "SELECT * FROM course_orders WHERE 
help_code='" . $courseOrderHeader['help_code'] . "'
AND pid='" . $courseOrderHeader['pid'] . "'
AND class_id='" . $courseOrderHeader['class_id'] . "'
AND course_id='" . $courseOrderHeader['course_id'] . "'
AND state=1
AND created_at>NOW()-INTERVAL 60 SECOND");
// DB::insert(DB::raw($sql)) ;
$db = CourseOrderItem::DB();
$db->beginTransaction();
$db->insert($sql);
$orderId = DB::getPdo()->lastInsertId();
if (empty($orderId)){
    error_response('下单中');
}

针对delete操作, 常常会慢的严重, 明明查询出来需要删除的记录才70多条, 执行一次都得1分多钟

需求, 下单未支付同样的信息的, 删除除了最大的一条记录的信息(这边假设单人下单不超过2次相同的下单信息的, 我实际的操作是多执行几次就够了)

• 原sql

delete from course_orders
where 
pid=1 
AND state=1
AND created_at<date_sub(now(),interval 7 DAY)
AND id IN (
    SELECT max_order_id FROM (
        SELECT use_uid,course_id,COUNT(1) AS num,MAX(order_id) AS max_order_id FROM `course_order_items`
        WHERE 
        pid=1 
        AND state=0
        AND created_at>'2020-01-01'
        GROUP BY use_uid,course_id
    ) a
    WHERE num>1
)

共 73 行受到影响

执行耗时   : 59.794 sec
传送时间   : 0.001 sec
总耗时      : 59.796 sec

• 优化后

delete co from course_orders co inner join (
    SELECT max_order_id FROM (
        SELECT use_uid,course_id,COUNT(1) AS num,MAX(order_id) AS max_order_id FROM `course_order_items`
        WHERE 
        pid=1 
        AND state=0
        AND created_at>'2020-01-01'
        GROUP BY use_uid,course_id
    ) a
    WHERE num>1
) bb on pid=1 
AND state=1
AND created_at<date_sub(now(),interval 7 DAY)
AND co.id=bb.max_order_id

执行结果
共 73 行受到影响

执行耗时   : 0.066 sec
传送时间   : 0.008 sec
总耗时      : 0.075 sec

redis的一些命令和执行的返回值

普通写法

function get_chance($chance = [
    '和谐' => 3000,
    '爱国' => 5000,
    '敬业' => 110,
    '友善' => 1000,
    '富强' => 3000,
])
{
    $cardRange = [];
    $num = rand(0, array_sum(array_values($chance)));
    $total = 0;
    foreach ($chance as $key => $value) {
        $totalBefore = $total;
        $total += $value;
        if ($num >= $totalBefore && $num < $total) {
            return $key;
        }
        $cardRange[$key] = [$totalBefore, $total];
    }
    return $key;
}

在swoole环境中的写法, 兼容所有环境

function get_chance($chance = [
    '和谐' => 3000,
    '爱国' => 5000,
    '敬业' => 110,
    '友善' => 1000,
    '富强' => 3000,
])
{
    // 在协程中避免rand一段时间内值一致的问题
    $cardRange = [];
    $total = array_sum(array_values($chance));
    // $randValue = substr(explode(' ', microtime())[0], 0, 8);
    $randValue = strrev(substr(explode(' ', microtime())[0], 2, 6)) / 1000000;
    $rangeBefore = 0;
    $total2 = 0;
    foreach ($chance as $key => $value) {
        $total2 += $value;
        $rangeAfter = $total2 / $total;
        if ($randValue >= $rangeBefore && $randValue < $rangeAfter) {
            return $key;
        }
        $rangeBefore = $rangeAfter;
    }
    return $key;
}

校验数据和预期值的差异量

$t = [
    '记' => 300,
    '得' => 900,
    '我' => 150,
];
$r = [
    '记' => 0,
    '得' => 0,
    '我' => 0,
];
for ($k = 0; $k <= 10000; $k++) {
    $r[get_chance($t)]++;
}
var_dump($r);