MariaDB 和 mysql 对于JSON数据存储处理对比

因为业务需求，故用到了数据库的JSON存储功能，JSON正迅速成为数据交换和非结构化数据的标准格式，可以将关系数据与非结构化数据混合。

json 可以 随意增减存储长度，对于不确定列个数和不确定列的字符串存储长度的情况，可以很好的完成存储功能；

然后因为我使用的是MariaDB 10.1版本，支持下面两种示例：

DROP TABLE IF EXISTS `test1`;
CREATE TABLE `test1`  (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `info`  blob,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_bin ROW_FORMAT = Compact;


INSERT INTO test1(info)
VALUES(COLUMN_CREATE('XB_NAME', '男', 'MZ', '汉族'));

INSERT INTO test1(info)
VALUES(COLUMN_CREATE('XB_NAME', '女', 'MZ', '回族'));

SELECT id,COLUMN_GET(info, 'XB_NAME' AS CHAR) AS XB_NAME FROM test1 where 
COLUMN_GET(info, 'XB_NAME' AS CHAR)='男'

上面这种个人觉得不是很方便，有条件检索，可以满足我的需求，但是传值的方式有点不好，所以，继续探索；

DROP TABLE IF EXISTS `test1`;
CREATE TABLE `test1`  (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `info`  blob,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_bin ROW_FORMAT = Compact;


INSERT INTO test1(info)
VALUES(COLUMN_CREATE('XB_NAME', '男', 'MZ', '汉族'));

INSERT INTO test1(info)
VALUES(COLUMN_CREATE('XB_NAME', '女', 'MZ', '回族'));

SELECT id,COLUMN_GET(info, 'XB_NAME' AS CHAR) AS XB_NAME FROM test1 where 
COLUMN_GET(info, 'XB_NAME' AS CHAR)='男'

 

然而并没有找到条件查询的方法；

然后网上有人说，MariaDB 10.2 不支持条件查询，为了自己检验一下，然后，我把数据库升级了，从10.1 升级到了10.4.6版本

本地windows 升级方法

    本地windows MariaDB 升版本 可以参考一下

下面这个连接是官网对于json的介绍和文档：

https://mariadb.com/resources/blog/json-with-mariadb-10-2/

好了，现在数据库也升级好了，那继续测试，如果想看更详细的示例，就去官网看

DROP TABLE IF EXISTS `products`;
CREATE TABLE products(id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(255) NOT NULL,
  price DECIMAL(9,2) NOT NULL,
  stock INTEGER NOT NULL,
  attr VARCHAR(1024),
  CHECK (attr IS NULL OR JSON_VALID(attr)));

插入数据： 

INSERT INTO products VALUES(NULL, 'Jeans', 10.5, 165, NULL);
INSERT INTO products VALUES(NULL, 'Shirt', 10.5, 78, '{"size": 42, "colour": "white"}');
INSERT INTO products VALUES(NULL, 'Blouse', 17, 15, '{"colour": "white"}');

添加索引给json

##增加一列
ALTER TABLE products ADD attr_colour VARCHAR(32) AS (JSON_VALUE(attr, '$.colour'));
#在增加列上面加索引
CREATE INDEX products_attr_colour_ix ON products(attr_colour);

#再增加一条数据
INSERT INTO products VALUES(NULL, 'Shirt', 10.5, 78, '{"size": 42, "colour": "red"}',null);

 

索引是一个计算列，跟你的attr数据相关，当attr改变，索引列数据也会随之改变

可以在索引上进行条件查询：

SELECT * FROM products WHERE attr_colour = 'white';

这种要做条件查询的话，需要加索引，但是因为我们当前的业务数据量较大，主要是单个json对象比较大，即单行数据比较多，可能单行有100多列，每个字段字符超过300的情况，每一个列都要加检索，这种情况下，数据库单表比较难以支撑，汉字不可超过2万多单行，字节不可超过65535单行，所以，再看看mysql的json处理方案；

-------------------------------------------------------------------我不是很擅长描述，mysql---------------------------------------------------------------

好了，在本机有装了一台mysql 8.0 版本，端口写的3308，因为3306已经被MariaDB 占用了

官方json文档地址：https://dev.mysql.com/doc/refman/8.0/en/json.html，大家可以自己去看，

MySQL支持由RFC 7159定义的原生JSON数据类型，它支持对JSON (JavaScript对象表示法)文档中的数据的有效访问。

#创建一张表
CREATE TABLE t1 (jdoc JSON);
INSERT INTO t1 VALUES('{"key1": "value1", "key2": "value2"}');
INSERT INTO t1 VALUES('[1, 2,3]');

# JSON_TYPE() 获取参数并转换json，返回对应的类型
SELECT JSON_TYPE('["a", "b", 1]');
SELECT JSON_TYPE('"hello"');

# JSON_OBJECT() 插入json对象  必须使用反斜杠转义每个引用字符
INSERT INTO t1 VALUES (JSON_OBJECT("mascot", "Our mascot is a dolphin named \"Sakila\"."));
#当直接插入 json时,需要使用双反斜杠，
INSERT INTO t1 VALUES ('{"mascot": "Our mascot is a dolphin named \\"Sakila\\"."}');

SELECT jdoc FROM t1;

条件查询，没问题： 

SELECT jdoc FROM t1 t where t.jdoc -> '$.mascot' LIKE '%O%';

还有很多其他的功能，大家可以去官网了解，目前为止，我需要的功能有了，开始写代码啦