求一触发器 现在要实现的功能是一张会员表 具体字段什么不清楚 但是可以确定有一个username字段现在要实现的是在我插入新记录的时候如果username已经存在,则转成username+后缀插入表中请问这个触发器要怎么写 解决方案 » 免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货 mysql> create table t4 ( -> id int primary key, -> username varchar(10), -> othercol int -> );Query OK, 0 rows affected (0.09 sec)mysql> delimiter //mysql>mysql> CREATE TRIGGER testref BEFORE INSERT ON t4 -> FOR EACH ROW BEGIN -> declare bExists INT default 0; -> select 1 into bExists from t4 where username=new.username; -> if bExists>0 then -> set new.username = concat(new.username,'.1'); -> end if; -> END; -> //Query OK, 0 rows affected (0.05 sec)mysql>mysql> delimiter ;mysql>mysql> insert into t4 values(1,'AA',10);Query OK, 1 row affected, 1 warning (0.08 sec)mysql> select * from t4;+----+----------+----------+| id | username | othercol |+----+----------+----------+| 1 | AA | 10 |+----+----------+----------+1 row in set (0.00 sec)mysql> insert into t4 values(2,'AA',10);Query OK, 1 row affected (0.05 sec)mysql> select * from t4;+----+----------+----------+| id | username | othercol |+----+----------+----------+| 1 | AA | 10 || 2 | AA.1 | 10 |+----+----------+----------+2 rows in set (0.02 sec) SQL 优化求助 字段替换问题~ 如何在触发器中更新同一个表中的其它记录? 谁知道MYSQL配置日志保存路径时能不能用日期变量 mysql如何insert into varbinary数据 sql语句语法错误,求指教 这个sql语句怎么写 关于触发器trigger的问题,触发器的触发条件是否可以限制在满足条件的某几行 Mysql索引使用问题 关于mysql数据迁移问题 请教一个归纳日期的查询语法? 请帮忙分解一个mysql的语句
-> id int primary key,
-> username varchar(10),
-> othercol int
-> );
Query OK, 0 rows affected (0.09 sec)mysql> delimiter //
mysql>
mysql> CREATE TRIGGER testref BEFORE INSERT ON t4
-> FOR EACH ROW BEGIN
-> declare bExists INT default 0;
-> select 1 into bExists from t4 where username=new.username;
-> if bExists>0 then
-> set new.username = concat(new.username,'.1');
-> end if;
-> END;
-> //
Query OK, 0 rows affected (0.05 sec)mysql>
mysql> delimiter ;
mysql>
mysql> insert into t4 values(1,'AA',10);
Query OK, 1 row affected, 1 warning (0.08 sec)mysql> select * from t4;
+----+----------+----------+
| id | username | othercol |
+----+----------+----------+
| 1 | AA | 10 |
+----+----------+----------+
1 row in set (0.00 sec)mysql> insert into t4 values(2,'AA',10);
Query OK, 1 row affected (0.05 sec)mysql> select * from t4;
+----+----------+----------+
| id | username | othercol |
+----+----------+----------+
| 1 | AA | 10 |
| 2 | AA.1 | 10 |
+----+----------+----------+
2 rows in set (0.02 sec)