MySQL数据库笔记

MySQL 数据库

并发问题诊断

INFORMATION_SCHEMA.INNODB_TRX

需要注意一点，在同时查看INNODB_TRX 和 PROCESSLIST两个表是，可能存在数据不一致的情况。

The INNODB_TRX table provides information about every transaction currently executing inside InnoDB, including whether the transaction is waiting for a lock, when the transaction started, and the SQL statement the transaction is executing, if any.
INNODB_TRX 表提供INNODB中当前执行的所有事务信息，包含执行开始等待锁、已经执行的SQL事务。

Notes

• Use this table to help diagnose performance problems that occur during times of heavy concurrent load. Its contents are updated as described in Section 15.15.2.3, “Persistence and Consistency of InnoDB Transaction and Locking Information”.
• 该表可以用来诊断在并发过程中产生的性能问题。
• You must have the PROCESS privilege to query this table.
• 访问该表需要具有PROCESS的访问权限。
• Use the INFORMATION_SCHEMA COLUMNS table or the SHOW COLUMNS statement to view additional information about the columns of this table, including data types and default values.

官方文档：The INFORMATION_SCHEMA INNODB_TRX Table

异常处理

DECLARE … HANDLER 语法

DECLARE handler_action HANDLER
    FOR condition_value [, condition_value] ...
    statement

handler_action: {
    CONTINUE
  | EXIT
  | UNDO
}

condition_value: {
    mysql_error_code
  | SQLSTATE [VALUE] sqlstate_value
  | condition_name
  | SQLWARNING
  | NOT FOUND
  | SQLEXCEPTION
}

示例 1

-- 当捕获到异常时可以执行特定语句
CREATE PROCEDURE CURDEMO()
BEGIN

DECLARE COL1 VARCHAR(255);
DECLARE DONE BOOLEAN DEFAULT FALSE;
DECLARE CUR1 CURSOR FOR SELECT COLUMN FROM TABLE;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET DONE = TRUE;

OPEN CUR1;

L1: LOOP
    FETCH CUR1 INTO COL1;
    IF DONE THEN
        LEAVE L1;
    END IF;
END LOOP L1;

CLOSE CUR1;

END

上述示例中，当LOOP…FETCH遍历游标到最后一行时抛出一个NOT FOUND的异常，HANDLER捕获到异常执行 代码语句。

示例 2

-- 当捕获到异常时可以执行特定语句
CREATE PROCEDURE CURDEMO()
BEGIN
    -- 定义变量
    DECLARE COL1 VARCHAR(255);
    DECLARE DONE BOOLEAN DEFAULT FALSE;
    -- 定义游标
    DECLARE CUR1 CURSOR FOR SELECT COLUMN FROM TABLE;
    -- 定义异常处理
    DECLARE CONTINUE HANDLER FOR NOT FOUND
    	BEGIN 
    		SET DONE = TRUE;
    	END;

    OPEN CUR1;
    
    L1: LOOP
        FETCH
            CUR1
        INTO
            COL1;
        IF DONE THEN
            LEAVE L1;
        END IF;
    END LOOP L1;
    
    CLOSE CUR1;
END

上述示例中，当LOOP......FETCH遍历游标到最后一行时抛出一个NOT FOUND的异常，HANDLER捕获到异常执行 代码块。

官方文档： DECLARE … HANDLER Syntax

GET DIAGNOSTICS 获取异常信息

CREATE PROCEDURE ERRORHANDLER()
BEGIN
  DECLARE dec_returned_sqlstate CHAR(5) DEFAULT '00000';
  DECLARE dec_mysql_errno int;
  DECLARE dec_message_text text;
  DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
      -- 首先检索条件计数
      GET DIAGNOSTICS @cno = NUMBER;
      GET DIAGNOSTICS CONDITION @cno
        -- mysql错误代码
        dec_mysql_errno = MYSQL_ERRNO,
        -- mysql错误信息
        dec_message_text = MESSAGE_TEXT,
        -- 代码执行状态
        dec_returned_sqlstate = RETURNED_SQLSTATE;
      -- 将捕获到的错误信息输出
      SELECT dec_returned_sqlstate, dec_mysql_errno,dec_message_text;
    END;
  SELECT UN_DECLARE_VARIABLE;
END;

捕获错误信息如下：

dec_returned_sqlstate	dec_mysql_errno	dec_message_text
42S22	1054	Unknown column ‘UN_DECLARE_VARIABLE’ in ‘field list’

在上述程序中，使用select语句查询了一个未定义的变量UN_DECLARE_VARIABLE。所以异常处理块中捕获到了异常，并通过GET DIAGNOSTICS获取诊断信息。前两列dec_returned_sqlstate和dec_mysql_errno对应mysql错误代码。dec_message_text对应错误信息。

官方文档： GET DIAGNOSTICS Syntax

GROUP_CONCAT(expr)
 

#fx

CONCAT(str1,str2,...)

STR_TO_DATE(str,format)

DATE_FORMAT(date,format)

FIND_IN_SET(str,strlist)

Server
查看已创建的Server
select * from mysql.servers;
例子：

CREATE SERVER s
FOREIGN DATA WRAPPER mysql
OPTIONS (USER 'Remote', HOST '198.51.100.106', DATABASE 'test');

CREATE SERVER Syntax