Creating a "secondary ID" in mysql

I have a table that stores "records" and already has primary key.

Table A
=======
id (INT) PK auto_increments
project_id (INT)
record_text (TEXT)

This table stores "records" for all of my projects. It is de开发者_JAVA百科sirable to have a secondary key that auto increments for the projects.

Example: If project 1 had id's of (1,5,8,9). It would be desirable to store them and present them to the user as (1,2,3,4).

I do not have the ability to use TRANSACTIONS and I'm having trouble thinking of a way of doing this without causing concurrency issues.

Hopefully I got my problem across clearly. Thank you in advance for the help.

Edit (Example): Assuming the structure:

id, project_id, record text

1 1 testing

2 1 testing

3 2 testing

4 1 testing

My ID's for project 1 would 1,2,4. But I'm looking for a way to store and display them for project 1 as 1,2,3

I can't tell from your question, but if you just want to present a cleanly numbered list to the user; I would handle this in the UI; and not even worry about storing it in the database.

If you really want to store it in the DB, I'd look into using a trigger that fires on INSERTED, and sets your value there.

You can generate row numbers using MySQL variables:

select
    id
,   project_id
,   if (@last_id = project_id, @n, @n := @n + 1) as RunningProjId
,   record_text
,   @last_id := project_id
from (select @n := 0, @last_id := -1) r, A
order by project_id

The row with the if increases the @n variable if the project differs from the last row. This relies on the order by clause to function. The row with ``@last_id := project_idsaves theproject_id` for reference when the next row is selected.

The first part of the from clause is the variable initialization. The second part is your table called A.

You can create your project_id field to auto_increment as well without making it a key.

I'm not sure what I was thinking, too late in the day probably, but I was able to achieve this by adding a column to the table:

id, pid, project_id, record_text

pid being the numbering system for the project.

The INSERT:

INSERT INTO A (id, pid, project_id, record_text) 
VALUES (
    NULL, 
    (SELECT COALESCE(new_id, 0) + 1 FROM (SELECT MAX(new_id) AS new_id FROM atest WHERE project_id = 1) AS x),
    1,
    'some text'
);

Now as my project changes my pid, id based off the project, auto-increments too:

id    pid    project_id    record_text
1     1      1             testing 123
2     2      1             testing 123
3     1      2             testing 123
4     3      1             testing 123

Thank you all for your input and I apologize for my poor description of the problem.