可以用触发器。 在before insert 触发器中 检查 new.b 是否 like 'test%'
示例: (注意:过程中的表“hehe”是不存在的,目的是为了过程出错事务回滚!) mysql> create table tb_test(id int, re varchar(50)); Query OK, 0 rows affected (0.00 sec)mysql> delimiter // mysql> create trigger tr_test -> before insert -> on tb_test -> for each row -> begin -> if left(new.re,4)='test' then -> delete from hehe; -> end if; -> end; -> // Query OK, 0 rows affected (0.01 sec)mysql> insert into tb_test values(1,'aaa'); -> // Query OK, 1 row affected (0.05 sec)mysql> delimiter ; mysql> insert into tb_test values(1,'testbbb'); ERROR 1146 (42S02): Table 'test.hehe' doesn't exist mysql> select * from tb_test; +------+--------+ | id | re | +------+--------+ | 1 | aaa | +------+--------+ 1 row in set (0.00 sec)mysql> insert into tb_test values(1,'ccc'); Query OK, 1 row affected (0.00 sec)mysql> select * from tb_test; +------+--------+ | id | re | +------+--------+ | 1 | aaa | | 1 | ccc | +------+--------+ 2 rows in set (0.00 sec)mysql> insert into tb_test values(1,'test--no'); ERROR 1146 (42S02): Table 'test.hehe' doesn't exist mysql> select * from tb_test; +------+--------+ | id | re | +------+--------+ | 1 | aaa | | 1 | ccc | +------+--------+ 2 rows in set (0.00 sec)mysql>
如何作?紧急 判断那个字段 如果like 'test%' 拒绝 插入 ==--有点困惑代码实现
感谢vinsonshen create trigger tr_test -> before insert -> on tb_test -> for each row -> begin -> if left(new.re,4)='test' then -> delete from hehe; -> end if; -> end; delete from hehe------有没有更好的表达方式 (感觉这个需要2个表的参与 而我的需求实际上只有一个表的字段约束 如果是test开头的 不让写入)
不会啊,那个表 "hehe"都不能是存在的,否则事务就不能回滚了要不改成这样好啦: create trigger tr_test before insert on tb_test for each row begin declare v_num int; if left(new.re,4)='test' then set v_num=1+'abc'; end if; end;
这个测试不能通过reate trigger tr_test before insert on tb_test for each row begin declare v_num int; if left(new.re,4)='test' then set v_num=1+'abc'; end if; end;
create trigger tr_test -> before insert -> on tb_test -> for each row -> begin -> if left(new.re,4)='test' then -> delete from hehe; -> end if; -> end; 这个通过了 但是发现不能删除记录 一删除 马上报 表hehe不存字啊
所以,你这个需求考虑用触发器吧或者
插入数据的过程调用存储过程接口来处理
# (阿呢陀佛,一切皆空)
---mysql目前check功能起不了作用
所以,你这个需求考虑用触发器吧或者
插入数据的过程调用存储过程接口来处理
请问该如何作这个 感觉不是好作
在before insert 触发器中 检查 new.b 是否 like 'test%'
Query OK, 0 rows affected (0.00 sec)mysql> delimiter //
mysql> create trigger tr_test
-> before insert
-> on tb_test
-> for each row
-> begin
-> if left(new.re,4)='test' then
-> delete from hehe;
-> end if;
-> end;
-> //
Query OK, 0 rows affected (0.01 sec)mysql> insert into tb_test values(1,'aaa');
-> //
Query OK, 1 row affected (0.05 sec)mysql> delimiter ;
mysql> insert into tb_test values(1,'testbbb');
ERROR 1146 (42S02): Table 'test.hehe' doesn't exist
mysql> select * from tb_test;
+------+--------+
| id | re |
+------+--------+
| 1 | aaa |
+------+--------+
1 row in set (0.00 sec)mysql> insert into tb_test values(1,'ccc');
Query OK, 1 row affected (0.00 sec)mysql> select * from tb_test;
+------+--------+
| id | re |
+------+--------+
| 1 | aaa |
| 1 | ccc |
+------+--------+
2 rows in set (0.00 sec)mysql> insert into tb_test values(1,'test--no');
ERROR 1146 (42S02): Table 'test.hehe' doesn't exist
mysql> select * from tb_test;
+------+--------+
| id | re |
+------+--------+
| 1 | aaa |
| 1 | ccc |
+------+--------+
2 rows in set (0.00 sec)mysql>
==--有点困惑代码实现
create trigger tr_test
-> before insert
-> on tb_test
-> for each row
-> begin
-> if left(new.re,4)='test' then
-> delete from hehe;
-> end if;
-> end;
delete from hehe------有没有更好的表达方式 (感觉这个需要2个表的参与
而我的需求实际上只有一个表的字段约束 如果是test开头的 不让写入)
create trigger tr_test
before insert
on tb_test
for each row
begin
declare v_num int;
if left(new.re,4)='test' then
set v_num=1+'abc';
end if;
end;
http://blog.csdn.net/ACMAIN_CHM/archive/2009/07/25/4380183.aspx
MySQL 中如何在触发器里中断记录的插入或更新?
before insert
on tb_test
for each row
begin
declare v_num int;
if left(new.re,4)='test' then
set v_num=1+'abc';
end if;
end;
-> before insert
-> on tb_test
-> for each row
-> begin
-> if left(new.re,4)='test' then
-> delete from hehe;
-> end if;
-> end; 这个通过了 但是发现不能删除记录
一删除 马上报 表hehe不存字啊
where a=2总是说 表 hehe不存在 删除失败
http://blog.csdn.net/ACMAIN_CHM/archive/2009/07/25/4380183.aspx