3.Mysql数据库使用与问题

Mysql

only_full_group_by

[Err] 1055 - Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column ‘xxx’
which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

select的列需要是group by里面的或者是使用了聚合函数的列，不然在only_full_group_by模式下会出现sql错误

1. 修改 my.ini 文件
2. 在 [mysqld] 下面添加代码：

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

   1、不同的系统，mysql 的配置文件名以及路径不同

2、Mac或Linux文件 /etc/my.cnf

3、windows 在 C:\ProgramData\MySQL\MySQL Server 8.0

3. 重启服务

这个问题暂时无解

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

SELECT
	id, user_id, user_answer, msg_time
FROM
	t_msg_record 
WHERE
	msg_flag = 1 
	AND game_id = 136 
	AND wx_group_id = 'wrjjxBCAAATLm7t5LQKIEzEXAaPaHTsA' 
	AND del_flag = 0 
ORDER BY msg_time DESC;

-- 通过id来反查
-- 使用min max 聚合函数来取代orderby ， 这时候[多orderby、orderby字段不唯一]都会增加复杂度
SELECT
 user_id, min(msg_time)
FROM
	t_msg_record 
WHERE
	msg_flag = 1 
	AND game_id = 136 
	AND wx_group_id = 'wrjjxBCAAATLm7t5LQKIEzEXAaPaHTsA' 
	AND del_flag = 0 
GROUP BY user_id

mybatis最大执行SQL的长度

写一个10wList数据的查询：

Packet for query is too large (4500277 > 4194304). You can change this value on the server by setting the max_allowed_packet’ variable.

查看大小：show VARIABLES like ‘%max_allowed_packet%’;
默认为：4194304 = 410241024 即：4M

命令行修改：
set global max_allowed_packet = 6*1024*1024;

配置文件修改：
可以编辑 my.cnf来修改 ,在[mysqld]段或者 mysql 的 server 配置段增加下面配置：

1
2

[mysqld]
max_allowed_packet=6M

mysql-如何选择直到总和达到某个值

如何选择直到总和达到某个值

您需要一个累加的总和才能起作用.一种方法使用变量：

1
2
3
4
5
6
7

select t.*
from (select *, (@sum := @sum + number) as cume_number
      from test cross join
           (select @sum := 0) params
      order by id 
     ) t
where cume_number < 9 or (cume_number >= 9 and cume_number - number < 9);

别的设备不能访问

开启允许外部访问

1
2
3

update user set host = '%' where user = 'root';
FLUSH PRIVILEGES;
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%'WITH GRANT OPTION;