mysql 记录

后天星期一

已于 2022-11-28 18:32:03 修改

阅读量844

点赞数 1

分类专栏： mysql 文章标签： mysql 数据库

于 2019-01-18 17:06:26 首次发布

本文链接：https://blog.csdn.net/zhang168/article/details/86543200

版权

mysql 专栏收录该内容

3 篇文章 0 订阅

订阅专栏

Mysql查询报错

	 SEVERE: Servlet.service() for servlet [dispatcherServlet] in context with path [/load] threw exception [Request processing failed; nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'tableName.step' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by] with root cause
	 com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'tableName.step' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

查询sql_mode

select @@sql_mode;
| @@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 |

若直接set sql_mode,则修改的是当前会话的sql_mode,我们需要修改全局sql_mode

SET @@GLOBAL.sql_mode=(SELECT REPLACE(@@GLOBAL.sql_mode,'ONLY_FULL_GROUP_BY',''));

查看binlog
mysqlbinlog --start-datetime='2021-08-19 00:00:00' --stop-datetime='2021-08-19 01:01:01' -d 库名 二进制文件

如果binlog是编码过的，加上 --base64-output=DECODE-ROWS -v
mysqlbinlog --start-datetime='2021-08-20 00:00:00' --stop-datetime='2021-08-20 01:01:01' --base64-output=DECODE-ROWS -v -d bolt binlog.000024


参考:  https://blog.csdn.net/hfdchhu/article/details/84050733

timestamp类型

MySQL的timestamp类型时间范围between '1970-01-01 00:00:01' and '2038-01-19 03:14:07'，超出这个范围则值记录为'0000-00-00 00:00:00'，无法入库

json类型的长度

json的类型长度不能大于max_allowed_packet的值
查看max_allowed_packet
show variables like 'max_allowed_packet';

+--------------------+------------+
| Variable_name      | Value      |
+--------------------+------------+
| max_allowed_packet | 67108864   |
+--------------------+------------+

max_allowed_packet 也可以修改

打开my.conf，添加
max_allowed_packet=10M

json使用

1、add_item 字段结构
[{"code": 1, "count": 1, "opCount": 2}, {"code": 1002, "count": 11, "opCount": 21}, {"code": 10021, "count": 11, "opCount": 201}]

2、查询add_item中有code=1002的记录
SELECT * FROM test_json WHERE JSON_EXTRACT(add_item, "$[*].code") = 1002

修改数据库字符集及排序规则
ALTER DATABASE dbName CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;

修改表的字符集及排序规则
ALTER TABLE tableName CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;