数据库Mysql常见问题

导入较大sql文件失败

C:\ProgramData\MySQL\MySQL Server 8.0
my.cnf文件设置
max_allowed_packet=1024M

导入sql报错

测试环境打印完整sql【带参数】

og4jdbc-log4j2-jdbc4
https://www.cnblogs.com/JCcccit/p/16868318.html
【JPA、MYBATIS可生效】

1. maven包

<!--监控sql日志-->
		<dependency>
			<groupId>org.bgee.log4jdbc-log4j2</groupId>
			<artifactId>log4jdbc-log4j2-jdbc4.1</artifactId>
			<version>1.16</version>
		</dependency>

2. 修改application.yml中的 driver-class-name 和url

  datasource:
    type: com.alibaba.druid.pool.DruidDataSource
    driver-class-name: net.sf.log4jdbc.sql.jdbcapi.DriverSpy
    url: jdbc:log4jdbc:mysql://localhost:33006/zs_spd_ll?useUnicode=true&characterEncoding=UTF-8&serverTimezone=GMT%2B8&useSSL=false&nullCatalogMeansCurrent=true

3. 新建配置文件： log4jdbc.log4j2.properties

# If you use SLF4J. First, you need to tell log4jdbc-log4j2 that you want to use the SLF4J logger
log4jdbc.spylogdelegator.name=net.sf.log4jdbc.log.slf4j.Slf4jSpyLogDelegator

4. 重启系统即可生效

java连接mysql出现The server time zone value ‘�й���׼ʱ��’ is unrecognized的

在Idea中连接数据库是抛出The server time zone value ‘�й���׼ʱ��’ is unrecogni错误 原因是因为使用了Mysql Connector/J 6.x以上的版本，然后就报了时区的错误。
解决办法：

1. 配置url中添加serverTimezone=UTC

spring.datasource.gz2.url=jdbc:mysql://localhost:3306/ylhcgl_gz_v2?useUnicode=true&useSSL=false&characterEncoding=utf8&allowMultiQueries=true&serverTimezone=Asia/Shanghai

2. 改数据库时区【临时解决】
   (mysql重启后会恢复时区，要重新设)
   root登录后

set global time_zone='+8:00';
flush privileges;
show variables like '%time_zone%';

mysql区分大小写

Mysql字段内容区分大小写

group时null数据不进行分组

增加一个UUID()，把b.name的NULL的值都转化为具有专一性的uuid,这样每个b.name都会不同,GROUP就不会对他们进行分组  
    SELECT a.id ,a.name ,b.name FROM A a   
    LEFT JOIN B b  
    ON(a.id = b.aid)   
    GROUP BY IFNULL(b.name,UUID())

;

死锁

1、查询是否锁表  show OPEN TABLES where In_use > 0;
2、查询进程   show processlist; 
3、查看正在锁的事务  SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS; 
4、查看等待锁的事务  SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;

1、用 show processlist 查看，看到一个进程id,执行一个语句执行还没有执行完，那就有问题了，就是产生了死锁

2、kill 掉这个进程id

3、用show open tables where in_use>0 验证（kill后再看是否还有锁）

[ERR] 1118 - Row size too large (> 8126). Changing some columns to TEXT or BLOB or using ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRESSED may help. In current row format, BLOB prefix of 768 bytes is stored inlin

set global innodb_strict_mode = OFF;

Specified key was too long; max key length is 767 bytes

修改索引字段长度或
或者启用innodb_large_prefix，那么限制值会增加到3072

set global innodb_file_format = BARRACUDA;
set global innodb_large_prefix = ON;

批量清空相同前缀表名的数据

-- 逐个清空表数据
Select CONCAT( 'TRUNCATE table ', table_name, ';' ) 
FROM information_schema.tables 
Where table_schema = 'zs_spd_v3' AND table_name LIKE 'sync%';

-- 一次性删除表
SELECT CONCAT( 'DROP TABLE ', GROUP_CONCAT(table_name) , ';' ) 
AS statement FROM information_schema.tables
WHERE table_schema = 'duty' AND table_name LIKE 'ZipSpeed%';

Mysql缓存

1. 不使用mysql缓存，可以在 SQL 查询语句的字段前增加 SQL_NO_CACHE 关键字
   select SQL_NO_CACHE * from t_user;
2. 不使用mysql缓存，释放全部缓存
   reset query cache;

MySQL错误-this is incompatible with sql_mode=only_full_group_by完美解决方案

临时解决方案

select @@GLOBAL.sql_mode;
SET @@global.sql_mode ='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

永久需要改mysql配置，重启
参考