使用mysql使用另一列获取唯一ID

I have two tables.

  • Action
  • Company

Action is related to company by company_id

So in the action table, action_id is the primary key and auto incremented. Based on the company_id the information will be displayed. If the user has company_id 1 assigned to him and he is logged in he can do actions associated with company_id 1. A different company will have different actions with lots of action_ids. For every company I want to create a unique action_id. How can I achieve this?

For example: There are 3 companies using my actions module

A company, B company, C company

  1. A company creates 10 actions first.
    • Now we have action_ids from 1-10 assigned in actions table.
  2. Now B company creates 5 actions.
    • Now we have action_id s from 11-15 assigned in actions table

  3. Now C company creates 5 actions.

    • Now we have action_ids from 16-20 assigned.

    • So when I display action_ids in A company it will displayed from 1- 10

    • But in B and C company they will have wrongs id's displayed.
    • As far as the companies knowledge that is the first action they have created but action_id will be displayed as 11, 12 and so on.
    • How do I solve this issue?

Expected result:

enter image description here

You can use concat(company_id , "_", action_id) as unique_id

-> mysql-concat-function

I would use INSERT...SELECT when inserting the new ids to the table, so that you can be assured you have the proper count:

INSERT INTO action (company_id, uid)
SELECT :company AS company_id, COUNT(*) + 1 AS uid
FROM action
WHERE action.company_id = :company

Of course, this means that upon deletion your ids will no longer be continuous. If you want to automatically generate unique ids then it becomes a little more tricky with pure sql. I would honestly just use something like an array index (if the number is the important part) for the naming at this point, instead of storing it manually, especially since it is calculated data.

$actions = /* your actions retrieval, assume associative array */;
$actions[0]; //uid == 1, index == 0, etc