在Mysql中存储具有一些格式的数据的时候使用JSON格式存储会很方便,Mysql 5.7.8 开始支持存储原生JOSN格式的数据,MariaDB 10.0.1版本优化了JOSN格式的处理。

两者还是有区别的,在此记录下如何对JSON字段的CURD

创建JSON格式字段

创建JOSN字段不能设置长度,和默认值,可以是NULL

--MYSQL
CREATE TABLE happyhack(
 `id` INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
 `area` JSON,
 `tags` JSON
);
--Mariadb
CREATE TABLE happyhack(
`area` BLOB
);

MariaDB [test]> DESC happyhack;
--------------
DESC happyhack
--------------

+-------+------------------+------+-----+---------+----------------+
| Field | Type             | Null | Key | Default | Extra          |
+-------+------------------+------+-----+---------+----------------+
| id    | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| area  | longtext         | YES  |     | NULL    |                |
| tags  | longtext         | YES  |     | NULL    |                |
+-------+------------------+------+-----+---------+----------------+

插入JSON数据

INSERT INTO `happyhack` (area,tags) VALUES ('{"id": 1, "name": "如皋"}', '[1, 2, 3]');

Mysql 也有两个函数用来插入Json格式的数据JSON_OBJECTJSON_ARRAY
MariaDB 使用COLUMN_CREATE

INSERT INTO `happyhack` (area, tags) VALUES (JSON_OBJECT("id", 2, "name", "江苏"), JSON_ARRAY(1, 3, 5));

-- 这个在MariaDB中没有成功..
INSERT INTO `happyhack` (area) VALUES(COLUMN_CREATE('id','1','name','中国'));
+----+-----------------------------+-----------+
| id | area                        | tags      |
+----+-----------------------------+-----------+
|  1 | {"id": 1, "name": "如皋"}   | [1, 2, 3] |
|  2 | {"id": 2, "name": "江苏"}   | [1, 3, 5] |
+----+-----------------------------+-----------+

查询JSON格式字段

查询 JSON 中的数据用 column->path 的形式,其中对象类型 path 这样表示 $.path, 而数组类型则是 $[index]

MariaDB 貌似不支持这种写法,使用JSON_EXTRACT函数可以实现

MariaDB [test]> SELECT area, JSON_EXTRACT(area,'$.id'),JSON_EXTRACT(tags,'$[0]') FROM happyhack;
--------------
SELECT area, JSON_EXTRACT(area,'$.id'),JSON_EXTRACT(tags,'$[0]') FROM happyhack
--------------

+-----------------------------+---------------------------+---------------------------+
| area                        | JSON_EXTRACT(area,'$.id') | JSON_EXTRACT(tags,'$[0]') |
+-----------------------------+---------------------------+---------------------------+
| {"id": 1, "name": "如皋"}   | 1                         | 1                         |
| {"id": 2, "name": "江苏"}   | 2                         | 1                         |
+-----------------------------+---------------------------+---------------------------+

--MariaDB
SELECT area, JSON_EXTRACT(area,'$.name'),JSON_EXTRACT(tags,'$[0]') FROM happyhack;
--Mysql
SELECT area,area->'$.name',tags->'$[0]' from happyhack;

取出来的对象数据会自带"号,可以使用JSON_UNQUOTE函数去掉,目前没有Mysql数据库,下次遇到在测试

按条件查询JSON字段数据

JSON格式的数据不同于字符串,使用字符串相等查询是查询不到的;
可以使用CAST函数将字符串转成JSON格式的查询。

SELECT area FROM happyhack WHERE area = CAST('{"id": 1, "name": "如皋"}' as JSON);

SELECT area FROM happyhack WHERE area->'$.name' ='如皋";
--以上两种方法没有在Mysql中测试,以后遇到会记录

使用JSON_EXTRACT查询

SELECT area FROM happyhack WHERE JSON_EXTRACT(area,'$.name') = '如皋';

PHP中将JSON存入DB注意点

使用json_encode方法转为json存数据库的时候,如果是中文字符会被转义成别的码...使用JSON_UNESCAPED_UNICODE就好啦!

json_encode($arr,JSON_UNESCAPED_UNICODE);

Laravel 中查询JSON

Client::whereRaw('JSON_EXTRACT(client_region, "$[0]") like ?', ["%$request->info%"])->get();

未完待续!

参考链接

http://www.lnmp.cn/mysql-57-new-features-json.html

https://www.omgdba.com/using-json-datatype-in-mariadb-and-mysql.html

最近写代码的状态真是不得了,两周时间做出一个项目出来了,并且在这之间使用的都是新技术
Vue,Laravel。这两周时间对个人能力的提升相当于半年的积累了。哈哈

有一个段子吐槽程序员的加班

程序员去面试
HR问他:简历上显示毕业到现在才2年,为何工作经验写的是三年?

程序员说:多出来的一年是加班加出来的...

今天看了下WakaTime的统计,上周五居然写了10个小时的代码!!!我自己都佩服我自己了!

放几张图Mark一下

由于使用CDN域名没有HTTPS连接,导致站点变成混合模式。暂先去掉图片链接!

picture
picture
picture

一般会自动更新,但是有时会失败,这时候就需要手动运行命令进行更新

sudo /usr/local/python/bin/certbot renew --force-renewal #强制更新

sudo /usr/local/python/bin/certbot renew #自动更新脚本

有很大的原因是因为当初创建证书的时候: DNS problem: NXDOMAIN looking up A for xxx.com 导致的

服务器上的DNS缓存未更新.

使用 nscd工具更新服务器DNS缓存

安装:

sudo apt-get install nscd

更新DNS缓存

sudo nscd -i hosts

#还可以更新passwd、group
sudo nscd -i passwd
sudo nscd -i group

如果以上两种方法都失效的话,将nginx配置文件备份,使用脚本重新生成

sudo cp /usr/local/nginx/conf/vhost/blog.happyhack.cn.conf{,_`date +%m%d`}

 注意重启nginx!

参考http://www.ptbird.cn/let-s-encrypt-ssl-dns-probles.html

Laravel 模型(Eloquent)

Eloquent(模型)查询一条数据是否存在返回值是一个collection的集合.

使用empty() isset()等函数对collection集合判断是否为空没有作用.

collection集合中定义了一些方法.
使用$collection->isEmpty()来判断值是否为空

举个茄子

$user = User::where('username', '=', '茄子')->get();

if( !$user->isEmpty()){
  retrun '有茄子';
}

return '没茄子';

Laravel join 子查询

框架中自带了selectRaw, whereRaw, orderByRaw等编写原生SQL的方法,但是没有 join 相关的原生查询方法。

DB::raw(); #运行原生SQL

举个茄子

现有一个需求,查询所有用户数据,并带出用户具有的权限.

# users 表
+-----------+-----+---------+----------------+
| Field     | Key | Default | Extra          |
+-----------+-----+---------+----------------+
| id        | PRI | NULL    | auto_increment |
| name      | UNI | NULL    |                |
| password  |     | NULL    |                |
| is_active |     | 1       |                |

# user_info 表
+-----------+-----+---------+----------------+
| Field     | Key | Default | Extra          |
+-----------+-----+---------+----------------+
| id        | PRI | NULL    | auto_increment |
| user_id   |     | NULL    |                |
| nick_name |     | NULL    |                |
| real_name |     | NULL    |                |
| email     |     | NULL    |                |

# permissions 表
+-----------+-----+---------+----------------+
| Field     | Key | Default | Extra          |
+-----------+-----+---------+----------------+
| id        | PRI | NULL    | auto_increment |
| name      |     | NULL    |                |
| guard_name|     | NULL    |                |
| created_at|     | NULL    |                |
| updated_at|     | NULL    |                |
+-----------+-----+---------+----------------+

# model_has_permissions 表
+----------------+-----+---------+-------+
| Field          | Key | Default | Extra |
+----------------+-----+---------+-------+
| permission_id  | PRI | NULL    |       |
| model_id       | PRI | NULL    |       |
| model_type     | PRI | NULL    |       |
+----------------+-----+---------+-------+

原生SQL写起来很简单:

$sql = <<<SQLCODE
      SELECT
        users.id,
        users.name,
        users_infos.nick_name,
        new.p_name
      FROM
        users
      LEFT JOIN
        users_infos
      ON
        users.id = users_infos.user_id
      LEFT JOIN
        (
          SELECT
            permission.name AS p_name,
            permission.id AS p_id,
            permission.guard_name,
            m.model_id
          FROM
            permissions
          RIGHT JOIN
            model_has_permissions AS m
          ON
            permission.id = m.permission_id
        ) AS new
        ON
          new.model_id = users.id
SQLCODE;

$user_infos = DB::select($sql);

将LEFT JOIN 中的子查询的结果起一个new 的新名字(类似于一个临时表)

Laravel自带的分页很棒,但是使用原生的SQL,就无法使用paginate()这个方法了,看来只能DB::raw()LEFT JOIN 添加原生SQL了

$pagesize = 10;
$users = DB::table('users')
            ->leftJoin('users_infos',
             'users.id', '=', 'users_infos.user_id')
            ->leftJoin(
                DB::raw("
                (SELECT
                  p.id,
                  p.name AS p_name,
                  m.model_id
                FROM
                  tb_permissions AS p
                RIGHT JOIN
                  tb_model_has_permissions AS m
                ON
                  p.id = m.permission_id
                ) AS tb_mo"),
                 'mo.model_id', '=', 'users.id')
            ->select('users.id',
             'users.name',
              'users.failed_count',
              'mo.p_name',
              'login_time',
              'users.is_active',
              'users.created_at',
              'users_infos.nick_name', 'users_infos.real_name',
              'users_infos.email',
              'users_infos.cell')
            ->paginate($pagesize);

return $users;

由于配置文件中配置了表前缀,在命名临时子查询的时候要将表前缀加上去

单列模式

单列模式主要作用是防止一个类被重复实例化而占用系统资源,单列类必须要有以下三种元素

  • 必须有一个标记为private的构造函数
  • 必须有一个保存该类实例的静态成员变量
  • 必须有一个访问该实例的公共静态方法,通常使用getInstance()
class Some{
    private $db;
    public static $_instance;

    private function __construct(){
        $this->db = pdo::content('xx');
    }

    //防止对象被复制或克隆
    private _clone(){};

    public static getInstance(){
        if(! (self::$_instance instanceof self)){
            self::$_instance = new self();
        }
        return self::$instance;
    }
    public function query(){
        retun 'sql';
    }
}

//使用
$db = Some::getInstance();
$db->query();

工厂模式

工厂模式的作用在于可以根据输入参数或者应用程序配置的不同,创建一种实例化的类,该类会根据参数返回不同功能类的实例化,工厂模式通常包含一个名为factory()的方法,这个方法必须访问一个实例化的对象。

工厂模式与接口配合使用,能够规定固定的API,让不同作用的类实现相同的功能,例如数据库的处理,不同类型数据库的数据处理就可以使用工厂模式。
接口定义对数据处理的规则,工厂类负责根据传入的参数,判断实例化哪一个数据库类。

先使用,在过程中更据条件,实例化选择返回不同的类。这就是工厂模式(个人理解)

//编写接口制定规则
interface IDatabasecontent {
    //数据库查询
    public function query();

    //insert
    public function insert($val, $table);
}

//mongodb 
class Mongodb implementes IDatabasescontent {
    public function query(){
        //mongodb查询
    }
    public function insert($val, $table){
        //mongodb插入
    }
}

//mysql
class Mysql implementes IDatabasecontent {
    public function query(){
        //mysql查询
    }
    public function insert($val, $table){
        //mysql插入
    }
}

//工厂类
class DatabaseFactory {
    public static function factory(){
        $type = loadTypeFromConfig();
        switch($type){
            case 'mongodb' :
                return new Mongodb();
                break;
            case 'mysql':
                return new Mysql();
                break;
            default:
                //其他
        }
    }
}

$instance = DatabaseFactory::factory('数据库链接');
$instance->query();