分类 开发 下的文章

{
    "Version":"1",
    "Statement":[
        {
            "Effect":"Allow",
            "Action":[
                "oss:ListBuckets",
                "oss:GetBucketStat",
                "oss:GetBucketInfo"
            ],
            "Resource":"acs:oss:*:*:*"
        },
        {
            "Effect":"Allow",
            "Action":[
                "oss:ListObjects",
                "oss:GetBucketAcl",
                "oss:PutBucketAcl"
            ],
            "Resource":"acs:oss:*:*:www-liugang-net-test"
        },
        {
            "Effect":"Allow",
            "Action":[
                "oss:ListObjects",
                "oss:GetObject",
                "oss:GetObjectAcl",
                "oss:DeleteObject",
                "oss:DeleteObjectAcl",
                "oss:PutObject",
                "oss:PutObjectAcl"
            ],
            "Resource":"acs:oss:*:*:www-liugang-net-test/*"
        }
    ]
}

php应用实现webSocket更多使用workerman或swoole框架.
serverSocket.php文件

<?php

class SocketService
{
    private $address;
    private $port;
    private $_sockets;
    public function __construct($address = '', $port='')
    {
            if(!empty($address)){
                $this->address = $address;
            }
            if(!empty($port)) {
                $this->port = $port;
            }
    }

    public function service(){
        //获取tcp协议号码。
        $tcp = getprotobyname("tcp");
        $sock = socket_create(AF_INET, SOCK_STREAM, $tcp);
        socket_set_option($sock, SOL_SOCKET, SO_REUSEADDR, 1);
        if($sock < 0)
        {
            throw new Exception("failed to create socket: ".socket_strerror($sock)."\n");
        }
        socket_bind($sock, $this->address, $this->port);
        socket_listen($sock, $this->port);
        echo "listen on $this->address $this->port ... \n";
        $this->_sockets = $sock;
    }

    public function run(){
        $this->service();
        $clients[] = $this->_sockets;
        while (true){
            $changes = $clients;
            $write = NULL;
            $except = NULL;
            socket_select($changes,  $write,  $except, NULL);
            foreach ($changes as $key => $_sock){
                if($this->_sockets == $_sock){ //判断是不是新接入的socket
                    if(($newClient = socket_accept($_sock))  === false){
                        die('failed to accept socket: '.socket_strerror($_sock)."\n");
                    }
                    $line = trim(socket_read($newClient, 1024));
                    $this->handshaking($newClient, $line);
                    //获取client ip
                    socket_getpeername ($newClient, $ip);
                    $clients[$ip] = $newClient;
                    echo  "Client ip:{$ip}   \n";
                    echo "Client msg:{$line} \n";
                } else {
                    socket_recv($_sock, $buffer,  2048, 0);
                    $msg = $this->message($buffer);
                    //在这里业务代码
                    echo "{$key} clinet msg:",$msg,"\n";
                    fwrite(STDOUT, 'Please input a argument:');
                    $response = trim(fgets(STDIN));
                    $this->send($_sock, $response);
                    echo "{$key} response to Client:".$response,"\n";
                }
            }
        }
    }

    /**
     * 握手处理
     * @param $newClient socket
     * @return int  接收到的信息
     */
    public function handshaking($newClient, $line){

        $headers = array();
        $lines = preg_split("/\r\n/", $line);
        foreach($lines as $line)
        {
            $line = chop($line);
            if(preg_match('/\A(\S+): (.*)\z/', $line, $matches))
            {
                $headers[$matches[1]] = $matches[2];
            }
        }
        $secKey = $headers['Sec-WebSocket-Key'];
        $secAccept = base64_encode(pack('H*', sha1($secKey . '258EAFA5-E914-47DA-95CA-C5AB0DC85B11')));
        $upgrade  = "HTTP/1.1 101 Web Socket Protocol Handshake\r\n" .
            "Upgrade: websocket\r\n" .
            "Connection: Upgrade\r\n" .
            "WebSocket-Origin: $this->address\r\n" .
            "WebSocket-Location: ws://$this->address:$this->port/websocket/websocket\r\n".
            "Sec-WebSocket-Accept:$secAccept\r\n\r\n";
        return socket_write($newClient, $upgrade, strlen($upgrade));
    }

    /**
     * 解析接收数据
     * @param $buffer
     * @return null|string
     */
    public function message($buffer){
        $len = $masks = $data = $decoded = null;
        $len = ord($buffer[1]) & 127;
        if ($len === 126)  {
            $masks = substr($buffer, 4, 4);
            $data = substr($buffer, 8);
        } else if ($len === 127)  {
            $masks = substr($buffer, 10, 4);
            $data = substr($buffer, 14);
        } else  {
            $masks = substr($buffer, 2, 4);
            $data = substr($buffer, 6);
        }
        for ($index = 0; $index < strlen($data); $index++) {
            $decoded .= $data[$index] ^ $masks[$index % 4];
        }
        return $decoded;
    }

    /**
     * 发送数据
     * @param $newClinet 新接入的socket
     * @param $msg   要发送的数据
     * @return int|string
     */
    public function send($newClinet, $msg){
        $msg = $this->frame($msg);
        socket_write($newClinet, $msg, strlen($msg));
    }

    public function frame($s) {
        $a = str_split($s, 125);
        if (count($a) == 1) {
            return "\x81" . chr(strlen($a[0])) . $a[0];
        }
        $ns = "";
        foreach ($a as $o) {
            $ns .= "\x81" . chr(strlen($o)) . $o;
        }
        return $ns;
    }

    /**
     * 关闭socket
     */
    public function close(){
        return socket_close($this->_sockets);
    }
}

$sock = new SocketService('127.0.0.1','9000');
$sock->run();

web.html 文件

<!doctype html>
<html lang="en">
 <head>
  <meta charset="UTF-8">
  <meta name="viewport" content="width=device-width,initial-scale=1, maximum-scale=1, user-scalable=no">
  <title>websocket</title>
 </head>
 <body>
 <input id="text" value="">
 <input type="submit" value="send" onclick="start()">
 <input type="submit" value="close" onclick="close()">
<div id="msg"></div>
 <script>
    /**
      *0：未连接
      *1：连接成功，可通讯
      *2：正在关闭
      *3：连接已关闭或无法打开
      */
    //创建一个webSocket 实例
    var webSocket  = new  WebSocket("ws://127.0.0.1:9000");


    webSocket.onerror = function (event){
        onError(event);
    };

    // 打开websocket
    webSocket.onopen = function (event){
        onOpen(event);
    };

    //监听消息
    webSocket.onmessage = function (event){
        onMessage(event);
    };


    webSocket.onclose = function (event){
        onClose(event);
    }

    //关闭监听websocket
    function onError(event){
        document.getElementById("msg").innerHTML = "<p>close</p>";
        console.log("error"+event.data);
    };

    function onOpen(event){
        console.log("open:"+sockState());
        document.getElementById("msg").innerHTML = "<p>Connect to Service</p>";
    };

    function onMessage(event){
        console.log("onMessage");
        document.getElementById("msg").innerHTML += "<p>response:"+event.data+"</p>"
    };

    function onClose(event){
        document.getElementById("msg").innerHTML = "<p>close</p>";
        console.log("close:"+sockState());
        webSocket.close();
    }

    function sockState(){
        var status = ['未连接','连接成功，可通讯','正在关闭','连接已关闭或无法打开'];
            return status[webSocket.readyState];
    }

    function start(event){
        console.log(webSocket);
        var msg = document.getElementById('text').value;
        document.getElementById('text').value = '';
        console.log("send:"+sockState());
        console.log("msg="+msg);
        webSocket.send("msg="+msg);
        document.getElementById("msg").innerHTML += "<p>request"+msg+"</p>"
    };

    function close(event){
        webSocket.close();
    }
 </script>
 </body>
</html>

html代码

    <div class="swiper-container">
        <div class="swiper-wrapper">
            <div class="swiper-slide">Slide 1</div>
            <div class="swiper-slide">Slide 2</div>
            <div class="swiper-slide">Slide 3</div>
            <div class="swiper-slide">Slide 4</div>
            <div class="swiper-slide">Slide 5</div>
            <div class="swiper-slide">Slide 6</div>
            <div class="swiper-slide">Slide 7</div>
            <div class="swiper-slide">Slide 8</div>
            <div class="swiper-slide">Slide 9</div>
            <div class="swiper-slide">Slide 10</div>
        </div>
    </div>

js代码

    <script>
var mySwiper = new Swiper('.swiper-container', {
            onTouchEnd: function(swiper, event) {
                console.log('onTouchEnd' + swiper.swipeDirection);

                if (swiper.isBeginning && swiper.swipeDirection == "prev") {
                    console.log("上一页")
                    location.href = "https://www.liugang.net/about.html";

                }
                if (swiper.isEnd && swiper.swipeDirection == "next") {
                    console.log("下一页")
                    location.href = "https://www.liugang.net/";

                }
            }
        }
    </script>

function add_arrstr($arrstr,$oldarrstr){
        $arr=explode(',',$arrstr);
        $oldarr=explode(',',$oldarrstr);
        $newarr=array_unique(array_merge($arr, $oldarr));
        return  implode(",",$newarr);
}

function remove_arrstr($arrstr,$oldarrstr){
        $arr=explode(',',$arrstr);
        $oldarr=explode(',',$oldarrstr);
        $newarr=array_unique(array_diff( $oldarr,$arr));
        return  implode(",",$newarr);
}

MySQL从5.7版本开始就支持JSON格式的数据，操作用起来挺方便的。

建表

在新建表时字段类型可以直接设置为json类型，比如我们创建一张表：

CREATE TABLE test_user (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR ( 50 ) NOT NULL,
info JSON
);
json类型字段可以为NULL

插入数据

INSERT INTO test_user(name, info) VALUES('xiaoming','{"sex": 1, "age": 18, "nick_name": "小萌"}');

json类型的字段必须时一个有效的json字符串

可以使用JSON_OBJECT()函数构造json对象：

INSERT INTO test_user(name, info) VALUES('xiaohua', JSON_OBJECT("sex", 0, "age", 17));

使用JSON_ARRAY()函数构造json数组

INSERT INTO test_user(name, info) VALUES('xiaozhang', JSON_OBJECT("sex", 1, "age", 19, "tag", JSON_ARRAY(3,5,90)));

查询test_user所有的数据

select * from test_user;

查询

表达式： 对象为json列->'$.键', 数组为json列->'$.键[index]'

select name, info->'$.nick_name', info->'$.sex', info->'$.tag[0]' from test_user;

等价于：对象为JSON_EXTRACT(json列 , '$.键') ，数组为JSON_EXTRACT(json列 , '$.键[index]')

select name, JSON_EXTRACT(info, '$.nick_name'), JSON_EXTRACT(info, '$.sex'), JSON_EXTRACT(info, '$.tag[0]') from test_user;

不过看到上面"小萌"是带双引号的，这不是我们想要的，可以用JSON_UNQUOTE函数将双引号去掉

select name, JSON_UNQUOTE(info->'$.nick_name') from test_user where name='xiaoming';

也可以直接使用操作符->>

select name, info->>'$.nick_name' from test_user where name='xiaoming';

当然属性也可以作为查询条件

select name, info->>'$.nick_name' from test_user where info->'$.nick_name'='小萌';

SELECT LoanNo FROM wbhj_loan lo WHERE lo.contractInfo ->> '$.contractNo'= '11' AND lo.loanResult=1

值得一提的是，可以通过虚拟列对JSON类型的指定属性进行快速查询。

创建虚拟列:

ALTER TABLE test_user ADD nick_name VARCHAR(50) GENERATED ALWAYS AS (info->>'$.nick_name') VIRTUAL;

注意用操作符->>

使用时和普通类型的列查询是一样:

select name,nick_name from test_user where nick_name='小萌';

更新

使用JSON_INSERT()插入新值，但不会覆盖已经存在的值

UPDATE test_user SET info = JSON_INSERT(info, '$.sex', 1, '$.nick_name', '小花') where id=2;

使用JSON_SET()插入新值，并覆盖已经存在的值

UPDATE test_user SET info = JSON_INSERT(info, '$.sex', 0, '$.nick_name', '小张') where id=3;

使用JSON_REPLACE()只替换存在的值

UPDATE test_user SET info = JSON_REPLACE(info, '$.sex', 1, '$.tag', '[1,2,3]') where id=2;

可以看到tag没有更新进去

jsonArray格式查询

SELECT
json_extract(a.summaryDesc,('$[0].sellerInfoSection[1].houseNo'))
FROM
channel_summary a

  1. 1
  2. ...
  3. 10
  4. 11
  5. 12
  6. 13
  7. 14
  8. 15