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
DECLARE maxy INT;
SET maxy = (SELECT ROUND(MAX(zzk),-1) from p_number);
IF ! NEW.zzk THEN
SET NEW.zzk = (maxy + 10);
SET NEW.IdPatient = CONCAT("P", NEW.zzk);
It's probably ugly, but it works. Any objections to this? The zzk and IdPatient fields
have unique validations on them.
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
> 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>
> 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 :)
> 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.