//用户有一张购物卡
create table card(
id int notnull primary key auto_increment,
cash int unsigned notnull);
//金额有1000
insert into card values(1,1000);
//一位大佬,每次买东西,金额都能增加//但这显然是不可以的
update card set cash = cash - (-20) where id =1;
//写个触发器来约束这种行为
mysql> create table card_err(
-> id int notnull,
-> old_cash int unsigned notnull,
-> new_cash int unsigned notnull,
-> user varchar(30),
-> time DATETIME);
mysql> create trigger tgr_card_update
-> before update on card
-> for each row
-> begin
-> ifnew.cash-old.cash >0 then
-> insert into card_err
-> select old.id,old.cash,new.cash,USER(),NOW();
-> setnew.cash = old.cash;
-> end if;
-> end;
-> ??//重复UPDATE,钱不会改变了,日志如下+----+----------+----------+----------------+---------------------+| id | old_cash | new_cash | user | time |+----+----------+----------+----------------+---------------------+|1|1020|1040| root@localhost |2017-09-3014:49:16||1|1020|1040| root@localhost |2017-09-3014:49:31|+----+----------+----------+----------------+---------------------+
索引组织表在InnoDB中,表是按照主键顺序组织存放的,被称为索引组织表。每张表都有主键,如果没有显性指定,会自动按照如下规则选定首先判断是否有非空的唯一索引(unique not null),有则为主键否则自动创建一个6字节大小的指针可以使用_rowid来查看单列的主键值,多列共同形成的主键就看不了。InnoDB逻辑存储结构表空间:存放表的所有数据段:分为数据段,索引段,回滚段等。数据段为B