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?

You have 2 tables that have auto increment primary keys and have 1 to many relationships. I don't see what the problem is. — Mukus, Mar 16 '14 at 22:58
Sorry i cleared the issue and updated the question. What im trying to do is autoincrementing itemid by userid occurrences. — user2102266, Mar 16 '14 at 22:59
`CREATE TRIGGER ai_seperatly BEFORE INSERT ON table FOR EACH ROW BEGIN IF user_id = 'theid' SET %maxid = %maxid + 1; END; INSERT INTO table VALUES ('theid',%maxid,'foo') ` I managed to write this but trigger needs the user id that will be inserted. Is it possible to insert values to triggers ?? @CodeBird — user2102266, Mar 16 '14 at 23:12

score 2 · Accepted Answer · answered Mar 16 '14 at 23:15

2

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
//

answered Mar 16 '14 at 23:15

CodeBird

3,883
2
20
35

This is exactly what i wanted. dml seems more capable then i thought. Thanks a lot. – user2102266 Mar 16 '14 at 23:23
side note: that's DDL, trigger creation is DDL. @user2102266 – CodeBird Mar 16 '14 at 23:27
What happens if multiple users try to access it concurrently? The max(item_id) may be an issue. – Mukus Mar 17 '14 at 00:36
What do you mean issue caused by multiple users? I posted a answer. Is it safe in your persfective? @Mukus – user2102266 Mar 17 '14 at 10:06
1

that won't happen as each insert will wait for the other. He's thinking if many inserts run simultaneously this would cause the trigger to fail to get the max(id) – CodeBird Mar 17 '14 at 10:19
@CodeBird not fail to get the max(id), but get incorrect max(id). What guarantees the wait? – Mukus Mar 17 '14 at 22:20
@user2102266 Have a look at MySQL documentation on LAST_INSERT_ID(). This is still not a safe way to do things but MAX_ID() is not safe at all if you have two inserts statements firing at the same time. At CodeBird please look at comments of the accepted answer on http://stackoverflow.com/questions/3837990/last-insert-id-mysql. That's how mysql works? – Mukus Mar 18 '14 at 00:12
@Mukus you are mixing up things. `last_insert_id` depends on mysql open link, so it will get you the last_insert_id that was inserted using your current link. `max` function gets you the max id of the table regardless of the link. That's what they're saying in the post you linked, that actually proves my point, what they're saying is that if an insert arrives to mysql before OP's second insert or before he selects max(id) his max(id) value will change which will be wrong for him but is good in this question because we want the max(itemid) of the table in all cases. – CodeBird Mar 18 '14 at 08:10

score 0 · Answer 2 · edited May 23 '17 at 12:05

0

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.

edited May 23 '17 at 12:05

Community

1
1

answered Mar 17 '14 at 10:03

user2102266

539
3
14

that's the same thing, additionally if you don't test your MAX(itemid) if there is no itemid at all, this will return null, and null + 1 stays null, so all your table itemid will be full of null – CodeBird Mar 17 '14 at 22:29

score 0 · Answer 3 · answered Mar 18 '14 at 00:21

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.

Mysql->autoincrementing related to an other field

3 Answers3