Best way to INSERT autoincrement field? (PHP/MySQL)

I have to insert data into two tables, Items and Class_Items. (A third table, Classes is related here, but is not being inserted into).

The primary key of Items is Item_ID, and it's an auto-incrementing integer. Aside from this primary key, there are no unique fields in Items. I need to know what the Item_ID is to match it to Classes in Class_Items.

This is all being done through a PHP interface. I'm wondering what the best way is to insert Items, and then match their Item_ID's into Class_Items. Here are the two main options I see:

• INSERT each Item, then use mysql_insert_id() to get its Item_ID for the Class_I开发者_如何学Gotems INSERT query. This means one query for every Item (thousands of queries in total).
• Get the next Autoincrement ID, then LOCK the Class_Items table so that I can just keep adding to an $item_id variable. This would mean just two queries (one for the Items, one for the Class_Items)

Which way is best and why? Also, if you have an unlisted alternative I'm open to whatever is most efficient.

---

The most efficient is probably going to be to use parameterized queries. That would require using the mysqli functions, but if you're to the point of needing to optimize this kind of query you should think about being there anyway.

No matter how you cut it, you've got two inserts to make. Doing the first, grabbing the new ID value as you've described (which imposes insignificant overhead, because the value is on hand to mysql already,) and using it in the second insert is pretty minimal.

---

I would investigate using stored procedures and/or transactions to make sure nothing bad happens.

---

I'm working on a project with mysql and what I did is the following (without using autoincrement fields):

1- I created a table called SEQUENCE with one field of type BIGINT called VALUE with an initial value of 1. This table will store the id value that will be incremented each time you insert a new record.

2- Create a store procedure and handle the id increment inside it within a transaction.

Here is an example.

CREATE PROCEDURE `SP_registerUser`(

IN _username VARCHAR(40),
IN _password VARCHAR(40),

)
BEGIN

DECLARE seq_user BIGINT;

START TRANSACTION;

#Validate that user does not exist etc..........


      #Register the user
      SELECT value FROM SEQUENCE INTO seq_user;
      UPDATE SECUENCE SET value = value + 1;

      INSERT INTO users VALUES(seq_user, _username, SHA1(_password));

      INSERT INTO user_info VALUES(seq_user, UTC_TIMESTAMP());


COMMIT;

END //

In my case I want to store the user id in two different tables (users and user_info)