Re: auto-increment sequence question
thank you all for your help, this was the explanation I needed, LAST_INSERT_ID() with an argument creates a value that can be treated just like it's an AUTO_INCREMENT value. That means you can call LAST_INSERT_ID() without an argument later in the current session to retrieve the value you generated. This also has the advantage that you don't need to set up any locks. thanks Gunnar p.s. for the spamfilter mysql, database - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
auto-increment sequence question
Hello everybody, I thing, that I have a problem that needs a sequence and that auto-increment can't do the job this time. I already consulted the manual and the mailarchive. I saw several different proposals, now I'm a bit confused and don't know exactly what the rigth way is to do it. Could somebody please clarify the following? We are using ISAM tables on mysql 3.22.32. We have an id-column but the id will be generated out of two different ranges. Example: all VIP-customers will get an id from 1000- and normal customers from 1-999. So, I think the only way to solve this is a sequence table. Lets take a single row, integer value for holding the sequence: CREATE TABLE sequence( id int4; ); My question is this: Is this the best way to get and evalute the sequence? LOCK TABLES sequence WRITE; UPDATE sequence SET id=id+1; SELECT id FROM sequence; UNLOCK TABLES; Thanks Gunnar von Boehn - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: auto-increment sequence question
At 13:49 +0200 2001-06-26, Gunnar von Boehn wrote: Lets take a single row, integer value for holding the sequence: CREATE TABLE sequence( id int4; ); That works, though I would use a single table for all sequences, not a new table per sequence. The other solution would be to do check the table itself what the maximum is: LOCK tables customer WRITE SELECT MAX(id) FORM customer WHERE id 1; ## your first normal customer INSERT INTO customer SET id=#new_id#, ... UNLOCK TABLES though MySQL's optimizer doesn't understand how to do find a ranged maximum by checking the index on the ID, so it's not a fast solution at present. = Hello MySQL developers: Please add that. Same for the equivalent ... ORDER BY id DESC LIMIT 1 -- Matthias Urlichs - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail mysql-unsubscribe-##L=##[EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: auto-increment sequence question
Hi Gunnar, Why don't you keep the key out of the business rule and create a column to identify the type of customer? How are you going to handle the application when your business reaches more than vip customers??? This is an issue that can be avoided by creating a type column . Siomara PS: This is just a suggestion of someone that once got this issue. From: Gunnar von Boehn [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] To: [EMAIL PROTECTED] Subject: auto-increment sequence question Date: Tue, 26 Jun 2001 13:49:33 +0200 Hello everybody, I thing, that I have a problem that needs a sequence and that auto-increment can't do the job this time. I already consulted the manual and the mailarchive. I saw several different proposals, now I'm a bit confused and don't know exactly what the rigth way is to do it. Could somebody please clarify the following? We are using ISAM tables on mysql 3.22.32. We have an id-column but the id will be generated out of two different ranges. Example: all VIP-customers will get an id from 1000- and normal customers from 1-999. So, I think the only way to solve this is a sequence table. Lets take a single row, integer value for holding the sequence: CREATE TABLE sequence( id int4; ); My question is this: Is this the best way to get and evalute the sequence? LOCK TABLES sequence WRITE; UPDATE sequence SET id=id+1; SELECT id FROM sequence; UNLOCK TABLES; Thanks Gunnar von Boehn - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php _ Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: auto-increment sequence question
On Tuesday 26 June 2001 04:49, Gunnar von Boehn wrote: Hello everybody, I thing, that I have a problem that needs a sequence and that auto-increment can't do the job this time. So, I think the only way to solve this is a sequence table. Lets take a single row, integer value for holding the sequence: CREATE TABLE sequence( id int4; ); My question is this: Is this the best way to get and evalute the sequence? LOCK TABLES sequence WRITE; UPDATE sequence SET id=id+1; SELECT id FROM sequence; UNLOCK TABLES; This is more work than you need. You can use this sequence: UPDATE sequence SET id=LAST_INSERT_ID(id+1) SELECT LAST_INSERT_ID() The first statement updates the sequence table atomically. It sets the id value to the id+1 and sets the last insert ID to id+1. This is done atomically. The next statement gets back the ID that was set in the first one. Notice that the SELECT does not have a FROM clause! The database handles the value of LAST_INSERT_ID on a connection by connection basis so, you'll get the right value. This set of SQL statements does not require table locks. We use it all the time. Best, Kyle - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: auto-increment sequence question
At 1:49 PM +0200 6/26/01, Gunnar von Boehn wrote: Hello everybody, I thing, that I have a problem that needs a sequence and that auto-increment can't do the job this time. I already consulted the manual and the mailarchive. I saw several different proposals, now I'm a bit confused and don't know exactly what the rigth way is to do it. Could somebody please clarify the following? We are using ISAM tables on mysql 3.22.32. I would use MyISAM, not ISAM, but the instructions below should work regardless. We have an id-column but the id will be generated out of two different ranges. Example: all VIP-customers will get an id from 1000- and normal customers from 1-999. So, I think the only way to solve this is a sequence table. Lets take a single row, integer value for holding the sequence: CREATE TABLE sequence( id int4; ); If you're going to need two sequences, you need two sequence generators. A single table with a single column won't do it. Try this, which will work for any number of sequences. First, create a table to hold your sequence numbers: # Create a table to be used for multiple sequences CREATE TABLE sequence ( nameCHAR(20) NOT NULL PRIMARY KEY, seq BIGINT UNSIGNED NOT NULL ); name identifies your sequence. It's a PRIMARY KEY for fast lookups. seq is the current sequence value for a given sequence. Next, intialize your sequence counters to one less than the first number you want to use in each sequence: INSERT INTO multiseq (name, seq) VALUES(vip,999); INSERT INTO multiseq (name, seq) VALUES(normal,); To generate a new VIP sequence number, do this: UPDATE multiseq SET seq = LAST_INSERT_ID(seq+1) WHERE name = vip; SELECT vip, LAST_INSERT_ID(); To generate a new Normal sequence number, do this: UPDATE multiseq SET seq = LAST_INSERT_ID(seq+1) WHERE name = normal; SELECT normal, LAST_INSERT_ID(); LAST_INSERT_ID() with an argument creates a value that can be treated just like it's an AUTO_INCREMENT value. That means you can call LAST_INSERT_ID() without an argument later in the current session to retrieve the value you generated. This also has the advantage that you don't need to set up any locks. The table can be extended to provide more than two sequences simply by inserting new rows. (This is one way to main hit counters for a bunch of Web pages using a single table, for example - although you'd want to use a longer name column in that case...) My question is this: Is this the best way to get and evalute the sequence? LOCK TABLES sequence WRITE; UPDATE sequence SET id=id+1; SELECT id FROM sequence; UNLOCK TABLES; Thanks Gunnar von Boehn -- Paul DuBois, [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
