I am not sure I understand. If I make the autoincrement column as part
of the primary key as (rev + cluster + file), how do I ensure that a
reset of the revision number is done as soon as (cluster + file)
combination changes? It looks like I need to do the following to mimic
the same behavior as that of an autoincrement column in MyISAM
SELECT @id := IFNULL(MAX(rev), 0) FROM table WHERE cluster='clusterA'
AND file='fileA' ;
SET @id := @id + 1;
INSERT INTO table (cluster, file, rev) VALUES ('clusterA', 'fileA', @id);
Additionally I guess the above needs to be encapsulated in a transaction
to ensure atomic updates to the 'rev' number for a given cluster and
file combination. Any thoughts?
Thanks
Aveek
Johan De Meersman wrote:
> You can't, iirc - if you add an autoincrement to InnoDB it MUST be the
> primary key.
>
> You *can*, however, add that, set it as PK and stick a unique index on
> (cluster, file) instead. Behaviour will be identical, but be aware
> that there will be some performance implications - you will now have
> to do an extra primary key lookup every time you select based on the
> (cluster,file) key.
>
>
> On Thu, Apr 22, 2010 at 7:03 AM, Aveek Misra <aveekm@stripped
> <mailto:aveekm@stripped>> wrote:
>
> I have a InnoDB table which contains columns named 'cluster' and
> 'file' ('cluster' + 'file' is a primary key). I want to add a new
> column that tracks the revision number of a file for a given
> cluster and a file. The situation is tailor made for a MyIsam
> table where I can add a new Auto Increment column as a secondary
> column in a multiple column index. How can I get the same behavior
> in an InnoDB table? Given below is a view of how the records will
> look like
>
> --------------------------------------------------------
> | Cluster | File | Rev |
> --------------------------------------------------------
> | clusterA | fileA | 1 |
> --------------------------------------------------------
> | clusterA | fileA | 2 |
> ---------------------------------------------------------
> | clusterB | fileA | 1 |
> --------------------------------------------------------
> | clusterB | fileB | 1 |
> --------------------------------------------------------
>
>
>
> Thanks
> Aveek
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/mysql?unsub=1
>
>
>
>
> --
> Bier met grenadyn
> Is als mosterd by den wyn
> Sy die't drinkt, is eene kwezel
> Hy die't drinkt, is ras een ezel
