mysql 5.7 新特性

发表于 | 分类于 | | 阅读
mysql 5.7 新特性

mysql 5.7 新特性


MySQL 5.7 提供了一个新的高级的功能集, 和早期版本的区别如：

3倍更快的性能

InnoDB 相关改进

新的优化器

多源复制

GIS 相关改进

原生 JSON 支持


安全性

  • 初始化完成以后 root@localhost 用户的密码不再是空 ，而是一个随机密码

  • 默认连接就采用SSL的加密方式

  • 用户密码过期策略

  ALTER USER 'tom'@'localhost' PASSWORD EXPIRE INTERVAL 90 DAY;
  • 禁止用户使用
   ALTER USER  'tom'@'localhost' ACCOUNT LOCK;
   ALTER USER | 'tom'@'localhost'  ACCOUNT UNLOCK;


数据结构灵活性


MySQL数据库从5.7.8版本开始，也提供了对JSON的支持

JSON

server层提供了一堆便于操作JSON的函数

存储将JSON编码成BLOB，然后交由存储引擎层进行处理

相比MongoDB的优点

1.可以混合存储结构化数据和非结构化数据，同时拥有关系型数据库和非关系型数据库的优点
2.能够提供完整的事务支持

generate column

generated column，就是数据库中这一列由其他列计算而得, 比如 根据长 宽 获取面积

generated column 有两种:

  • virtual generated column 每次读取的时候，计算得到 (默认)

  • stored generated column 存在磁盘上 ， 占用更多的空间

例如：


CREATE TABLE area (length DOUBLE, wide DOUBLE, area DOUBLE AS (length * wide));

show create table area \G;

# 查看创建表的过程

 Table: area
Create Table: CREATE TABLE `area` (
  `length` double DEFAULT NULL,
  `wide` double DEFAULT NULL,
  `area` double GENERATED ALWAYS AS ((`length` * `wide`)) VIRTUAL  # 默认是virtual
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

使用generate column 就变得非常简单, 可以对通过计算得到的值列 进行索引

alter table area add index index_area(area);

JSON 函数 简介说明


NameDescription
JSON_APPEND() Append data to JSON document
JSON_ARRAY() Create JSON array
JSON_ARRAY_APPEND() Append data to JSON document
JSON_ARRAY_INSERT() Insert into JSON array
-> Return value from JSON column after evaluating path; equivalent to JSON_EXTRACT().
JSON_CONTAINS() Whether JSON document contains specific object at path
JSON_CONTAINS_PATH() Whether JSON document contains any data at path
JSON_DEPTH() Maximum depth of JSON document
JSON_EXTRACT() Return data from JSON document
->> Return value from JSON column after evaluating path and unquoting the result; equivalent to JSON_UNQUOTE(JSON_EXTRACT()).
JSON_INSERT() Insert data into JSON document
JSON_KEYS() Array of keys from JSON document
JSON_LENGTH() Number of elements in JSON document
JSON_MERGE() Merge JSON documents
JSON_OBJECT() Create JSON object
JSON_QUOTE() Quote JSON document
JSON_REMOVE() Remove data from JSON document
JSON_REPLACE() Replace values in JSON document
JSON_SEARCH() Path to value within JSON document
JSON_SET() Insert data into JSON document
JSON_TYPE() Type of JSON value
JSON_UNQUOTE() Unquote JSON value
JSON_VALID() Whether JSON value is valid

常用函数 介绍:

JSON_ARRAY 生成一个包含指定元素的json数组

    SELECT JSON_ARRAY(1, "abc", NULL, TRUE, CURTIME());
    +---------------------------------------------+
    | JSON_ARRAY(1, "abc", NULL, TRUE, CURTIME()) |
    +---------------------------------------------+
    | [1, "abc", null, true, "11:30:24.000000"]   |
    +---------------------------------------------+

JSON_OBJECT 生成一个包含指定K-V对的json object


    
   SELECT JSON_OBJECT('id', 87, 'name', 'carrot');
    +-----------------------------------------+
    | JSON_OBJECT('id', 87, 'name', 'carrot') |
    +-----------------------------------------+
    | {"id": 87, "name": "carrot"}            |
    +-----------------------------------------+

JSON_QUOTE 给选择的内容加上 “” 引号


select json_quote(name) from test_json limit 1;
+------------------+
| json_quote(name) |
+------------------+
| "json1"          |
+------------------+

CONVERT 转换成 json

select CONVERT('{"mail":"ojbk@qq.com"}', JSON);
+-----------------------------------------+
| CONVERT('{"mail":"ojbk@qq.com"}', JSON) |
+-----------------------------------------+
| {"mail": "ojbk@qq.com"}                 |
+-----------------------------------------+

…


mysql 5.7 与 mysql 5.6 的兼容性

sql_mode

select @@GLOBAL.sql_mode;-- 查询全局值
select @@SESSION.sql_mode; -- 查询当前会话值

mysql 5.6版本 默认是 不严格模式 NO_ENGINE_SUBSTITUTION

(1) 对自增主键插入空字符串''，虽然提示warning，但并不影响自增主键的生成。

INSERT INTO demo.`django_migrations` (id, app, NAME, applied) VALUES("", "name", "app", "applied");
       
# Incorrect integer value: '' for column 'id' at row 1


(2) 严格模式下  非聚集字段不能出现在查询列表中 分组的时候不能查询非聚集字段

在MySQL 5.7中，sql_mode调整为了

ONLY_FULL_GROUP_BY,
STRICT_TRANS_TABLES,
NO_ZERO_IN_DATE,
NO_ZERO_DATE,
ERROR_FOR_DIVISION_BY_ZERO,
NO_AUTO_CREATE_USER,
NO_ENGINE_SUBSTITUTION

MYSQL 5.6 版本的值为：

STRICT_TRANS_TABLES,
NO_ENGINE_SUBSTITUTION

ONLY_FULL_GROUP_BY：

出现在select语句、HAVING条件和ORDER BY语句中的列，必须是GROUP BY的列或者依赖于
GROUP BY列的函数列。

NO_AUTO_VALUE_ON_ZERO：

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

STRICT_TRANS_TABLES：

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

NO_ZERO_IN_DATE：

这个模式影响了是否允许日期中的月份和日包含0。如果开启此模式，2016-01-00是不允许的，
但是0000-02-01是允许的。它实际的行为受到 strict mode是否开启的影响1。

NO_ZERO_DATE：

设置该值，mysql数据库不允许插入零日期。它实际的行为受到 strictmode是否开启的影响

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后，不能用双引号来引用字符串，因为它被解释为识别符
Buy me a 肥仔水！