mysql 数据库问题整理

1. 数据库中出现乱码

问题描述: 在html 页面中设置了页面的编码,设置了后台req.setCharacterEncoding("UTF-8"); 但是数据库中还是有乱码,查看数据库编码和表编码均设为UTF-8,

解决: 发现配置的 mysql url 中没有带编码为UTF-8; 例如: jdbc:mysql://localhost:3306/httpclientcrawler?useUnicode=true&characterEncoding=UTF-8

参考: https://www.cnblogs.com/tom-plus/p/5701741.html

2. mysql Lock wait timeout exceeded; try restarting transaction (数据库锁超时等待)

问题描述: 后台经常报锁超时等待,是因为有线程拿住了锁,其他线程拿不到锁,超时等待的原因造成的,是当前事务在等待其它事务释放锁资源造成的。

解决: 在mysql查询中执行以下指令
SELECT * FROM information_schema.innodb_trx

查看没有参数的线程,并杀死,

kill + mysql_thread_id  杀死多余的线程
参考: https://blog.csdn.net/wanderlustLee/article/details/79235005

3.Could not retrieve transaction read-only status from server
Caused by: java.sql.SQLException: ResultSet is from UPDATE. No Data.

mysql 数据库报只读事务

问题描述:在select 操作时,事务时只读事务,如果后面接着操作 delete 等事务,就会报这个问题,下面一行 No Data 可能是另外的错

解决:

查询数据隔离级别:SHOW VARIABLES LIKE '%iso%';
如果隔离级别: REPEATABLE-READ 则改成 READ-COMMITTED
sql :SET GLOBAL tx_isolation='READ-COMMITTED';  （记得java重启应用）
                                           要永久生效的就改my.cnf。
分析：当数据库隔离级别为REPEATABLE-READ时，发布一个select语句也算是事物的开始，而且在hibernate里会把以select语句开头的事务标记为只读事务，此时在这个事务里再执行insert、update、delete等

参考: https://blog.csdn.net/zero_plus/article/details/47295507

4  1093 错误,你不能先根据某个表查询,在删除或更新某个表

1093

You can't specify target table 'user_friend' for update in FROM clause

说明:如果in 后面跟的是正常的 id 或者是用其他表查出的则不会报错,只限于mysql数据库

4.1删除例子:

DELETE FROM user_friend WHERE account IN (SELECT friend_account FROM user_friend WHERE account = 'xxx' AND friend_group_id='2') AND friend_account = 'xxx'

4.2更新例子:

UPDATE `user` SET `gid`='123' WHERE  id IN (SELECT id FROM `user` WHERE sid=2)  ;

结果报错,可改为:

UPDATE  `user` AS A INNER JOIN (SELECT id FROM `user` WHERE `sid`=2) AS B ON A.id=B.id SET A.gid='123'

参考:https://blog.csdn.net/y18749247391/article/details/79014537

参考: https://www.jb51.net/article/60926.htm

5 mybaties 一个delete 同时写删除多个表的多条sql 语句

要在jdbc.url 的后面加上 批量更新的语句 allowMultiQueries=true

可以同时update/delete 多条语句

6. mysql : ERROR 1813 (HY000): Tablespace '`db1`.`table1`' exists.

参考:https://blog.csdn.net/hero_hope/article/details/82256458?utm_source=blogxgwz0

mysql 某个表突然数据丢失,将表删除后,无法继续新建同名表,提示表不存在

1. 表现:table1表被删以后,想要重新建,结果新建不了
2. 原因:table1表在对应的表空间依然存在
3. 解决办法:删除对应的.ibd文件即可,比如上面这个,找到mysql下,db1文件夹下的table1.ibd文件,干掉即可

7. MySQL数据库表中有自增主键ID，当用SQL插入语句中插入语句带有ID列值记录的时候；如何表示该id列

1. 将该列表示为 0 或 null,mysql 会自动处理该AUTO_INCREMENT自增字段

2. 手动指定需要插入的列，不插入这一个字段的数据！

参考 : https://blog.csdn.net/qq_16143757/article/details/75303442

8. mysql 避免重复插入

参考: https://www.cnblogs.com/duanxiaojun/p/6855680.html?utm_source=itdadao&utm_medium=referral

1.insert ignore into 

出现相同列时,自动忽略新的列,但是有错误,也会自动忽略
2.on duplicate key update 

前提: 是需要将将主键列写入insert 语句中,有错误不会忽略

3.insert … select … where not exist

根据select的条件判断是否插入，可以不光通过primary 和unique来判断，也可通过其它条件

例如: 

INSERT INTO public_message_status(列一,列二)   
 SELECT 值一,值二 FROM DUAL WHERE NOT EXISTS(SELECT public_message_status_id FROM public_message_status WHERE ...)

4.replace into 

如果存在相同纪录,先删除旧数据,在插入新数据