When I saw this message a few weeks ago, I *knew* MySQL had something for
this, but I couldn't remember where I saw it, and I couldn't find it. Today
I found it.
Take a look at: http://dev.mysql.com/doc/mysql/en/Miscellaneous_functions.html
and scroll down to UUID()
Returns a Universal Unique
with implicit join :
INSERT into table1(id, list of other columns)
select m.newID, list of other columns from table2, tmpIDMAP
where
m.id = table2.id
UPDATE table3, tmpIDMAP
SET table3.parentID = m.newID
where
m.id = table3.parentID
Andrew Mull wrote:
Thank you. I'll have to review the SQL
Andrew Mull wrote:
I'm working on a rather large database with many cross-linked tables
currently using auto increment IDs. The system is primarily a web based
system, however, there will be times that the system will be run as a stand
alone server...meaning no internet connection is
You are correct in that I need to merge two tables. However, I'm not clear on
how to update the FKs in the sub tables.
Example, the main table (Person) has a sub table (Address). Person has a 1:N
relationship with Address. So Address has the Person's ID as a FK. How do I
update the FK in
Create a temporary table that will act as a map between your old IDs and
your new ones. That way you can re-use those values and change them from
the old values to the new values.
LOCK TABLE table1 WRITE, table2 WRITE, table3 WRITE;
select @max_id:=max(id) FROM table1;
CREATE TEMPORARY TABLE
Thank you. I'll have to review the SQL carefully as I haven't used inner
joins in quite some time :)
Quoting [EMAIL PROTECTED]:
Create a temporary table that will act as a map between your old IDs and
your new ones. That way you can re-use those values and change them from
the old
On 12 Feb 2004 at 22:57, Bill Easton wrote:
You can use + 1 instead of + interval 1 second, but it may give
different results some day if MySQL changes the precision of
timestamp.
Actually, that won't work in all cases. If the current timestamp is
20040213114859, then adding 1 (rather than
You definitely don't want to use timestamping for unique
IDs. You want to use an auto-incrementing column or similar.
See
http://www.mysql.com/doc/en/example-AUTO_INCREMENT.html
Eamon Daly
NextWave Media Group LLC
Tel: 1 773 975-1115
Craig Jackson [EMAIL PROTECTED] wrote:
I have a very large web app that uses timestamp for unique IDs.
Everything was rolling fine until we started getting many users per
second, causing some of the unique IDs to not be unique -- users were
being assigned the same timestamp. Since the web app
You have a design flaw in your database, using timestamp as a unique
ID. There really is no work around. You can't reliably keep using
timestamps for unique IDs. As a rule, a unique ID should not be
dependent on anything else and should not represent anything else aside
from a unique id. For
On Thu, 2004-02-12 at 11:47, Keith C. Ivey wrote:
Craig Jackson [EMAIL PROTECTED] wrote:
I have a very large web app that uses timestamp for unique IDs.
Everything was rolling fine until we started getting many users per
second, causing some of the unique IDs to not be unique -- users
MySQL),
sleep for a second, then try the insert again. I doubt there's a good way
to hang up the database on the issue.
Cheers;
-M
From: Craig Jackson [EMAIL PROTECTED]
Reply-To: [EMAIL PROTECTED]
To: Keith C. Ivey [EMAIL PROTECTED]
CC: [EMAIL PROTECTED]
Subject: Re: Unique IDs
Date: Thu, 12 Feb
, February 12, 2004 9:57 AM
To: Keith C. Ivey
Cc: [EMAIL PROTECTED]
Subject:Re: Unique IDs
On Thu, 2004-02-12 at 11:47, Keith C. Ivey wrote:
Craig Jackson [EMAIL PROTECTED] wrote:
I have a very large web app that uses timestamp for unique IDs.
Everything was rolling fine until
On 12 Feb 2004 at 11:57, Craig Jackson wrote:
Thanks for the speedy reply and I have already recommended
auto_increment for the solution. We do need that quick fix until the
problem is fixed. How would I go about making Mysql wait one second
between inserts. We only get about 1000 hits per
as everyone has pointed out, using timestamps as a unique id was a
design flaw. you should fix the problem using an auto-increment field.
that said, can you change the column type you are currently using as a
timestamp to be an auto-increment int field? the return type in both
cases is
Original Message
From: Jeff Mathis [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Cc: Keith C. Ivey [EMAIL PROTECTED], [EMAIL PROTECTED]
Date: Thu, Feb-12-2004 11:13 AM
Subject: Re: Unique IDs
as everyone has pointed out, using timestamps as a unique id was a
design flaw. you should fix
, but it may give
different results some day if MySQL changes the precision of timestamp.
HTH
Bill Easton
Subject: Re: Unique IDs
From: Craig Jackson [EMAIL PROTECTED]
To: Keith C. Ivey [EMAIL PROTECTED]
Cc: [EMAIL PROTECTED]
Date: Thu, 12 Feb 2004 11:57:24 -0600
On Thu, 2004-02-12 at 11:47
Hi Craig,
I have a very large web app that uses timestamp for unique IDs.
Everything was rolling fine until we started getting many users per
second, causing some of the unique IDs to not be unique -- users were
being assigned the same timestamp. Since the web app is so large we
don't want
18 matches
Mail list logo
