最新公告
  • 欢迎您光临编程知识分享网 加入我们
  • MySQL JSON数据类型详解

    简介

    在MySQL5.7.8之后开始支持一种可高效获取JSON文本中数据的原生JSON类型,该类型具有以下优点:

    • JSON数据有效性检查:BLOB类型无法在数据库层做这样的约束性检查
    • 查询性能的提升:查询不需要遍历所有字符串才能找到数据
    • 支持索引:通过虚拟列的功能可以对JSON中的部分数据进行索引

    另外,系统对JSON格式做了一些限制:

    • JSON文本的最大长度取决有系统常量:max_allowed_packet。该值仅在服务器进行存储的时候进行限制,在内存中进行计算的时候是允许超过该值的。
    • JSON列不可有默认值
    • JSON列与其他二进制类型列一样是无法创建索引。但是可以从JSON列中所存储的文本中某些表列值进行创建索引。MySQL最优控制器同样在通过JSON表达创建的索引中进行查询。

    如何使用

    1. 创建表

    create table `test`(
        `id` INT AUTO_INCREMENT PRIMARY KEY,
    	`content` JSON
    ) CHARSET = utf8;
    
    
    

    2. 插入两条数据

    INSERT INTO test (content) VALUES ('{
    	"name": "baidu",
    	"host": "www.baidu.com"
    }');
    INSERT INTO test (content) VALUES ('{
    	"name": "alibaba",
    	"host": "www.alibaba.com"
    }');
    

    注意:

    JSON列存储的必须是JSON格式数据,否则会报错。ERROR 3140 (22032): Invalid JSON text: "Invalid value." at position 0 in value for column 'test.content'.

    3. 查询

    这里主要将关于JSON的查询。

    对于表中JSON数据的查询,可以根据JSON中的key值进行查询,看下面SQL语句

    select JSON_EXTRACT(content,’$.name’),JSON_EXTRACT(content,’$.host’) from test;

     

    select JSON_EXTRACT(content,’$.name’),JSON_EXTRACT(content,’$.host’) from test where JSON_EXTRACT(content,’$.name’) = “baidu”;

    JSON函数支持

    https://dev.mysql.com/doc/refman/5.7/en/json-function-reference.html

    JSON中虚拟列的使用

    对于索引,JSON字段无法对其中的一个key值进行索引,但是虚拟列可以,我们可以建立一个虚拟列和JSON中key值建立联系。

    1. 增加虚拟列v_name,v_host

    注意:养成加前缀的好习惯, 例如这里使用”v_”来标记该字段是一个虚拟字段,在团队开发时,共同遵守一个约定, 相互配合起来会非常顺利。

    ALTER TABLE test ADD COLUMN v_name CHAR(10) AS (content->’$.name’); ALTER TABLE test ADD COLUMN v_host CHAR(30) AS (content->’$.host’);

    下面对虚拟列建立索引

    alter table test add index virtual_index(v_name);

    然后查看基于v_name的查找的执行计划:

    explain select content from test where v_name = “baidu”;

    从结果来看,查找已经走索引了。

    上表中字段信息解释:

    字段名 解释
    id 选择标识符
    select_type 查询的类型
    table 输出结果的表,也就是被查询的表
    partions 表示匹配的分区
    type 表示表的连接类型
    possible_keys 表示查询时,可能使用的索引
    key 表示实际使用的索引
    key_len 表示字段的长度
    ref 列与索引的比较
    rows 扫描出的行数(估算的行数)
    filtered 按查询条件过滤的行百分比
    Extra 执行情况的描述和说明

    详细的说明参考这篇博客:https://www.cnblogs.com/tufujie/p/9413852.html

    对虚拟列的简介

    在MySQL 5.7中,支持两种Generated Column,即Virtual Generated Column和Stored Generated Column,前者只将Generated Column保存在数据字典中(表的元数据),并不会将这一列数据持久化到磁盘上;后者会将Generated Column持久化到磁盘上,而不是每次读取的时候计算所得。很明显,后者存放了可以通过已有数据计算而得的数据,需要更多的磁盘空间,与Virtual Column相比并没有优势,因此,MySQL 5.7中,不指定Generated Column的类型,默认是Virtual Column。

    注意:

    在更新和插入数据时,不要给虚拟列设定值,否则会引发错误ERROR 3105 (HY000): The value specified for generated column 'v_name' in table 'test' is not allowed.

    赞赏

    微信赞赏支付宝赞赏

    编程知识分享网,一个有趣的平台!
    编程知识分享网|编程教程|资源下载|源码下载 » MySQL JSON数据类型详解

    常见问题FAQ

    免费下载或者VIP会员专享资源能否直接商用?
    本站所有资源版权均属于原作者所有,这里所提供资源均只能用于参考学习用,请勿直接商用。若由于商用引起版权纠纷,一切责任均由使用者承担。更多说明请参考 VIP介绍。
    提示下载完但解压或打开不了?
    最常见的情况是下载不完整: 可对比下载完压缩包的与网盘上的容量,若小于网盘提示的容量则是这个原因。这是浏览器下载的bug,建议用百度网盘软件或迅雷下载。若排除这种情况,可在对应资源底部留言,或 联络我们.。
    找不到素材资源介绍文章里的示例图片?
    对于PPT,KEY,Mockups,APP,网页模版等类型的素材,文章内用于介绍的图片通常并不包含在对应可供下载素材包内。这些相关商业图片需另外购买,且本站不负责(也没有办法)找到出处。 同样地一些字体文件也是这种情况,但部分素材会在素材包内有一份字体下载链接清单。
    关于编程知识分享网(www.ittce.com)
    编程知识分享网,一个有趣的平台,小心有毒!

    发表评论

    提供最优质的资源集合

    立即查看 了解详情
    升级SVIP尊享更多特权立即升级