mysql

存储过程打断点，对比数据库等可以使用开发工具

dbForge Studio for MySQL

mysql查看当前实时连接数

show status like 'Threads%';

+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| Threads_cached | 58 |
| Threads_connected | 57 | ###这个数值指的是打开的连接数
| Threads_created | 3676 |
| Threads_running | 4 | ###这个数值指的是激活的连接数，这个数值一般远低于connected数值
+-------------------+-------+

Threads_connected 跟show processlist结果相同，表示当前连接数。准确的来说，Threads_running是代表当前并发数

这是是查询数据库当前设置的最大连接数

show variables like '%max_connections%';

+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| max_connections | 100 |
+-----------------+-------+

设置这个最大连接数值

方法1

set GLOBAL max_connections=1024;
show variables like '%max_connections%';

方法2

可以在/etc/my.cnf里面设置数据库的最大连接数
max_connections = 1000

索引

-- 查看SQL是否使用索引，前面加上explain即可
explain select * from emp where name = 'Jefabc';

锁

-- 查询是否锁表
show open tables where in_use>0;

-- 查询进程，然后 kill id
show processlist;

-- 当前运行的所有事务
select * from information_schema.innodb_trx;

-- 当前出现的锁
select * from information_schema.innodb_locks; 

-- 锁等待的对应关系
select * from information_schema.innodb_lock_waits;

杀存储过程线程

SHOW PROCESSLIST;
KILL   10866;

事务和异常处理

DECLARE t_error integer DEFAULT 0;
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET t_error = 1;
START TRANSACTION;

#...
#...

IF t_error = 1 THEN
    ROLLBACK;
ELSE 
    COMMIT;
END IF;

CONTINUE （游标select语句错误，会出现死循环问题）

BEGIN
  DECLARE v_id bigint DEFAULT 0;

  DECLARE done int DEFAULT 0;
  DECLARE t_error integer DEFAULT 0;

  DECLARE cu_sc CURSOR FOR (SELECT id,a FROM t_user WHERE id = -1);
  DECLARE EXIT HANDLER FOR NOT FOUND SET done = 1;

  #事务处理
  DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET t_error = 1;

  START TRANSACTION;

　　 OPEN cu_sc;
    cursor_loop:
    LOOP FETCH cu_sc INTO v_id;
      IF done = 1 OR t_error = 1 THEN
        LEAVE cursor_loop;
      END IF;

    END LOOP cursor_loop;
    CLOSE cu_sc;

    IF t_error = 1 THEN
      ROLLBACK;
    ELSE
      COMMIT;
    END IF;
END

EXIT （游标select语句错误，不会出现死循环问题）

BEGIN
  DECLARE v_id bigint DEFAULT 0;

  DECLARE done int DEFAULT 0;
  DECLARE t_error integer DEFAULT 0;

  DECLARE cu_sc CURSOR FOR (SELECT id,a FROM t_user WHERE id = -1);
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
  
  #事务处理
  DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
      ROLLBACK;
    END;
  START TRANSACTION;

  OPEN cu_sc;
  cursor_loop:LOOP FETCH cu_sc INTO v_id;
    IF done = 1 THEN
        LEAVE cursor_loop;
    END IF;

  END LOOP cursor_loop;
  CLOSE cu_sc;

  COMMIT;
END

CONTINUE：继续执行封闭代码块(BEGIN ... END)。
EXIT：处理程序声明封闭代码块的执行终止。

循环结果集

DECLARE done int DEFAULT 0;
DECLARE cu_sc CURSOR FOR (SELECT * FROM t_user);#定义游标
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

OPEN cu_sc; #打开游标

cursor_loop:LOOP FETCH cu_sc INTO v_...; #开始循

#跳出循环
IF done = 1 THEN
    LEAVE cursor_loop;
END IF;

#...

END LOOP cursor_loop; #结束循环

CLOSE cu_sc; #关闭游标

#跳出本次循环
IF .... THEN
    ITERATE read_loop;
END IF;

事件

SHOW VARIABLES LIKE '%event_sche%';#查看event是否开启
SET GLOBAL event_scheduler = 1;#将事件计划开启
SET GLOBAL event_scheduler = 0;#将事件计划关闭
ALTER EVENT eventName ON COMPLETION PRESERVE DISABLE;#关闭事件任务
ALTER EVENT eventName ON COMPLETION PRESERVE ENABLE;#开启事件任务
SHOW EVENTS;#查看事件任务

游标嵌套游标

-- 创建存储过程
create procedure company_attachment_cursor(in _type int)
begin
-- 申明局部变量 declare
    declare _company_id varchar(25);
    declare targetId varchar(25);
    declare num int;
    DECLARE done int DEFAULT FALSE;
    -- 申明游标
    declare cur1 CURSOR FOR select distinct company_id from company_attachment where type=_type;
    -- 第二个游标需使用第一个循环的变量_company_id，故将变量申明在最外层
    declare cur2 CURSOR FOR select id from company_attachment where company_id=_company_id and type=_type;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    open cur1;
    read_loop: LOOP
        fetch cur1 into _company_id;
        IF done THEN
        LEAVE read_loop;
        END IF;
        -- 每次循环开始时重新对num进行赋值
        set num = 0;
        -- 开启第二个游标
        open cur2;
        inner_loop: LOOP
            fetch cur2 into targetId;
            -- 打印过程
            -- select num;
            IF done THEN
            LEAVE inner_loop;
            END IF;
            update company_attachment set number=num where id=targetId;
            set num=num+1;
        END LOOP;
        close cur2;
        -- 第一个循环结束时done=1
        -- 故需手动设置done=0,否则外层循环仅会执行一次
        SET done = 0;   
    END LOOP;
    close cur1;
end;

三种循环

一

declare i int;
set i=0;
while i<5 do
  insert into t1(filed) values(i);
    set i=i+1;
end while;

二

declare i int default 0;
repeat
    insert into t1(filed) values(i);
    set i=i+1;
    until i>=5
end repeat;

注意until i>=5后面不要加分号，如果加分号，就是提示语法错误

三

declare i int default 0;
loop_label: loop
    insert into t1(filed) values(i);
    set i=i+1;
    if i>=5 then
        leave loop_label;
    end if;
end loop;

时间加减

select date_add(now(), interval 1 day); - 加1天

select date_add(now(), interval 1 hour); -加1小时

select date_add(now(), interval 1 minute); - 加1分钟

select date_add(now(), interval 1 second); -加1秒

select date_add(now(), interval 1 microsecond);-加1毫秒

select date_add(now(), interval 1 week);-加1周

select date_add(now(), interval 1 month);-加1月

select date_add(now(), interval 1 quarter);-加1季

select date_add(now(), interval 1 year);-加1年

mysql时抛出Lock wait timeout exceeded; try restarting transaction异常

select * from information_schema.innodb_trx
执行命令kill +线程号即可解决。

清理日志

SHOW MASTER LOGS;
PURGE MASTER LOGS BEFORE DATE_SUB(CURRENT_DATE, INTERVAL 10 DAY);
RESET MASTER;

mysql查询数据库中所有表名和表注释

select table_name,table_comment from information_schema.tables where table_schema='数据库'

for update 死锁问题

两个客户端session A B

select * from test_gapLock where id = 11 for update;

insert into test_gapLock(id,remark) VALUES(10,55);
insert into test_gapLock(id,remark) VALUES(12,55);
insert into test_gapLock(id,remark) VALUES(19,55);
insert into test_gapLock(id,remark) VALUES(20,55);

> 1205 - Lock wait timeout exceeded; try restarting transaction >1205-超过锁定等待超时；尝试重新启动事务

在客户端A未提交之前，在客户端B运行如下sql，可以运行，说明gap锁与gap锁之间不是互斥的：

select * from test_gapLock where id = 12 for update;

如果gap锁与gap锁直接是可以相互访问的，update排它锁与排它锁直接互斥，那么可能造成死锁问题：

如果客户端A获取到了[10,20)之间的间隙锁，另一个客户端B也可以获取到[10,20)之间的间隙锁。这时就可能会发生死锁问题，如下案例。
客户端A获取到了[10,20)之间的间隙锁不允许其他的DDL操作，在事务提交，间隙锁释放之前，客户端B也获取到了间隙锁[10,20)；

客户端A，获取锁成功，执行后，没有手动提交：

select * from test_gapLock where id = 11 for update;
insert into test_gapLock(id,remark) VALUES(15,88);

客户端B：

select * from test_gapLock where id = 12 for update;
insert into test_gapLock(id,remark) VALUES(16,88);

show full processlist; 查看，有两个执行力900多秒的任务线程：

SELECT * FROM information_schema.INNODB_TRX;发现两个事务（trx_mysql_thread_id），执行了10多分钟了

可能是发生了死锁，kill掉死锁线程id（trx_mysql_thread_id）

posted on 2018-06-25 15:27 1zfang1 阅读(181) 评论(0) 编辑收藏举报

刷新页面返回顶部

小毛芋头

mysql

导航

公告