【Mysql】常用SQL及问题排查

• 死锁解决

查询是否锁表

show OPEN TABLES where In_use > 0;

查询所有进程：SHOW PROCESSLIST

查看当前的事务：
SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX;

查看当前锁定的事务：

SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;

查看当前等锁的事务：
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;

杀死进程ID：kill 线程ID

• 表数据过量清理

查询各个表数据量：

SELECT table_name,table_rows FROM information_schema.tables WHERE TABLE_SCHEMA = 'you_database_name' ORDER BY table_rows DESC;

清理表：

delete from table_name;(可以回滚)

truncate table_name;(快，不回滚)

• 修改字段：

ALTER TABLE table_name ADD column_name datatype
ALTER TABLE table_name DROP COLUMN column_name
ALTER TABLE table_name ALTER COLUMN column_name datatype

• 查询表结构

select COLUMN_NAME, column_comment from INFORMATION_SCHEMA.Columns where table_name=''

• 导入导出

1.导出数据库
mysqldump -u dbuser -p dbname > dbname.sql

2.导出一个表
mysqldump -u dbuser -p dbname users> dbname_users.sql

3.导出一个数据库结构
mysqldump -u dbuser -p -d --add-drop-table dbname >d:/dbname_db.sql
-d 没有数据 --add-drop-table 在每个create语句之前增加一个drop table

4.导入数据库
mysql>use dbname
mysql>source d:/dbname.sql

• Union

1. 查询列数相同
2. 类型不必相同
3. 列名以第一个查询为准
4. 查询结果去重（不去重，使用union all）
5. 子句包含order by和limit，需给所有子句加小括号：（）

• Could not execute JDBC batch update

SQL语句长度限制导致，默认查询数据流不应大于1M。

查询当前配置：默认一般是1048576字节，即1M。

show VARIABLES like '%max_allowed_packet%';

修改：

配置：[my.ini]中[mysqld]下添加max_allowed_packet = 10M；

命令：set global max_allowed_packet = 10485760；

PS：正常使用中，应避免过长SQL，避免in查询大量数据。

• Windows环境下忘记密码

1. 任务管理器里 关闭正在运行的MySQL服务
2. 打开cmd窗口，转到mysql.exe所在目录
3. 输入mysqld --skip-grant-tables 回车 （跳过权限认证）
4. 再开一个cmd窗口（因为刚才那个cmd窗口已经不能动了），转到mysql.exe目录 
5. 输入mysql回车即可
6. 连接权限数据库： use mysql;
6. 改密码：update user set password=password("root") where user="root"; 
   mysql5.7以上版本-->    UPDATE user SET authentication_string=PASSWORD("root") WHERE User="root";
7. 刷新权限：flush privileges;　
8. 退出 quit； 
9. 重启mysl，再进入，使用用户名root和刚才设置的新密码登录。

 

 

 

 

---

爱家人，爱生活，爱设计，爱编程，拥抱精彩人生！