Category: DataBase

MySQL目前还不支持动态游标(可能这个名词用在这里不准确)，但很多时候我们需要这样的一个功能(http://bugs.mysql.com/bug.php?id=17153)
比如在存储过程中根据传入的参数动态查询出数据，然后遍历数据，这个时候我们当然想动态的构建出这个游标出来

人民大众就开始想尽办法来实现类似的功能，比如下面的创建一个临时表来完成

mysql> CREATE TEMPORARY TABLE tmp_data (uid int(11) NOT NULL) ENGINE=MEMORY DEFAULT CHARSET=utf8;

注意这里为了提高速度用的是MEMORY引擎，当然它有一些限制行的东西，比如表的大小限制，某些类型的字段不能保存
可以根据自己的情况来抉择
更多信息可以参考http://dev.mysql.com/doc/refman/5.1/en/memory-storage-engine.html
还有一些参考意义的测评也可以借鉴，比如http://www.mysqlperformanceblog.com/2007/08/16/how-much-overhead-is-caused-by-on-disk-temporary-tables/

这里还给它增加了个索引
mysql> ALTER TABLE tmp_data ADD INDEX idx_uid (uid);
mysql> SHOW INDEX FROM tmp_data;

当然用完之后记得清理表，简单的方法就是
mysql> DROP TEMPORARY TABLE tmp_data;

临时表在不同连接之间是不可见的，但是在多线程的环境中，这个还是应该值得注意

还有更多的问题值得研究，比如create和drop临时表不会产生事务提交
但直接DROP TABLE tmp_data;也会删除临时表，但是会提交前面打开事务

临时表和正式表重名机制等等

另外，其实在MySQL内部它也大量使用了临时表，当然对程序员来说都是透明的，你不用关心它什么时候用，数据是放在内存还是放在硬盘上的
想看的话可以通过explain查看

有关MySQL奇技淫巧(一般都是Linux下试过的，其它平台自行尝试)

0、学会查看MySQL错误日志
日志名一般为HOST_NAME.err，在里面说不定很容易找到你想要的东西

1、开启MySQL General Query Log
想查看MySQL执行了哪些SQL语句？
那么就开启这个日志吧
http://dev.mysql.com/doc/refman/5.1/en/query-log.html
http://dev.mysql.com/doc/refman/5.1/en/log-destinations.html
可以通过启动mysqld加入相关参数或者动态开启

当然最方便的还是动态开启（不用重启数据库，貌似对MySQL版本有要求）
中文参考：http://www.taobaodba.com/html/477_动态开启mysql-general-log.html

mysql> set global log_output = file;
mysql> set global general_log_file=’/home/guohai/dev/x/topics/mysql/sql.log’;
mysql> set global general_log=on;

还有个要求就是MySQL对该日志文件有读写权限，我把它的own和group全改成跟MySQL对应的了，要不然报错

关闭只需要
mysql> set global general_log=off;

2、innotop使用
去http://code.google.com/p/innotop/下载最新版的安装包，这里用的是innotop-1.8.0
按照安装说明安装好，更详细的安装说明可以参见http://crazytoon.com/2008/05/21/mysql-how-do-you-install-innotop-to-monitor-innodb-in-real-time/
可能innotop版本不一样，自己稍微变通下
第一次运行innotop的时候会叫你输入一些配置信息，比如数据库链接信息
这个你可以在~/.innotop/innotop.conf中修改
guohai@KNIGHT:~/.innotop$ tree
.
|– innotop.conf
`– plugins

1 directory, 1 file

man innotop是永远不能少的
另外可以参见http://5iwww.blog.51cto.com/856039/570669
http://blog.csdn.net/qiuyepiaoling/archive/2010/11/29/6042379.aspx

3、mysql命令的一些有用参数选项
生产环境中手工操作数据库是个技术活
有些参数可以方便我们获取需要的数据，或者节省我们的时间
一些明显的信息提示或许可以帮助我们减少出错的概率
比如
mysql>prompt \h:\u@\d>
PROMPT set to ‘\h:\u@\d>’
localhost:root@hpm>
这样你就明显的看到，你是在哪台机器，用户是什么，操作的数据库是什么

MySQL Command Line Pager
mysql>\P more
mysql>\P less
mysql>\P

下面是别人总结出来有用的
http://www.developer.com/db/10-command-line-timesavers-for-mysql-tasks.html
http://www.ningoo.net/html/2008/mysql_cmdline_html_xml_prompt.html
自己也可以查看手册找到一些有用的信息

4、create table log_20110630 like log_template;
like子句会复制表结构，包括索引等，不包括权限

create table log_20110701 as select * from log_20110630;
复制数据，不包括索引

突然要弄MySQL存储过程
好久没有怎么接触过数据库存储过程了，都忘了，记在这里

create table email (id int auto_increment primary key, name text, content text, int size);
create table email_trash (id int auto_increment primary key, name text, content text, int size);

create table attachment (id int auto_increment primary key, email_id int, url text);
create table attachment_trash (id int auto_increment primary key, email_id int, url text);

delimiter //
drop procedure if exists fix_data_trash_magic //

CREATE PROCEDURE `fix_data_trash_magic`()
BEGIN

    DECLARE done INT DEFAULT 0;

    DECLARE _amount INT DEFAULT 0;
    DECLARE _size INT;
    DECLARE _id INT;
    DECLARE _newid LONG;

    DECLARE email_loop CURSOR FOR select id, size from email_trash where name = 'jemmy';

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

    OPEN email_loop;
        REPEAT
        FETCH email_loop INTO _id, _size;
            IF NOT done THEN
                insert into email(name, content, size) select name,content,size from email_trash where id = _id;

                select last_insert_id() into _newid;

                insert into attachment(email_id, url) select _newid,url from attachment_trash where email_id = _id;
                
                SET _amount = _size + _amount;
            END IF;
        UNTIL done END REPEAT;
    CLOSE email_loop;
END

call fix_data_trash_magic()//

另外还有常用的几个命令

mysql> show procedure status;
mysql> show create procedure procedure_name; // SHOW THE CREATING SQL OF THE SP

包含异常处理，保存点的存储过程

DELIMITER ;;
CREATE PROCEDURE ps_with_exception_etc(in_domain varchar(50), in_account varchar(32), in_passwd varchar(32), OUT out_no int, OUT out_msg varchar(50))
BEGIN
    DECLARE s_fullid varchar(100);

    DECLARE EXIT HANDLER FOR SQLSTATE '23000'
    BEGIN
        SET out_no = -1;
        SET @prc_work_error = 'Repeated key';
        ROLLBACK TO sp_01;
    END;

    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        SET out_no = -1;
        SET @prc_work_error = 'Unknown error';
        ROLLBACK TO sp_01;
    END;

    -- 声明之类的都应该放在前面

    SET AUTOCOMMIT = 0;
        SELECT concat(in_account, '@', in_domain) INTO s_fullid;
        START TRANSACTION;
        SAVEPOINT sp_01;
        -- DO OO HERE
        -- DO XX HERE
        COMMIT;
    SET out_no = 1;
END
;;
DELIMITER ;

存储过程中的动态SQL
这里只是演示动态SQL的写法，要是真这么写太浪费了，直接可以写出来的SQL就不需要动态SQL

DELIMITER ;;
CREATE PROCEDURE ps_with_dynamic_sql(in_domain varchar(50), in_account varchar(32), OUT out_no int, OUT out_msg varchar(50))
BEGIN
    DECLARE fullid varchar(100);

    DECLARE DROP_USER_TABLEK varchar(500);
    DECLARE DROP_USER_MAILS varchar(500);

    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        SET out_no = -1;
        SET @prc_work_error = 'Unknown error';
        ROLLBACK TO sp_01;
    END;

    SET AUTOCOMMIT = 0;
    START TRANSACTION;
    SAVEPOINT sp_01;

        SET @MyQuery = concat('DELETE FROM tablek WHERE uuid = ', in_account);
        PREPARE DROP_USER_TABLEK from @MyQuery;

        SET @MyQuery = concat('DELETE FROM mails WHERE uuid = ', in_account);
        PREPARE DROP_USER_MAILS from @MyQuery;

        EXECUTE DROP_USER_TABLEK;
        EXECUTE DROP_USER_MAILS;
    COMMIT;
    SET out_no = 1;
END
;;
DELIMITER ;

还有个简单的

CREATE PROCEDURE `insert_data`(in item integer)
BEGIN
    DECLARE counter INT;
    SET counter = item;
    WHILE counter >= 1 DO
        // DO YOUR BIZ HERE
        SET counter = counter - 1;
    END WHILE;
END

另外在存储过程中声明变量，游标，处理器是有一定的顺序的，否则会报语法错误

触发器

create trigger insert_after_addr
after insert on addr
for each row
begin
set @aid=new.id;
insert into data(addr_id, type, value) values (@aid, 5, 'hello trigger');
end

show triggers//
drop trigger insert_after_addr//

alter table data modify id int(22) not null auto_increment

CREATE trigger insert_before_tablek
    before
        insert on tablek
    for each row
        begin
            set new.status = 25;
        end