/ Afred's Blog / mysql使用收集

mysql使用收集

2013-12-21 posted in [mysql]

mysql命令行调用带OUT参数的存储过程

从网上摘录的示例代码，没有运行过，但是参数使用方法时正确的:

mysql> DELIMITER //
mysql> create procedure usp_GetEmployeeName(IN id INT, OUT name VARCHAR(20))
    -> begin
    -> select emp_name into name from employee where emp_id = id;
    -> end//
Query OK, 0 rows affected (0.52 sec)

mysql> DELIMITER ;

mysql> call usp_GetEmployeeName(103, @name);
Query OK, 1 row affected (0.05 sec)

mysql> select @name;
+-------+
| @name |
+-------+
| Jack  |
+-------+
1 row in set (0.00 sec)

这里用@var_name定义用户变量（官方文档）。

mysql添加索引

alter table table_name add index index_name (column_list) ;
alter table table_name add unique (column_list) ;
alter table table_name add primary key (column_list) ;

show profile

show profile和show profles可以查看当前会话的资源使用情况。默认情况下是关闭的，可以通过设置profiling打开配置:

mysql>set profiling=on;

执行完sql之后，使用:

show profiles

查看query id,然后：

show profile for query QUERY_ID

查看详细信息。

mysql连接超时的异常

刮刮卡使用mysql时，报错:

### Cause: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure

The last packet successfully received from the server was 122,377 milliseconds ago.  The last packet sent successfully to the server was 10 milliseconds ago.

原因是mysql服务长时间不连接使用之后断开，断开之后java这边的连接池不知情，这时候拿着已经断开的连接去请求mysql server就会报异常，暂时的解决方法：

但是有没有用目前还不清楚，参数文档1，参考文档2

mysql 时间和字符串函数

DATE_ADD(date,INTERVAL expr unit), DATE_SUB(date,INTERVAL expr unit)

官方文档说明

mysql字符集操作

mysql> show variables like '%character_set%';

设置字符集，可以通过如下代码：

SET NAMES 'utf8';

这句代码相当于：

SET character_set_client = utf8;  
SET character_set_results = utf8;   
SET character_set_connection = utf8;

mysql存储过程启动事务

在Mysql中，可以通过START TRANSACTION 或者 BEGIN 启动一个新的事务。默认情况下，mysql的session变量autocommit是开启的，也就是说，只要执行了一个更改数据表的sql语句，mysql会立即把这个更改持久化到硬盘，通过START TRANSACTION，可以关闭这个选项。从这里可以看出，如果没有明确地启动事务，关键字commit和rollback是没有任何效果的。

mysql存储引擎能处理事务安全的数据表和非事务安全的数据表，可以在这里看到mysql存储引擎的介绍，需要注意的是，如果要在存储过程中用到事务，需要选择一个支持事务安全的存储引擎，否则，rollback无效。

可以通过

show create table TABLE_NAME;

查看指定表TABLE_NAME的存储引擎，如果想要修改指定表的存储引擎，通过以下命令：

alter table TABLE_NAME engine=innodb;

如果在存储过程中用到事务，并且要处理多张表，最好是用统一的引擎，否则，可能出现以下情况：

For best results, transactions should be performed using only tables managed by a single transaction-safe storage engine. Otherwise, the following problems can occur:

If you use tables from more than one transaction-safe storage engine (such as InnoDB and BDB), and the transaction isolation level is not SERIALIZABLE, it is possible that when one transaction commits, another ongoing transaction that uses the same tables will see only some of the changes made by the first transaction. That is, the atomicity of transactions is not guaranteed with mixed engines and inconsistencies can result. (If mixed-engine transactions are infrequent, you can use SET TRANSACTION ISOLATION LEVEL to set the isolation level to SERIALIZABLE on a per-transaction basis as necessary.)

If you use tables that are not transaction-safe within a transaction, changes to those tables are stored at once, regardless of the status of autocommit mode.

If you issue a ROLLBACK statement after updating a nontransactional table within a transaction, an ER_WARNING_NOT_COMPLETE_ROLLBACK warning occurs. Changes to transaction-safe tables are rolled back, but not changes to nontransaction-safe tables.

Each transaction is stored in the binary log in one chunk, upon COMMIT. Transactions that are rolled back are not logged. (Exception: Modifications to nontransactional tables cannot be rolled back. If a transaction that is rolled back includes modifications to nontransactional tables, the entire transaction is logged with a ROLLBACK statement at the end to ensure that modifications to the nontransactional tables are replicated.) See Section 5.2.3, “The Binary Log”.

mysql和Oracle处理异常的语法完全不同，在mysql存储过程中，可以声明特定的handler处理特定的条件或异常，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

自己测试了一下添加存储过程的异常处理handler，创建了表Drawrecord，其中id是自增主键，主键990已经存在：

begin declare exit handler for sqlexception 
begin 
set count=1;
rollback;
end; 
declare exit handler for sqlwarning 
begin 
set count=2;
rollback;
end; 
start transaction;
insert into Drawrecord(ssoid, drawtime) values('ttttt', now());
insert into Drawrecord(id) values(990);
commit;
select count;
end

为了调试存储过程，这里用到了一个技巧，给存储过程test添加了一个输出变量count，在mysql命令行中执行这个存储过程：

mysql > call test(@count) 
mysql > select @count 
+--------+ 
| @count | 
+--------+ 
| 1 | 
+--------+

可以看到执行了sqlexception的handler。查看结果，ssoid为ttttt的数据也没有成功插入，说明事务回滚成功。

相关说明，可以看这里，还有这里。

comments powered by Disqus