List:General Discussion« Previous MessageNext Message »
From:Jim McNeely Date:February 23 2011 9:29pm
Subject:Re: auto_increment by more than 1
View as plain text  
I have read the manual, and you're right, the auto-increment_increment is a system wide
setting. I only want this on one table. I am in this instance creating ID's for a
separate system via HL7 for a Filemaker system, and FileMaker is too lame and slow to
actually spit out an ID in time for the foreign system to function correctly within its
workflow requirements for the end users. So, I am going to offset the ID's so that MySQL
issues ID's on the 10's, and FM issues ID's on the 5's. That way, it works similar to the
way some people set up replication, but I only need it on this one table, I want the other
tables to continue to increment normally. I don't want to do this in another instance of
MySQL or another DB because I am otherwise trying to keep it simple. Here is the solution
I came up with:

CREATE DEFINER=`user`@`%` TRIGGER `XXXX`.`p_number_zzk`
BEFORE INSERT ON `XXXX`.`p_number`
FOR EACH ROW
BEGIN
DECLARE maxy INT;
SET maxy = (SELECT ROUND(MAX(zzk),-1) from p_number);
IF ! NEW.zzk THEN
SET NEW.zzk = (maxy + 10);
END IF;
SET NEW.IdPatient = CONCAT("P", NEW.zzk);
END

It's probably ugly, but it works. Any objections to this? The zzk and IdPatient fields
have unique validations on them.

Thanks,

Jim McNeely

On Feb 23, 2011, at 12:48 PM, Singer X.J. Wang wrote:

> Its theoretically possible, but its a hackish solution.. can you explain why you want
> this?
> 
> 
> 
> On Wed, Feb 23, 2011 at 15:46, Singer X.J. Wang <wang@stripped> wrote:
> Right.. and that's not his question..
> 
> 
> 
> On Wed, Feb 23, 2011 at 15:34, Shawn Green (MySQL) <shawn.l.green@stripped>
> wrote:
> On 2/23/2011 12:41, Jim McNeely wrote:
> Is there a way to set the auto-increment for a particular table to increase by some
> number more than one, like maybe 10?
> 
> Thanks in advance,
> 
> Jim McNeely
> 
> 
> The manual is your friend. Don't be afraid of it :)
> 
>
> http://dev.mysql.com/doc/refman/5.5/en/replication-options-master.html#sysvar_auto_increment_increment
> 
> -- 
> Shawn Green
> MySQL Principal Technical Support Engineer
> Oracle USA, Inc. - Hardware and Software, Engineered to Work Together.
> Office: Blountville, TN
> 
> 
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:    http://lists.mysql.com/mysql?unsub=1
> 
> 
> 
> --
> The best compliment you could give Pythian for our service is a referral.
> 


Thread
auto_increment by more than 1Jim McNeely23 Feb
  • Re: auto_increment by more than 1Carsten Pedersen23 Feb
    • Re: auto_increment by more than 1Jim McNeely23 Feb
  • Re: auto_increment by more than 1MySQL)23 Feb
Re: auto_increment by more than 1Jim McNeely23 Feb
  • Re: auto_increment by more than 1Reindl Harald23 Feb
Re: auto_increment by more than 1Reindl Harald23 Feb