mysql5.7和8 mysql5.7和8.0版本语法有什么区别

转载

AI独步天下 2023-08-07 21:21:54

文章标签 mysql5.7和8 mysql 数据库 database 字符串 文章分类 MySQL 数据库

1、mysql 5.7和mysql 8.0版本两个版本有啥区别

(1)、 NoSql存储
Mysql从5.7 版本提供了NoSQL的存储功能,在8.0中这部分得到一些修改,不过这个在实际中用的极少

(2)、隐藏索引
隐藏索引的特性对于性能调试非常有用,在8.0 中,索引可以被隐藏和显示,当一个索引隐藏时,他不会被查询优化器所使用

也就是说可以隐藏一个索引,然后观察对数据库的影响.如果性能下降,就说明这个索引是有效的,于是将其”恢复显示”即可;如果数据库性能看不出变化,说明这个索引是多于的,可以删掉了

隐藏一个索引的语法

ALTER TABLE t ALTER INDEX i INVISIBLE;
 1

恢复显示该索引的语法是：

VISIBLE;
 1

当一个索引被隐藏时,我们可以从show index命令的输出汇总看出,该索引visible属性值为No

**注意:**当索引被隐藏时,他的内容仍然是和正常索引一样实时更新的,这个特性本身是专门为了优化调试而使用的,如果你长期隐藏一个索引,那还不如干掉,因为索引的存在会影响数据的插入\更新和删除功能

(3)、设置持久化
MySQL 的设置可以在运行时通过 SET GLOBAL 命令来更改，但是这种更改只会临时生效，到下次启动时数据库又会从配置文件中读取。
MySQL 8 新增了 SET PERSIST 命令，例如：
SET PERSIST max_connections = 500;
MySQL 会将该命令的配置保存到数据目录下的 mysqld-auto.cnf 文件中，下次启动时会读取该文件，用其中的配置来覆盖缺省的配置文件。

(4)、UTF-8 编码
从 MySQL 8 开始，数据库的缺省编码将改为 utf8mb4，这个编码包含了所有 emoji 字符。多少年来我们使用 MySQL 都要在编码方面小心翼翼，生怕忘了将缺省的 latin 改掉而出现乱码问题。从此以后就不用担心了。

(5)、通用表表达式（Common Table Expressions）with语句使用和with递归
复杂的查询会使用嵌入式表，with可以大大减少临时表的数量，提升代码的可读性、可维护性。例如：

SELECT t1.*, t2.* FROM
      (SELECT col1 FROM table1) t1,
      (SELECT col2 FROM table2) t2;

而有了 CTE，我们可以这样写：

WITH
      t1 AS (SELECT col1 FROM table1),
      t2 AS (SELECT col2 FROM table2)
     SELECT t1.*, t2.* 
     FROM t1, t2;

2、mysql 5.5版本以上出现order by和limit混用的时候，出现了排序的混乱情况

mysq5.6的版本上，优化器在遇到order by limit语句的时候，做了一个优化，即使用了priority queue

大概意思是：一旦 order by 的 colunm 有多个相同的值的话，结果集是非常不稳定

混乱排序sql如下：

select * from people order by create_time asc limit 5,10;

解决办法：

(1)、通过id排序

select * from people order by id asc limit 5,10;

(1)、在排序的字段上在加第二个排序字段，提高排序的唯一性，比如id

select * from people order by create_time,id asc limit 5,10;

可参考地址：Mysql排序后分页，因数据重复导致分页数据紊乱的问题

3、MySQL 5.7 虚拟列（virtual columns）：可以通过已有数据计算而得的数据

场景说明：为了实现对json数据中部分数据的索引查询，可以使用MySQL5.7中的虚拟列（virtual column）功能

(1)、创建表：

CREATE TABLE USER(uid INT AUTO_INCREMENT,DATA json,PRIMARY KEY(uid));

(2)、新增数据：

insert into user values (NULL,'{"name":"wang","address":"shenyang"}');
 insert into user values (NULL,'{"name":"zhao","address":"riben"}');

mysql5.7和8 mysql5.7和8.0版本语法有什么区别_database

(3)、构建姓名的虚拟列

ALTER TABLE USER ADD user_name VARCHAR(20) generated always AS (DATA->'$.name');

mysql5.7和8 mysql5.7和8.0版本语法有什么区别_mysql_02

(4)、构建索引

alter table user add index idx_name(user_name);

4、msyql 5.7可以支持json数据格式字符串
但是客户端软件一般看不到json类型，只有手写sql才可以加入
(1)、创建有json字段的表

CREATE TABLE t_json(id INT PRIMARY KEY, sname VARCHAR(20) , info JSON);

mysql5.7和8 mysql5.7和8.0版本语法有什么区别_database_03

(2)、插入记录

-- 插入含有json数组的记录 
 INSERT INTO t_json(id,sname,info) VALUES( 1, 'name1', JSON_ARRAY(1, "abc", NULL, TRUE, CURTIME())); 
 -- 插入含有json对象的记录 
 INSERT INTO t_json(id,sname,info) VALUES( 2, 'name2', JSON_OBJECT("age", 20, "time", NOW())); 
 INSERT INTO t_json(id,sname,info) VALUES( 3, 'name3', '{"age":20, "time":"2018-07-14 10:52:00"}');

mysql5.7和8 mysql5.7和8.0版本语法有什么区别_字符串_04

(3)、查询记录

JSON_EXTRACT 查找所有指定数据

JSON_KEYS 查找所有指定键值

SELECT sname,JSON_EXTRACT(info,'$.age') FROM t_json;

mysql5.7和8 mysql5.7和8.0版本语法有什么区别_字符串_05

SELECT sname,info->'$.age' FROM t_json;

-- 查询key

SELECT id,json_keys(info) FROM t_json;

5、mysql sum(a+b) 索引失效解决方法
很多开发人员在使用MySQL时经常会在部分列上进行函数计算等，导致无法走索引，在数据量大的时候，查询效率低下。针对此种情况本文从MySQL5.7 及MySQL8.0中分别进行不同方式的优化
MySQL8.0增加函数索引
比如：

alter  table tb_function add key idx_create_time((date(create_time))); --   注意里面字段的括号
 select  *  from  tb_function  where   date(create_time)='2020-07-01';

6、mysql中如何查询数据跟实际想要的数据不一致，排查这种问题

(1)、自建mysql数据库也提供审计sql功能

(2)、重点推荐：最好使用阿里云的sql审计功能，可以查看到线上数据库每一个sql执行情况，将执行的sql汇总到阿里云日志中，通过日志检索可以查询

如图所示：

mysql5.7和8 mysql5.7和8.0版本语法有什么区别_数据库_06

参考地址：日志字段详情 - 日志服务 - 阿里云

7、如何查询慢日志

开启慢查询日志

mysql> set global slow_query_log=1;

定义时间SQL查询的超时时间 mysql默认时间为10秒，即10秒及以上的查询被归为了慢查询

mysql> set global long_query_time =10;

查看慢查询日志的保存路径

mysql> show global variables like ‘slow_query_log_file’;

查看慢查询

cat /var/log/mysql/slow.log

8、mysq中主键用int和varchar区别

(1)、插入数据时，varchar所用时间比int要长

(2)、插入数据时，随机字符串的主键数据的写入会有很多碎片产生，很多逻辑上相近的页其实分布在磁盘和内存的各个地方

(3)、当物理文件大小，2张表大小刚好一样时，int装的数据要比varchar要多

(4)、比如要对id范围查询，那么int特别适合

9、mysql快速导入/导出文件

导出：select * from users where sex=1 into outfile '/tmp/users.txt';

导入：load data infile '/tmp/users.txt' into table users;

10、mysql查询分页列表时，返回行数

SELECT @rownum:=@rownum+1 AS ‘rownum’, t.* FROM `b_store` AS t LIMIT 3,3

mysql5.7和8 mysql5.7和8.0版本语法有什么区别_mysql_07

11、mysql函数有哪些

(1)、聚合函数(常用于GROUP BY从句的SELECT查询中)

AVG(col)返回指定列的平均值
COUNT(col)返回指定列中非NULL值的个数
MIN(col)返回指定列的最小值
MAX(col)返回指定列的最大值
SUM(col)返回指定列的所有值之和
GROUP_CONCAT(col) 返回由属于一组的列值连接组合而成的结果

(2)、字符串函数

ASCII(char)返回字符的ASCII码值
BIT_LENGTH(str)返回字符串的比特长度
CONCAT(s1,s2...,sn)将s1,s2...,sn连接成字符串
CONCAT_WS(sep,s1,s2...,sn)将s1,s2...,sn连接成字符串，并用sep字符间隔
INSERT(str,x,y,instr) 将字符串str从第x位置开始，y个字符长的子串替换为字符串instr，返回结果
FIND_IN_SET(str,list)分析逗号分隔的list列表，如果发现str，返回str在list中的位置
LCASE(str)或LOWER(str) 返回将字符串str中所有字符改变为小写后的结果
LEFT(str,x)返回字符串str中最左边的x个字符
LENGTH(s)返回字符串str中的字符数
LTRIM(str) 从字符串str中切掉开头的空格
POSITION(substr,str) 返回子串substr在字符串str中第一次出现的位置
QUOTE(str) 用反斜杠转义str中的单引号
REPEAT(str,srchstr,rplcstr)返回字符串str重复x次的结果
REVERSE(str) 返回颠倒字符串str的结果
RIGHT(str,x) 返回字符串str中最右边的x个字符
RTRIM(str) 返回字符串str尾部的空格
STRCMP(s1,s2)比较字符串s1和s2
TRIM(str)去除字符串首部和尾部的所有空格
UCASE(str)或UPPER(str) 返回将字符串str中所有字符转变为大写后的结果

(3)、日期和时间函数

CURDATE()或CURRENT_DATE() 返回当前的日期
CURTIME()或CURRENT_TIME() 返回当前的时间
DATE_ADD(date,INTERVAL int keyword)返回日期date加上间隔时间int的结果(int必须按照关键字进行格式化),如：SELECTDATE_ADD(CURRENT_DATE,INTERVAL 6 MONTH);
DATE_FORMAT(date,fmt) 依照指定的fmt格式格式化日期date值
DATE_SUB(date,INTERVAL int keyword)返回日期date加上间隔时间int的结果(int必须按照关键字进行格式化),如：SELECTDATE_SUB(CURRENT_DATE,INTERVAL 6 MONTH);
DAYOFWEEK(date) 返回date所代表的一星期中的第几天(1~7)
DAYOFMONTH(date) 返回date是一个月的第几天(1~31)
DAYOFYEAR(date) 返回date是一年的第几天(1~366)
DAYNAME(date) 返回date的星期名，如：SELECT DAYNAME(CURRENT_DATE);
FROM_UNIXTIME(ts,fmt) 根据指定的fmt格式，格式化UNIX时间戳ts
HOUR(time) 返回time的小时值(0~23)
MINUTE(time) 返回time的分钟值(0~59)
MONTH(date) 返回date的月份值(1~12)
MONTHNAME(date) 返回date的月份名，如：SELECT MONTHNAME(CURRENT_DATE);
NOW() 返回当前的日期和时间
QUARTER(date) 返回date在一年中的季度(1~4)，如SELECT QUARTER(CURRENT_DATE);
WEEK(date) 返回日期date为一年中第几周(0~53)
YEAR(date) 返回日期date的年份(1000~9999)

(4)、加密函数

AES_ENCRYPT(str,key) 返回用密钥key对字符串str利用高级加密标准算法加密后的结果，调用AES_ENCRYPT的结果是一个二进制字符串，以BLOB类型存储
AES_DECRYPT(str,key) 返回用密钥key对字符串str利用高级加密标准算法解密后的结果
DECODE(str,key) 使用key作为密钥解密加密字符串str
ENCRYPT(str,salt) 使用UNIXcrypt()函数，用关键词salt(一个可以惟一确定口令的字符串，就像钥匙一样)加密字符串str
ENCODE(str,key) 使用key作为密钥加密字符串str，调用ENCODE()的结果是一个二进制字符串，它以BLOB类型存储
MD5() 计算字符串str的MD5校验和
PASSWORD(str) 返回字符串str的加密版本，这个加密过程是不可逆转的，和UNIX密码加密过程使用不同的算法。
SHA() 计算字符串str的安全散列算法(SHA)校验和

(5)、控制流函数

MySQL有4个函数是用来进行条件操作的，这些函数可以实现SQL的条件逻辑，允许开发者将一些应用程序业务逻辑转换到数据库后台。

MySQL控制流函数：

CASE WHEN[test1] THEN [result1]...ELSE [default] END如果testN是真，则返回resultN，否则返回default
CASE [test] WHEN[val1] THEN [result]...ELSE [default]END 如果test和valN相等，则返回resultN，否则返回default
IF(test,t,f) 如果test是真，返回t；否则返回f
IFNULL(arg1,arg2) 如果arg1不是空，返回arg1，否则返回arg2
NULLIF(arg1,arg2) 如果arg1=arg2返回NULL；否则返回arg1

(6)、格式化函数