dslreports logo
site
 
    All Forums Hot Topics Gallery
spc

spacer




how-to block ads


Search Topic:
uniqs
1760
share rss forum feed


Nordy

join:2007-10-20
kudos:1

Mysql auto incrementing 2 columns

I'm trying to auto increment the art_id in the table below based on the art_userid, Is this possible without writing any extra php code? The gen_id has to remain and cannot be removed. I'm using Mysql and Php

CREATE TABLE `articles` (
        `gen_id` INT(10) NULL AUTO_INCREMENT,
        `art_id` TINYINT(3) NULL DEFAULT '0',
        `art_userid` INT(10) NULL DEFAULT '0',
        `art_title` VARCHAR(150) NULL DEFAULT NULL,
        PRIMARY KEY (`gen_id`)
 

So in the end, if user 1 posted an article, his art_id would be 1. The next one he posts would have the id 2.

But if user 2 posted and article, his art_id would be 1 again. The next one he posts would have the id 2. Not the continuation of the 1st users art_id.

gen_id | art_id | art_userid | art_title
-----------------------------------------
1         1        1           Title A
2         2        1           Title B
3         1        2           Title A
4         2        2           Title B
5         3        1           Title A
 

I heard using UNIQUE KEY would help, but how to use it?

Thanks


cdru
Go Colts
Premium,MVM
join:2003-05-14
Fort Wayne, IN
kudos:7
What does art_id get used for? Is it an actual id? Or are you just using it to track the number of articles a particular user has associated with them? If you are using it for ordering, just use gen_id. If you need the Nth article, all flavors of SQL support some type of windowing capability to pull out the Nth article. If you really need the art_id column, and it needs to increment with each subsequent article, then just run a query getting the columns MAX for that art_userid and add 1. It's not an auto-increment, but it mimics the functionality.


Tenar

join:2008-01-02
Midland, ON
reply to Nordy
I think you need a trigger. I'm pretty sure my sql can do triggers.

Maybe use "before insert" trigger to get the max art_id for that user and add one, then run the insert statement using the new value.

Maybe this will help
»www.java2s.com/Tutorial/MySQL/02···gger.htm

and this too
»net.tutsplus.com/tutorials/datab···riggers/


teddy

join:2002-02-20
Kingston, ON
I'm not sure about MySQL, but in Oracle you cannot query a table which the trigger is firing on without getting a mutating table error.

This sounds like it needs to be application logic.


Tenar

join:2008-01-02
Midland, ON
I've never worked with Oracle but that sounds like a threading problem and should be solvable.

Never had that problem is MS SQL and I use triggers quite frequently.


teddy

join:2002-02-20
Kingston, ON
Reviews:
·voip.ms
·Start Communicat..
·Cogeco Cable
Like I said it might be an Oracle specific thing but it is one I can completely understand.

It's not that it can't do it, it's that it won't. Mutating table means you are trying to query the data while it is changing. A trigger fires while the contents of the table are being modified.

I've also had problems executing stored functions in an insert/update statement where the stored function uses the contents of the table I am inserting into.

I use triggers extremely frequently as well, luckily this kind of error is rare. A quick Google search suggests a mutating table error might be possible in MySQL in the same way (though called something else).

In summary - I suspect it isn't possible to query a table which you are triggering on.


Tenar

join:2008-01-02
Midland, ON
hmm.. that's possible. If I were doing this in MS SQL I would use "instead of insert" however, MySQL doesn't have this, which is why I suggest "before insert"

If your trigger is firing before inserting then it shouldn't have any problem as the table hasn't yet changed.

a problem could arise if another process changes the table before the trigger has finished. If you are really worried about that, locking the table, or changing to single thread would prevent that.

Edit:
Honestly, if the art_id is supposed to be an id field, it really should be in an art table and inserted prior to inserting into any joining table, and then hold the titles in the art table. But I have no idea what the reasons are for the current structure.


teddy

join:2002-02-20
Kingston, ON
Reviews:
·voip.ms
·Start Communicat..
·Cogeco Cable

1 recommendation

I would think, logically "before insert" would not include the record you are inserting and "after insert" would include it. This is not how Oracle does it.

Instead of is an interesting idea.

I think a nice way to do this without application logic would be to wrap the whole process in a stored procedure. It could:
- lock the table
- calculate the necessary ID values
- insert the record
- release the lock
The application would then call this stored proc instead of running the INSERT SQL (of course, in an ideal world this is how it would be all the time).

This would serialize the inserts, no worries about other threads/processes/sessions changing the data mid way and accomplish what the OP is trying to do.


cowboyro
Premium
join:2000-10-11
Shelton, CT
reply to Nordy
All data manipulation should be done in stored procedures. ALL.
You also have the option of doing a transaction and rolling back if something fails.


Nordy

join:2007-10-20
kudos:1
reply to Nordy
Well, I kinda solved it, but with one less column. The engine type needs to be MyIsam for this. Then, do a create like this:

    CREATE TABLE `articles` (
    `art_id` INT(10) NOT NULL AUTO_INCREMENT,
    `art_userid` INT(10) NOT NULL DEFAULT '0',
    PRIMARY KEY (`art_userid`, `art_id`)
    )
    COLLATE='utf8_general_ci'
    ENGINE=MyISAM;
 

Then when you insert art_userid, you'll get a unique value for art_id starting from 1 for each user. It was the Engine type that was causing problems.

Thanks to all who replied :)