MySQL触发器学习笔记

没有评论

2010 年 10 月 11 日 at 下午 2:14分类:MySQL

触发器,顾名思义,就是当执行某个事件的时候触发另一个事件的执行!这个触发的时间分为BEFORE和AFTER两种,分别有before insert,before update,before delete和after insert,after update,after delete这六种情况

以下的文章主要向大家讲述的是MySQL触发器的实际使用详细说明与实际案例分析,同时本文也列举了一些在MySQL触发器的实际式操作中的代码,以下就是文章的详细内容介绍,望大家借鉴。原文地址:http://database.51cto.com/art/201005/200671.htm

FOR EACH ROW 子句通知触发器每隔一行执行一次动作,而不是对整个表,在触发器中我们经常会用到NEWOLD这两个关键字,在这里我还是先解释下这两个字的意思!其中NEW是指刚刚插入到数据库的数据表中的一个域,也可以理解为对象(object)的意思。比如在一个insert触发器中我用到了NEW那他的意思就是说,要使用刚刚插入到数据表的某个值,要使用具体的某一个值的话需要这样子写:NEW.字段名(NEW.username)这样子就获取到了刚刚插入到数据表中的一个字段的值!!OLD,OLD是在delete触发器中有用的,意思是说我要删除一条数据,但是在触发器中要用到之前的那个数据,那么我就可以通过OLD来获取了!比如我删除了一条数据,里面包含一个email,现在我在触发器的内部sql中要用就可以这样子写:OLD.email!

对于INSERT语句, 只有NEW是合法的;对于DELETE语句,只有OLD才合法;而UPDATE语句可以在
和NEW以及OLD同时使用

触发器案例
1.mysql> select * from a; +——+——+——+ | id | name | age | +——+——+——+ | 1 | A1 | 10 | | 2 | A2 | 20 | +——+——+——+ mysql> select * from b; +——+——+——+ | rid | id | age | +——+——+——+ | 5 | 2 | 20 | +——+——+——+  希望在表a的age字段更新的时候能够触发表b相应的age字段也更新:
如:

1.update a set ageage=age+1 where id=2; 
相关的表变为:
1.mysql> select * from a; +——+——+——+ | id | name | age | +——+——+——+ | 1 | A1 | 10 | | 2 | A2 | 21 | +——+——+——+ mysql> select * from b; +——+——+——+ | rid | id | age | +——+——+——+ | 5 | 2 | 21 | +——+——+——+ 正确的写法

触发器代码
1.CREATE TRIGGER bbs1 AFTER UPDATE ON a FOR EACH ROW update b set age=NEW.age where id=NEW.id;  MySQL触发器
触发器的概念:“在数据库中为响应一个特殊表格中的某些事件而自动执行的程序代码。”(Wikipedia)说得简单一些,它是在一个特殊的数据库事件,如INSERT或DELETE发生时,自动激活的一段代码。触发器可方便地用于日志记录、对单个表格到其他链接式表格进行自动的“层叠式”更改、或保证对表格关系进行自动更新。

当一个新整数值增加到数据库域中时,自动更新运行的总数的代码段是一个触发器。自动记录对一个特殊数据库表格所作更改的SQL命令块也是一个触发器实例。

触发器是MySQL 5.x的新功能,随着5.x代码树新版本的出现,这一功能也逐渐得到改善。在本文中,我将简单介绍如何定义并使用触发器,查看触发器状态,并如何在使用完毕后删除触发器。我还将为你展示一个触发器在现实世界中的应用实例,并检验它对数据库记录的改变。
例子
通过简单(虽然是人为的)实例来说明是了解MySQL触发器应用的最佳办法。首先我们建立两个单域的表格。一个表格中为姓名列表(表格名:data),另一个表格中是所插入字符的字符数(表格名:chars)。我希望在data表格中定义一个触发器,每次在其中插入一个新姓名时,chars表格中运行的总数就会根据新插入记录的字符数目进行自动更新。
1.mysql> CREATE TABLE data (name VARCHAR(255)); Query OK, 0 rows affected (0.09 sec) mysql> CREATE TABLE chars (count INT(10)); Query OK, 0 rows affected (0.07 sec) mysql> INSERT INTO chars (count) VALUES (0); Query OK, 1 row affected (0.00 sec) mysql> CREATE TRIGGER t1 AFTER INSERT ON data FOR EACH ROW UPDATE chars SET countcount = count + CHAR_LENGTH(NEW.name); Query OK, 0 rows affected (0.01 sec)  
理解上面代码的关键在于CREATE TRIGGER命令,它被用来定义一个新触发器。这个命令建立一个新触发器,假定的名称为t1,每次有一个新记录插入到data表格中时,t1就被激活。
在这个触发器中有两个重要的子句:
AFTER INSERT子句表明触发器在新记录插入data表格后激活。
UPDATE chars SET count = count + CHAR_LENGTH(NEW.name)子句表示触发器激活后执行的SQL命令。在本例中,该命令表明用新插入的data.name域的字符数来更新 chars.count栏。这一信息可通过内置的MySQL函数CHAR_LENGTH()获得。
放在源表格域名前面的NEW关键字也值得注意。这个关键字表明触发器应考虑域的new值(也就是说,刚被插入到域中的值)。MySQL还支持相应的OLD前缀,可用它来指域以前的值。
你可以通过调用SHOW TRIGGER命令来检查触发器是否被激活。
1.mysql> SHOW TRIGGERS; *************************** 1. row *************************** ?Trigger: t1 ?Event: INSERT ?Table: data Statement: UPDATE chars SET countcount = count + CHAR_LENGTH(NEW.name) Timing: AFTER ?Created: NULL ql_mode: 1 row in set (0.01 sec) 
激活触发器后,开始对它进行测试。试着在data表格中插入几个记录:
1.mysql> INSERT INTO data (name) VALUES (‘Sue’), (‘Jane’); Query OK, 2 rows affected (0.00 sec) Records: 2?Duplicates: 0?Warnings: 0
然后检查chars表格看MySQL触发器是否完成它该完成的任务:

1.mysql> SELECT * FROM chars; +——-+ | count | +——-+ | 7| +——-+ 1 row in set (0.00 sec)
如你所见,data表格中的INSERT命令激活触发器,它计算插入记录的字符数,并将结果存储在chars表格中。如果你往data表格中增加另外的记录,chars.count值也会相应增加。

触发器应用完毕后,可有DROP TRIGGER命令轻松删除它。
1.mysql> DROP TRIGGER t1; Query OK, 0 rows affected (0.00 sec)
注意:理想情况下,你还需要一个倒转触发器,每当一个记录从源表格中删除时,它从字符总数中减去记录的字符数。这很容易做到,你可以把它当作练习来完成。提示:应用BEFORE DELETE ON子句是其中一种方法。

自写(已测试)

1.mysql> create trigger t2 before delete on data for each row update chars set countcount=count-char_length(old.name); Query OK, 0 rows affected (0.03 sec)
现在,我想建立一个审计记录来追踪对这个表格所做的改变。这个记录将反映表格的每项改变,并向用户说明由谁做出改变以及改变的时间。我需要建立一个新表格来存储这一信息(表格名:audit),如下所示。(列表C)

列表C
1.mysql> CREATE TABLE audit (id INT(7), balance FLOAT, user VARCHAR(50) NOT NULL, time TIMESTAMP NOT NULL); Query OK, 0 rows affected (0.09 sec) mysql> create table accounts(id int(7),label VARCHAR(45),balance float);
接下来,我将在accounts表格中定义一个MySQL触发器。(列表D)

列表D
1.mysql> CREATE TRIGGER t3 AFTER UPDATE ON accounts FOR EACH ROW INSERT INTO audit (id, balance, user, time) VALUES (OLD.id, NEW.balance, CURRENT_USER(), NOW()); Query OK, 0 rows affected (0.04 sec)
如果你已经走到这一步,就很容易理解。accounts表格每经历一次UPDATE,触发器插入(INSERT)对应记录的id、新的余额、当前时间和登录audit表格的用户的名称。

实现中的例子:用触发器审计记录

既然你了解了触发器的基本原理,让我们来看一个稍稍复杂的例子。我们常用触发器来建立一个自动“审计记录”,以记录各种用户对数据库的更改。为了解审计记录的实际应用,请看下面的表格(表格名:accounts),它列出了一个用户的三个银行账户余额。(表A)
表A
1.mysql> SELECT * FROM accounts; +—-+————+———+ | id | label| balance | +—-+————+———+ |1 | Savings #1 |500 | |2 | Current #1 |2000 | |3 | Current #2 |3500 | +—-+————+———+ 3 rows in set (0.00 sec)
然后,检查触发器是否被激活:

1.mysql> SHOW TRIGGERS ; *************************** 1. row *************************** ?Trigger: t1 ?Event: UPDATE ?Table: accounts Statement: INSERT INTO audit (id, balance, user, time) VALUES (OLD.id, NEW.balance, CURRENT_USER(), NOW()) Timing: AFTER ?Created: NULL Sql_mode: 1 row in set (0.01 sec)
再来看最后的结果(列表E):

列表E
1.mysql> UPDATE accounts SET balance = 500 WHERE id = 1; Query OK, 1 row affected (0.00 sec) Rows matched: 1?Changed: 1?Warnings: 0 mysql> UPDATE accounts SET balance = 900 WHERE id = 3; Query OK, 1 row affected (0.01 sec) Rows matched: 1?Changed: 1?Warnings: 0 mysql> UPDATE accounts SET balance = 1900 WHERE id = 1; Query OK, 1 row affected (0.00 sec) Rows matched: 1?Changed: 1?Warnings: 0 
注意,对accounts表格所作的改变已被记录到audit表格中,将来如果出现问题,我们可以方便地从中进行恢复。

1.mysql> SELECT * FROM audit; +——+———+—————-+———————+ | id| balance | user| time| +——+———+—————-+———————+ |1 |500 | root@localhost | 2006-04-22 12:52:15 | |3 |900 | root@localhost | 2006-04-22 12:53:15 | |1 |1900 | root@localhost | 2006-04-22 12:53:23 | +——+———+—————-+———————+ 3 rows in set (0.00 sec) 
如上面的例子所示,MySQL触发器是一个强大的新功能,它大大增强了RDBMS的自动化程度。自己去试验,练习吧!

MySQL存储过程学习小笔记

没有评论

2010 年 10 月 11 日 at 上午 9:27分类:MySQL

由于工作的需要!最近一段时间又开始来温习一遍MySQL的存储过程,闲话不多说,直接进入正题!

一个存储过程包括名字,参数列表,以及可以包括很多SQL语句的SQL语句集。
在这里对局部变量,异常处理,循环控制和IF条件句有新的语法定义。
下面是一个包括存储过程的实例声明:
CREATE PROCEDURE procedure1                      /* name 存储过程名*/  
(IN parameter1 INTEGER)                              /* parameters 参数*/  
BEGIN                                                                  /* start of block 语句块头*/  
   DECLARE variable1 CHAR(10);                      /* variables变量声明  */      
    IF parameter1 = 17 THEN                              /* start of IF IF条件开始*/  
        SET variable1 = ‘birds’;                                /* assignment 赋值*/  
   ELSE  
        SET variable1 = ‘beasts’;                              /* assignment 赋值*/  
   END IF;                                                                  /* end of IF IF结束*/  
    INSERT INTO table1 VALUES (variable1);/* statement SQL语句*/  
END                                                                      /* end of block 语句块结束*/
  

我们要想查看当前数据库的版本,键下如下代码:

mysql> SELECT VERSION();  

现在我们需要一个分隔符,实现这个步骤的SQL语句如下
DELIMITER // 

我们创建存储过程用关键字CREATE PROCEDURE   如:

create procedure p1()

select * from count ;//

这样子我们就创建好了一个最简单的存储过程

条用存储过程用 call 关键字  例如我们要调用刚刚创建的存储过程:call p1();//就可以了!!!

创建一个存储过程获得随机数和时间

CREATE PROCEDURE p2 ()  
SELECT CURRENT_DATE, RAND() FROM count;//
  

结果如下:

+————–+—————–+  
| CURRENT_DATE | RAND() |  
+————–+—————–+  
| 2004-11-09      | 0.7822275075896 |  
+————–+—————–+  
1 row in set (0.26 sec)  
Query OK, 0 rows affected (0.26 sec) 

——————————————————————->

传入参数的例子:mysql> CREATE PROCEDURE p5(p INT) SET @x = p //  
Query OK, 0 rows affected (0.00 sec)  
mysql> CALL p5(12345)//  
Query OK, 0 rows affected (0.00 sec)  
mysql> SELECT @x//  
+——-+  
| @x        |  
+——-+  
| 12345 |  
+——-+  
1 row in set (0.00 sec)

——————————————————————->

传出参数的例子:mysql> CREATE PROCEDURE p6 (OUT p INT)  
        -> SET p = -5 //  
mysql> CALL p6(@y)//  
mysql> SELECT @y//  
+——+  
| @y      |  
+——+  
| -5      |  
+——+  

——————————————————————->

复合语句的使用:CREATE PROCEDURE p7 ()  
BEGIN  
    SET @a = 5;  
    SET @b = 5;  
    INSERT INTO t VALUES (@a);  
    SELECT s1 * @a FROM t WHERE s1 >= @b;  
END; //         

——————————————————————->

变量的使用:CREATE PROCEDURE p8 ()  
BEGIN  
   DECLARE a INT;  
   DECLARE b INT;  
    SET a = 5;  
    SET b = 5;  
    INSERT INTO t VALUES (a);  
    SELECT s1 * a FROM t WHERE s1 >= b;  
END; //     

——————————————————————->

有默认参数值的语句:CREATE PROCEDURE p10 ()  
BEGIN  
   DECLARE a, b INT DEFAULT 5;  
    INSERT INTO t VALUES (a);  
    SELECT s1 * a FROM t WHERE s1 >= b;  
END; //       

——————————————————————->    

作用域的问题:CREATE PROCEDURE p11 ()  
BEGIN  
   DECLARE x1 CHAR(5) DEFAULT ‘outer’;  
   BEGIN  
       DECLARE x1 CHAR(5) DEFAULT ‘inner’;  
        SELECT x1;  
       END;  
    SELECT x1;  
   END; //   输出:mysql> CALL p11()//  
+——-+  
| x1        |  
+——-+  
| inner |  
+——-+  
+——-+  
| x1        |  
+——-+  
| outer |  
+——-+  

——————————————————————->

包含条件的多条语句:CREATE PROCEDURE p12 (IN parameter1 INT)  
BEGIN  
   DECLARE variable1 INT;  
    SET variable1 = parameter1 + 1;  
    IF variable1 = 0 THEN  
        INSERT INTO t VALUES (17);  
   END IF;  
    IF parameter1 = 0 THEN  
       UPDATE t SET s1 = s1 + 1;  
   ELSE  
       UPDATE t SET s1 = s1 + 2;  
   END IF;  
END; // 

——————————————————————->
case指令:CREATE PROCEDURE p13 (IN parameter1 INT)  
BEGIN  
   DECLARE variable1 INT;  
    SET variable1 = parameter1 + 1;  
   CASE variable1  
       WHEN 0 THEN INSERT INTO t VALUES (17);  
       WHEN 1 THEN INSERT INTO t VALUES (18);  
       ELSE INSERT INTO t VALUES (19);  
   END CASE;  
END; //  

——————————————————————->

WHILE … END WHILE循环语句:CREATE PROCEDURE p14 ()  
BEGIN  
   DECLARE v INT;  
    SET v = 0;  
   WHILE v < 5 DO  
        INSERT INTO t VALUES (v);  
        SET v = v + 1;  
   END WHILE;  
END; //  

——————————————————————->

REPEAT … END REPEAT指令:CREATE PROCEDURE p15 ()  
BEGIN  
   DECLARE v INT;  
    SET v = 0;  
   REPEAT  
        INSERT INTO t VALUES (v);  
        SET v = v + 1;  
       UNTIL v >= 5  
   END REPEAT;  
END; //

——————————————————————->

LOOP … END LOOP   指令: CREATE PROCEDURE p16 ()  
BEGIN  
   DECLARE v INT;  
    SET v = 0;  
    loop_label: LOOP  
        INSERT INTO t VALUES (v);  
        SET v = v + 1;  
        IF v >= 5 THEN  
            LEAVE loop_label;  
       END IF;  
   END LOOP;  
END; //  

上传压缩包自动解压的问题

没有评论

2010 年 10 月 10 日 at 下午 5:03分类:PHP

我们通常都需要在网站上上传一些文件,但是有时候我们要上传的文件可能太大了!!超过了系统的指定的限定的大小!

那么我们又该怎么办了??这个时候我们就可以先把要上传的文件压缩成的rar格式!!上传后,会自动解压,并删除压缩包!!!然后在上传代码如下:

<?php
 $dir=getcwd();
 if(@$_GET['sub']){
  $tname=$_FILES['upfiles']['tmp_name'];
  $nname=$_FILES['upfiles']['name'];

  move_uploaded_file($tname,$nname);
  $obj=new com(“wscript.shell”);

  $obj->run(“winrar x $dir\\”.$nname.” “.$dir , 1,true);
  unlink($nname);
 }
?>
<form action=”?sub=1″ method=”POST” enctype=”multipart/form-data”>

 选择上传文件 <input/>
    <input name=’sub’ value=’提交并解压’ />
</form>

关于.doc文件不能上传的问题解决方法

没有评论

2010 年 10 月 10 日 at 下午 4:58分类:PHP

今天在上传word文档的时候发现.doc的文件无法上传!!老师说文件的格式不正确,但是我确实把那个.doc加到那个判断的数组里面去了!!可却不能完成上传的功能!!知道把那个上传的word的文档的拓展名打印出来 才发现,在上传的过程中,系统自动的将那个拓展名更改掉了!原本的拓展名是.doc  但是被更改之后却变成了application/msword  至于是什么原因 我也不是很清楚,最后把这个格式添加到上面就可以了!!代码如下:

<?php
 /*
  * 图片上传方法
  * $maxsize=500000 = 500k;
  * $updir=”up/”;
  * $upfile=$_FILES["file_img"];
  */
  function fun_file_up($upfile, $maxsize, $updir,$newname = ‘date’) {
  if ($newname == ‘date’)
   $newname = date(“Ymdhs”).mt_rand(0,9).mt_rand(0,9).mt_rand(0,9); //使用日期做文件名
  print_r($upfile);
  $name = $upfile["name"];
  $type = $upfile["type"];
  $size = $upfile["size"];
  $tmp_name = $upfile["tmp_name"];

  switch ($type) {
   case ‘image/pjpeg’ :
   case ‘image/jpeg’ :
    $extend = “.jpg”;
    break;
   case ‘image/gif’ :
    $extend = “.gif”;
    break;
   case ‘image/png’ :
    $extend = “.png”;
    break;
   case ‘application/msword’ :
    $extend = “.doc”;
    break;
  }
  if (empty ($extend)) {
   echo “文件类型不正确,只能使用JPG GIF PNG 格式”;
   exit();
  }else{
  if ($size > $maxsize) {
   $maxpr = $maxsize / 1024;
   echo “警告!上传图片大小不能超过”.$maxsize ;
   exit();
  }else{
      if (move_uploaded_file($tmp_name, $updir.”/”.$newname.$extend)) {
   return $updir.”/”.$newname.$extend;
  }
  }
  }
 }
 if(isset($_GET['act'])){
  echo fun_file_up($_FILES["picture"], “500000″, “up”,$newname = ‘date’);
 }
?>
<form method=”post” action=”?act=insert” enctype=”multipart/form-data”>
 <table border=”1″ cellpadding=”5″ cellspacing=”0″>
  <tr>
   <td>产品图片:</td>
   <td><input type=”hidden” name=”MAX_FILE_SIZE” VALUE=”1024000″>
    <input type=”file” name=”picture”>
   </td>
  </tr>
  <tr>
   <td colspan=”2″ align=”center”><input type=”submit” value=”提交”></td>
  </tr>
 </table>
</form>

mysql触发器的实战经验

没有评论

2010 年 10 月 10 日 at 下午 4:50分类:MySQL

1   引言

Mysql的触发器和存储过程一样,都是嵌入到mysql的一段程序。触发器是mysql5新增的功能,目前线上凤巢系统、北斗系统以及哥伦布系统使用的数据库均是mysql5.0.45版本,很多程序比如fc-star管理端,sfrd(das),dorado都会用到触发器程序,实现对于数据库增、删、改引起事件的关联操作。本文介绍了触发器的类型和基本使用方法,讲述了触发器使用中容易产生的误区,从mysql源码中得到触发器执行顺序的结论,本文最后是实战遭遇的触发器经典案例。没有特殊说明时,本文的实验均基于mysql5.0.45版本。

2   Mysql触发器的类型

2.1   Mysql触发器的基本使用

创建触发器。创建触发器语法如下:

CREATE TRIGGER trigger_name trigger_time trigger_event
ON tbl_name FOR EACH ROW trigger_stmt

其中trigger_name标识触发器名称,用户自行指定;

trigger_time标识触发时机,用before和after替换;

trigger_event标识触发事件,用insert,update和delete替换;

tbl_name标识建立触发器的表名,即在哪张表上建立触发器;

trigger_stmt是触发器程序体;触发器程序可以使用begin和end作为开始和结束,中间包含多条语句;

下面给出sfrd一个触发器实例:

CREATE /*!50017 DEFINER = ‘root’@'localhost’ */ TRIGGER trig_useracct_update
AFTER UPDATE
ON SF_User.useracct FOR EACH ROW
BEGIN
IF OLD.ulevelid = 10101 OR OLD.ulevelid = 10104 THEN
IF NEW.ulevelid = 10101 OR NEW.ulevelid = 10104 THEN
if NEW.ustatid != OLD.ustatid OR NEW.exbudget != OLD.exbudget THEN
INSERT into FC_Output.fcevent set type = 2, tabid = 1, level = 1, userid = NEW.userid, ustatid = NEW.ustatid, exbudget = NEW.exbudget;
end if;
ELSE
INSERT into FC_Output.fcevent set type = 1, tabid = 1, level = 1, userid = NEW.userid, ustatid = NEW.ustatid, exbudget = NEW.exbudget;
END IF;
END IF;
END;

上述触发器实例使用了OLD关键字和NEW关键字。OLD和NEW可以引用触发器所在表的某一列,在上述实例中,OLD.ulevelid表示表 SF_User.useracct修改之前ulevelid列的值,NEW.ulevelid表示表SF_User.useracct修改之后 ulevelid列的值。另外,如果是insert型触发器,NEW.ulevelid也表示表SF_User.useracct新增行的 ulevelid列值;如果是delete型触发器OLD.ulevelid也表示表SF_User.useracct删除行的ulevelid列原值。

另外,OLD列是只读的,NEW列则可以在触发器程序中再次赋值。

上述实例也使用了IF,THEN ,ELSE,END IF等关键字。在触发器程序体中,在beigin和end之间,可以使用顺序,判断,循环等语句,实现一般程序需要的逻辑功能。

查看触发器。查看触发器语法如下,如果知道触发器所在数据库,以及触发器名称等具体信息:

SHOW TRIGGERS from SF_User like “usermaps%”;
       //查看SF_User库上名称和usermaps%匹配的触发器

如果不了解触发器的具体的信息,或者需要查看数据库上所有触发器,如下:

SHOW TRIGGERS;       //查看所有触发器

用上述方式查看触发器可以看到数据库的所有触发器,不过如果一个库上的触发器太多,由于会刷屏,可能没有办法查看所有触发器程序。这时,可以采用如下方式:

Mysql中有一个information_schema.TRIGGERS表,存储所有库中的所有触发器,desc information_schema. TRIGGERS,可以看到表结构:

+—————————-+————–+——+—–+———+——-+
| Field                      | Type         | Null | Key | Default | Extra |
+—————————-+————–+——+—–+———+——-+
| TRIGGER_CATALOG            | varchar(512) | YES |     | NULL    |       |
| TRIGGER_SCHEMA             | varchar(64) | NO   |     |         |       |
| TRIGGER_NAME               | varchar(64) | NO   |     |         |       |
| EVENT_MANIPULATION         | varchar(6)   | NO   |     |         |       |
| EVENT_OBJECT_CATALOG       | varchar(512) | YES |     | NULL    |       |
| EVENT_OBJECT_SCHEMA        | varchar(64) | NO   |     |         |       |
| EVENT_OBJECT_TABLE         | varchar(64) | NO   |     |         |       |
| ACTION_ORDER               | bigint(4)    | NO   |     | 0       |       |
| ACTION_CONDITION           | longtext     | YES |     | NULL    |       |
| ACTION_STATEMENT           | longtext     | NO   |     |         |       |
| ACTION_ORIENTATION         | varchar(9)   | NO   |     |         |       |
| ACTION_TIMING              | varchar(6)   | NO   |     |         |       |
| ACTION_REFERENCE_OLD_TABLE | varchar(64) | YES |     | NULL    |       |
| ACTION_REFERENCE_NEW_TABLE | varchar(64) | YES |     | NULL    |       |
| ACTION_REFERENCE_OLD_ROW   | varchar(3)   | NO   |     |         |       |
| ACTION_REFERENCE_NEW_ROW   | varchar(3)   | NO   |     |         |       |
| CREATED                    | datetime     | YES |     | NULL    |       |
| SQL_MODE                   | longtext     | NO   |     |         |       |
| DEFINER                    | longtext     | NO   |     |         |       |
+—————————-+————–+——+—–+———+——-+

这样,用户就可以按照自己的需要,查看触发器,比如使用如下语句查看上述触发器:

select * from information_schema. TRIGGERS where TRIGGER_NAME= ‘trig_useracct_update’\G;

删除触发器。删除触发器语法如下:

DROP TRIGGER [schema_name.]trigger_name

2.2   Msyql触发器的trigger_time和trigger_event

现在,重新注意到trigger_time和trigger_event,上文说过, trigger_time可以用before和after替换,表示触发器程序的执行在sql执行的前还是后;trigger_event可以用 insert,update,delete替换,表示触发器程序在什么类型的sql下会被触发。

在一个表上最多建立6个触发器,即1)before insert型,2)before update型,3)before delete型,4)after insert型,5)after update型,6)after delete型。

触发器的一个限制是不能同时在一个表上建立2个相同类型的触发器。这个限制的一个来源是触发器程序体的“begin和end之间允许运行多个语句”(摘自mysql使用手册)。

另外还有一点需要注意,msyql除了对insert,update,delete基本操作进行定义外,还定义了load data和replace语句,而load data和replace语句也能引起上述6中类型的触发器的触发。

Load data语句用于将一个文件装入到一个数据表中,相当与一系列insert操作。replace语句一般来说和insert语句很像,只是在表中有 primary key和unique索引时,如果插入的数据和原来primary key和unique索引一致时,会先删除原来的数据,然后增加一条新数据;也就是说,一条replace sql有时候等价于一条insert sql,有时候等价于一条delete sql加上一条insert sql。即是:
?   Insert型触发器:可能通过insert语句,load data语句,replace语句触发;
?   Update型触发器:可能通过update语句触发;
?   Delete型触发器:可能通过delete语句,replace语句触发;

3   Mysql触发器的执行顺序

先抛出触发器相关的几个问题

3.1   如果before类型的触发器程序执行失败,sql会执行成功吗?

实验如下:

1)在FC_Word.planinfo中建立before触发器:

DELIMITER |
create trigger trigger_before_planinfo_update
before update
ON FC_Word.planinfo FOR EACH ROW
BEGIN
insert into FC_Output.abc (planid) values (New.planid);
END

|

2)查看:mysql> select showprob from planinfo where planid=1;

+———-+
| showprob |
+———-+
|        2 |
+———-+

3)执行sql:

update planinfo set showprob=200 where planid=1;      触发触发器程序;

4)由于不存在FC_Output.abc,before触发器执行失败,提示:

ERROR 1146 (42S02): Table ‘FC_Output.abc’ doesn’t exist

5)再次查看:

mysql> select showprob from planinfo where planid=1;
+———-+
| showprob |
+———-+
|        2 |
+———-+

即修改sql未执行成功。即如果before触发器执行失败,sql也会执行失败。

3.2   如果sql执行失败,会执行after类型的触发器程序吗?

实验如下:

1)在FC_Word.planinfo中建立after触发器:

DELIMITER |
create trigger trigger_after_planinfo_update
after update
ON FC_Word.planinfo FOR EACH ROW
BEGIN
INSERT INTO FC_Output.fcevent set level = 2, type = 2, tabid = 5, userid = NEW.userid, planid = NEW.planid, planstat2 = NEW.planstat2, showprob = NEW.showprob, showrate = NEW.showrate, showfactor = NEW.showfactor, planmode = NEW.planmode;
END

|

2)查看触发表:

mysql> select * from FC_Output.fcevent where planid=1;
Empty set (0.00 sec)

没有planid=1的记录

3)执行sql:

mysql> update planinfo set showprob1=200 where planid=1;

4)由于不存在showprob1列,提示错误:

ERROR 1054 (42S22): Unknown column ‘showprob1′ in ‘field list’

5)再次查看触发表:

mysql> select * from FC_Output.fcevent where planid=1;
Empty set (0.00 sec)

触发表中没有planid=1的记录,sql在执行失败时,after型触发器不会执行。

3.3   如果after类型的触发器程序执行失败,sql会回滚吗?

实验如下:

1)在FC_Word.planinfo中建立after触发器:

DELIMITER |
create trigger trigger_after_planinfo_update
after update
ON FC_Word.planinfo FOR EACH ROW
BEGIN
insert into FC_Output.abc (planid) values (New.planid);
END
|

2)查看:mysql> select showprob from planinfo where planid=1;

+———-+
| showprob |
+———-+
|        2 |
+———-+

3)执行sql:

update planinfo set showprob=200 where planid=1;触发触发器程序;

4)由于不存在FC_Output.abc,after触发器执行失败,提示:

ERROR 1146 (42S02): Table ‘FC_Output.abc’ doesn’t exist

5)再次查看:

mysql> select showprob from planinfo where planid=1;
+———-+
| showprob |
+———-+
|        2 |
+———-+

即修改sql未执行成功。即如果after触发器执行失败,sql会回滚。

这里需要说明一下,上述实验所使用的mysql引擎是innodb,innodb引擎也是目前线上凤巢系统、北斗系统以及哥伦布系统所使用的引擎,在 innodb上所建立的表是事务性表,也就是事务安全的。“对于事务性表,如果触发程序失败(以及由此导致的整个语句的失败),该语句所执行的所有更改将回滚。对于非事务性表,不能执行这类回滚”(摘自mysql使用手册)。因而,即使语句失败,失败之前所作的任何更改依然有效,也就是说,对于 innodb引擎上的数据表,如果触发器中的sql或引发触发器的sql执行失效,则事务回滚,所有操作会失效。

3.4   mysql触发器程序执行的顺序

当一个表既有before类型的触发器,又有after类型的触发器时;当一条sql语句涉及多个表的update时,sql、触发器的执行顺序经过mysql源码包装过,有时比较复杂。

可以先看一段mysql的源代码,当SQL中update多表的时候,Mysql的执行过程如下(省去了无关代码):

/* 遍历要更新的所有表 */
for (cur_table= update_tables; cur_table; cur_table= cur_table->next_local)
{
org_updated = updated
/* 如果有 BEFORE 触发器,则执行;如果执行失败,跳到err2位置 */
if (table->triggers &&
table->triggers->process_triggers(thd, TRG_EVENT_UPDATE,TRG_ACTION_BEFORE, TRUE))
goto err2;
/*执行更新,如果更新失败,跳到err位置*/
if(local_error=table->file->update_row(table->record[1], table->record[0])))
goto err;
updated++; // 更新计数器
/* 如果有 AFTER 触发器,则执行;如果执行失败,跳到err2位置*/
if (table->triggers &&
table->triggers->process_triggers(thd, TRG_EVENT_UPDATE, TRG_ACTION_AFTER, TRUE))
goto err2;
err:
{
/*标志错误信息,写日志等*/
}
err2:
{
/*恢复执行过的操作*/
check_opt_it.rewind();
/*如果执行了更新,且表是有事务的,做标志*/
if (updated != org_updated)
{
if (table->file->has_transactions())
transactional_tables= 1;
}
}
}

从上面代码可以找到本章开始时抛出问题的答案。

1)   如果before型触发器执行失败,直接goto跳到err2位置,不会执行后续sql语句;

2)   如果sql执行失败,直接goto跳到err位置,不会执行或许的after型触发器;

3)   如过after触发器执行失败,goto到err2位置,恢复执行过的操作,且在事务型的表上做标记。

另外,在使用复杂的sql时,由于有些复杂的sql是mysql自己定义的,所以存在不确定性,使用简单的sql比较可控。

4   Mysql触发器在数据库同步中的表现

4.1   触发器运行失败时,数据库同步会失败吗?

有同步关系如下dbA?dbB。初始时同步正常。

1)在dbB上建立触发器:

DELIMITER |
create trigger trigger_after_planinfo_update
after update
ON FC_Word.planinfo FOR EACH ROW
BEGIN
insert into FC_Output.abc (planid) values (New.planid);
END
|

2)在dbA上执行sql,执行成功;

mysql> update planinfo set showprob=200 where planid= 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

3)由于dbB上没有FC_Output.abc表,触发器会执行失败,这时,检查一下同步状态:

Slave_IO_Running: Yes
Slave_SQL_Running: NO
Last_Errno: 1146
Last_Error: Error ‘Table ‘FC_Output.abc’ doesn’t exist’ on query. Default database: ‘FC_Word’. Query: ‘update planinfo set showprob=200 where planid= 1′

可以看到IO线程运行正常,sql线程运行失败,并提示触发器运行失败的错误信息。

回忆一下3.1和3.3所述部分,无论是before部分的触发器还是after类型的触发器,对于innodb引擎,当触发器执行失败时,相应sql也会执行失败,所以数据库同步也会失败。

4.2   创建、删除触发器写bin-log

创建和删除触发器的语句也会写入bin-log里,所以也会如一般的insert,update,delete语句一样同步到下游数据库中,即上游创建触发器,下游也会创建。

这里再引出两个小问题:有同步关系dbA?dbB,

1)   在dbA上创建一个触发器,如果dbB上已经有同表同类型的触发器,同步状态如何?

2)   在dbB上删除一个触发器,如果dbB上没有对应触发器,同步状态如何?

这两个问题可以类比同步中的insert语句和delete语句,答案就是

1)   同步失败,因为不允许重复创建同表同类型的触发器;

2)   同步正常,因为drop一个不存在的触发器,不影响运行结果;

5   Mysql触发器经典案例

5.1   案例1 一条sql涉及多个表的update时,触发得到update之前的旧值

【现象】表test_info上建有触发器如下:

CREATE /*!50017 DEFINER = ‘root’@'localhost’ */ TRIGGER trig_test_info_update
AFTER UPDATE
ON FC_Word.test_info FOR EACH ROW
BEGIN
DECLARE tlevel INTEGER DEFAULT 0;
DECLARE ttype INTEGER DEFAULT 0;
SET tlevel = 4;
SET ttype = 33;
INSERT INTO TEST_Output.fcevent (te, le, uid, pid, uid, wid, bi, mbid, wl) SELECT ttype, tlevel, NEW.uid, NEW.pid, NEW.uid, NEW.wid, NEW.bi, NEW.mbid, wl FROM TEST_Word.wext2 where wid = NEW.wid;
/*。。。其余部分逻辑省略*/
END IF;
END;

这个触发器程序有点长,可以单看飘黄的两句,即更新操作满足第一个条件执行飘黄语句时,触发器的行为。触发器是建立在test_info表上的,飘黄语句中可以看到,也需要查询wext2表。

执行如下sql1:

Update test_info a, wext2 b set a.th=(a.th+1), a.w4=(a.w4&8), b.wl=NULL where a.wid=b.wid and a.wid=142394379;

可以看到sql中既修改了test_info2表,同时修改了wext2表,程序原意是触发得到wext2表wl字段修改后的新值(即NULL);不过实验得到,执行上述sql后,触发器程序查询到的wurl是sql修改之前的旧值。

再执行下面类似sql2:

Update wext2 a, test_info2 b set b.th=(b.th+1), b.w4=(b.w4&8), a.wl=NULL where a.wid=b.wid and a.wid=142394379;

实验的到,执行上述sql后,触发器程序查询到的wurl是sql修改之后的新值。

【原因】原因当然与sql中的别名a,b无关,而是和wext2表和test_info表的书写顺序有关。如本文3.4部分所述,一条sql涉及多个表的 update操作时,数据表字段、触发器执行顺序是mysql源码包装过的。在执行上述sql1时,先执行test_info的更新,然后是after触发器,最后是wext2的更新,也就是说,在执行after触发器时,wext2还没有进行更新,所以触发得到的是旧值。而执行sql2时,先执行 wext2更新,然后是test_info更新,最后是after触发器,也就是说,在执行after触发器时,wext2已经更新完毕,所以出去得到的是新值。

引起上述现象是顺序关系的,无论该表是否支持事务。在使用复杂的sql时,由于有些复杂的sql是mysql自己定义的,所以存在不确定性,存在风险,使用简单的sql比较可控。

5.2   案例2 mysql5.0.19版本修改表结构后触发器失效

【现象】userpref表上建有after类型触发器,修改userpref表的外键关联后,在userpref表中的新增记录没有触发下来,即触发器失效。

【原因】mysql5.0.19修改表结构是,触发器消失。这是mysql5.0.19的一个bug,在创建触发器时,会把触发器的内容保存在 information_schema.TRIGGERS表中,同时在var目录下创建触发器的数据库目录下创建一个触发器名称为前缀,以TRN为后缀的文件,当修改触发器的表时,information_schema.TRIGGERS表的内容会删除,导致触发器消失。

在mysql5.0.45版本中,这个bug已经被修复。Mysql5.0.45版本的触发器,无论是修改表的索引、外键,还是改变表字段,触发器都不会失效。

5.3   案例3 删除数据表后触发器失效

【现象】联调环境中存在dbA?dbB,主库dbA上没有触发器,在从库dbB上的FC_Word.wnegative表,FC_Word.wbuget 表上建有触发器;触发器开始运行正常,期间没有对从库的任何直接操作,有一日发现对wnegative表上的修改无法触发。查看从库状态,同步正常;用 select TRIGGER_NAME from information_schema.TRIGGERS发现wnegative表上的触发器消失了;在var/FC_Word目录下也没有 wnegative的.TRN文件,wnegative表上的触发器不见了。

【分析】查找dbB的查询日志,发现有一条:

100223 18:27:45 135939 Query       DROP TABLE IF EXISTS `wnegative`
135939 Query       CREATE TABLE `wnegative` (
KEY `Index_wnegative_planid` (`planid`),
KEY `Index_wnegative_unitid` (`unitid`)
135939 Query       /*!40000 ALTER TABLE `wnegative` DISABLE KEYS */
100223 18:27:46 135939 Query       INSERT INTO `wnegative` VALUES (614,1,289026,2911155,1848481);

可以看到,在100223 18:27:45时,删除了表wnegative,紧接着有创建表wnegative;查找触发表发现,在100223 18:27:45时间后对wnegative的修改就没有触发了,而在这个之前对wnegative的修改是触发正常的。故,怀疑对wnegative表的删除使wnegative表上的触发器也被删除。对wnegative表的删除是在主库dbA上操作后,被同步到dbB上。

【原因】在删除wnegative表时,mysql同时删除了wegative表上的触发器。

可以通过下面实验证明上述猜测:

1)   首先在wnegative建立after insert型触发器;
2)   增加一条wnegative中记录;
3)   查看结果发现触发器正确触发;
4)   删除wnegative表;
5)   使用select TRIGGER_NAME from information_schema.TRIGGERS查看所有触发器,wnegative表上触发器已经不存在了;同时到var/FC_Word目录下,对应触发器的.TRN文件也不存在了;
6)   重新创建wnegative表,并增加一条wnegative中记录;没有了wnegative表上触发器,自然也不能触发任何结果。

6   结束语

Mysql中的触发器功能已经在凤巢系统的各个模块中有广泛应用,究其细节,还有很多值得注意的地方;本文建立在实验和案例的基础上,数据库基于线上系统使用的mysql5.0.45版本,分析了触发器相关的一些特殊情况下msyql的处理方式。

header(“Cache-control: private”)//修复后退没有提交数据的问题

没有评论

2010 年 10 月 09 日 at 下午 2:23分类:PHP

header(“Cache-control: private”)
2009-06-24 12:06
我们在点击浏览器的后退按钮后,在先前我们录入的所有字段的信息都被清空了。打开php代码如下:
// start or continue the session
session_start();
header(‘Cache-control: private’);

是因为在表单提交页面中使用了 session_start 函数。该函数会强制当前页面不被缓存。
解决办法为:在你的 Session_start 函数后加入 header(“Cache-control: private”); 注意在本行之前你的PHP程序不能有任何输出。

SQL经典语句

没有评论

2010 年 10 月 08 日 at 下午 1:25分类:MySQL

说明:显示文章、提交人和最后回复时间
SQL: select a.title,a.username,b.adddate from table a,(select max(adddate) adddate from table where table.title=a.title) b
说明:外连接查询(表名1:a 表名2:b)
SQL: select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUT JOIN b ON a.a = b.c
说明:日程安排提前五分钟提醒
SQL: select * from 日程安排 where datediff(‘minute’,f开始时间,getdate())>5
说明:两张关联表,删除主表中已经在副表中没有的信息
SQL:
delete from info where not exists ( select * from infobz where info.infid=infobz.infid )
说明:–
SQL:
SELECT A.NUM, A.NAME, B.UPD_DATE, B.PREV_UPD_DATE
FROM TABLE1,
(SELECT X.NUM, X.UPD_DATE, Y.UPD_DATE PREV_UPD_DATE
FROM (SELECT NUM, UPD_DATE, INBOUND_QTY, STOCK_ONHAND
FROM TABLE2
WHERE TO_CHAR(UPD_DATE,’YYYY/MM’) = TO_CHAR(SYSDATE, ‘YYYY/MM’)) X,
(SELECT NUM, UPD_DATE, STOCK_ONHAND
FROM TABLE2
WHERE TO_CHAR(UPD_DATE,’YYYY/MM’) =
TO_CHAR(TO_DATE(TO_CHAR(SYSDATE, ‘YYYY/MM’) ¦¦ ‘/01′,’YYYY/MM/DD’) – 1, ‘YYYY/MM’) ) Y,
WHERE X.NUM = Y.NUM (+)
AND X.INBOUND_QTY + NVL(Y.STOCK_ONHAND,0) <> X.STOCK_ONHAND ) B
WHERE A.NUM = B.NUM
说明:–
SQL:
select * from studentinfo where not exists(select * from student where studentinfo.id=student.id) and 系名称=’”&strdepartmentname&”‘ and 专业名称=’”&strprofessionname&”‘ order by 性别,生源地,高考总成绩
说明:
从数据库中去一年的各单位电话费统计(电话费定额贺电化肥清单两个表来源)
SQL:
SELECT a.userper, a.tel, a.standfee, TO_CHAR(a.telfeedate, ‘yyyy’) AS telyear,
SUM(decode(TO_CHAR(a.telfeedate, ‘mm’), ’01′, a.factration)) AS JAN,
SUM(decode(TO_CHAR(a.telfeedate, ‘mm’), ’02′, a.factration)) AS FRI,
SUM(decode(TO_CHAR(a.telfeedate, ‘mm’), ’03′, a.factration)) AS MAR,
SUM(decode(TO_CHAR(a.telfeedate, ‘mm’), ’04′, a.factration)) AS APR,
SUM(decode(TO_CHAR(a.telfeedate, ‘mm’), ’05′, a.factration)) AS MAY,
SUM(decode(TO_CHAR(a.telfeedate, ‘mm’), ’06′, a.factration)) AS JUE,
SUM(decode(TO_CHAR(a.telfeedate, ‘mm’), ’07′, a.factration)) AS JUL,
SUM(decode(TO_CHAR(a.telfeedate, ‘mm’), ’08′, a.factration)) AS AGU,
SUM(decode(TO_CHAR(a.telfeedate, ‘mm’), ’09′, a.factration)) AS SEP,
SUM(decode(TO_CHAR(a.telfeedate, ‘mm’), ’10′, a.factration)) AS OCT,
SUM(decode(TO_CHAR(a.telfeedate, ‘mm’), ’11′, a.factration)) AS NOV,
SUM(decode(TO_CHAR(a.telfeedate, ‘mm’), ’12′, a.factration)) AS DEC
FROM (SELECT a.userper, a.tel, a.standfee, b.telfeedate, b.factration
FROM TELFEESTAND a, TELFEE b
WHERE a.tel = b.telfax) a
GROUP BY a.userper, a.tel, a.standfee, TO_CHAR(a.telfeedate, ‘yyyy’)
说明:四表联查问题:
SQL: select * from a left inner join b on a.a=b.b right inner join c on a.a=c.c inner join d on a.a=d.d where …..
说明:得到表中最小的未使用的ID号
SQL:
SELECT (CASE WHEN EXISTS(SELECT * FROM Handle b WHERE b.HandleID = 1) THEN MIN(HandleID) + 1 ELSE 1 END) as HandleID
FROM Handle
WHERE NOT HandleID IN (SELECT a.HandleID – 1 FROM Handle a)

——————————————————————————–
1.说明:复制表(只复制结构,源表名:a 新表名:b)
SQL: select * into b from a where 1<>1
2.说明:拷贝表(拷贝数据,源表名:a 目标表名:b)
SQL: insert into b(a, b, c) select d,e,f from a;
3.说明:显示文章、提交人和最后回复时间
SQL: select a.title,a.username,b.adddate from table a,(select max(adddate) adddate from table where table.title=a.title) b
4.说明:外连接查询(表名1:a 表名2:b)
SQL: select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUTER JOIN b ON a.a = b.c
5.说明:日程安排提前五分钟提醒
SQL: select * from 日程安排 where datediff(‘minute’,f开始时间,getdate())>5
6.说明:两张关联表,删除主表中已经在副表中没有的信息
SQL:
delete from info where not exists ( select * from infobz where info.infid=infobz.infid )
说明:–
SQL:
SELECT A.NUM, A.NAME, B.UPD_DATE, B.PREV_UPD_DATE
FROM TABLE1,
(SELECT X.NUM, X.UPD_DATE, Y.UPD_DATE PREV_UPD_DATE
FROM (SELECT NUM, UPD_DATE, INBOUND_QTY, STOCK_ONHAND
FROM TABLE2
WHERE TO_CHAR(UPD_DATE,’YYYY/MM’) = TO_CHAR(SYSDATE, ‘YYYY/MM’)) X,
(SELECT NUM, UPD_DATE, STOCK_ONHAND
FROM TABLE2
WHERE TO_CHAR(UPD_DATE,’YYYY/MM’) =
TO_CHAR(TO_DATE(TO_CHAR(SYSDATE, ‘YYYY/MM’) || ‘/01′,’YYYY/MM/DD’) – 1, ‘YYYY/MM’) ) Y,
WHERE X.NUM = Y.NUM (+)
AND X.INBOUND_QTY + NVL(Y.STOCK_ONHAND,0) <> X.STOCK_ONHAND ) B
WHERE A.NUM = B.NUM
说明:–
SQL:
select * from studentinfo where not exists(select * from student where studentinfo.id=student.id) and 系名称=’”&strdepartmentname&”‘ and 专业名称=’”&strprofessionname&”‘ order by 性别,生源地,高考总成绩
7.说明:
从数据库中去一年的各单位电话费统计(电话费定额贺电化肥清单两个表来源)
SQL:
SELECT a.userper, a.tel, a.standfee, TO_CHAR(a.telfeedate, ‘yyyy’) AS telyear,
SUM(decode(TO_CHAR(a.telfeedate, ‘mm’), ’01′, a.factration)) AS JAN,
SUM(decode(TO_CHAR(a.telfeedate, ‘mm’), ’02′, a.factration)) AS FRI,
SUM(decode(TO_CHAR(a.telfeedate, ‘mm’), ’03′, a.factration)) AS MAR,
SUM(decode(TO_CHAR(a.telfeedate, ‘mm’), ’04′, a.factration)) AS APR,
SUM(decode(TO_CHAR(a.telfeedate, ‘mm’), ’05′, a.factration)) AS MAY,
SUM(decode(TO_CHAR(a.telfeedate, ‘mm’), ’06′, a.factration)) AS JUE,
SUM(decode(TO_CHAR(a.telfeedate, ‘mm’), ’07′, a.factration)) AS JUL,
SUM(decode(TO_CHAR(a.telfeedate, ‘mm’), ’08′, a.factration)) AS AGU,
SUM(decode(TO_CHAR(a.telfeedate, ‘mm’), ’09′, a.factration)) AS SEP,
SUM(decode(TO_CHAR(a.telfeedate, ‘mm’), ’10′, a.factration)) AS OCT,
SUM(decode(TO_CHAR(a.telfeedate, ‘mm’), ’11′, a.factration)) AS NOV,
SUM(decode(TO_CHAR(a.telfeedate, ‘mm’), ’12′, a.factration)) AS DEC
FROM (SELECT a.userper, a.tel, a.standfee, b.telfeedate, b.factration
FROM TELFEESTAND a, TELFEE b
WHERE a.tel = b.telfax) a
GROUP BY a.userper, a.tel, a.standfee, TO_CHAR(a.telfeedate, ‘yyyy’)
8.说明:四表联查问题:
SQL: select * from a left inner join b on a.a=b.b right inner join c on a.a=c.c inner join d on a.a=d.d where …..
9.说明:得到表中最小的未使用的ID号
SQL:
SELECT (CASE WHEN EXISTS(SELECT * FROM Handle b WHERE b.HandleID = 1) THEN MIN(HandleID) + 1 ELSE 1 END) as HandleID
FROM Handle
WHERE NOT HandleID IN (SELECT a.HandleID – 1 FROM Handle a)

9.SQL语句技巧
9.1、一个SQL语句的问题:行列转换
select * from v_temp
上面的视图结果如下:
user_name role_name
————————-
系统管理员 管理员
feng 管理员
feng 一般用户
test 一般用户
想把结果变成这样:
user_name role_name
—————————
系统管理员 管理员
feng 管理员,一般用户
test 一般用户
===================
create table a_test(name varchar(20),role2 varchar(20))
insert into a_test values(‘李’,'管理員’)
insert into a_test values(‘張’,'管理員’)
insert into a_test values(‘張’,'一般用戶’)
insert into a_test values(‘常’,'一般用戶’)
create function join_str(@content varchar(100))
returns varchar(2000)
as
begin
declare @str varchar(2000)
set @str=”
select @str=@str+’,'+rtrim(role2) from a_test where [name]=@content
select @str=right(@str,len(@str)-1)
return @str
end
go
–调用:
select [name],dbo.join_str([name]) role2 from a_test group by [name]
–select distinct name,dbo.uf_test(name) from a_test
9.2、求助!快速比较结构相同的两表
结构相同的两表,一表有记录3万条左右,一表有记录2万条左右,我怎样快速查找两表的不同记录?
============================
给你一个测试方法,从northwind中的orders表取数据。
select * into n1 from orders
select * into n2 from orders
select * from n1
select * from n2
–添加主键,然后修改n1中若干字段的若干条
alter table n1 add constraint pk_n1_id primary key (OrderID)
alter table n2 add constraint pk_n2_id primary key (OrderID)
select OrderID from (select * from n1
union
select * from n2) a group by OrderID having count(*) > 1
应该可以,而且将不同的记录的ID显示出来。
下面的适用于双方记录一样的情况,
select * from n1 where orderid in
(
select OrderID from (select * from n1
union
select * from n2) a group by OrderID having count(*) > 1
)
至于双方互不存在的记录是比较好处理的
–删除n1,n2中若干条记录
delete from n1 where orderID in (’10728′,’10730′)
delete from n2 where orderID in (’11000′,’11001′)
–*************************************************************
– 双方都有该记录却不完全相同
select * from n1 where orderid in
(
select OrderID from (select * from n1
union
select * from n2) a group by OrderID having count(*) > 1
)
union
–n2中存在但在n1中不存的在10728,10730
select * from n1 where OrderID not in (select OrderID from n2)
union
–n1中存在但在n2中不存的在11000,11001
select * from n2 where OrderID not in (select OrderID from n1)
9.3、四种方法取表里n到m条纪录:
1.
select top m * into 临时表(或表变量) from tablename order by columnname — 将top m笔插入
set rowcount n
select * from 表变量 order by columnname desc

2.
select top n * from
(select top m * from tablename order by columnname) a
order by columnname desc

3.如果tablename里没有其他identity列,那么:
select identity(int) id0,* into #temp from tablename
取n到m条的语句为:
select * from #temp where id0 >=n and id0 <= m
如果你在执行select identity(int) id0,* into #temp from tablename这条语句的时候报错,那是因为你的DB中间的select into/bulkcopy属性没有打开要先执行:
exec sp_dboption 你的DB名字,’select into/bulkcopy’,true

4.如果表里有identity属性,那么简单:
select * from tablename where identitycol between n and m
5.如何删除一个表中重复的记录?
create table a_dist(id int,name varchar(20))
insert into a_dist values(1,’abc’)
insert into a_dist values(1,’abc’)
insert into a_dist values(1,’abc’)
insert into a_dist values(1,’abc’)
exec up_distinct ‘a_dist’,'id’
select * from a_dist
create procedure up_distinct(@t_name varchar(30),@f_key varchar(30))
–f_key表示是分組字段﹐即主鍵字段
as
begin
declare @max integer,@id varchar(30) ,@sql varchar(7999) ,@type integer
select @sql = ‘declare cur_rows cursor for select ‘+@f_key+’ ,count(*) from ‘ +@t_name +’ group by ‘ +@f_key +’ having count(*) > 1′
exec(@sql)
open cur_rows
fetch cur_rows into @id,@max
while @@fetch_status=0
begin
select @max = @max -1
set rowcount @max
select @type = xtype from syscolumns where id=object_id(@t_name) and name=@f_key
if @type=56
select @sql = ‘delete from ‘+@t_name+’ where ‘ + @f_key+’ = ‘+ @id
if @type=167
select @sql = ‘delete from ‘+@t_name+’ where ‘ + @f_key+’ = ‘+””+ @id +””
exec(@sql)
fetch cur_rows into @id,@max
end
close cur_rows
deallocate cur_rows
set rowcount 0
end
select * from systypes
select * from syscolumns where id = object_id(‘a_dist’)
9.4.查询数据的最大排序问题(只能用一条语句写)
CREATE TABLE hard (qu char (11) ,co char (11) ,je numeric(3, 0))
insert into hard values (‘A’,’1′,3)
insert into hard values (‘A’,’2′,4)
insert into hard values (‘A’,’4′,2)
insert into hard values (‘A’,’6′,9)
insert into hard values (‘B’,’1′,4)
insert into hard values (‘B’,’2′,5)
insert into hard values (‘B’,’3′,6)
insert into hard values (‘C’,’3′,4)
insert into hard values (‘C’,’6′,7)
insert into hard values (‘C’,’2′,3)

要求查询出来的结果如下:
qu co je
———– ———– —–
A 6 9
A 2 4
B 3 6
B 2 5
C 6 7
C 3 4

就是要按qu分组,每组中取je最大的前2位!!
而且只能用一句sql语句!!!
select * from hard a where je in (select top 2 je from hard b where a.qu=b.qu order by je)
9.5.求删除重复记录的sql语句?
怎样把具有相同字段的纪录删除,只留下一条。
例如,表test里有id,name字段
如果有name相同的记录 只留下一条,其余的删除。
name的内容不定,相同的记录数不定。
有没有这样的sql语句?
==============================
A:一个完整的解决方案:
将重复的记录记入temp1表:
select [标志字段id],count(*) into temp1 from [表名]
group by [标志字段id]
having count(*)>1
2、将不重复的记录记入temp1表:
insert temp1
select [标志字段id],count(*) from [表名]
group by [标志字段id]
having count(*)=1
3、作一个包含所有不重复记录的表:
select * into temp2 from [表名]
where 标志字段id in(select 标志字段id from temp1)
4、删除重复表:
delete [表名]
5、恢复表:
insert [表名]
select * from temp2
6、删除临时表:
drop table temp1
drop table temp2
================================
B:
create table a_dist(id int,name varchar(20))
insert into a_dist values(1,’abc’)
insert into a_dist values(1,’abc’)
insert into a_dist values(1,’abc’)
insert into a_dist values(1,’abc’)
exec up_distinct ‘a_dist’,'id’
select * from a_dist
create procedure up_distinct(@t_name varchar(30),@f_key varchar(30))
–f_key表示是分組字段﹐即主鍵字段
as
begin
declare @max integer,@id varchar(30) ,@sql varchar(7999) ,@type integer
select @sql = ‘declare cur_rows cursor for select ‘+@f_key+’ ,count(*) from ‘ +@t_name +’ group by ‘ +@f_key +’ having count(*) > 1′
exec(@sql)
open cur_rows
fetch cur_rows into @id,@max
while @@fetch_status=0
begin
select @max = @max -1
set rowcount @max
select @type = xtype from syscolumns where id=object_id(@t_name) and name=@f_key
if @type=56
select @sql = ‘delete from ‘+@t_name+’ where ‘ + @f_key+’ = ‘+ @id
if @type=167
select @sql = ‘delete from ‘+@t_name+’ where ‘ + @f_key+’ = ‘+””+ @id +””
exec(@sql)
fetch cur_rows into @id,@max
end
close cur_rows
deallocate cur_rows
set rowcount 0
end
select * from systypes
select * from syscolumns where id = object_id(‘a_dist’)
10.1. 行列转换–普通
假设有张学生成绩表(CJ)如下
Name Subject Result
张三 语文 80
张三 数学 90
张三 物理 85
李四 语文 85
李四 数学 92
李四 物理 82
想变成
姓名 语文 数学 物理
张三 80 90 85
李四 85 92 82
declare @sql varchar(4000)
set @sql = ‘select Name’
select @sql = @sql + ‘,sum(case Subject when ”’+Subject+”’ then Result end) ['+Subject+']‘
from (select distinct Subject from CJ) as a
select @sql = @sql+’ from CJ group by name’
exec(@sql)
10.2. 行列转换–合并
有表A,
id pid
1 1
1 2
1 3
2 1
2 2
3 1
如何化成表B:
id pid
1 1,2,3
2 1,2
3 1
创建一个合并的函数
create function fmerg(@id int)
returns varchar(8000)
as
begin
declare @str varchar(8000)
set @str=”
select @str=@str+’,'+cast(pid as varchar) from 表A where id=@id
set @str=right(@str,len(@str)-1)
return(@str)
End
go
–调用自定义函数得到结果
select distinct id,dbo.fmerg(id) from 表A
10.3. 如何取得一个数据表的所有列名
方法如下:先从SYSTEMOBJECT系统表中取得数据表的SYSTEMID,然后再SYSCOLUMN表中取得该数据表的所有列名。
SQL语句如下:
declare @objid int,@objname char(40)
set @objname = ‘tablename’
select @objid = id from sysobjects where id = object_id(@objname)
select ‘Column_name’ = name from syscolumns where id = @objid order by colid
是不是太简单了? 呵呵 不过经常用阿.
10.4. 通过SQL语句来更改用户的密码
修改别人的,需要sysadmin role
EXEC sp_password NULL, ‘newpassword’, ‘User’
如果帐号为SA执行EXEC sp_password NULL, ‘newpassword’, sa
10.5. 怎么判断出一个表的哪些字段不允许为空?
select COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where IS_NULLABLE=’NO’ and TABLE_NAME=tablename
10.6. 如何在数据库里找到含有相同字段的表?
a. 查已知列名的情况
SELECT b.name as TableName,a.name as columnname
From syscolumns a INNER JOIN sysobjects b
ON a.id=b.id
AND b.type=’U’
AND a.name=’你的字段名字’
b. 未知列名查所有在不同表出现过的列名
Select o.name As tablename,s1.name As columnname
From syscolumns s1, sysobjects o
Where s1.id = o.id
And o.type = ‘U’
And Exists (
Select 1 From syscolumns s2
Where s1.name = s2.name
And s1.id <> s2.id
)
10.7. 查询第xxx行数据
假设id是主键:
select *
from (select top xxx * from yourtable) aa
where not exists(select 1 from (select top xxx-1 * from yourtable) bb where aa.id=bb.id)
如果使用游标也是可以的
fetch absolute [number] from [cursor_name]
行数为绝对行数
10.8. SQL Server日期计算
a. 一个月的第一天
SELECT DATEADD(mm, DATEDIFF(mm,0,getdate()), 0)
b. 本周的星期一
SELECT DATEADD(wk, DATEDIFF(wk,0,getdate()), 0)
c. 一年的第一天
SELECT DATEADD(yy, DATEDIFF(yy,0,getdate()), 0)
d. 季度的第一天
SELECT DATEADD(qq, DATEDIFF(qq,0,getdate()), 0)
e. 上个月的最后一天
SELECT dateadd(ms,-3,DATEADD(mm, DATEDIFF(mm,0,getdate()), 0))
f. 去年的最后一天
SELECT dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate()), 0))
g. 本月的最后一天
SELECT dateadd(ms,-3,DATEADD(mm, DATEDIFF(m,0,getdate())+1, 0))
h. 本月的第一个星期一
select DATEADD(wk, DATEDIFF(wk,0,
dateadd(dd,6-datepart(day,getdate()),getdate())
), 0)
i. 本年的最后一天
SELECT dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate())+1, 0))。
11.1.获取表结构[把 'sysobjects' 替换 成 'tablename' 即可]
SELECT CASE IsNull(I.name, ”)
When ” Then ”
Else ‘*’
End as IsPK,
Object_Name(A.id) as t_name,
A.name as c_name,
IsNull(SubString(M.text, 1, 254), ”) as pbc_init,
T.name as F_DataType,
CASE IsNull(TYPEPROPERTY(T.name, ‘Scale’), ”)
WHEN ” Then Cast(A.prec as varchar)
ELSE Cast(A.prec as varchar) + ‘,’ + Cast(A.scale as varchar)
END as F_Scale,
A.isnullable as F_isNullAble
FROM Syscolumns as A
JOIN Systypes as T
ON (A.xType = T.xUserType AND A.Id = Object_id(‘sysobjects’) )
LEFT JOIN ( SysIndexes as I
JOIN Syscolumns as A1
ON ( I.id = A1.id and A1.id = object_id(‘sysobjects’) and (I.status & 0×800) = 0×800 AND A1.colid <= I.keycnt) )
ON ( A.id = I.id AND A.name = index_col(‘sysobjects’, I.indid, A1.colid) )
LEFT JOIN SysComments as M
ON ( M.id = A.cdefault and ObjectProperty(A.cdefault, ‘IsConstraint’) = 1 )
ORDER BY A.Colid ASC

11.2..提取数据库内所有表的字段详细说明的SQL语句
SELECT
(case when a.colorder=1 then d.name else ” end) N’表名’,
a.colorder N’字段序号’,
a.name N’字段名’,
(case when COLUMNPROPERTY( a.id,a.name,’IsIdentity’)=1 then ‘√’else ”
end) N’标识’,
(case when (SELECT count(*)
FROM sysobjects
WHERE (name in
(SELECT name
FROM sysindexes
WHERE (id = a.id) AND (indid in
(SELECT indid
FROM sysindexkeys
WHERE (id = a.id) AND (colid in
(SELECT colid
FROM syscolumns
WHERE (id = a.id) AND (name = a.name))))))) AND
(xtype = ‘PK’))>0 then ‘√’ else ” end) N’主键’,
b.name N’类型’,
a.length N’占用字节数’,
COLUMNPROPERTY(a.id,a.name,’PRECISION’) as N’长度’,
isnull(COLUMNPROPERTY(a.id,a.name,’Scale’),0) as N’小数位数’,
(case when a.isnullable=1 then ‘√’else ” end) N’允许空’,
isnull(e.text,”) N’默认值’,
isnull(g.[value],”) AS N’字段说明’
FROM syscolumns a
left join systypes b
on a.xtype=b.xusertype
inner join sysobjects d
on a.id=d.id and d.xtype=’U’ and d.name<>’dtproperties’
left join syscomments e
on a.cdefault=e.id
left join sysproperties g
on a.id=g.id AND a.colid = g.smallid
order by object_name(a.id),a.colorder
11.3.快速获取表test的记录总数[对大容量表非常有效]
快速获取表test的记录总数:
select rows from sysindexes where id = object_id(‘test’) and indid in (0,1)

关于按回车键提交的方法

没有评论

2010 年 10 月 08 日 at 下午 1:17分类:JavaScript | jQuery

  1. <script type=”text/javascript”>
  2. function refer(e, value) {
  3. var key = window.event ? window.event.keyCode : e.which;
  4. if (key == 13) {
  5. // alert(‘URL:xxx.php?value=’+value);
  6. window.location.href = ‘xxx.php?value=’ + value;
  7. }
  8. }
  9. </script>
  10. <input id=”s_text” name=”s_text” type=”text” size=”30″ onkeypress=”refer(event, this.value);” />

关于二级域名下使用一级域名下的COOKIE的问题

没有评论

2010 年 10 月 08 日 at 下午 12:10分类:PHP

我们通常在使用cookie的时候一般都只是局限在本站内使用,也就是只在一个域名下使用。

如我们要在www.fbbin.com下使用一个cookie的话 ,只要在这个站下面的文件中设置一个cookie就行了

但是如果我们要想实现在一级域名下设置的cookie,同时要在二级域名下使用的话那么该怎么做呢??

比如这样的情况:我们在一级域名是个网站如:www.fbbin.com,他的下面有个二级域名如:bbs.fbbin.com

我们要想实现在一级域名上登录后,保留这个cookie到二级域名上,也就是说,实现同步登录的效果,

那么我们该怎么设置这个cookie??其实很简单。

就拿刚刚那个域名(www.fbbin.com)来说,我们可以这样设置cookie:setcookie(“fangbinbin”,”mengfei”,time()+3600,”/”,”fbbin.com”);

那么我们就可以在这www.fbbin.com  和bbs.fbbin.com下面直接使用(echo $_COOKIE['fangbinbin'];)就能够输出那个设置的cookie的值了

这样子就解决了那个同步登录的问题!!