虽然现在流行NoSQL,但是关系型数据库还是非常好用的。大多数项目还是关系型数据库为主的。

MySQL里面其实是有一个JSON数据类型的,如果用来存储一些字段本身容易变动的数据还是非常非常方便的,研究了一下,做个记录。

原始表如下

1	{"A": 1, "B": 1, "C": 111, "D": "string0"}
2	{"A": 2, "B": 1, "C": 1, "D": "string1"}
3	{"A": 3, "B": 2, "C": 999, "D": "string2"}
4	{"A": 4, "B": 2, "C": 1, "D": "string3"}
5	{"A": 0, "B": 66, "C": 111}

简单查询如下

SELECT * from  pa_stock WHERE Data->'$.A'=1
SELECT * from  pa_stock WHERE Data->'$.B'=2  
SELECT * from  pa_stock WHERE Data->'$.B'=2  AND Data->'$.A'=3

#注意这三种写法,->>和JSON_UNQUOTE等效
SELECT Data->"$.D",Data->>"$.D",JSON_UNQUOTE(Data->"$.D") from  pa_stock WHERE Data->'$.B'=2  AND Data->'$.A'=3

特别要注意数字文本引号的问题

#正确写法
SELECT * from  pa_stock WHERE Data->'$.B'=2  
#错误写法
SELECT * from  pa_stock WHERE Data->'$.B'='2'

 

更新

#JSON_SET如果存在则更新,不存在则添加
UPDATE `pa_stock` SET `Data` = JSON_SET(`Data`,'$.A',44) WHERE Code=5
UPDATE `pa_stock` SET `Data` = JSON_SET(`Data`,'$.A',55,'$.B',66) WHERE Code=5

#JSON_INSERT如果存不做任何操作,不存在则添加
UPDATE `pa_stock` SET `Data` = JSON_INSERT(`Data`,'$.A',11) WHERE Code=5

#JSON_REPLACE如果存在则更新,不存在则不做任何操作
UPDATE `pa_stock` SET `Data` = JSON_REPLACE(`Data`,'$.A',11) WHERE Code=5

#JSON_REMOVE如果存在则删除,不存在则不做任何操作
UPDATE `pa_stock` SET `Data` = JSON_REMOVE(`Data`,'$.A') WHERE Code=5

 

另外,如果在dapper中使用的话,需要做适配

    public class JsonObjectTypeHandler : SqlMapper.ITypeHandler
    {
        public void SetValue(IDbDataParameter parameter, object value)
        {
            parameter.Value = (value == null)
                ? (object)DBNull.Value
                : JsonConvert.SerializeObject(value);
            parameter.DbType = DbType.String;
        }
        public object Parse(Type destinationType, object value)
        {
            return JsonConvert.DeserializeObject(value.ToString(), destinationType);
        }
    }

在程序的初始阶段配置一下

SqlMapper.AddTypeHandler(typeof(DataJSONInfo), new Framework.BLL.Dapper.JsonObjectTypeHandler());




Leave a Reply