mysql

已于 2024-06-26 16:16:26 修改
阅读量396 收藏
点赞数
文章标签： mysql 数据库
于 2022-10-27 11:26:59 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/a3562323/article/details/104940568
版权

文章目录

语法

GROUP_CONCAT

在这里插入图片描述
GROUP_CONCAT的作用是将结果结合成字符串，

默认用逗号分隔

select GROUP_CONCAT(user_name) from score1

在这里插入图片描述

指定分隔符号

select GROUP_CONCAT(user_name separator ';') from score1

在这里插入图片描述

结果去重

select GROUP_CONCAT(distinct user_name) from score1

在这里插入图片描述

倒序排列

select GROUP_CONCAT(user_name ORDER BY user_name desc) from score1

在这里插入图片描述

按照名称分组

select GROUP_CONCAT(user_name) from score1 GROUP BY user_name

在这里插入图片描述

cast

CAST函数语法规则是：Cast(字段名 as 转换的类型 )，其中类型可以为：

CHAR[(N)] 字符型
DATE 日期型
DATETIME 日期和时间型
DECIMAL float型
SIGNED int
TIME 时间型
把data字段转为signed类型

select cast(date as signed) as date from  table1;

left

LEFT()函数是一个字符串函数，它返回具有指定长度的字符串的左边部分，
从左往右返回2位长度字符

select left('张三李四',2)

在这里插入图片描述

时间相关函数

  • NOW()
    获取当前时间
select NOW() 当前时间和时间,CURDATE() 当前日期,CURTIME() 当前时间 FROM dual

在这里插入图片描述

死锁

information_schema.INNODB_TRX

需要管理员权限

SELECT * FROM information_schema.INNODB_TRX

在执行结果中可以看到是否有表锁等待或者死锁，如果有死锁发生，可以通过下面的命令来杀掉当前运行的事务：

KILL 165667

show status like 'innodb_row_lock%'

Innodb_row_lock_current_waits : 当前等待锁的数量
Innodb_row_lock_time : 系统启动到现在，锁定的总时间长度
Innodb_row_lock_time_avg : 每次平均锁定的时间
Innodb_row_lock_time_max : 最长一次锁定时间
Innodb_row_lock_waits : 系统启动到现在总共锁定的次数

+----------------------------+---------------------+------+-----+---------------------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------------------+---------------------+------+-----+---------------------+-------+
| trx_id | varchar(18) | NO | | | |#事务ID
| trx_state | varchar(13) | NO | | | |#事务状态：
| trx_started | datetime | NO | | 0000-00-00 00:00:00 ||#事务开始时间；
| trx_requested_lock_id | varchar(81) | YES | | NULL ||#innodb_locks.lock_id
| trx_wait_started | datetime | YES | | NULL | |#事务开始等待的时间
| trx_weight | bigint(21) unsigned | NO | | 0 | |#事务的权重，反应一个事务修改和锁定的行数，当发现死锁需要回滚时，权重越小的值被回滚
| trx_mysql_thread_id | bigint(21) unsigned | NO | | 0 ||#事务线程ID
| trx_query | varchar(1024) | YES | | NULL | |#具体SQL语句
| trx_operation_state | varchar(64) | YES | | NULL ||#事务当前操作状态
| trx_tables_in_use | bigint(21) unsigned | NO | | 0 ||#事务中有多少个表被使用
| trx_tables_locked | bigint(21) unsigned | NO | | 0 ||#事务拥有多少个锁
| trx_lock_structs | bigint(21) unsigned | NO | | 0 | |#
| trx_lock_memory_bytes | bigint(21) unsigned | NO | | 0 ||#事务锁住的内存大小（B）
| trx_rows_locked | bigint(21) unsigned | NO | | 0 ||#事务锁住的行数
| trx_rows_modified | bigint(21) unsigned | NO | | 0 ||#事务更改的行数
| trx_concurrency_tickets | bigint(21) unsigned | NO | | 0 ||#事务并发票数
| trx_isolation_level | varchar(16) | NO | | | |#事务隔离级别
| trx_unique_checks | int(1) | NO | | 0 | |#是否唯一性检查
| trx_foreign_key_checks | int(1) | NO | | 0 | |#是否外键检查
| trx_last_foreign_key_error | varchar(256) | YES | | NULL ||#最后的外键错误
| trx_adaptive_hash_latched | int(1) | NO | | 0 | |#
| trx_adaptive_hash_timeout | bigint(21) unsigned | NO | | 0 ||#

暴力解决
KILL 165667 ; KILL 后面的数字指的是 trx_mysql_thread_id 值

trx_mysql_thread_id：MySQL中的进程ID，与show processlist中的ID值相对应
trx_query：事务运行的SQL语句

死锁表

select * from performance_schema.data_locks;

+-------------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+---------------------+------+-----+---------+-------+
| lock_id | varchar(81) | NO | | | |#锁ID
| lock_trx_id | varchar(18) | NO | | | |#拥有锁的事务ID
| lock_mode | varchar(32) | NO | | | |#锁模式
| lock_type | varchar(32) | NO | | | |#锁类型
| lock_table | varchar(1024) | NO | | | |#被锁的表
| lock_index | varchar(1024) | YES | | NULL | |#被锁的索引
| lock_space | bigint(21) unsigned | YES | | NULL | |#被锁的表空间号
| lock_page | bigint(21) unsigned | YES | | NULL | |#被锁的页号
| lock_rec | bigint(21) unsigned | YES | | NULL | |#被锁的记录号
| lock_data | varchar(8192) | YES | | NULL | |#被锁的数据

# 查询死锁等待时间
select * from sys.innodb_lock_waits;

locked_table : 哪张表出现的等待

waiting_trx_id: 等待的事务(与上个视图trx_id 对应)

waiting_pid : 等待的线程号(与上个视图trx_mysql_thread_id)

blocking_trx_id : 当前拥有锁的事务ID

blocking_pid : 当前拥有锁的锁ID

死锁的排查

未提交的事务

processlist中未提交的事务
show processlist

根据INNODB_TRX中的trx_mysql_thread_id查询进程明细，host为客户端ip段进口

SELECT * FROM information_schema.processlist where id=4086

在这里插入图片描述

未提交事务的Command为Sleep，State为空，Info为NULL

information_schema.innodb_trx中的未提交事务

information_schema.innodb_trx.trx_query为NULL，无法提供未提交事务的SQL语句

SELECT * FROM information_schema.INNODB_TRX

在这里插入图片描述

performance_schema.events_statements_current中的未提交事务
select * from performance_schema.events_statements_current 
where sql_text not like 'select * from performance_schema.events_statements_current%'

一个事务可能有一组sql组成，这个方法只能看到事务最后执行的是什么SQL，无法看到全部。可以通过performance_schema.threads表来关联，将information_schema.processlist和performance_schema.events_statements_current一一对应起来

select t1.id, t2.thread_id, t3.sql_text from information_schema.processlist t1,performance_schema.threads t2,performance_schema.events_statements_current t3
where t1.id=4806 and t1.id=t2.processlist_id and t2.thread_id = t3.thread_id

如果没有死锁，并且状态一直是running，running了很久，可能是事物没有设置回滚，

select * from information_schema.`PROCESSLIST` where id in (SELECT trx_mysql_thread_id from information_schema.INNODB_TRX where trx_state='RUNNING')

在这里插入图片描述

事务一直running

#查看日志状态
show variables like 'general_log';
#设置是否开启日志
set global general_log=off;
#设置日志记录方式 table、file
set global log_output='table'
#根据thread_id查sql
SELECT *,CONVERT (argument USING utf8) as `sql` from mysql.general_log where thread_id = 17 ORDER BY event_time DESC;
# 清空日志表
truncate table mysql.general_log

死锁日志

show engine innodb status

# 这行表示事务id为3105550，事务处于活跃状态9s，starting index read表示正在使用索引读取数据行
---TRANSACTION 3105550, ACTIVE 9 sec starting index read
#这行表示该事务正在使用1个表，且涉及锁的表有1个
mysql tables in use 1, locked 1
# 这行表示在等待3把锁，占用内存1136字节，涉及1行记录
LOCK WAIT 3 lock struct(s), heap size 1136, 1 row lock(s), undo log entries 22
# 这行表示该事务的线程ID信息，操作系统句柄信息，连接来源、用户
MySQL thread id 5809, OS thread handle 140507283683072, query id 3017641 192.168.0.0.1 ztb_gansu updating
# 这行表示事务执行的最后一条SQL信息
update yw_bd set ab=null, address=null, aqwmcsf=null, bdcode='BD2022001875', bdfl=null, bdname='zzzzzz', bgplabdid=null, bjfs=null, bzjjnfs='', bzjkhh='', bzjyh='', bzjzh='', cbjsj=null, cgdwlxdh='18100001111', cgdwlxr='1', cgdwztid='1', cgdwztname='1', cglx=null, cgpmcodes=null, cgpmnames=null, cjryhl=null, content=null, cretime=null, currency=null, extinfo='{"extZjsjYear":{"remark":"最近一个数据审计年","value":"2022"},"extSfsjssgsgy":{"remark":"是否涉及上市公司国有股权间接转让","value":"0"},"extSfcztxqlr":{"remark":"是否存在他项权利人","value":"1"},"extJzrsj
Trx read view will not see trx with id >= 3105550, sees < 3100092
------- TRX HAS BEEN WAITING 9 SEC FOR THIS LOCK TO BE GRANTED:
# 这行信息表示等待的锁是一个record lock，空间id是503 ，页编号为64 ，大概位置在页的80位处，锁发生在表ztb_gansu.test_tb的yw_bd索引上，是一个X锁，但是不是gap lock，waiting表示正在等待锁
RECORD LOCKS space id 503 page no 64 n bits 80 index PRIMARY of table `ztb_gansu`.`yw_bd` trx id 3105550 lock_mode X locks rec but not gap waiting
Record lock, heap no 8 PHYSICAL RECORD: n_fields 96; compact format; info bits 0
 0: len 30; hex 613935616661346235393365343034336135316230373337383764323039; asc a95afa4b593e4043a51b073787d209; (total 32 bytes);
 1: len 6; hex 0000002f589e; asc    /X ;;
 2: len 7; hex 02000000ee23e7; asc      # ;;
 3: len 30; hex 643162323764333863633465343935656139316230353930396131623463; asc d1b27d38cc4e495ea91b05909a1b4c; (total 32 bytes);
 4: SQL NULL;
 5: len 5; hex 99ae374146; asc   7AF;;
 6: len 7; hex 61616161616161; asc aaaaaaa;;
 7: len 12; hex 424432303232303031383735; asc BD2022001875;;
 8: len 11; hex 8000000000000001000000; asc            ;;
 9: SQL NULL;
 10: SQL NULL;
 11: len 0; hex ; asc ;;
 12: len 0; hex ; asc ;;
 13: len 1; hex 31; asc 1;;
 14: len 11; hex 3138313030303031313131; asc 18100001111;;
 15: SQL NULL;
 16: SQL NULL;
 17: SQL NULL;
 18: SQL NULL;
 19: len 1; hex 30; asc 0;;
 20: SQL NULL;
 21: SQL NULL;
 22: SQL NULL;
 23: SQL NULL;
 24: SQL NULL;
 25: SQL NULL;
 26: SQL NULL;
 27: SQL NULL;
 28: SQL NULL;
 29: SQL NULL;
 30: SQL NULL;
 31: SQL NULL;
 32: SQL NULL;
 33: SQL NULL;
 34: SQL NULL;
 35: SQL NULL;
 36: SQL NULL;
 37: SQL NULL;
 38: len 1; hex 31; asc 1;;
 39: len 1; hex 31; asc 1;;
 40: SQL NULL;
 41: SQL NULL;
 42: SQL NULL;
 43: SQL NULL;
 44: SQL NULL;
 45: SQL NULL;
 46: SQL NULL;
 47: SQL NULL;
 48: SQL NULL;
 49: SQL NULL;
 50: SQL NULL;
 51: SQL NULL;
 52: SQL NULL;
 53: SQL NULL;
 54: SQL NULL;
 55: SQL NULL;
 56: SQL NULL;
 57: SQL NULL;
 58: SQL NULL;
 59: SQL NULL;
 60: SQL NULL;
 61: SQL NULL;
 62: SQL NULL;
 63: SQL NULL;
 64: SQL NULL;
 65: SQL NULL;
 66: SQL NULL;
 67: SQL NULL;
 68: SQL NULL;
 69: SQL NULL;
 70: SQL NULL;
 71: SQL NULL;
 72: SQL NULL;
 73: SQL NULL;
 74: SQL NULL;
 75: SQL NULL;
 76: SQL NULL;
 77: SQL NULL;
 78: SQL NULL;
 79: SQL NULL;
 80: SQL NULL;
 81: SQL NULL;
 82: len 1; hex 31; asc 1;;
 83: SQL NULL;
 84: SQL NULL;
 85: len 11; hex 8000000000000001000000; asc            ;;
 86: len 1; hex 30; asc 0;;
 87: SQL NULL;
 88: len 4; hex 46584a4a; asc FXJJ;;
 89: len 3; hex 4f4e45; asc ONE;;
 90: SQL NULL;
 91: SQL NULL;
 92: len 0; hex ; asc ;;
 93: len 0; hex ; asc ;;
 94: SQL NULL;
 95: len 30; hex 002900170c23010600290106002f010700360107003d010700440107004b; asc  )   #   )   /   6   =   D   K; (total 3096 bytes);

------------------
# 事物3103156，活跃2619s
---TRANSACTION 3103156, ACTIVE 2619 sec
# 等待1把锁，内存1136，涉及一行数据
1 lock struct(s), heap size 1136, 1 row lock(s)
# 线程id 5804,以及ip和用户
MySQL thread id 5804, OS thread handle 140510655903488, query id 2996772 192.168.0.0.1 root
---TRANSACTION 3103114, ACTIVE 2660 sec
2 lock struct(s), heap size 1136, 1 row lock(s)
MySQL thread id 5796, OS thread handle 140507265332992, query id 2996402 192.168.0.0.1 root
---TRANSACTION 3101888, ACTIVE 3665 sec
1 lock struct(s), heap size 1136, 1 row lock(s)
MySQL thread id 5776, OS thread handle 140510624380672, query id 2987217 192.168.0.0.1 root
---TRANSACTION 3101267, ACTIVE 4349 sec
1 lock struct(s), heap size 1136, 2 row lock(s)
MySQL thread id 5770, OS thread handle 140519116973824, query id 2989155 192.168.0.0.1 root
---TRANSACTION 3101247, ACTIVE 4375 sec
1 lock struct(s), heap size 1136, 1 row lock(s)
MySQL thread id 5769, OS thread handle 140507283977984, query id 2981788 192.168.0.0.1 root
---TRANSACTION 3100092, ACTIVE 5668 sec
2 lock struct(s), heap size 1136, 1 row lock(s), undo log entries 1
MySQL thread id 5749, OS thread handle 140520111859456, query id 2971455 192.168.0.0.1 root

错误

zhangsan这个用户没有user表的权限

SELECT command denied to user 'zhangsan'@'192.168.0.79' for table 'user'

查看sql记录

一、mysql查看正在执行的sql语句

show processlist;

二、mysql查看已经执行的历史sql语句（方法：开启日志模式）

-- 查看日志功能设置状态
show variables like 'general_log'; 
-- 查看当前日志输出类型：默认file
show variables like 'log_output';

-- 打开日志记录功能
set global general_log=on; 
-- 关闭日志记录功能
set global general_log=off; 

-- 设置日志输出至table
set global log_output='table';

-- 日志输出至table模式，查看日志记录
SELECT * from mysql.general_log ORDER BY event_time DESC;

-- 设置日志输出至file
set global log_output='file'; 

-- 查看日志输出文件的保存路径
show variables like 'general_log_file';

-- 修改日志输出文件的保存路径
set global general_log_file='tmp/general.log'; 

-- 日志输出至table模式，清空日志记录
truncate table mysql.general_log;

-- 日志输出至file模式，查看日志记录
cat /tmp/general.log

默认文件位置：/home/root/mysql/data/mysql/general_log.CSV

注意：在查询到所需要的记录之后，应尽快关闭日志模式，占用磁盘空间比较大

查看当前数据库的连接数量

最大连接数量

show VARIABLES like 'max_connections'

在这里插入图片描述

SHOW STATUS LIKE ‘Threads_connected’;

SHOW STATUS LIKE 'Threads_connected';

在这里插入图片描述
断开一个后查看
在这里插入图片描述

show status like ‘%connect%’;

在这里插入图片描述

WuWuII
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 1
    评论
mysql innodb_trx参数详解
dayulxl的博客
04-19 336
1、innodb_trx表提供了当前innodb引擎内每个事务的信息（只读事务除外），包括当一个事务启动，事务是否在等待一个锁，以及交易正在执行的语句（如果有的话）。复制。
MySql 8.0.37
最新发布
05-30
MySql 8.0.37
mysql SELECT * FROM information_schema.innodb_trx；
Jack_2085
03-29 1099
mysql SELECT * FROM information_schema.innodb_trx; https://blog.csdn.net/David_jiahuan/article/details/91558816
SELECT * FROM information_schema.columns
zmzkkk的专栏
03-03 1972
SELECT * FROM information_schema.columns
information_schema.INNODB_TRX表详解
CaptHua的博客
08-18 9583
information_schema.INNODB_TRX表；MySQL查看事务；事务与锁查看。
mysql数据库操作常用命令
小胖_@的博客
02-20 234
mysql数据库常用命令 1. 查询数据库连接下的所有数据库 select * from information_schema.SCHEMATA; 2. 查询某个数据库下的所有表 select * from information_schema.tables where TABLE_SCHEMA = ‘数据库名’; 3. 数据库表备份命令 mysqldump -h[DB_IP] -u[D...
mysql问题分析、解决
有梦想的咸鱼的博客
03-21 861
查看正在进行中的事务 SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX; 杀死进程id(就是上面命令查到的trx_mysql_thread_id列) kill 线程id 查看当前锁定的事务 SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS; 查看当前等锁的事务 SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS; 查看超过60s的长事务 s.
MySQL max_allowed_packet设置及问题
java开发指南博客 【转载】
10-09 775
mysql根据配置文件会限制server接受的数据包大小。 有时候大的插入和更新会被max_allowed_packet 参数限制掉，导致失败。 查看目前配置 show VARIABLES like '%max_allowed_packet%'; 显示的结果为： +--------------------+---------+ | Variable_...
MySQL事务问题排查语句
bigwhitetao9527的博客
06-13 812
查询当前事务隔离级别 select @@tx_isolation; 查询当前执行中事务 select * from information_schema.innodb_trx; *************************** 1. row *************************** trx_id: 113963 ...
Mysql中如何查看线程
qq_36527174的博客
11-11 1108
show processlist 命令：显示哪些线程正在运行 执行结果如下： 各列的含义和用途： ID列 　　一个标识，你要kill一个语句的时候很有用，用命令杀掉此查询 /*/mysqladmin kill 进程号。 user列 　　显示单前用户，如果不是root，这个命令就只显示你权限范围内的sql语句。 host列 　　显示这个语句是从哪个ip的哪个端口上发出的。用于追踪出问题语句的用户。 db列 　　显示这个进程目前连接的是哪个数据库。 command列 　　显示当前连接的执行的命令，.
mysql8.0升级版本
12-12
MySQL 8.0 升级版本 在本文中，我们将详细介绍 MySQL 8.0 升级版本的步骤和注意事项。MySQL 是一个流行的开源关系数据库管理系统，广泛应用于各种Web 应用程序中。升级 MySQL 版本可以提高数据库性能、修复 bugs 和...
深入浅出MySQL全文
06-29
深入浅出MySQL全文 MySQL 是一种关系型数据库管理系统，广泛应用于各种行业和领域。本资源将从基础篇开始，详细讲解 MySQL 的安装、配置、启动和关闭服务、SQL 基础等知识点。 MySQL 的安装与配置 MySQL 的安装是...
MySQL5.5.62
11-06
MySQL 5.5.62 是 MySQL 数据库管理系统的一个重要版本，主要针对 Windows 操作系统提供了32位和64位的安装包。MySQL 是一个广泛使用的开源关系型数据库管理系统（RDBMS），由 Oracle 公司维护。它以其高效、稳定和...
mysql 5.7 perl
08-08
离线安装mysql文件包，包含依赖，以及peal全套安装包
mysql省市区表完整版文件
10-07
MySQL数据库在许多应用中用于存储地理信息，如省市区数据，尤其在中国的网站和应用程序中很常见。"mysql省市区表完整版文件"提供了一个全面的数据库表结构，包含了中国所有省份、城市和区县的详细信息，这些信息通常...
mysql57驱动jar包
12-25
mysql57驱动jar包mysql57驱动jar包mysql57驱动jar包mysql57驱动jar包mysql57驱动jar包mysql57驱动jar包mysql57驱动jar包mysql57驱动jar包mysql57驱动jar包mysql57驱动jar包mysql57驱动jar包mysql57驱动jar包mysql57...
MySQL找出未提交事务的信息
wzy0623的专栏
03-28 4873
目录 一、processlist中的未提交事务 二、information_schema.innodb_trx中的未提交事务 三、performance_schema.events_statements_current中的未提交事务 参考： 我们经常会碰到这样的情况，某个事务执行完了未提交，后续再来一个DDL和DML操作，导致后面的session要么处于waiting f......
MySQL如何定位未提交事务执行的SQL语句?
weixin_34153893的博客
02-06 1124
2019独角兽企业重金招聘Python工程师标准>>> ...
mysql : show processlist 详解
【CSDN】
09-10 422
最近排查一些MySQL的问题，会经常用到 show processlist，所以在这里把这个命令总结一下，做个备忘，以备不时只需。 首先是几条常用的SQL。 1、按客户端 IP 分组，看哪个客户端的链接数最多 1 selectclient_ip,count(client_ip)asclient_numfrom(selectsubstring_index(host,':',1)asclient_ipfrominformation_schema.proc...
03.MySQL数据库-new.pptx
06-07
03.MySQL数据库-new.pptx

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论 1
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值