SET和JSON类型学习

文章背景：

        公司新增需求：新增一个开放权限范围authRole，一开始得想法是直接使用string存储，后来前端说不可以使用明文，不可以传入"学生、老师"这样 ​ 传入得参数最勉强也是"1、4"，这里继续使用string类型的话，在进行模糊查询的时候就会出现一个错误，例如在查询权限包含1的数据时，包含11的也会被查询出来 ​ 不符合需求，然后看着这些静态变量萌生了一个想法是否可以使用set类型，然后开始学习set，问为什么不多加一个管理表，首先我是在别人的代码基础上改的，新增 一个表我觉得很麻烦，而且只是新增一个字段就多加一个表我觉得不太好。所以我想着试试可不可以使用SET类型。

SET类型

        SET是一个字符串对象，可以有零或多个值，其值来自表创建时规定的允许的一列值。指定包括多个SET成员的SET列值时各成员之间用逗号(‘,’)间隔开。所以SET成员值本身不能包含逗号。SET最多可以有64个不同的成员，当创建表时，SET成员值的尾部空格将自动被删除；当检索时，保存在SET列的值使用列定义中所使用的大小写来显示。请注意可以为SET列分配字符集和校对规则。对于二进制或大小写敏感的校对规则，当为列分配值时应考虑大小写。(官方解释，不懂没事直接看例子)

如何定义SET类型

 

CREATE TABLE `set_tabless` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `auth_role` SET('value1','value2','value3','value4','value5','value6','value7','value8','value9','value10') DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MYISAM AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

如何插入数据

INSERT INTO set_tabless(`auth_role`) VALUES ('value1');
INSERT INTO set_tabless(`auth_role`) VALUES ('value2');
INSERT INTO set_tabless(`auth_role`) VALUES ('value3');
INSERT INTO set_tabless(`auth_role`) VALUES ('3');
INSERT INTO set_tabless(`auth_role`) VALUES ('5');
INSERT INTO set_tabless(`auth_role`) VALUES ('1');

新增结果

                ​​​​​​​        ​​​​​​​        

通过上面的插入数据例子就能看出来set底层用的是二进制存储，如下面列表

SET成员	十进制	二进制
value1	1	0001
value2	2	0010
value3	4	0100

如何查询数据

SELECT * FROM set_tabless WHERE FIND_IN_SET('value1',auth_role)>0;   //包含value1的都会查询出来不包括16

SELECT * FROM set_tabless WHERE auth_role LIKE '%1%';  //模糊查询包含value1的都会查询出来
/**
官方解释 ：第1个语句找出SET_col包含value set成员的行。第2个类似，但有所不同：它在其它地方找出set_col包含value的行，甚至是在另一个SET成员的子字符串中。
**/

SELECT * FROM set_tabless WHERE auth_role & 5;   //包含value1和value3的都会查询出来不包括16，寻找包含第1个set成员的值

SELECT * FROM set_tabless WHERE auth_role = 1;    //精确查询，只会查出来只包含value1

/**
官方解释 ：第1个语句寻找包含第1个set成员的值。第2个语句寻找一个确切匹配的值。应注意第2类的比较。将set值与'val1,val2'比较返回的结果与同'val2,val1'比较返回的结果不同。指定值时的顺序应与在列定义中所列的顺序相同。如果想要为SET列确定所有可能的值，使用SHOW COLUMNS FROM tbl_name LIKE set_col并解析输出中第2列的SET定义。
**/

 

学完了SET类型的用法回到业务需求 这里我主要是想要实现拓展性auth_role          SET('value1','value2','value3','value4','value5','value6','value7','value8','value9','value10') DEFAULT NULL, 这里只需要到value10就可以存储到512，这里权限可以存1，可以存4，也可以存1、4，也能存3，但是不能存储1、3（也可能是我没想到） 所以我只能放弃，开始了解JSON

JSON：从MySQL 5.7.8开始支持原生JSON（RFC 7159规范定义）数据类型，可以更加高效的存储和管理JSON文档。与在字符数据类型中存储json数据相比，原生JSON数据类型提供了自动的格式验证以及优化的存储格式，可以快速访问文档中的元素节点。JSON数据最大长度不能超过max_allowed_packet的限制。

 

如何定义JSON类型

CREATE TABLE EXAMPLES (
  id INT AUTO_INCREMENT PRIMARY KEY,
  DATAS JSON
);

如何插入数据

这里存储数组，也可以存储对象
INSERT INTO EXAMPLE (DATA) VALUES ('[1]');
INSERT INTO EXAMPLE (DATA) VALUES ('[2]');
INSERT INTO EXAMPLE (DATA) VALUES ('[3]');
INSERT INTO EXAMPLE (DATA) VALUES ('[4]');
INSERT INTO EXAMPLE (DATA) VALUES ('[1,2]');
INSERT INTO EXAMPLE (DATA) VALUES ('[1,3]');
INSERT INTO EXAMPLE (DATA) VALUES ('[2,3]');
INSERT INTO EXAMPLE (DATA) VALUES ('[11]');

 

           ​​​​​​​        ​​​​​​​        ​​​​​​​​​​​​​​        ​​​​​​​        

查询数据

SELECT * FROM `example` WHERE JSON_CONTAINS(`data`, JSON_ARRAY(1));//这里会查询出来全部包含1数据

学会了开始实践

//实体类
public class Example {
    private Integer id;
    private List<Integer> data;
    private String datas; 
/**
为什么还要多此一举设置一个datas呢，是因为mybatis不支持json类型，像sql语句直接插入的就是一个集合，mybatis这里需要将前端传进来集合转为json字符串，将json字符串存进数据库，查询的时候也是，将查询出来的data赋给datas，然后json字符串转数组赋给data，传给前端
**/

	<resultMap id="ExampleEntityMap" type="com.cy.rs.entity.Example">
        <id column="id" property="id"></id>
        <result column="data" property="datas"></result>
    </resultMap>
    <insert id="insert" useGeneratedKeys="true" keyProperty="id">
        INSERT INTO
            example (
            data
        ) VALUES
            (#{datas})
    </insert>
    <select id="select" resultMap="ExampleEntityMap">
        SELECT * FROM example WHERE  JSON_CONTAINS(data, JSON_ARRAY(#{data}));
    </select>	

 本文仅供参考，是自己的一篇小笔记，可能存在很多错误