mysql日常查询记录

联表查询

-- 连表修改 openid 
update temlate_push_kd_user a, wechat_info b set a.openid = b.openid where a.mobile = b.phoneNumber;

时间范围查询

-- 今天
select * from 表名 where to_days(now()) = to_days(字段名);

-- 昨天
select * from 表名 where DATEDIFF(now(), 字段名) = 1;

-- 近 7 天内
select * from 表名 where DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= DATE(字段名)

-- 近 30 天内
select * from 表名 where DATE_SUB(CURDATE(), INTERVAL 30 DAY) <= DATE(字段名)

-- 本周 周一至周天
select * from 表名 where YEARWEEK(date_format(字段名, '%Y-%m-%d'), 1) = YEARWEEK(now(), 1)

-- 本周 周天至周六
select * from 表名 where YEARWEEK(date_format(字段名, '%Y-%m-%d')) = YEARWEEK(now()) 

-- 上周 周一至周天
select * from 表名 where YEARWEEK(date_format(字段名, '%Y-%m-%d'), 1) = YEARWEEK(now(), 1) - 1

-- 上周 周天至周六
select * from 表名 where YEARWEEK(date_format(字段名, '%Y-%m-%d')) = YEARWEEK(now()) - 1

-- 本月
select * from 表名 where DATE_FORMAT(字段名, '%Y%m') = DATE_FORMAT(CURDATE(), '%Y%m');

-- 上月
select * from 表名 where PERIOD_DIFF(date_format(now(), '%Y%m' ), date_format(字段名, '%Y%m')) = 1

-- 本季度
select * from 表名 where QUARTER(字段名) = QUARTER(now());

-- 上季度
select * from 表名 where QUARTER(字段名) = QUARTER(DATE_SUB(now(), interval 1 QUARTER))

-- 本年
select * from 表名 where YEAR(字段名) = YEAR(NOW());

-- 上一年
select * from 表名 where year(字段名) = year(date_sub(now(), interval 1 year))

事务相关查询

# 查询所有正在运行的事务及运行时间
select t.*,to_seconds(now())-to_seconds(t.trx_started) idle_time 
from INFORMATION_SCHEMA.INNODB_TRX t

# 查询事务详细信息及执行的SQL
select now(),	(UNIX_TIMESTAMP(now()) - UNIX_TIMESTAMP(a.trx_started)) diff_sec,	b.id, b.user, b.host, b.db, d.SQL_TEXT 
from information_schema.innodb_trx a 
	inner join information_schema.PROCESSLIST b on a.TRX_MYSQL_THREAD_ID=b.id and b.command = 'Sleep'
	inner join performance_schema.threads c ON b.id = c.PROCESSLIST_ID
	inner join performance_schema.events_statements_current d ON d.THREAD_ID = c.THREAD_ID;

# 查询事务执行过的所有历史SQL记录
SELECT
  ps.id 'PROCESS ID',
  ps.USER,
  ps.HOST,
  esh.EVENT_ID,
  trx.trx_started,
  esh.event_name 'EVENT NAME',
  esh.sql_text 'SQL',
  ps.time 
FROM
  PERFORMANCE_SCHEMA.events_statements_history esh
  JOIN PERFORMANCE_SCHEMA.threads th ON esh.thread_id = th.thread_id
  JOIN information_schema.PROCESSLIST ps ON ps.id = th.processlist_id
  LEFT JOIN information_schema.innodb_trx trx ON trx.trx_mysql_thread_id = ps.id 
WHERE
  trx.trx_id IS NOT NULL 
  AND ps.USER != 'SYSTEM_USER' 
ORDER BY
  esh.EVENT_ID;
  
 # 简单查询事务锁
 select * from sys.innodb_lock_waits
 
 # 查询事务锁详细信息
 SELECT
  tmp.*,
  c.SQL_Text blocking_sql_text,
  p.HOST blocking_host 
FROM
  (
  SELECT
    r.trx_state wating_trx_state,
    r.trx_id waiting_trx_id,
    r.trx_mysql_thread_Id waiting_thread,
    r.trx_query waiting_query,
    b.trx_state blocking_trx_state,
    b.trx_id blocking_trx_id,
    b.trx_mysql_thread_id blocking_thread,
    b.trx_query blocking_query 
  FROM
    information_schema.innodb_lock_waits w
    INNER JOIN information_schema.innodb_trx b ON b.trx_id = w.blocking_trx_id
    INNER JOIN information_schema.innodb_trx r ON r.trx_id = w.requesting_trx_id 
  ) tmp,
  information_schema.PROCESSLIST p,
  PERFORMANCE_SCHEMA.events_statements_current c,
  PERFORMANCE_SCHEMA.threads t 
WHERE
  tmp.blocking_thread = p.id 
  AND t.thread_id = c.THREAD_ID 
  AND t.PROCESSLIST_ID = p.id

持续更新中...