auto_increment by more than 1

auto_increment by more than 1

am 23.02.2011 18:41:06 von Jim McNeely

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=3Dgcdmg-mysql-2@m.gmane.o rg

Re: auto_increment by more than 1

am 23.02.2011 19:26:54 von Carsten Pedersen

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=gcdmg-mysql-2@m.gmane.org

Re: auto_increment by more than 1

am 23.02.2011 21:34:13 von shawn.l.green

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-m aster.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=gcdmg-mysql-2@m.gmane.org

Re: auto_increment by more than 1

am 23.02.2011 22:29:41 von Jim McNeely

--Apple-Mail-4-214318300
Content-Transfer-Encoding: quoted-printable
Content-Type: text/plain;
charset=us-ascii

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=3D`user`@`%` TRIGGER `XXXX`.`p_number_zzk`
BEFORE INSERT ON `XXXX`.`p_number`
FOR EACH ROW
BEGIN
DECLARE maxy INT;
SET maxy =3D (SELECT ROUND(MAX(zzk),-1) from p_number);
IF ! NEW.zzk THEN
SET NEW.zzk =3D (maxy + 10);
END IF;
SET NEW.IdPatient =3D 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?
>=20
>=20
>=20
> On Wed, Feb 23, 2011 at 15:46, Singer X.J. Wang =
wrote:
> Right.. and that's not his question..
>=20
>=20
>=20
> On Wed, Feb 23, 2011 at 15:34, Shawn Green (MySQL) =
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?
>=20
> Thanks in advance,
>=20
> Jim McNeely
>=20
>=20
> The manual is your friend. Don't be afraid of it :)
>=20
> =
http://dev.mysql.com/doc/refman/5.5/en/replication-options-m aster.html#sys=
var_auto_increment_increment
>=20
> --=20
> Shawn Green
> MySQL Principal Technical Support Engineer
> Oracle USA, Inc. - Hardware and Software, Engineered to Work Together.
> Office: Blountville, TN
>=20
>=20
> --=20
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: =
http://lists.mysql.com/mysql?unsub=3Dwang@singerwang.com
>=20
>=20
>=20
> --
> The best compliment you could give Pythian for our service is a =
referral.
>=20


--Apple-Mail-4-214318300--

Re: auto_increment by more than 1

am 23.02.2011 22:31:10 von Jim McNeely

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?
>>=20
>> Thanks in advance,
>>=20
>> Jim McNeely
>=20
> CREATE TABLE t (
> ...
> ) AUTO_INCREMENT=3D10;
>=20
>=20
> / Carsten
>=20
> --=20
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: =
http://lists.mysql.com/mysql?unsub=3Djim@newcenturydata.com
>=20


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg

Re: auto_increment by more than 1

am 23.02.2011 22:42:52 von Reindl Harald

--------------enig6655DF23B59CFBBEEA3B5353
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable



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=3Dvalue"

Command-Line Format --auto_increment_increment[=3D#]
Option-File Format auto_increment_increment
Option Sets Variable Yes, auto_increment_increment
Variable Name auto_increment_increment
Variable Scope Global, Session


--------------enig6655DF23B59CFBBEEA3B5353
Content-Type: application/pgp-signature; name="signature.asc"
Content-Description: OpenPGP digital signature
Content-Disposition: attachment; filename="signature.asc"

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.11 (GNU/Linux)
Comment: Using GnuPG with Fedora - http://enigmail.mozdev.org/

iEYEARECAAYFAk1lf1wACgkQhmBjz394AnkzwgCeMeu0AafyWd7erXUQ/1lR U/Eh
xHoAnR4Fjoc24Ak62/liLEh9LrJKEFZd
=HK6z
-----END PGP SIGNATURE-----

--------------enig6655DF23B59CFBBEEA3B5353--

Re: auto_increment by more than 1

am 23.02.2011 23:43:07 von Reindl Harald

--------------enigCB9334FA155D783A664DCA94
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: quoted-printable



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 state=
ment.. eg.
>=20
> [other stuff]
> set auto_increment_increment =3D X;
> insert into that table you want
> set auto_increment_increment =3D 1;
> [other stuff]
>=20
> Now you have to remmeber doing that everywhere.. and everytime..

this was not the question, but hopefully your app does not inline-query i=
n
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 ailto:h.reindl@thelounge.net>> wrote:
> =20
> Am 23.02.2011 22:29, schrieb Jim McNeely:
> > I have read the manual, and you're right, the auto-increment_incr=
ement is a system wide setting
>=20
> No, scope session means "set VAR=3Dvalue"
>=20
> Command-Line Format --auto_increment_increment[=3D#]
> Option-File Format auto_increment_increment
> Option Sets Variable Yes, auto_increment_increment
> Variable Name auto_increment_increment
> Variable Scope Global, Session
>=20
>=20
> --
> The best compliment you could give Pythian for our service is a referra=
l.
>=20

--=20

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/


--------------enigCB9334FA155D783A664DCA94
Content-Type: application/pgp-signature; name="signature.asc"
Content-Description: OpenPGP digital signature
Content-Disposition: attachment; filename="signature.asc"

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.11 (GNU/Linux)
Comment: Using GnuPG with Fedora - http://enigmail.mozdev.org/

iEYEARECAAYFAk1ljXsACgkQhmBjz394AnluJACfW3kVCXEJzv1T5Sx84nqZ 0lGt
HisAnRmh9RR0auiTUmVdF1fmcG+dCEWy
=aId4
-----END PGP SIGNATURE-----

--------------enigCB9334FA155D783A664DCA94--