mysql5.7 win安装教程

https://blog.csdn.net/weixin_41003771/article/details/106216866 mysql 下载地址：https://dev.mysql.com/downloads/mysql/

Window Server MySQL 5.7 详细下载安装配置教程_黑白独行的博客

区分大小写

1、linux下mysql安装完后是默认：区分表名的大小写，不区分列名的大小写；

2、用root帐号登录后，在/etc/my.cnf 中的[mysqld]后添加添加lower_case_table_names=1，重启MYSQL服务，这时已设置成功：不区分表名的大小写；

  1. lower_case_table_names = 1
  2. # 0：区分大小写，1：不区分大小写

MySQL在Linux下数据库名、表名、列名、别名大小写规则是这样的：
　　 1、数据库名与表名是严格区分大小写的；
　　 2、表的别名是严格区分大小写的；
　　 3、列名与列的别名在所有的情况下均是忽略大小写的；
　　 4、变量名也是严格区分大小写的；

MySQL在Windows下都不区分大小写。

备份

不压缩备份

  1. mysqldump -uusername -ppassword DatabaseName > /home/dbback/DatabaseName_$(date +%Y%m%d_%H%M%S).sql

压缩备份

  1. mysqldump -hhost -uusername -ppassword DatabaseName | gzip > /home/dbback/DatabaseName_$(date +%Y%m%d_%H%M%S).sql.gz

host：mysql 服务地址，本地(localhost)的就不需要写

username：用户名（注意这个用户要有足够的权限 参考）

password：密码

DatabaseName：数据库名称

导入sql文件

  1. mysql -h127.0.0.1 -uroot -p testdb < testdb_20210909_115107.sql

-h ：数据库连接地址
-u ：用户名
-p : 密码 ，回车后输入
testdb ：指的是数据库名称，就是把某个sql文件导入到这个数据库
testdb_20210909_115107.sql : sql文件

MySQL中使用GROUP_CONCAT()指定分隔符

  1. GROUP_CONCAT(`password` SEPARATOR ';' )

mysql根据json字段内容作为查询条件（包括json数组），检索数据

https://blog.csdn.net/qq_21187515/article/details/90760337

mysql - 图1

  1. select  * from json where json_str ->'$.data.branchTaskNum' = 3 or json_str ->'$.data.branchTaskNum' = 2 ;

mysql - 图2

MySql使用group_concat()后去重

  1. SELECT
  2.     sur.a,
  3.     group_concat(
  4.         DISTINCT `sur`.`b` SEPARATOR '--'
  5.     ) AS `name`
  6. FROM
  7.     A sur
  8. GROUP BY
  9.     sur.a
  10. -- DISTINCT  将名字去重
  11. -- SEPARATOR 每个名字间有使用"--"连接

按照经纬度排序的问题

  1. SELECT *,
  2.     (
  3.         6371 * acos (
  4.             cos ( radians('30.4138') )
  5.             * cos( radians(lat) )
  6.             * cos( radians(lng) - radians('104.234') )
  7.             + sin ( radians('30.4138') )
  8.             * sin( radians(lat) )
  9.         )
  10.     ) AS distance 
  11. FROM sys_branch where `level` = 2
  12. GROUP BY distance

错误信息：

  1. this is incompatible with sql_mode=only_full_group_by

原因：

  1. # 原理层面这个错误发生在 mysql5.7 版本及以上版本会出现的问题：mysql5.7 版本默认的sql配置是:sql_mode="ONLY_FULL_GROUP_BY"，这个配置严格执行了"SQL92标准"。很多从5.6升级到5.7时，为了语法兼容，大部分都会选择调整sql_mode，使其保持跟5.6一致，为了尽量兼容程序。# sql层面在sql执行时，出现该原因：简单来说就是：输出的结果是叫 target list，就是 select 后面跟着的字段，还有一个地方 group by column，就是 group by后面跟着的字段。由于开启了 ONLY_FULL_GROUP_BY 的设置，所以如果一个字段没有在 target list 和 group by字段中同时出现，或者是聚合函数的值的话，那么这条 sql 查询是被 mysql 认为非法的，会报错误。

查看sql_mode的语句如下

  1. select @@GLOBAL.sql_mode;

解决方案一：sql语句暂时性修改 sql_mode

  1. set @@GLOBAL.sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'

解决方案二：完美解决方案。修改 my.cnf 文件

  1. vim /etc/my.cnf

内容如下

  1. sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

重启mysql服务，顺利解决。

SYSDATE 与 NOW 的区别

  • NOW 取的是 语句开始执行 的时间
  • SYSDATE 取的是 动态 的实时时间 
  1. SELECT NOW(),SYSDATE(),SLEEP(3),NOW(),SYSDATE()

先查询了 NOW() 和 SYSDATE()，然后sleep了3秒，再查询 NOW() 和 SYSDATE()，结果如下：

mysql - 图3

NOW() 还有3个同义词，效果跟 NOW() 一样，而且都有2种形式写法：

LOCALTIME 或 LOCALTIME()
LOCALTIMESTAMP 或 LOCALTIMESTAMP()
CURRENT_TIMESTAMP 或 CURRENT_TIMESTAMP()

MYSQL 计算年龄

  1. TIMESTAMPDIFF(YEAR, ui.birthday, CURDATE())

多个值匹配查询，使用正则

mysql - 图4

  1. select * from user_info where user_id REGEXP '(^|,) (1|12|5|9) (,|$)';

相当于

  1. FIND_IN_SET('1',type_id) OR FIND_IN_SET('12',type_id) OR FIND_IN_SET('5',type_id)

unsigned 用法

unsigned 既为非负数，用此类型可以 增加数据长度，只针对整型

例如: tinyint 最大是127，那 tinyint unsigned 最大就可以到 127 * 2

mysql命令执行外部sql文件

  • mysql 调用法

操作方式如下：

  1. mysql –u用户名 –p密码 –D数据库<【sql脚本文件路径全名】

如果想输出到文件，可以按下面的方式执行：

  1. mysql –uroot –p123456 -Dtest<d:testss.sql>d:dd.txt

注：该方法在 windows 和 linux 平台下都可以 。

  • source 命令法

该方法是先登陆进入mysql控制台，并通过source 命令调用sql文件 ，如：

  1. mysql –uroot –p123456 -Dtestmysql> source /opt/test.sql

mysql开启远程访问

  1. use mysql;
  2. grant all privileges  on *.* to root@'%' identified by "54d9c65dd3aaa52a";
  3. flush privileges;

mysql的root用户无法给普通用户授权问题处理

参考：http://blog.itpub.net/28497416/viewspace-2132403/

  1. update mysql.user set Grant_priv='Y' where User='root' and Host='%';
  2. flush privileges;

错误 log_bin_trust_function_creators variable

创建函数和复制函数报错

  1. This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)

https://www.cnblogs.com/kerrycode/p/7641835.html

如何处理

  1.  set global log_bin_trust_function_creators=1;

必须在my.cnf中设置，加上 log_bin_trust_function_creators=1，这样就会永久生效。

批量修改主键ID

  1. UPDATE app_product SET id = LAST_INSERT_ID(id-60) WHERE id >= 60

城市按照拼音排序

mysql - 图5

  1. SELECT GET_FIRST_PINYIN_CHAR(city_name) as city_char,GET_FIRST_PINYIN_CHAR(province_name) as province_char,city_name as name,city_id,c.province_id FROM `city` cleft join province p on c.province_id = p.province_id ORDER BY province_char,c.city_id

mysql - 图6

GET_FIRST_PINYIN_CHAR 函数

  1. DELIMITER //CREATE DEFINER=`root`@`%` FUNCTION `GET_FIRST_PINYIN_CHAR`(PARAM VARCHAR(255)) RETURNS varchar(2) CHARSET utf8BEGIN DECLARE V_RETURN VARCHAR(255);    DECLARE V_FIRST_CHAR VARCHAR(2);    SET V_FIRST_CHAR = UPPER(LEFT(PARAM,1));    SET V_RETURN = V_FIRST_CHAR;    IF LENGTH( V_FIRST_CHAR) <> CHARACTER_LENGTH( V_FIRST_CHAR ) THEN    SET V_RETURN = ELT(INTERVAL(CONV(HEX(LEFT(CONVERT(PARAM USING gbk),1)),16,10),        0xB0A1,0xB0C5,0xB2C1,0xB4EE,0xB6EA,0xB7A2,0xB8C1,0xB9FE,0xBBF7,        0xBFA6,0xC0AC,0xC2E8,0xC4C3,0xC5B6,0xC5BE,0xC6DA,0xC8BB,        0xC8F6,0xCBFA,0xCDDA,0xCEF4,0xD1B9,0xD4D1),    'A','B','C','D','E','F','G','H','J','K','L','M','N','O','P','Q','R','S','T','W','X','Y','Z');    END IF;    RETURN V_RETURN;    END//

配置文件解释

  1. [mysql]
  2. # 设置mysql客户端默认字符集
  3. default-character-set=utf8 
  4. sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
  6. [mysqld]
  7. #设置3306端口
  8. port = 3306
  10. #设置mysql的安装目录
  11. basedir=E:\mysql
  13. #设置mysql数据库的数据的存放目录
  14. datadir=E:\mysql\data
  16. #允许最大连接数
  17. max_connections=200
  19. #服务端使用的字符集默认为8比特编码的latin1字符集
  20. character-set-server=utf8
  22. #创建新表时将使用的默认存储引擎
  23. default-storage-engine=INNODB
  24. sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

常用函数

case when

语法：

  1. case when 条件 then 值
  3. when 条件 then 值
  5. when 条件 then 值
  7. when 条件 then 值
  9. .........
  11. when 条件 then 值 else 0 end 字段名

示例：比如工单状态的回显

  1. select o.order_code ,CASE WHEN o.`order_status`= 6  THEN '已取消' 
  2.  WHEN o.`order_status`= 0  THEN '待派单' 
  3.  WHEN o.`order_status`= 1  THEN '待抢单' 
  4.  WHEN o.`order_status`= 2  THEN '待确认' 
  5.  WHEN o.`order_status`= 3  THEN '待到达现场' 
  6.  WHEN o.`order_status`= 4  THEN '执行中' 
  7.  WHEN o.`order_status`= 5  THEN '已完成' 
  8. ELSE '' END  order_status_name,
  9. o.create_time ,o.serve_type_name , o.detail_address ,o.content ,
  10. from ems_maintain_order o

trim

比如通过姓名查询用户信息，查询 张三 ,正常是可以查询到的，
但是查询 张三 ，张三左右有两个空格，就查询不到，所以去除空格搜索

应用场景：查询条件去除两边空格

  1. <if test="title != null  and title != ''"> 
  2.  and o.title like concat('%', TRIM(#{title}), '%')
  3. </if>

[

](https://blog.csdn.net/qq_37323658/article/details/89372522)

常见问题

Variable ‘time_zone’ can’t be set to the value of ‘NULL’

参考：https://blog.csdn.net/qq_41980563/article/details/121909615

在sql文件的末尾中有这样的注释：

  1. /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

删掉即可。

Navicat中出现MySQL server has gone away错误

导入sql文件的时候报错，sql文件大小84M，出现这种错误一般是sql语句太大导致了

解决方法如下：
navicat->工具->服务器监控->mysql
image.png
默认值67108864（64M），最大值1073741824（1G）

too many connections

最近写 javaee 项目的时候，mysql 报了 too many connections 的错误，百度的内容有一些有问题，所以我重新写一下我的解决方法。

  1. 查看所有正在运行的线程

    1. show processlist;

    image.png
    查看连接数，可以发现有很多连接处于 sleep 状态，这些其实是暂时没有用的，所以可以 kill 掉

  2. 查看最大连接数

    1. show variables like "max_connections";

    image.png
    查看最大连接数，应该是与上面查询到的连接数相同，才会出现 too many connections 的情况

  3. 修改最大连接数，但是这不是一劳永逸的方法，应该要让它自动杀死那些 sleep 的进程。

    1. set GLOBAL max_connections=1000;

  4. show global variables like ‘wait_timeout’;
    这个数值指的是 mysql 在关闭一个非交互的连接之前要等待的秒数，默认是 28800s

  5. set global wait_timeout=300;
    修改这个数值，这里可以随意，最好控制在几分钟内

  6. set global interactive_timeout=500;

修改这个数值，表示 mysql 在关闭一个连接之前要等待的秒数，至此可以让 mysql 自动关闭那些没用的连接，但要注意的是，正在使用的连接到了时间也会被关闭，因此这个时间值要合适

  1. 批量 kill 之前没用的 sleep 连接，在网上搜索的方法对我都不奏效，因此只好使用最笨的办法，一个一个 kill

  • 先把要 kill 的连接 id 都查询出来

    1. select concat('KILL ',id,';') from information_schema.processlist where user='root';
    1. ![image.png](https://cdn.nlark.com/yuque/0/2021/png/386580/1639733016893-2ed2d9bc-6c59-4e9f-ab63-be39a30a336c.png#clientId=u81ed604e-8ba2-4&crop=0&crop=0&crop=1&crop=1&from=paste&height=212&id=qMLdG&margin=%5Bobject%20Object%5D&name=image.png&originHeight=423&originWidth=918&originalType=binary&ratio=1&rotation=0&showTitle=false&size=22145&status=done&style=shadow&taskId=u6af55ee9-b2a5-48f5-9695-040587c8b51&title=&width=459)

  • 复制中间的kill id;内容到 word 文档

  • 替换掉符号 “|” 和回车符（在 word 中查询 ^p 即可查询到回车符）
  • 把修改过的内容复制回终端，最后按回车执行！

    迁移函数报错：you might want to use the less safe log_bin_trust_function_creators variable

This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its de 错误解决办法

Mysql 从5.6升级到8.0，拷贝表结构、存储过程和函数，WTF? 表和存储过程都可以拷贝，函数不行，网上说在启动日志操作后对函数的操作需要设定参数值否则无法使用.

用root账户登录，执行以下sql语句：

  1. SET GLOBAL log_bin_trust_function_creators = 1;

重新导入即可。




MySQL报错：com.mysql.cj.jdbc.exceptions.CommunicationsException: Communications link failure

报错如下

image.png

image.png[

](https://www.cnblogs.com/bulter/p/15316496.html)
如何处理：

image.png

参考：https://www.cnblogs.com/bulter/p/15316496.html[

](https://blog.csdn.net/qq_37323658/article/details/89372522)