Mysql->autoincrementing related to an other field

Question

I am stuck on a project design. One of the table has 1-M relation with users table. So it has a foreign key. Same field is also primary key.

Table as follows

Itemid:

• Primarykey
• Autoincrement

Useriditem:

• Primarykey
• Foreign key of id in users table

Itemname:

• Not null

Values:

-----------------------------------------
|  **ITEMID** | **USERID** | ITEMNAME   |
-----------------------------------------
| 1           | 1          | fooooooo   |
-----------------------------------------
| 2           | 1          | tinytext   |
-----------------------------------------
| 3           | 1          | MediumText |
-----------------------------------------
| 4           | 2          | LARGEtext  |
-----------------------------------------
| 5           | 2          | HUGETEXT   |
-----------------------------------------
| 6           | 1          | BLOOOOOB   |
-----------------------------------------
| 7           | 3          | 001010101  |
-----------------------------------------

This is the result of the current design. What i am wondering is that a way to make auto increment for each user separately.

Something like "Autoincrement item id GROUP BY user id"

-----------------------------------------
|  **ITEMID** | **USERID** | ITEMNAME   |
-----------------------------------------
| 1           | 1          | fooooooo   |
-----------------------------------------
| 2           | 1          | tinytext   |
-----------------------------------------
| 3           | 1          | MediumText |
-----------------------------------------
|   1         | 2          | LARGEtext  |
-----------------------------------------
|   2         | 2          | HUGETEXT   |
-----------------------------------------
| 4           | 1          | BLOOOOOB   |
-----------------------------------------
|      1      | 3          | 001010101  |
-----------------------------------------

Is there a way to do this using mysql?

Answer 1

The safer way to do this is taking into account that your application can get hit by more than 1 user at any given time

START TRANSACTION;
 Insert into table A
 Select Last inserted id from table A using Last_Insert_ID()
 Update table B
COMMIT;

At least you are guaranteed to get this last inserted id from table A into table B.

Answer 2

I dont know what happens when multiple users execute queries but i think i managed to narrow down the algorithm.

how to "insert into table (col1, col2) values (select max(id) from table2, select id from table3); "?

INSERT INTO table VALUES ( 
(SELECT (MAX(itemid)+1)
FROM table
WHERE userid = 'theid') , 'theid' , 'foo1');

Can this solve the simultaneous execution by multiple user problem.

Answer 3

You want something like this:

Demo

CREATE TRIGGER item_id_auto_inc
BEFORE INSERT ON items
FOR EACH ROW 

BEGIN
    SET NEW.item_id := (SELECT CASE WHEN ISNULL(MAX(item_id)) THEN 0 ELSE MAX(item_id) END +1 FROM items WHERE user_id=NEW.user_id);
END
//