Andre Matos wrote:
SET AUTOCOMMIT=0;
START TRANSACTION;
INSERT INTO staff (`Name`) VALUES ('ABC');
INSERT INTO changes (`Key`, `Table`, `Value`) VALUES (LAST_INSERT_ID(),
'staff', 'ABC');
COMMIT;
SET AUTOCOMMIT=1;
This works fine in my test environment, however what about many users doing
at the same time. Does the LAST_INSERT_ID() get the correct ID for each
user?
Is there a better way to do this or this is fine? I will be using this with
PHP4.
To further clarify (my initial reply didn't give much detail), when an
auto increment value is created for inserting it is in the scope of the
current connection, and is not changed by the outcome of the transaction.
If you follow the string of SQL statements against your tables you'll
see that the ID assigned to the record is not released on a rollback,
the second insert gets a new auto increment value.
mysql> SET AUTOCOMMIT=0;
Query OK, 0 rows affected (0.00 sec)
mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO staff (`Name`) VALUES ('ABC');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO changes (`Key`, `Table`, `Value`) VALUES
(LAST_INSERT_ID(),
-> 'staff', 'ABC');
Query OK, 1 row affected (0.00 sec)
mysql> select * from staff;
+----+------+
| ID | Name |
+----+------+
| 1 | ABC |
+----+------+
1 row in set (0.00 sec)
mysql> select * from changes;
+----+-----+-------+-------+
| ID | Key | Table | Value |
+----+-----+-------+-------+
| 1 | 1 | staff | ABC |
+----+-----+-------+-------+
1 row in set (0.00 sec)
mysql> select last_insert_id();
+------------------+
| last_insert_id() |
+------------------+
| 1 |
+------------------+
1 row in set (0.00 sec)
mysql> rollback;
Query OK, 0 rows affected (0.26 sec)
mysql> select * from staff;
Empty set (0.00 sec)
mysql> select * from changes;
Empty set (0.00 sec)
mysql> INSERT INTO staff (`Name`) VALUES ('ABC');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO changes (`Key`, `Table`, `Value`) VALUES
(LAST_INSERT_ID(),
-> 'staff', 'ABC');
Query OK, 1 row affected (0.00 sec)
mysql> select * from staff;
+----+------+
| ID | Name |
+----+------+
| 2 | ABC |
+----+------+
1 row in set (0.00 sec)
mysql> select * from changes;
+----+-----+-------+-------+
| ID | Key | Table | Value |
+----+-----+-------+-------+
| 2 | 2 | staff | ABC |
+----+-----+-------+-------+
1 row in set (0.00 sec)
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
