MySQL 序列化 JSON 数据类型

Sequelize JSON data type

提问by MikkoP

I have defined a model as

我已经定义了一个模型

module.exports = function (sequelize, DataTypes) {
  const MyModel = sequelize.define('MyModel', {
    data: {
      type: DataTypes.JSON,
      ...
    },
    ...
  });

  return MyModel;
};

I query it using

我查询它使用

MyModel.findAll().then(myModels => ...);

However, datafield in the query result is a string, not a JSON object. How do I fix it?

但是data查询结果中的字段是字符串，而不是JSON对象。我如何解决它？

回答by Jalal

It's not supported yet MySQL JSON Data Type #4727. But you can do something like this:

尚不支持MySQL JSON 数据类型 #4727。但是你可以做这样的事情：

 module.exports = function (sequelize, DataTypes) {
      const MyModel = sequelize.define('MyModel', {
        data: {
          type: Sequelize.TEXT,
           get: function () {
                return JSON.parse(this.getDataValue('value'));
            },
            set: function (value) {
                this.setDataValue('value', JSON.stringify(value));
            },
    ,
          ...
        },
        ...
      });

      return MyModel;
};

I also found this package on github sequelize-jsonyou can give it a try if you don't want to use getters and setters.

我还在 github sequelize-json上找到了这个包，如果您不想使用 getter 和 setter，可以尝试一下。

回答by Ira Herman

Jalal's answer was great but didn't work for me unless I tweaked it a little. Here's what worked for me:

Jalal 的回答很好，但对我不起作用，除非我稍微调整一下。以下是对我有用的内容：

First:create a migration that adds the field you want as type TEXT. example - I want to add a field called addressto the Storestable:

首先：创建一个迁移，将您想要的字段添加为 type TEXT。示例 - 我想向表中添加一个名为address的Stores字段：

module.exports = {
  up: (queryInterface, Sequelize) => {
    return queryInterface.addColumn("Stores", "address", Sequelize.TEXT);
  },

  down: (queryInterface, Sequelize) => {
    return queryInterface.removeColumn("Stores", "address");
  }
};

Next:Inside your model, add the field with getters and setters to your list (object) of fields and datatypes:

下一步：在您的模型中，将带有 getter 和 setter 的字段添加到您的字段和数据类型列表（对象）中：

address: {
  type: DataTypes.TEXT,
    get: function() {
      return JSON.parse(this.getDataValue("address"));
    },
    set: function(value) {
      return this.setDataValue("address", JSON.stringify(value));
    }
},

So my entire model looks like this:

所以我的整个模型看起来像这样：

module.exports = (sequelize, DataTypes) => {
  const Store = sequelize.define(
    "Store",
    {
      name: DataTypes.STRING,
      isActive: DataTypes.BOOLEAN,
      address: {
        type: DataTypes.TEXT,
        get: function() {
          return JSON.parse(this.getDataValue("address"));
        },
        set: function(value) {
          return this.setDataValue("address", JSON.stringify(value));
        }
      }
    },
    {}
  );
  Store.associate = function(models) {
    // associations can be defined here
    Store.hasMany(models.Order, {
      foreignKey: "id",
      targetKey: "storeId"
    });
  };
  return Store;
};

Nowyou can create and retrieve records just like you would with a JSON type field in the db.

现在，您可以像在 db 中使用 JSON 类型字段一样创建和检索记录。

For example: const store = await Store.create({name: "Joe's corner store", address: {address1: "123 test street", city: "Los Angeles", state: "CA"}})

例如： const store = await Store.create({name: "Joe's corner store", address: {address1: "123 test street", city: "Los Angeles", state: "CA"}})

Some notes:

一些注意事项：

In the above code blocks...

在上面的代码块中...

• Replace addresswith the field name you want to use.
• Replace Storeswith your model/table name.
• I added returnon the setter, otherwise it was erroring out when trying to create a new record (the way Jalal has it).

• 替换address为您要使用的字段名称。
• 替换Stores为您的模型/表名称。
• 我添加return了设置器，否则在尝试创建新记录时出错（Jalal 拥有它的方式）。

回答by redeye

JSON data types aren't supported for MySQL.

MySQL 不支持 JSON 数据类型。

See the docs here http://docs.sequelizejs.com/en/v3/docs/models-definition/#data-types

请参阅此处的文档http://docs.sequelizejs.com/en/v3/docs/models-definition/#data-types

A work around could be to use text and stringify/parse when querying it

解决方法可能是在查询时使用文本和字符串化/解析

回答by Hamza Hmem

The easiest solution is to use this little library called sequelize-json

最简单的解决方案是使用这个名为sequelize-json 的小库

Create a database and a Schema:

创建数据库和架构：

var Sequelize = require('sequelize'),
  JsonField = require('sequelize-json'),
  db,
  User;

db = new Sequelize('database', 'username', 'password', {
  dialect: 'sqlite',
  logging: false
});

User = db.define('User', {
  username: Sequelize.STRING,
  jsonField: JsonField(db, 'User', 'jsonField')
});

Note the parameters of JsonField, you pass your Sequelize instance, the name of the model, and the name of the field. A little awkard, but this is needed in order to add the proper hooks to the model instance.

注意 JsonField 的参数，传递你的 Sequelize 实例、模型名称和字段名称。有点尴尬，但这是向模型实例添加适当钩子所必需的。

Now, you can always treat that field as a json object:

现在，您始终可以将该字段视为 json 对象：

User.create({
      username: 'Scott',
      jsonField: {
        likes: ['running', 'node']
      }
    })
    .then(function(user) {
      user.jsonField.likes.push('tests');
      return user.save();
    })
    .then(function(user) {
      expect(user.jsonField).to.be.a('object');
      expect(user.jsonField.likes).to.have.length(3);
    });

它将与普通的保存命令以及 updateAttribute 命令一起使用。

回答by sharad shetty

No need to use getters and setters as JSON is now supported by sequelize.See sequelize api

无需使用 getter 和 setter，因为 sequelize 现在支持 JSON。请参阅sequelize api