mysql单表一对多查询,将多条记录合并到一条记录


有时候会遇到运营或者产品同学分析数据,帮忙导出数据的需求。1对多的数据这种导出又希望将对应的数据合并到一个字段上。
数据关系如下图所示:
公司表:

公司表

城市表:

公司城市副表

查出来的城市整理到公司的记录上,后面的GROUP BY语句不能少。

SELECT a.id, GROUP_CONCAT(b.city_code SEPARATOR ",") as online_city FROM vip_company_manage as a 
LEFT JOIN vip_company_city as b ON a.id=b.company_id AND b.`status`=1 GROUP BY a.id;

查询结果:

查询结果


json数据的校验


作为一名后端研发,工作中避免不了的是与前端进行接口交互,有时候传的是json格式的数据,之前传的json数据也都是比较简单的格式,只有一层key-value,这次中有好多层的数据,校验起来相当麻烦,问了同事也没有好的解决办法。后来在网上查找资料发下有了灵丹妙药。就是今天要介绍的justinrainbow/json-schema,github地址

下面介绍下用法,先上代码:
需要一个模板文件,格式为json,里面定义了数据的属性和类型,类似许多框架的校验类。

<?php 
$tmp = [
    "type" => "object",
    "properties" => [
        "index_page" => [
            "type" => "object",
            "properties" => [
                "tpl_no" => [
                    "type" => "integer"
                ],
                "primary_vision" => ['type' => "string"],
                "coupon_pic" => ['type' => "string"],
                "fetch_button" => [
                    "type" => "object",
                    "properties" => [
                        "text" => ['type' => "string"],
                        "text_color" => ['type' => "string"],
                        "color" => ['type' => "string"],
                    ]
                ],
                "bg_color" => ['type' => "string"],
                "page_text_color" => ['type' => "string"],
                "slide" => [
                    "type" => "array",
                    "minItems" => 1,
                    "items" => [
                        "type" => "object",
                        "properties" => [
                            "url" => ["type" => "string"],
                            "router" => ["type" => "string"],
                        ],
                        "required" => ['url', 'router']
                    ]

                ],
                "logo" => ["type" => "string"],
            ],
            "required"=>['tpl_no',"primary_vision","coupon_pic","fetch_button","bg_color","page_text_color","slide","logo"]
        ],
        "copywriting" => [
            "type" => "object",
            "properties" => [
                "share_pic" => ['type' => "string"],
                "index_share_title" => ['type' => "string"],
                "index_share_desc" => ['type' => "string"],
                "rule" => ['type' => "string"],
            ],
            "required"=>['share_pic',"index_share_title","index_share_desc","rule"]
        ],
        "submit" => ['type' => "string"],
        "activity_code" => ['type' => "string"],
        "email_prefix" => ['type' => "string"]
    ],
    "required"=>['index_page',"copywriting","activity_code","email_prefix"]
];
$tmp = json_encode($tmp);

$data = '{"index_page":{"tpl_no":1,"primary_vision":"http://10.16.34.42:8080/group3/M00/10/91/ChAiKlyYTXiAAdpnAAEDFxTmJWw032.jpg","coupon_pic":"http://10.16.34.42:8080/group3/M00/10/91/ChAiKlyYTXuAYaFqAAEDFxTmJWw580.jpg","fetch_button":{"text":"立即领取","text_color":"#fff","color":"#ff961e"},"bg_color":"#3d4347","rule_color":"#ccc","slide":[{"url":"http://10.16.34.42:8080/group3/M00/10/91/ChAiKlyYTZ6AbiWEAAEDFxTmJWw983.jpg","router":"123"}],"logo":"http://10.16.34.46:8080/group2/M00/16/EE/ChAiLlyYTbmARJh1AAEDFxTmJWw337.jpg"},"copywriting":{"share_pic":"http://10.16.34.46:8080/group2/M00/16/EE/ChAiLlyYTaaAfZcMAAEDFxTmJWw903.jpg","index_share_title":"122","index_share_desc":"11","rule":"<p>s<span style=\"font-weight: bold;\">assssfgsssssss啧啧啧</span></p>"},"submit":"1","activity_code":"rentLuckyCoupon","email_prefix":"ac"}';

require __DIR__ . '/../vendor/autoload.php';

$data = json_decode($data);

// Validate
$validator = new JsonSchema\Validator();
$validator->check($data, json_decode($tmp));

if ($validator->isValid()) {
    echo "The supplied JSON validates against the schema.\n";
} else {
    echo "JSON does not validate. Violations:\n";
    foreach ($validator->getErrors() as $error) {
        echo sprintf("[%s] %s\n", $error['property'], $error['message']);
    }
}

上面就也可以校验json的结构了,方便了好多。目前仅用到了上面的功能,并不能过滤掉不需要的结构,更多的功能请参考文档。

https://json-schema.org/understanding-json-schema/reference/object.html
https://github.com/justinrainbow/json-schema


Yii的model赋值自定义表单的数据


普通api的接口传递参数时,默认是没有formName的,通常是$_POST或者$_GET,此时是不能直接调用model的load方法进行属性赋值的。
查看load方法的源码

public function load($data, $formName = null)
{
    $scope = $formName === null ? $this->formName() : $formName;
    if ($scope === '' && !empty($data)) {
        $this->setAttributes($data);
    
        return true;
    } elseif (isset($data[$scope])) {
        $this->setAttributes($data[$scope]);

        return true;
    } else {
        return false;
    }
}

则处理方法如下:
方法一:load方法的第二个参数传空字符串'';这种情况就可以给model的属性赋值了。

$model = new UserForm();
$post = ['name' => '小王', 'sex' => '女'];
$model->load($post, '');

方法二:给model的attributes属性赋值

$model = new UserForm();
$post = ['name' => '小王', 'sex' => '女'];
$model->attributes = $post;

此时model的属性就会被赋值。


lookandsay序列


    function lookAndSay($s) {
        // 将返回值初始化为一个空串
        $r = '';
        // $m包含要统计的字符,初始化为字符串中的第一个字符
        $m = $s[0];
        // $n是已查看过的$m个数,初始化为1;
        $n = 1;
        for ($i = 1, $j = strlen($s); $i < $j; $i++) {
            // 如果这个字符与上一个相同
            if ($s[$i] == $m) {
                // 将这个字符个数加1
                $n++;
            } else {
                // 否则,将字符个数和字符本身增加到返回值
                $r .= $n.$m;
                // 将要查找的字符串设置为当前字符
                $m = $s[$i];
                // 并将字符个数重置为1
                $n = 1;
            }

        }
        // 返回构建的字符串以及最后的字符串个数及字符
        return $r.$n.$m;
    }

    for ($i = 0, $s = 1; $i < 10; $i++) {
        $s = lookAndSay($s);
        print $s."<br>";
    }

结果为:
请输入图片描述

“Look and Say”序列,因为每个元素是通过查看前一个元素并说出其中足什么来得到的。例如,查看第一个元素1,你会说“一个1”,所以第二个元素是“11”。这就是两个1,所以第三个元素是“21” 。类似地,这里有一个2和一个1,所以第四个元素是“1211”,依此类推。


redis批量删除key


  1. DEL直接删除多个key

    redis-cli 连接redis
    `DEL key1 key2 key3`
    
  2. linux管道操作批量删除

    **redis没有设置访问密码时:**
    `redis-cli KEYS "bj_*"|xargs redis-cli DEL`
    **redis设置了访问密码时:**
    `redis-cli -a password KEYS "bj_*"|xargs redis-cli -a password DEL`
     
    **`注:bj_*属于通配符`**