MYSQL 触发器错误。如何解决？

Question

SQL:

DELIMITER $$ CREATE TRIGGER `Activation_code` BEFORE UPDATE 
ON `user_users` FOR EACH ROW 
BEGIN
   IF OLD.activation_code_time < UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 15 MINUTE)) THEN SET NEW.activation_code = SELECT(ROUND((RAND() * (999999-100000))+100000)), NEW.activation_code_time = SELECT(UNIX_TIMESTAMP());
END
$$ DELIMITER ;

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'SELECT(ROUND((RAND() * (999999-100000))+100000)), NEW.activation_code_time = SEL' at line 4

谁能告诉我哪里出错了。我该如何解决？

Answer 1

选择需要用括号括起来，所有语句都需要以;结束如果

DELIMITER $$ 
CREATE TRIGGER `Activation_code` BEFORE UPDATE 
ON `user_users` FOR EACH ROW 
BEGIN
   IF OLD.activation_code_time < UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 15 MINUTE)) THEN
   SET NEW.activation_code = (SELECT(ROUND((RAND() * (999999-100000))+100000))), 
   NEW.activation_code_time = (SELECT(UNIX_TIMESTAMP()));
   END IF;
END $$ 
DELIMITER ;

如果 activation_code_time 有可能为空，您应该为其编码。

drop trigger if exists `Activation_code`;
DELIMITER $$ 
create TRIGGER `Activation_code` BEFORE UPDATE 
ON `users` FOR EACH ROW 
BEGIN
   IF coalesce(OLD.activation_code_time,0) < UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 1 MINUTE)) THEN
   SET NEW.activation_code = (SELECT(ROUND((RAND() * (999999-100000))+100000))), 
   NEW.activation_code_time = (SELECT(UNIX_TIMESTAMP()));
   END IF;
END
$$ DELIMITER ;
mysql> describe users;
+----------------------+-------------+------+-----+---------+-------------------+
| Field                | Type        | Null | Key | Default | Extra             |
+----------------------+-------------+------+-----+---------+-------------------+
| id                   | int(11)     | NO   | PRI | NULL    | auto_increment    |
| name                 | varchar(20) | YES  |     | NULL    |                   |
| uid                  | int(11)     | YES  |     | NULL    |                   |
| NAMID                | varchar(20) | YES  |     | NULL    | VIRTUAL GENERATED |
| activation_code      | int(11)     | YES  |     | NULL    |                   |
| activation_code_time | int(11)     | YES  |     | NULL    |                   |
+----------------------+-------------+------+-----+---------+-------------------+
6 rows in set (0.00 sec)
mysql> select * from users;
+----+------+------+-------+-----------------+----------------------+
| id | name | uid  | NAMID | activation_code | activation_code_time |
+----+------+------+-------+-----------------+----------------------+
|  1 | aaa  |    1 | aaa|1 |          589392 |           1514804785 |
|  2 | bbb  |    2 | bbb|2 |            NULL |                 NULL |
+----+------+------+-------+-----------------+----------------------+
2 rows in set (0.00 sec)

mysql> select * from users;update users set name = 'aaa' where id = 1;select * from users;
+----+------+------+-------+-----------------+----------------------+
| id | name | uid  | NAMID | activation_code | activation_code_time |
+----+------+------+-------+-----------------+----------------------+
|  1 | aaa  |    1 | aaa|1 |          589392 |           1514804785 |
|  2 | bbb  |    2 | bbb|2 |            NULL |                 NULL |
+----+------+------+-------+-----------------+----------------------+
2 rows in set (0.00 sec)

Query OK, 1 row affected (0.05 sec)
Rows matched: 1  Changed: 1  Warnings: 0

+----+------+------+-------+-----------------+----------------------+
| id | name | uid  | NAMID | activation_code | activation_code_time |
+----+------+------+-------+-----------------+----------------------+
|  1 | aaa  |    1 | aaa|1 |          616615 |           1514805252 |
|  2 | bbb  |    2 | bbb|2 |            NULL |                 NULL |
+----+------+------+-------+-----------------+----------------------+
2 rows in set (0.00 sec)