谈谈 MySQL 的 JSON 数据类型

来到golang学习网的大家，相信都是编程学习爱好者，希望在这里学习数据库相关编程知识。下面本篇文章就来带大家聊聊《谈谈 MySQL 的 JSON 数据类型》，介绍一下MySQL、JSON、数据库、thinkjs，希望对大家的知识积累有所帮助，助力实战开发！

MySQL 5.7 增加了 JSON 数据类型的支持，在之前如果要存储 JSON 类型的数据的话我们只能自己做

CREATE TABLE user (
  id INT(11) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(30) NOT NULL,
  info JSON
);

表创建成功之后我们就按照经典的 CRUD 数据操作来讲讲怎么进行 JSON 数据类型的操作。

添加数据

添加数据这块是比较简单，不过需要理解 MySQL 对 JSON 的存储本质上还是字符串的存储操作。只是当定义为 JSON 类型之后内部会对数据再进行一些索引的创建方便后续的操作而已。所以添加 JSON 数据的时候需要使用字符串包装。

//adapter.js
const MySQL = require('think-model-mysql');
exports.model = {
  type: 'mysql',
  mysql: {
    handle: MySQL,
    ...
    jsonFormat: true
  }
};

//user.js
module.exports = class extends think.Controller {
  async indexAction() {
    const userId = await this.model('user').add({
      name: 'lilei',
      info: {
        sex: 'male',
        age: 16,
        hobby: ['basketball', 'football'],
        score: [85, 90, 100]
      }
    });

    return this.success(userId);
  }
}

下面让我们来看看最终存储到数据库中的数据是什么样的

//user.js
module.exports = class extends think.Controller {
  async indexAction() {
    const userModel = this.model('user');
    const field = "name, JSON_EXTRACT(info, '$.age') AS age, info->'$.sex' as sex";
    const users = await userModel.field(field).where('1=1').select();
    return this.success(users);
  }
}

返回喜欢篮球的男性用户

//user.js
module.exports = class extends think.Controller {
  async indexAction() {
    const userModel = this.model('user');
    const where = {
      _string: [
        "JSON_CONTAINS(info, '\"male\"', '$.sex')",
        "JSON_SEARCH(info, 'one', 'basketball', null, '$.hobby')"
      ]
    };

    const where1 = {
      _string: [
        "JSON_VALUE(`info`, '$.sex') = 'male'",
        "'basketball' MEMBER OF (JSON_VALUE(`info`, '$.hobby'))"
      ]
    };

    const where2 = {
      _string: [
        "JSON_VALUE(`info`, '$.sex') = 'male'",
        "JSON_OVERLAPS(JSON_VALUE(`info`, '$.hobby'), JSON_QUOTE('basketball'))"
      ]
    }
    const users = await userModel.field('name').where(where).select();
    return this.success(users);
  }
}

修改数据

MySQL 提供的 JSON 操作函数中，和修改操作相关的方法主要如下：

• //user.js
  module.exports = class extends think.Controller {
    async indexAction() {
      const userModel = this.model('user');
      await userModel.where({name: 'lilei'}).update({
        info: ['exp', "JSON_REPLACE(info, '$.age', 20)"]
      });
      return this.success();
    }
  }

  修改用户的爱好

  //user.js
  module.exports = class extends think.Controller {
    async indexAction() {
      const userModel = this.model('user');
      await userModel.where({name: 'lilei'}).update({
        info: ['exp', "JSON_ARRAY_APPEND(info, '$.hobby', 'badminton')"]
      });
      return this.success();
    }
  }

  删除用户的分数

  //user.js
  module.exports = class extends think.Controller {
    async indexAction() {
      const userModel = this.model('user');
      // 删除分数
      await userModel.where({name: 'lilei'}).update({
        info: ['exp', "JSON_REMOVE(info, '$.score[0]')"]
      });
      // 删除兴趣
      await userModel.where({name: 'lilei'}).update({
        info: ['exp', "JSON_REMOVE(`info`, JSON_UNQUOTE(JSON_SEARCH(`info`, 'one', 'badminton')))"]
      }); 
      return this.success();
    }
  }

  后记

  由于最近有一个需求，有一堆数据，要记录这堆数据的排序情况，方便根据排序进行输出。一般情况下肯定是给每条数据增加一个

  order

  字段来记录该条数据的排序情况。但是由于有着批量操作，在这种时候使用单字段去存储会显得特别麻烦。在服务端同事的建议下，我采取了使用 JSON 字段存储数组的情况来解决这个问题。

  也因为这样了解了一下 MySQL 对 JSON 的支持情况，同时将

  think-model

  做了一些优化，对 JSON 数据类型增加了支持。由于大部分 JSON 操作需要通过内置的函数来操作，这个本身是可以通过 EXP 条件表达式来完成的。所以只需要对 JSON 数据的添加和查询做好优化就可以了。

  整体来看，配合提供的 JSON 操作函数，MySQL 对 JSON 的支持完成一些日常的需求还是没有问题的。除了作为 WHERE 条件以及查询字段之外，其它的

  ORDER

  ,

  GROUP

  ,

  JOIN

  等操作也都是支持

  JSON

  数据的。

  不过对比 MongoDB 这种天生支持 JSON 的话，在操作性上还是要麻烦许多。特别是在类型转换这块，使用一段时间后发现非常容易掉坑。什么时候会带引号，什么时候会不带引号，什么时候需要引号，什么时候不需要引号，这些都容易让新手发憷。另外

  JSON_SEARCH()

  不支持数字查找这个也是一个不小的坑了。

  本篇关于《谈谈 MySQL 的 JSON 数据类型》的介绍就到此结束啦，但是学无止境，想要了解学习更多关于数据库的相关知识，请关注golang学习网公众号！