mysql json格式数据查询操作

预设表名是 base_data , json列名是 json_value

json_value的值是

{“ids”:[“a”,“b”],“datas”:[{“name”:“张三”}]}

那么查询语句应该是

select * from base_data where JSON_CONTAINS(json_value -> "$.ids", JSON_ARRAY('a'));

或者

select * from base_data where JSON_CONTAINS(json_value -> "$.ids[*]", JSON_ARRAY('a'));

两种都行,具体什么区别暂时还不得而知

补充知识:mysql中varchar字段长度超过限制长度自动截取的问题-----MySQL的sql_mode模式说明及设置

MySQL的sql_mode合理设置

sql_mode是个很容易被忽视的变量,默认值是空值,在这种设置下是可以允许一些非法操作的,比如允许一些非法数据的插入。在生产环境必须将这个值设置为严格模式,所以开发、测试环境的数据库也必须要设置,这样在开发测试阶段就可以发现问题.

sql model 常用来解决下面几类问题

(1) 通过设置sql mode, 可以完成不同严格程度的数据校验,有效地保障数据准备性。

(2) 通过设置sql model 为宽松模式,来保证大多数sql符合标准的sql语法,这样应用在不同数据库之间进行迁移时,则不需要对业务sql 进行较大的修改。

(3) 在不同数据库之间进行数据迁移之前,通过设置SQL Mode 可以使MySQL 上的数据更方便地迁移到目标数据库中。

sql_mode常用值如下:

ONLY_FULL_GROUP_BY:

对于GROUP BY聚合操作,如果在SELECT中的列,没有在GROUP BY中出现,那么这个SQL是不合法的,因为列不在GROUP BY从句中

NO_AUTO_VALUE_ON_ZERO:

该值影响自增长列的插入。默认设置下,插入0或NULL代表生成下一个自增长值。如果用户 希望插入的值为0,而该列又是自增长的,那么这个选项就有用了。

STRICT_TRANS_TABLES:

在该模式下,如果一个值不能插入到一个事务表中,则中断当前的操作,对非事务表不做限制

NO_ZERO_IN_DATE:

在严格模式下,不允许日期和月份为零

NO_ZERO_DATE:

设置该值,mysql数据库不允许插入零日期,插入零日期会抛出错误而不是警告。

ERROR_FOR_DIVISION_BY_ZERO:

在INSERT或UPDATE过程中,如果数据被零除,则产生错误而非警告。如 果未给出该模式,那么数据被零除时MySQL返回NULL

NO_AUTO_CREATE_USER:

禁止GRANT创建密码为空的用户

NO_ENGINE_SUBSTITUTION:

如果需要的存储引擎被禁用或未编译,那么抛出错误。不设置此值时,用默认的存储引擎替代,并抛出一个异常

PIPES_AS_CONCAT:

将"||"视为字符串的连接操作符而非或运算符,这和Oracle数据库是一样的,也和字符串的拼接函数Concat相类似

ANSI_QUOTES:

启用ANSI_QUOTES后,不能用双引号来引用字符串,因为它被解释为识别符

ORACLE的sql_mode设置等同:

PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE, NO_KEY_OPTIONS, NO_TABLE_OPTIONS, NO_FIELD_OPTIONS, NO_AUTO_CREATE_USER.

如果使用mysql,为了继续保留大家使用oracle的习惯,可以对mysql的sql_mode设置如下:

在my.cnf添加如下配置

[mysqld]

sql_mode='ONLY_FULL_GROUP_BY,NO_AUTO_VALUE_ON_ZERO,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,

ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,PIPES_AS_CONCAT,ANSI_QUOTES'

注意:

MySQL5.6和MySQL5.7默认的sql_mode模式参数是不一样的,5.6的mode是NO_ENGINE_SUBSTITUTION,其实表示的是一个空值,相当于没有什么模式设置,可以理解为宽松模式。5.7的mode是STRICT_TRANS_TABLES,也就是严格模式。

如果设置的是宽松模式,那么我们在插入数据的时候,即便是给了一个错误的数据,也可能会被接受,并且不报错,例如:我在创建一个表时,该表中有一个字段为name,给name设置的字段类型时char(10),如果我在插入数据的时候,其中name这个字段对应的有一条数据的长度超过了10,例如'1234567890abc',超过了设定的字段长度10,那么不会报错,并且取前十个字符存上,也就是说你这个数据被存为了'1234567890',而'abc'就没有了,但是我们知道,我们给的这条数据是错误的,因为超过了字段长度,但是并没有报错,并且mysql自行处理并接受了,这就是宽松模式的效果,其实在开发、测试、生产等环境中,我们应该采用的是严格模式,出现这种错误,应该报错才对,所以MySQL5.7版本就将sql_mode默认值改为了严格模式,并且我们即便是用的MySQL5.6,也应该自行将其改为严格模式,而你记着,MySQL等等的这些数据库,都是想把关于数据的所有操作都自己包揽下来,包括数据的校验,其实好多时候,我们应该在自己开发的项目程序级别将这些校验给做了,虽然写项目的时候麻烦了一些步骤,但是这样做之后,我们在进行数据库迁移或者在项目的迁移时,就会方便很多,这个看你们自行来衡量。mysql除了数据校验之外,你慢慢的学习过程中会发现,它能够做的事情还有很多很多,将你程序中做的好多事情都包揽了。

改为严格模式后可能会存在的问题:

若设置模式中包含了NO_ZERO_DATE,那么MySQL数据库不允许插入零日期,插入零日期会抛出错误而不是警告。例如表中含字段TIMESTAMP列(如果未声明为NULL或显示DEFAULT子句)将自动分配DEFAULT '0000-00-00 00:00:00'(零时间戳),也或者是本测试的表day列默认允许插入零日期 '0000-00-00' COMMENT '日期';这些显然是不满足sql_mode中的NO_ZERO_DATE而报错。

模式设置和修改(以解决上述问题为例):

方式一:

先执行select @@sql_mode,复制查询出来的值并将其中的NO_ZERO_IN_DATE,NO_ZERO_DATE删除,然后执行set sql_mode = '修改后的值'或者set session sql_mode='修改后的值';,例如:set session sql_mode='STRICT_TRANS_TABLES';改为严格模式

此方法只在当前会话中生效,关闭当前会话就不生效了。

方式二:

先执行select @@global.sql_mode,复制查询出来的值并将其中的NO_ZERO_IN_DATE,NO_ZERO_DATE删除,然后执行set global sql_mode = '修改后的值'。

此方法在当前服务中生效,重新MySQL服务后失效

方法三:在mysql的安装目录下,或my.cnf文件(windows系统是my.ini文件),新增

sql_mode = ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,

添加my.cnf如下:

[mysqld]

sql_mode=ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER

然后重启mysql。

此方法永久生效.当然生产环境上是禁止重启MySQL服务的,所以采用方式二加方式三来解决线上的问题,那么即便是有一天真的重启了MySQL服务,也会永久生效了。

以上这篇mysql json格式数据查询操作就是小编分享给大家的全部内容了,希望能给大家一个参考,也希望大家多多支持我们。

(0)

相关推荐

  • mysql大数据查询优化经验分享(推荐)

    正儿八经mysql优化! mysql数据量少,优化没必要,数据量大,优化少不了,不优化一个查询10秒,优化得当,同样查询10毫秒. 这是多么痛的领悟! mysql优化,说程序员的话就是:索引优化和where条件优化. 实验环境:MacBook Pro MJLQ2CH/A,mysql5.7,数据量:212万+ ONE: select * from article INNER JOIN ( SELECT id FROM article WHERE length(content_url) > 0 an

  • 浅谈MySQL数据查询太多会OOM吗

    目录 全表扫描对server层的影响 全表扫描对InnoDB的影响 InnoDB内存管理 小结 我的主机内存只有100G,现在要全表扫描一个200G大表,会不会把DB主机的内存用光? 逻辑备份时,可不就是做整库扫描吗?若这样就会把内存吃光,逻辑备份不是早就挂了? 所以大表全表扫描,看起来应该没问题.这是为啥呢? 全表扫描对server层的影响 假设,我们现在要对一个200G的InnoDB表db1. t,执行一个全表扫描.当然,你要把扫描结果保存在客户端,会使用类似这样的命令: mysql -h$

  • mysql聚合统计数据查询缓慢的优化方法

    写在前面 在我们日常操作数据库的时候,比如订单表.访问记录表.商品表的时候. 经常会处理计算数据列总和.数据行数等统计问题. 随着业务发展,这些表会越来越大,如果处理不当,查询统计的速度也会越来越慢,直到业务无法再容忍. 所以,我们需要先了解.思考这些场景知识点,在设计之初,便预留一些优化空间支撑业务发展. sql聚合函数 在mysql等数据中,都会支持聚合函数,方便我们计算数据. 常见的有以下方法 取平均值 AVG() 求和 SUM() 最大值 MAX() 最小值 MIN() 行数 COUNT

  • MySQL与PHP的基础与应用专题之数据查询语句

    目录 概述 ORDERBY 例1 例2 LIMIT 例1 例2 GROUPBY 例1 例2 HAVING 例1 例2 GROUP_CONCAT 例1 例2 DISTINCT 例1 例2 概述 从今天开始, 小白我将带领大家一起来补充一下 数据库的知识. ORDER BY 在 SQL 中, 我们可以使用 ORDER BY 对查询结果进行一列或多列排序. SQL 语句: SELECT 列名1, 列名2 FROM 表名1, 表名2 ORDER BY 列名, 列名[ASC|DESC] 注意: ASC 表

  • 详解MySQL数据库千万级数据查询和存储

    百万级数据处理方案 数据存储结构设计 表字段设计 表字段 not null,因为 null 值很难查询优化且占用额外的索引空间,推荐默认数字 0. 数据状态类型的字段,比如 status, type 等等,尽量不要定义负数,如 -1.因为这样可以加上 UNSIGNED,数值容量就会扩大一倍. 可以的话用 TINYINT.SMALLINT 等代替 INT,尽量不使用 BIGINT,因为占的空间更小. 字符串类型的字段会比数字类型占的空间更大,所以尽量用整型代替字符串,很多场景是可以通过编码逻辑来实

  • MySQL与PHP的基础与应用专题之数据查询

    目录 概述 条件查询 例1 例2 运算符IN 例1 例2 运算符Like 例1 例2 概述 从今天开始, 小白我将带领大家一起来补充一下 数据库的知识. 条件查询 我们可以使用关键词Where来指定条件, 用于插入, 修改删除或者查询数据. 运算符 描述 = 等于 <> 不等于 > 大于 < 小于 >= 大于等于 <= 小于等于 between and 小于等于 and 与, 表示左右两边的条件同时成立 or 或, 表示左右两边只要有一个条件成立 表记录: 例 1 从表

  • mysql json格式数据查询操作

    预设表名是 base_data , json列名是 json_value json_value的值是 {"ids":["a","b"],"datas":[{"name":"张三"}]} 那么查询语句应该是 select * from base_data where JSON_CONTAINS(json_value -> "$.ids", JSON_ARRAY('

  • PHP封装XML和JSON格式数据接口操作示例

    本文实例讲述了PHP封装XML和JSON格式数据接口操作.分享给大家供大家参考,具体如下: 使用PHP开发app后端的接口时,我们需要返回数据,最常用的就是XML和JSON格式的数据,那么样返回呢? 1.JSON json数据是最容制造的,只要使用php自带的json_encode()函数就可以简单的制作出json数据了 2.XML xml数据就要复杂一点了,让我们看一下xml数据的格式 那么怎样构造呢? 3.制作生产接口数据的类 <?php //定义生成接口数据类 class Api { /*

  • Mysql怎么存储json格式数据详解

    目录 前言 JSON 数据类型推荐使用在不经常更新的静态数据存储 查询 json数据 增加索引 使用场景 总结 前言 Mysql5.7版本以后新增的功能,Mysql提供了一个原生的Json类型,Json值将不再以字符串的形式存储,而是采用一种允许快速读取文本元素(document elements)的内部二进制(internal binary)格式.在Json列插入或者更新的时候将会自动验证Json文本,未通过验证的文本将产生一个错误信息 JSON 数据类型推荐使用在不经常更新的静态数据存储 创

  • php操作JSON格式数据的实现代码

    知识点: 1.JSON数据格式介绍 2.对数据编码成JSON格式 3.对JSON数据进行解码,并操作 JSON数据格式表示方式如下: 复制代码 代码如下: { "programmers": [ { "firstName": "Brett", "lastName":"McLaughlin", "email": "aaaa" }, { "firstName&qu

  • C++使用JsonCpp库操作json格式数据示例

    本文实例讲述了C++使用JsonCpp库操作json格式数据的方法.分享给大家供大家参考,具体如下: 前言 JSON是一个轻量级的数据定义格式,比起XML易学易用,而扩展功能不比XML差多少,用之进行数据交换是一个很好的选择 JSON的全称为:JavaScript Object Notation ,顾名思义,JSON是用于标记javascript对象的,详情参考http://www.json.org/. 本文选择第三方库JsonCpp来解析json,JsonCpp是比较出名的c++解析库,在js

  • jQuery+Ajax+js实现请求json格式数据并渲染到html页面操作示例

    本文实例讲述了jQuery+Ajax+js实现请求json格式数据并渲染到html页面操作.分享给大家供大家参考,具体如下: 1.先给json格式的数据: [ {"id":1,"name":"stan"}, {"id":2,"name":"jack"}, {"id":3,"name":"lucy"}, {"id&quo

  • php处理json格式数据经典案例总结

    本文实例总结了php处理json格式数据的方法.分享给大家供大家参考,具体如下: 1.json简介: 何为json? 简 单地说,JSON 可以将 JavaScript 对象中表示的一组数据转换为字符串,然后就可以在函数之间轻松地传递这个字符串,或者在异步应用程序中将字符串从 Web 客户机传递给服务器端程序. 通俗点讲,它是一种数据的存储格式,就像php序列化后的字符串一样. 它也是一种数据描述,比如:我们将一个数组序列化后存放,就可以很容易的反序列化后应用:json也是如此,只不过它搭建的是

  • jQuery向后台传入json格式数据的方法

    本文实例讲述了jQuery向后台传入json格式数据的方法.分享给大家供大家参考.具体分析如下: 前后台数据交互一般都用json格式,后台可以直接将json对应转化为实体对象.方便以后的操作.jQuery向后台传数据的时候,我们会发现他会自动转化成查询字符串,不能真正传入一个json.而且用jquery对表单序列化的时候,返回的格式是一个数组,还需要作进一步转换.其实只要我们在ajax方法中配置一些东西就可以完成.代码如下: <form id="ff"> <input

  • php+Ajax处理xml与json格式数据的方法示例

    本文实例讲述了php+Ajax处理xml与json格式数据的方法.分享给大家供大家参考,具体如下: 一.ajax如何处理xml数据格式 register.php 只需修改上一篇<php+Ajax无刷新验证用户名操作>中chuli函数部分 functionchuli(){ // window.alert("cuhli函数被调用"+myXmlHttpRequest.readyState); //我要取出从register.php返回的数据 if(myXmlHttpRequest

  • JS对象与JSON格式数据相互转换

    目前的项目数据交互几乎都用JQuery,所以处理流程是:前端页面数据->JS对象->jQuery提交->python处理,另外一种就是倒过来.python肯定不能直接处理JS对象数据,所以要把JS对象转换成为python能处理的一种数据格式(通常是字典dict),同样,python取数据反馈到前端也要把字典数据转换成JS能处理的对象,这个中间转换数据格式通常就是JSON. 一.JS对象转换成为JSON 流程:读取前端页面数据,组装成为JS对象,并通过jQuery的$.post()方法传递

随机推荐