求一个mysql触发器 表table1 结构如下id name当插入数据后把刚插入的数据做如下替换比如把字符串中的 aaa到bbb的所有字符替换为空如提交的数据是 “测试数据aaa广告开始,广告结束bbb”希望最终插入name列的数据是"测试数据" 解决方案 » 免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货 mysql> create table table1 (id int primary key,col varchar(100));Query OK, 0 rows affected (0.23 sec)mysql> delimiter |mysql>mysql> CREATE TRIGGER testref BEFORE INSERT ON table1 -> FOR EACH ROW BEGIN -> declare i ,j int; -> set i=instr(new.col,'aaa'); -> set j=instr(new.col,'bbb'); -> if i>0 and j>i then -> set new.col=concat(left(new.col,i-1),substring(new.col,j+3)); -> end if; -> END; -> |Query OK, 0 rows affected (0.19 sec)mysql>mysql> delimiter ;mysql>mysql> insert into table1 values (1,'aasdfaewrwrew');Query OK, 1 row affected (0.45 sec)mysql> select * from table1;+----+---------------+| id | col |+----+---------------+| 1 | aasdfaewrwrew |+----+---------------+1 row in set (0.09 sec)mysql> insert into table1 values (2,'测试数据aaa广告开始,广告结束bbb');Query OK, 1 row affected (0.06 sec)mysql> select * from table1;+----+---------------+| id | col |+----+---------------+| 1 | aasdfaewrwrew || 2 | 测试数据 |+----+---------------+2 rows in set (0.00 sec)mysql> update table1 set name=concat(substring_index(name,'aaa',1),substring_index(name,'bbb',-1)); sql脚本 如何判断字段或表是否存在 mysql误删存储过程,如何恢复? 怎样统计新增用户,高分求解 求助,Mysql中datetime错误 vc 下ado 连接mysql问题 导进去的库不能用,请高手帮我看看,急 !!! 关于mysql和cygwin的问题 怎样访问远程数据库? select * form name where inst in('11','22')能得到结果,为什么group_concat生成的不行 ubuntu中Mysql5.7安装报错: 老问题,求助!!在线等 每天一张表的查询思路
Query OK, 0 rows affected (0.23 sec)mysql> delimiter |
mysql>
mysql> CREATE TRIGGER testref BEFORE INSERT ON table1
-> FOR EACH ROW BEGIN
-> declare i ,j int;
-> set i=instr(new.col,'aaa');
-> set j=instr(new.col,'bbb');
-> if i>0 and j>i then
-> set new.col=concat(left(new.col,i-1),substring(new.col,j+3));
-> end if;
-> END;
-> |
Query OK, 0 rows affected (0.19 sec)mysql>
mysql> delimiter ;
mysql>
mysql> insert into table1 values (1,'aasdfaewrwrew');
Query OK, 1 row affected (0.45 sec)mysql> select * from table1;
+----+---------------+
| id | col |
+----+---------------+
| 1 | aasdfaewrwrew |
+----+---------------+
1 row in set (0.09 sec)mysql> insert into table1 values (2,'测试数据aaa广告开始,广告结束bbb');
Query OK, 1 row affected (0.06 sec)mysql> select * from table1;
+----+---------------+
| id | col |
+----+---------------+
| 1 | aasdfaewrwrew |
| 2 | 测试数据 |
+----+---------------+
2 rows in set (0.00 sec)mysql>
update table1 set name=concat(substring_index(name,'aaa',1),substring_index(name,'bbb',-1));