mysql> create table animals(id int not null auto_increment primary key, name cha r(32) not null); Query OK, 0 rows affected (0.08 sec)mysql> insert into animals(name) values ('dog'), ('cat'); Query OK, 2 rows affected (0.00 sec) Records: 2 Duplicates: 0 Warnings: 0mysql> select * from animals; +----+------+ | id | name | +----+------+ | 1 | dog | | 2 | cat | +----+------+ 2 rows in set (0.00 sec)mysql> CREATE TABLE account (acct_num INT, amount DECIMAL(10,2)); mysql> CREATE TRIGGER ins_sum BEFORE INSERT ON account -> FOR EACH ROW SET @sum = @sum + NEW.amount;mysql> SET @sum = 0; mysql> INSERT INTO account VALUES(137,14.98),(141,1937.50),(97,-100.00); mysql> SELECT @sum AS 'Total amount inserted'; +-----------------------+ | Total amount inserted | +-----------------------+ | 1852.48 | +-----------------------+mysql> DROP TRIGGER account.ins_sum;
触发器: DELIMITER $$DROP TRIGGER /*!50032 IF EXISTS */ `zz`.`gg`$$CREATE /*!50017 DEFINER = 'root'@'localhost' */ TRIGGER `zz`.`gg` AFTER INSERT ON `zz`.`bbb` FOR EACH ROW BEGIN if (select count(*) from bbb)>9 then delete from bbb where cid=new.cid; end if; END; $$DELIMITER ;
sequence: 1、用自增字段; 2、用查询生成,前提是表中有唯一标识的字段。
1.序列。mysql> create table seq(id int not null auto_increment primary key); Query OK, 0 rows affected (0.01 sec)mysql> insert into seq select NULL; Query OK, 1 row affected (0.02 sec) Records: 1 Duplicates: 0 Warnings: 0mysql> insert into seq select NULL from seq; Query OK, 1 row affected (0.00 sec) Records: 1 Duplicates: 0 Warnings: 0mysql> insert into seq select NULL from seq; Query OK, 2 rows affected (0.00 sec) Records: 2 Duplicates: 0 Warnings: 0mysql> insert into seq select NULL from seq; Query OK, 4 rows affected (0.00 sec) Records: 4 Duplicates: 0 Warnings: 0mysql> select * from seq; +----+ | id | +----+ | 1 | | 2 | | 3 | | 4 | | 5 | | 6 | | 7 | | 8 | +----+ 8 rows in set (0.00 sec)2、触发器。 mysql> create table tr(id int not null); Query OK, 0 rows affected (0.00 sec)mysql> delimiter || mysql> create trigger tr_test after insert on seq for each row -> begin -> insert into tr values(new.id); -> end|| Query OK, 0 rows affected (0.00 sec)mysql> delimiter ; mysql> insert into seq values (10); Query OK, 1 row affected (0.00 sec)mysql> select * from seq; +----+ | id | +----+ | 1 | | 2 | | 3 | | 4 | | 5 | | 6 | | 7 | | 8 | | 10 | +----+ 9 rows in set (0.00 sec)mysql> select * from tr; +----+ | id | +----+ | 10 | +----+ 1 row in set (0.00 sec)
r(32) not null);
Query OK, 0 rows affected (0.08 sec)mysql> insert into animals(name) values ('dog'), ('cat');
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0mysql> select * from animals;
+----+------+
| id | name |
+----+------+
| 1 | dog |
| 2 | cat |
+----+------+
2 rows in set (0.00 sec)mysql> CREATE TABLE account (acct_num INT, amount DECIMAL(10,2));
mysql> CREATE TRIGGER ins_sum BEFORE INSERT ON account
-> FOR EACH ROW SET @sum = @sum + NEW.amount;mysql> SET @sum = 0;
mysql> INSERT INTO account VALUES(137,14.98),(141,1937.50),(97,-100.00);
mysql> SELECT @sum AS 'Total amount inserted';
+-----------------------+
| Total amount inserted |
+-----------------------+
| 1852.48 |
+-----------------------+mysql> DROP TRIGGER account.ins_sum;
DELIMITER $$DROP TRIGGER /*!50032 IF EXISTS */ `zz`.`gg`$$CREATE
/*!50017 DEFINER = 'root'@'localhost' */
TRIGGER `zz`.`gg` AFTER INSERT ON `zz`.`bbb`
FOR EACH ROW BEGIN
if (select count(*) from bbb)>9 then
delete from bbb where cid=new.cid;
end if;
END;
$$DELIMITER ;
1、用自增字段;
2、用查询生成,前提是表中有唯一标识的字段。
1.序列。mysql> create table seq(id int not null auto_increment primary key);
Query OK, 0 rows affected (0.01 sec)mysql> insert into seq select NULL;
Query OK, 1 row affected (0.02 sec)
Records: 1 Duplicates: 0 Warnings: 0mysql> insert into seq select NULL from seq;
Query OK, 1 row affected (0.00 sec)
Records: 1 Duplicates: 0 Warnings: 0mysql> insert into seq select NULL from seq;
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0mysql> insert into seq select NULL from seq;
Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0mysql> select * from seq;
+----+
| id |
+----+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
| 8 |
+----+
8 rows in set (0.00 sec)2、触发器。
mysql> create table tr(id int not null);
Query OK, 0 rows affected (0.00 sec)mysql> delimiter ||
mysql> create trigger tr_test after insert on seq for each row
-> begin
-> insert into tr values(new.id);
-> end||
Query OK, 0 rows affected (0.00 sec)mysql> delimiter ;
mysql> insert into seq values (10);
Query OK, 1 row affected (0.00 sec)mysql> select * from seq;
+----+
| id |
+----+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
| 8 |
| 10 |
+----+
9 rows in set (0.00 sec)mysql> select * from tr;
+----+
| id |
+----+
| 10 |
+----+
1 row in set (0.00 sec)
insert into seq select NULL;