auto_increment by more than 1
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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: auto_increment by more than 1
Den 23-02-2011 18:41, Jim McNeely skrev: 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 CREATE TABLE t ( ... ) AUTO_INCREMENT=10; / Carsten -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: auto_increment by more than 1
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=arch...@jab.org
Re: auto_increment by more than 1
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 ``.`p_number_zzk` BEFORE INSERT ON ``.`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.
Re: auto_increment by more than 1
This doesn't work, it just sets the starting number, but it will still increment by one unless you set the auto_increment_increment system variable, but this affects all the tables in the DB and not just the particular table. Thanks, Jim McNeely On Feb 23, 2011, at 10:26 AM, Carsten Pedersen wrote: Den 23-02-2011 18:41, Jim McNeely skrev: 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 CREATE TABLE t ( ... ) AUTO_INCREMENT=10; / Carsten -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=j...@newcenturydata.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: auto_increment by more than 1
Am 23.02.2011 22:29, schrieb Jim McNeely: I have read the manual, and you're right, the auto-increment_increment is a system wide setting No, scope session means set VAR=value Command-Line Format --auto_increment_increment[=#] Option-File Format auto_increment_increment Option Sets VariableYes, auto_increment_increment Variable Name auto_increment_increment Variable Scope Global, Session signature.asc Description: OpenPGP digital signature
Re: auto_increment by more than 1
Am 23.02.2011 22:55, schrieb Singer X.J. Wang: Yes, you can set it up so that it increases it by X only for that statement.. eg. [other stuff] set auto_increment_increment = X; insert into that table you want set auto_increment_increment = 1; [other stuff] Now you have to remmeber doing that everywhere.. and everytime.. this was not the question, but hopefully your app does not inline-query in every second line, if so you should think about the apllication design it is generally senseless to change this value if you are not using master/master-replications where you do this globally On Wed, Feb 23, 2011 at 16:42, Reindl Harald h.rei...@thelounge.net mailto:h.rei...@thelounge.net wrote: Am 23.02.2011 22:29, schrieb Jim McNeely: I have read the manual, and you're right, the auto-increment_increment is a system wide setting No, scope session means set VAR=value Command-Line Format --auto_increment_increment[=#] Option-File Format auto_increment_increment Option Sets VariableYes, auto_increment_increment Variable Name auto_increment_increment Variable Scope Global, Session -- The best compliment you could give Pythian for our service is a referral. -- Mit besten Grüßen, Reindl Harald the lounge interactive design GmbH A-1060 Vienna, Hofmühlgasse 17 CTO / software-development / cms-solutions p: +43 (1) 595 3999 33, m: +43 (676) 40 221 40 icq: 154546673, http://www.thelounge.net/ signature.asc Description: OpenPGP digital signature
