CODEWITHSUNDEEP
mysql
Frank AK
Frank AK

Reputation: 1781

How to set a auto increment field based on other field in MySQL?

I have a login log table, its already define a id (auto increment) as primary key! but it's very difficult to catch each user data. let's see that table demo below.

+----+------+--------+
| id | uid  | money  |
+----+------+--------+
|  1 |  101 | 200.00 |
|  2 |  101 | 100.00 |
|  3 |  102 | 300.00 |
|  4 |  102 | 500.00 |
+----+------+--------+

Assume we need to the user latest 7days record, we have to filter by uid and sort it! so I want to redesign a table like below structure:

+----+---+------+-------+
| id |pid|uid  | money  |
+----+---+------+-------+
|  1 | 1 | 101 | 200.00 |
|  2 | 2 | 101 | 100.00 |
|  3 | 1 | 102 | 300.00 |
|  4 | 3 | 101 | 300.00 |
|  5 | 2 | 102 | 500.00 |
+----+---+-----+--------+

I just want to set a auto increment based on the uid. Example, if we already has store 101 on table, the pid will be incre 1 otherwise set 1. How can i reach this goal?

Upvotes: 0

Views: 555

Answers (2)

M Khalid Junaid
M Khalid Junaid

Reputation: 64486

How about to get you a view like desired results using the existing structure

select a.*,
(
  select count(*)
  from demo b 
  where a.uid = b.uid
  and a.id > b.id
) + 1 pid
from demo a
order by a.id

DEMO

Upvotes: 1

swithen colaco
swithen colaco

Reputation: 167

Have you tried using indexes, rather than this complex idea.

Try this link What is an index in SQL?

If you were to do it as your idea. You would need to query the table first to identify if the record already exist. If it does, then fetch its last pid. Then insert a record wit pid + 1. This has a huge overhead and multiple IO operations on the database server. This would end up slowing down your entire system.

Upvotes: 0

Related Questions