各位老大，帮忙看看这个问题！谢谢！

zhangjiakouzf

想做一个词频统计，写了一个存储过程

mysql> delimiter //
mysql> create procedure keyword_stat( IN Keyword varchar(100) , IN Type varchar(100) , OUT count INT )
    -> BEGIN
    -> IF EXISTS( select * from keyword where word=Keyword&&type=Type)
    -> THEN
    -> update keyword set freq=freq+1 where word=Keyword&&type=Type;
    -> ELSE
    -> insert into keyword ( word, type,freq) values ( Keyword , Type , 1 );
    -> END IF;
    -> END//
delimiter ;Query OK, 0 rows affected (0.00 sec)

在运行后
mysql> call keyword_stat( "1" , "2" , @a) ;
Query OK, 1 row affected (0.01 sec)

mysql> select * from keyword;
+------+------+---------------------+------+
| word | type | time                | freq |
+------+------+---------------------+------+
| 1    | 2    | 2009-08-03 17:02:12 |    1 |
+------+------+---------------------+------+
1 row in set (0.01 sec)

又运行
mysql> call keyword_stat( "1" , "3" , @a) ;
Query OK, 1 row affected (0.00 sec)

mysql> select * from keyword;
+------+------+---------------------+------+
| word | type | time                | freq |
+------+------+---------------------+------+
| 1    | 2    | 2009-08-03 17:02:12 |    2 |
+------+------+---------------------+------+
1 row in set (0.00 sec)

结果并没有新增一条记录，而是把第一条记录频率给加了1.

再运行
mysql> call keyword_stat( "2" , "3" , @a) ;
Query OK, 1 row affected (0.00 sec)

mysql> select * from keyword;
+------+------+---------------------+------+
| word | type | time                | freq |
+------+------+---------------------+------+
| 1    | 2    | 2009-08-03 17:02:12 |    2 |
| 2    | 3    | 2009-08-03 17:03:48 |    1 |
+------+------+---------------------+------+
2 rows in set (0.00 sec)

感觉where 后面的条件只有第一个执行了判断，第二个条件没起作用

各位帮忙看看啊。谢谢！

我是mysql新手，呵呵

cenalulu

你的输入参数 Type是mysql的保留字。改个变量名就ok了。同时把&&改成and

delimiter // create procedure keyword_stat( IN Keyword varchar(100) , IN m_type varchar(100) , OUT count INT ) BEGIN IF EXISTS( select * from keyword where word= Keyword and type= m_type) THEN update keyword set freq=freq+1 where word=Keyword and type=m_type; ELSE insert into keyword ( word, type,freq) values ( Keyword ,m_type , 1 ); END IF; END//

zhangjiakouzf

mysql 版本

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 24
Server version: 5.1.37-community MySQL Community Server (GPL)

linux rhel5

枫影谁用了

&& and

zhangjiakouzf

谢谢楼上，and 也试过了，结果一样啊

枫影谁用了

mysql> select * from `um_roles`    where RoleName="企业供货商角色" and id=7; +----+-----------------------+-------------+ | ID | RoleName              | Description | +----+-----------------------+-------------+ |  7 | 企业供货商角色        | NULL        | +----+-----------------------+-------------+ 1 row in set (0.00 sec) mysql> drop PROCEDURE if EXISTS keyword_stat; Query OK, 0 rows affected (0.00 sec) mysql> delimiter // mysql> create procedure keyword_stat( IN Keyword varchar(100) , IN Type INT )     ->  BEGIN     ->  IF EXISTS( select * from `um_roles`    where RoleName=Keyword and id=Type)     ->  THEN     -> select 3;     -> ELSE     -> select 4;     ->  END IF;     ->  END// Query OK, 0 rows affected (0.00 sec) mysql> delimiter ; mysql> call  keyword_stat("企业供货商角色","5"); +---+ | 4 | +---+ | 4 | +---+ 1 row in set (0.00 sec) Query OK, 0 rows affected (0.00 sec) mysql> call  keyword_stat("企业供货商角色","7"); +---+ | 3 | +---+ | 3 | +---+ 1 row in set (0.00 sec) Query OK, 0 rows affected (0.00 sec) mysql>

zhangjiakouzf

谢谢啦，呵呵