触发器例子如下 mysql> create table a (id int primary key auto_increment,col int); Query OK, 0 rows affected (0.08 sec)mysql> create table b (id int); Query OK, 0 rows affected (0.05 sec)mysql> CREATE TRIGGER testref AFTER INSERT ON a -> FOR EACH ROW -> INSERT INTO b SET id = NEW.id; Query OK, 0 rows affected (0.06 sec)mysql> insert into a (col) values (10); Query OK, 1 row affected (0.31 sec)mysql> select * from a; +----+------+ | id | col | +----+------+ | 1 | 10 | +----+------+ 1 row in set (0.00 sec)mysql> select * from b; +------+ | id | +------+ | 1 | +------+ 1 row in set (0.00 sec)mysql>
如果不使用触发器,你可以通过 LAST_INSERT_ID() 来得到刚才插入的第一个记录的ID。 但是有一些限制,比如下例中的第二个插入,多条记录时则不容易控制。mysql> USE test; Database changed mysql> CREATE TABLE t ( -> id INT AUTO_INCREMENT NOT NULL PRIMARY KEY, -> name VARCHAR(10) NOT NULL -> ); Query OK, 0 rows affected (0.09 sec)mysql> INSERT INTO t VALUES (NULL, 'Bob'); Query OK, 1 row affected (0.01 sec)mysql> SELECT * FROM t; +----+------+ | id | name | +----+------+ | 1 | Bob | +----+------+ 1 row in set (0.01 sec)mysql> SELECT LAST_INSERT_ID(); +------------------+ | LAST_INSERT_ID() | +------------------+ | 1 | +------------------+ 1 row in set (0.00 sec)mysql> INSERT INTO t VALUES -> (NULL, 'Mary'), (NULL, 'Jane'), (NULL, 'Lisa'); Query OK, 3 rows affected (0.00 sec) Records: 3 Duplicates: 0 Warnings: 0mysql> SELECT * FROM t; +----+------+ | id | name | +----+------+ | 1 | Bob | | 2 | Mary | | 3 | Jane | | 4 | Lisa | +----+------+ 4 rows in set (0.01 sec)mysql> SELECT LAST_INSERT_ID(); +------------------+ | LAST_INSERT_ID() | +------------------+ | 2 | +------------------+ 1 row in set (0.00 sec)
那能不能直接插入LAST_INSERT_ID(),比如insert into b values(NUll, LAST_INSERT_ID()); 这样可以吗?
mysql> create table a (id int primary key auto_increment,col int);
Query OK, 0 rows affected (0.08 sec)mysql> create table b (id int);
Query OK, 0 rows affected (0.05 sec)mysql> CREATE TRIGGER testref AFTER INSERT ON a
-> FOR EACH ROW
-> INSERT INTO b SET id = NEW.id;
Query OK, 0 rows affected (0.06 sec)mysql> insert into a (col) values (10);
Query OK, 1 row affected (0.31 sec)mysql> select * from a;
+----+------+
| id | col |
+----+------+
| 1 | 10 |
+----+------+
1 row in set (0.00 sec)mysql> select * from b;
+------+
| id |
+------+
| 1 |
+------+
1 row in set (0.00 sec)mysql>
但是有一些限制,比如下例中的第二个插入,多条记录时则不容易控制。mysql> USE test;
Database changed
mysql> CREATE TABLE t (
-> id INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
-> name VARCHAR(10) NOT NULL
-> );
Query OK, 0 rows affected (0.09 sec)mysql> INSERT INTO t VALUES (NULL, 'Bob');
Query OK, 1 row affected (0.01 sec)mysql> SELECT * FROM t;
+----+------+
| id | name |
+----+------+
| 1 | Bob |
+----+------+
1 row in set (0.01 sec)mysql> SELECT LAST_INSERT_ID();
+------------------+
| LAST_INSERT_ID() |
+------------------+
| 1 |
+------------------+
1 row in set (0.00 sec)mysql> INSERT INTO t VALUES
-> (NULL, 'Mary'), (NULL, 'Jane'), (NULL, 'Lisa');
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0mysql> SELECT * FROM t;
+----+------+
| id | name |
+----+------+
| 1 | Bob |
| 2 | Mary |
| 3 | Jane |
| 4 | Lisa |
+----+------+
4 rows in set (0.01 sec)mysql> SELECT LAST_INSERT_ID();
+------------------+
| LAST_INSERT_ID() |
+------------------+
| 2 |
+------------------+
1 row in set (0.00 sec)
这样可以吗?
可以,你为什么不自己试一下呢? 这种YES/NO的问题,其实自己动一手就知道了。
这个LAST_INSERT_ID 是连接级安全的,不同连接中的不会相互影响。 但如果你的多线程下共中一个connection 则有问题。能,事务其实是锁表了。A表被锁了,其它进和也写不了