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 <w...@singerwang.com> wrote:
> Right.. and that's not his question..
>
>
>
> On Wed, Feb 23, 2011 at 15:34, Shawn Green (MySQL) <shawn.l.gr...@oracle.com>
> 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=w...@singerwang.com
>
>
>
> --
> The best compliment you could give Pythian for our service is a referral.
>
