sequences and auto_increment
Hi all,
I'm new to MySQL coming from PostgreSQL backgroud. I'd like to know how to
obtain the same effect of a sequence + concat as default value of a table in
mysql. For example, consider the following table definition:
CREATE TABLE competenza
(
id_competenza character varying(30) NOT NULL DEFAULT ('comp-06-'::text ||
(nextval('sequenza_competenza'::regclass))::text),
descrizione character varying(100),
CONSTRAINT competenza_pkey PRIMARY KEY (id_competenza)
)
there, id_competenza is compound by a string comp-06 and the next value of a
sequence (similar to auto_increment). In MySQL there're no sequences, or
better, there's only an auto_increment action on an int field. How can I
obtain the same effect of the concatenation of a sequence and a string?
Thanks,
Luca
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: sequences and auto_increment
Hi,
I'm new to MySQL coming from PostgreSQL backgroud. I'd like to know how to
obtain the same effect of a sequence + concat as default value of a table
in
mysql. For example, consider the following table definition:
CREATE TABLE competenza
(
id_competenza character varying(30) NOT NULL DEFAULT ('comp-06-'::text
||
(nextval('sequenza_competenza'::regclass))::text),
descrizione character varying(100),
CONSTRAINT competenza_pkey PRIMARY KEY (id_competenza)
)
there, id_competenza is compound by a string comp-06 and the next value
of a
sequence (similar to auto_increment). In MySQL there're no sequences, or
better, there's only an auto_increment action on an int field. How can I
obtain the same effect of the concatenation of a sequence and a string?
Hmm, well, I think: not at all.
Unless you implement something like sequences yourself.
MySQL doesn't allow functions to be used in the DEFAULT clause either.
Martijn Tonies
Database Workbench - development tool for MySQL, and more!
Upscene Productions
http://www.upscene.com
My thoughts:
http://blog.upscene.com/martijn/
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: sequences and auto_increment
I don't think MySQL has exactly what you are looking for, but you may be able
to get the behavior you want.
The auto_increment value is actually based on an index and doesn't have to be unique. So you could create a compound index that has
one or more fields plus the auto_increment field. The effect would be having multiple sequence numbers.
CREATE TABLE competenza (
competenza varchar(30) NOT NULL default 'comp-06-',
id_competenza int unsigned not null auto_increment,
descrizione varchar(100),
PRIMARY KEY (competenza, id_competenza)
)
Since your PRIMARY KEY is a combination of 2 fields (competenza + id_competenza ), each competenza value will have it's own auto
increment (id_competenza ) sequence. So id_competenza won't be unique, but the combination of competenza + id_competenza will be.
- Original Message -
From: Luca Ferrari [EMAIL PROTECTED]
To: mysql@lists.mysql.com
Sent: Tuesday, January 02, 2007 8:54 AM
Subject: sequences and auto_increment
Hi all,
I'm new to MySQL coming from PostgreSQL backgroud. I'd like to know how to
obtain the same effect of a sequence + concat as default value of a table in
mysql. For example, consider the following table definition:
CREATE TABLE competenza
(
id_competenza character varying(30) NOT NULL DEFAULT ('comp-06-'::text ||
(nextval('sequenza_competenza'::regclass))::text),
descrizione character varying(100),
CONSTRAINT competenza_pkey PRIMARY KEY (id_competenza)
)
there, id_competenza is compound by a string comp-06 and the next value of a
sequence (similar to auto_increment). In MySQL there're no sequences, or
better, there's only an auto_increment action on an int field. How can I
obtain the same effect of the concatenation of a sequence and a string?
Thanks,
Luca
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: sequences and auto_increment
On Tuesday 02 January 2007 16:51 Brent Baisley's cat, walking on the keyboard, wrote: CREATE TABLE competenza ( competenza varchar(30) NOT NULL default 'comp-06-', id_competenza int unsigned not null auto_increment, descrizione varchar(100), PRIMARY KEY (competenza, id_competenza) ) Since your PRIMARY KEY is a combination of 2 fields (competenza + id_competenza ), each competenza value will have it's own auto increment (id_competenza ) sequence. So id_competenza won't be unique, but the combination of competenza + id_competenza will be. Thanks for your suggestion, but this would make my queries more complex, since to get the id of a skill (italian is competenza) will require querying two fields. It's true that I can simulate sequences with a table with auto_increment, but this means I need to insert a record before in such table to get the new id and then insert in my competenza table. This would require a transaction and will result even more complex than the above, I guess. Any other idea? Thanks, Luca -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: sequences and auto_increment
Hi, CREATE TABLE competenza ( competenza varchar(30) NOT NULL default 'comp-06-', id_competenza int unsigned not null auto_increment, descrizione varchar(100), PRIMARY KEY (competenza, id_competenza) ) Since your PRIMARY KEY is a combination of 2 fields (competenza + id_competenza ), each competenza value will have it's own auto increment (id_competenza ) sequence. So id_competenza won't be unique, but the combination of competenza + id_competenza will be. Thanks for your suggestion, but this would make my queries more complex, since to get the id of a skill (italian is competenza) will require querying two fields. It's true that I can simulate sequences with a table with auto_increment, but this means I need to insert a record before in such table to get the new id and then insert in my competenza table. This would require a transaction and will result even more complex than the above, I guess. Any other idea? Well, transactions are never a bad idea :-) What I would suggest to simulate sequences, is a Stored Function that returns the new value. Then use that function to retrieve the new value beforehand and use that when creating a new row in your table. Martijn Tonies Database Workbench - tool for InterBase, Firebird, MySQL, NexusDB, Oracle MS SQL Server Upscene Productions http://www.upscene.com My thoughts: http://blog.upscene.com/martijn/ Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: sequences and auto_increment
I had a similar problem a while ago and I got round it by using a trigger - called a function on an insert to read a value from a table (use for update when selecting) update with incremented value then use the return value from the ffunction to set the new value - you can do all your login or your primary key in it. Not at a machine where I have the code handy and not sure if mysql has implemented an autonomous pragma for this kind of thing yet but select for update should deal with things as long as you keep the function tight Ken Luca Ferrari wrote: On Tuesday 02 January 2007 16:51 Brent Baisley's cat, walking on the keyboard, wrote: CREATE TABLE competenza ( competenza varchar(30) NOT NULL default 'comp-06-', id_competenza int unsigned not null auto_increment, descrizione varchar(100), PRIMARY KEY (competenza, id_competenza) ) Since your PRIMARY KEY is a combination of 2 fields (competenza + id_competenza ), each competenza value will have it's own auto increment (id_competenza ) sequence. So id_competenza won't be unique, but the combination of competenza + id_competenza will be. Thanks for your suggestion, but this would make my queries more complex, since to get the id of a skill (italian is competenza) will require querying two fields. It's true that I can simulate sequences with a table with auto_increment, but this means I need to insert a record before in such table to get the new id and then insert in my competenza table. This would require a transaction and will result even more complex than the above, I guess. Any other idea? Thanks, Luca -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
4 byte utf8 sequences
Any ideas on whether and when MySQL is likely to support 4 byte utf8 sequences? Chris Key
sequences
Newbie question. Does mysql have sequences? If not what is the functional equivalent?
Re: sequences
Hello Chad, Does mysql have sequences? No, it does not. If not what is the functional equivalent? auto-increment would be the closest. Martijn Tonies Database Workbench - tool for InterBase, Firebird, MySQL, Oracle MS SQL Server Upscene Productions http://www.upscene.com My thoughts: http://blog.upscene.com/martijn/ Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Counting string sequences in row(s)
Hi! I am struggling with trying to count how many string patterns of a given kind occur in any given row of a mysql table. My table looks like this: Pattern varchar(200) HowMany varchar(3) The patterns that appear in the Pattern field can be aphabetic chars only, but spaces can appear at the beginning or ends of lines, and anywhere in between. Sometimes spaces seperate sequences, but not always. I can't look for 3 Ws together because sometimes a sequence is 3 of them, but sometimes it is 23 of them of 5 of them, etc. Any given sequence will never exceed 200 chars, and each char actually represents a month that the person was in a program. A line is an individual's program history. . Here is an example: W WW WW HHHWWWHH = this is 7 different sequences So WW is two sequences, as is WW I'd like to use a select statement to count the number of sequences of chars that occur on each and every line in the table, (and store that number to a variable). It is like looking for words, except that the sequences are not always separated by spaces. I can easily use REGEXP to count the rows in which a particular pattern occurs, but I can't seem to figure out how to count the number of sequences in each and every row. Can anyone help? Thanks heaps!! -Warren -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: counting sequences
[EMAIL PROTECTED] wrote: Hi! I need to use SQL to count some sequences. We have taken a short snapshot of 1 year for people registered in programs. So we have data in a table like so: ID m1 m2 m3 m4 m5 m6 m7m8 m9 m10 m11 m12 The m1, m2, m3 refers to month 1, month2, month3, etc. The data for 1 person might look like so: 0023 1 1 1 0 0 2 2 1 1 0 1 0and there is 1 row for each person. The 1 means that they were in a program called SW and the 2 means Other and the 0 means Not in any program. My problem is to count durations for each person, by program, and get average stays in program 1 and in program 2. This is unclear. Given the example row you provide, is the answer for person 0023 a stay of 6 months in program SW (1), 2 months in other programs (2), and 4 months in no program (0), or is it 3 stays in program SW (1) with average length of 2 months ([3 + 2 + 1]/3) and 1 stay in other programs (2) with average length 2 months? Do you then want an average stay in SW and an average stay in other programs, taken over all people? Is there a nice way to do this with SQL? Yes, I think it can be done, but nice? -- eye of the beholder, I suppose. Something like SELECT AVG( IF(m1 = 1, 1, 0) + IF(m2 = 1, 1, 0) + IF(m3 = 1, 1, 0) + IF(m4 = 1, 1, 0) + IF(m5 = 1, 1, 0) + IF(m6 = 1, 1, 0) + IF(m7 = 1, 1, 0) + IF(m8 = 1, 1, 0) + IF(m9 = 1, 1, 0) + IF(m10 = 1, 1, 0) + IF(m11 = 1, 1, 0) + IF(m12 = 1, 1, 0) ) AS SW, AVG( IF(m1 = 2, 1, 0) + IF(m2 = 2, 1, 0) + IF(m3 = 2, 1, 0) + IF(m4 = 2, 1, 0) + IF(m5 = 2, 1, 0) + IF(m6 = 2, 1, 0) + IF(m7 = 2, 1, 0) + IF(m8 = 2, 1, 0) + IF(m9 = 2, 1, 0) + IF(m10 = 2, 1, 0) + IF(m11 = 2, 1, 0) + IF(m12 = 2, 1, 0) ) AS Other, AVG( IF(m1 = 0, 1, 0) + IF(m2 = 0, 1, 0) + IF(m3 = 0, 1, 0) + IF(m4 = 0, 1, 0) + IF(m5 = 0, 1, 0) + IF(m6 = 0, 1, 0) + IF(m7 = 0, 1, 0) + IF(m8 = 0, 1, 0) + IF(m9 = 0, 1, 0) + IF(m10 = 0, 1, 0) + IF(m11 = 0, 1, 0) + IF(m12 = 0, 1, 0) ) AS 'No program' FROM program_months; should get you started. The IFs check each month column for a program match and add up the matches. Thus, we get the number of months in a program for each person (row). Then we take an average of those numbers. This average includes people who were never in the program. Did you want to exclude them? I'd guess from your earlier message that you have more programs to track than 'SW' and 'other', and the numbers in the month columns are actually the ids of the corresponding rows in the programs table. If so, you could change the above to something like SELECT p.id, p.name, AVG( IF(pm.m1 = p.id, 1, 0) + IF(pm.m2 = p.id, 1, 0) + IF(pm.m3 = p.id, 1, 0) + IF(pm.m4 = p.id, 1, 0) + IF(pm.m5 = p.id, 1, 0) + IF(pm.m6 = p.id, 1, 0) + IF(pm.m7 = p.id, 1, 0) + IF(pm.m8 = p.id, 1, 0) + IF(pm.m9 = p.id, 1, 0) + IF(pm.m10 = p.id, 1, 0) + IF(pm.m11 = p.id, 1, 0) + IF(pm.m12 = p.id, 1, 0) ) AS 'Average months' FROM programs p, program_months pm; Notice there's no join condition, so this is a Cartesian product. That's intentional. For each program id, we need to look at each and every row of the program_months table for matching ids in the 12 month columns. As that means processing N*M rows (where N is the number of rows in table programs and M is the number of rows in table program_months), this could be huge. Maybe too huge. If so, you could limit the processing to a particular program or programs by adding a WHERE clause restricting the programs to consider. Something like WHERE p.name = 'SW' Thanks heaps! -Alex By the way, I could easily be wrong, but your description gives the impression that you've put the cart before the horse. That is, you've designed (and filled) the table first, then turned to the problem of how to use it to answer your questions. If at all possible, you should list the questions first, then design the tables to facilitate answering those questions. Of course, if the questions are new or the tables inherited... Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
counting sequences
Hi! I need to use SQL to count some sequences. We have taken a short snapshot of 1 year for people registered in programs. So we have data in a table like so: ID m1 m2 m3 m4 m5 m6 m7m8 m9 m10 m11 m12 The m1, m2, m3 refers to month 1, month2, month3, etc. The data for 1 person might look like so: 0023 1 1 1 0 0 2 2 1 1 0 1 0and there is 1 row for each person. The 1 means that they were in a program called SW and the 2 means Other and the 0 means Not in any program. My problem is to count durations for each person, by program, and get average stays in program 1 and in program 2. Is there a nice way to do this with SQL? Thanks heaps! -Alex
Sequences and Synomyms
Hi, I am new to mysql. I want to create sequences in mysql 4.0 which should be equivalent to oracle sequences. I gone through the mysql manual, Auto_Increment(), C API mysql_insert_id() and LAST_INSERT_ID() are there for sequences. Last_insert_id() gives the last value. I want actual migration to mysql for the following oracle statement create sequence msdba.ms_sequence maxvalue 1 cycle order; Could you please suggest how to do it. And also I want to create synonym in mysql for the following oracle statement create synonym msuser.ms_sequence for msdba.msuser_sequence; Please help me in this. Thanks, Narasimha Confidentiality Notice The information contained in this electronic message and any attachments to this message are intended for the exclusive use of the addressee(s) and may contain confidential or privileged information. If you are not the intended recipient, please notify the sender at Wipro or [EMAIL PROTECTED] immediately and destroy all copies of this message and any attachments. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Sequences and Synomyms
Hello, I am new to mysql. I want to create sequences in mysql 4.0 which should be equivalent to oracle sequences. I gone through the mysql manual, Auto_Increment(), C API mysql_insert_id() and LAST_INSERT_ID() are there for sequences. Last_insert_id() gives the last value. I want actual migration to mysql for the following oracle statement create sequence msdba.ms_sequence maxvalue 1 cycle order; Could you please suggest how to do it. MySQL doesn't support sequences. It supports auto-inc fields only. And also I want to create synonym in mysql for the following oracle statement create synonym msuser.ms_sequence for msdba.msuser_sequence; Not supported in MySQL. When using MyISAM, you can probably create a symbolic link of some sorts (in the file system, not MySQL itself), but nothing like a real synonym. With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird, MySQL MS SQL Server. Upscene Productions http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Sequences and Synomyms
Hi, I am new to mysql. I want to create sequences in mysql 4.0 which should be equivalent to oracle sequences. I gone through the mysql manual, Auto_Increment(), C API mysql_insert_id() and LAST_INSERT_ID() are there for sequences. Last_insert_id() gives the last value. I want actual migration to mysql for the following oracle statements create sequence msdba.msuser_sequence maxvalue 1 cycle order; msuser_sequence.currval select msuser_sequence.nextval from dual; grant all on msuser_sequence to msuser; Could you please suggest how to do it. And also I want to create synonym in mysql for the following oracle statement create synonym msuser.ms_sequence for msdba.msuser_sequence; Please help me in this. Thanks, Narasimha Confidentiality Notice The information contained in this electronic message and any attachments to this message are intended for the exclusive use of the addressee(s) and may contain confidential or privileged information. If you are not the intended recipient, please notify the sender at Wipro or [EMAIL PROTECTED] immediately and destroy all copies of this message and any attachments. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL implementation of Oracle sequences
Hello - I was wondering if others have had to deal with an Oracle to MySQL migration and how you handled the implementation equivalent of Oracle sequences in MySQL. Our application uses a bunch of Oracle sequences to keep ID uniqueness for each sequence type. For example, we have: UserIDSequence NodeIDSequence etc. When we create new records, we first ask the Oracle sequences for all new IDs. Then we generate a bunch of insert statements and ultimately insert the new records. We've thought of 3 possible solutions from easiest to difficult based on our source semantics and amount of work. I would appreciate any helpful insights that others might have. We are using MySQL 4.1.5-Gamma with the InnoDB engine. 1- Create a single table with a single auto-increment column to hold a system-wide unique ID. Every time we want a unique ID, we insert in this table and get the value with the LAST_INSERT_ID() function. This approach seems to create a bottleneck at this single table. 2- Create a single table with 2 columns: sequencename, counter. Every time we want a unique ID for a particular sequence, we increment the counter and get it back immediately. This approach seems to create a bottleneck also. But I would imagine this approach is more costly than solution #1. 3- Redo our semantics by replacing our insert statements and allowing AUTO_INCREMENTed columns to keep the IDs unique. Thanks for you feedback. -ken Kenneth Lim Software Engineer Senvid, Inc. 2445 Faber Place, Suite #200 Palo Alto, CA 94303 phone: 650-354-3612 fax: 650-354-8890 email: [EMAIL PROTECTED] http://www.senvid.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL implementation of Oracle sequences
Having done one of these conversions in the past, I can say that auto-incremented columns work just fine. You insert the row, and then make a SELECT last_insert_id() call - this returns the value of the last auto-increment generated via an insert for the connection (so some other database connection won't overwrite your value). You can then propagate that value into child-records. This is much easier, and more efficient than either of the two other suggestions. David Kenneth Lim wrote: Hello - I was wondering if others have had to deal with an Oracle to MySQL migration and how you handled the implementation equivalent of Oracle sequences in MySQL. Our application uses a bunch of Oracle sequences to keep ID uniqueness for each sequence type. For example, we have: UserIDSequence NodeIDSequence etc. When we create new records, we first ask the Oracle sequences for all new IDs. Then we generate a bunch of insert statements and ultimately insert the new records. We've thought of 3 possible solutions from easiest to difficult based on our source semantics and amount of work. I would appreciate any helpful insights that others might have. We are using MySQL 4.1.5-Gamma with the InnoDB engine. 1- Create a single table with a single auto-increment column to hold a system-wide unique ID. Every time we want a unique ID, we insert in this table and get the value with the LAST_INSERT_ID() function. This approach seems to create a bottleneck at this single table. 2- Create a single table with 2 columns: sequencename, counter. Every time we want a unique ID for a particular sequence, we increment the counter and get it back immediately. This approach seems to create a bottleneck also. But I would imagine this approach is more costly than solution #1. 3- Redo our semantics by replacing our insert statements and allowing AUTO_INCREMENTed columns to keep the IDs unique. Thanks for you feedback. -ken Kenneth Lim Software Engineer Senvid, Inc. 2445 Faber Place, Suite #200 Palo Alto, CA 94303 phone: 650-354-3612 fax: 650-354-8890 email: [EMAIL PROTECTED] http://www.senvid.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
SEQUENCES
I was wondering whether it was possible to make and AUTO_INCREMENT field instead of always adding 1 and starting at zero, into a SEQUENCE type field so that it is say a 10 digit integer and numbers are created according to the SEQUENCE. thanks Graham This email has been scanned for all viruses by the MessageLabs Email Security System. For more information on a proactive email security service working around the clock, around the globe, visit http://www.messagelabs.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: SEQUENCES
[snip] I was wondering whether it was possible to make and AUTO_INCREMENT field instead of always adding 1 and starting at zero, into a SEQUENCE type field so that it is say a 10 digit integer and numbers are created according to the SEQUENCE. [/snip] I was wondering if you had tried it in a test table. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SEQUENCES
Hi, As far as I know, definitely not. However, you could use an AUTO_INCREMENT field as the independent variable for some application-level function you use to generate the values in the sequence. Best regards, Chris Graham Little wrote: I was wondering whether it was possible to make and AUTO_INCREMENT field instead of always adding 1 and starting at zero, into a SEQUENCE type field so that it is say a 10 digit integer and numbers are created according to the SEQUENCE. thanks Graham This email has been scanned for all viruses by the MessageLabs Email Security System. For more information on a proactive email security service working around the clock, around the globe, visit http://www.messagelabs.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: SEQUENCES
I looked in the documentation but could not find any mention of SEQUENCES. The AUTO_INCREMENT documentation seems to say that you can change a server variable to adjust the incremented count, but unless i can put an equation into their, i don't see how changing that would help. thanks Graham -Original Message- From: Jay Blanchard [mailto:[EMAIL PROTECTED] Sent: 15 December 2003 14:03 To: Graham Little; [EMAIL PROTECTED] Subject: RE: SEQUENCES [snip] I was wondering whether it was possible to make and AUTO_INCREMENT field instead of always adding 1 and starting at zero, into a SEQUENCE type field so that it is say a 10 digit integer and numbers are created according to the SEQUENCE. [/snip] I was wondering if you had tried it in a test table. This email has been scanned for all viruses by the MessageLabs Email Security System. For more information on a proactive email security service working around the clock, around the globe, visit http://www.messagelabs.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: SEQUENCES
Try Insert INTO `table` ( `inc_field` ) values (10) the auto inc field will then generate the next sequential numbers HTH Peter -Original Message- From: Graham Little [mailto:[EMAIL PROTECTED] Sent: 15 December 2003 14:01 To: '[EMAIL PROTECTED]' Subject: SEQUENCES I was wondering whether it was possible to make and AUTO_INCREMENT field instead of always adding 1 and starting at zero, into a SEQUENCE type field so that it is say a 10 digit integer and numbers are created according to the SEQUENCE. thanks Graham This email has been scanned for all viruses by the MessageLabs Email Security System. For more information on a proactive email security service working around the clock, around the globe, visit http://www.messagelabs.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: SEQUENCES
Hi Chris, Thanks for your help, i will find another way around it. Graham -Original Message- From: Chris Nolan [mailto:[EMAIL PROTECTED] Sent: 15 December 2003 14:13 To: Graham Little Cc: '[EMAIL PROTECTED]' Subject: Re: SEQUENCES Hi, As far as I know, definitely not. However, you could use an AUTO_INCREMENT field as the independent variable for some application-level function you use to generate the values in the sequence. Best regards, Chris Graham Little wrote: I was wondering whether it was possible to make and AUTO_INCREMENT field instead of always adding 1 and starting at zero, into a SEQUENCE type field so that it is say a 10 digit integer and numbers are created according to the SEQUENCE. thanks Graham This email has been scanned for all viruses by the MessageLabs Email Security System. For more information on a proactive email security service working around the clock, around the globe, visit http://www.messagelabs.com This email has been scanned for all viruses by the MessageLabs Email Security System. For more information on a proactive email security service working around the clock, around the globe, visit http://www.messagelabs.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: SEQUENCES
On Mon, 15 Dec 2003, Peter Lovatt wrote: Try Insert INTO `table` ( `inc_field` ) values (10) the auto inc field will then generate the next sequential numbers HTH Peter Or just use ALTER TABLE table AUTO_INCREMENT=10 That way you don't have to enter a record just to set the AUTO_INCREMENT value. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL -- Sequences -- Do they exist
Hello I am looking to create a sequence to use as a primary key for a table, I have read about them on the mysql website but can?t seem to get them working. Do they exist for version 4.1 and if so could someone give me some sample code. If not is there anyway to automatically generate primary keys for tables in MySQL. Thanks Noel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL -- Sequences -- Do they exist
Hi Noel, I am looking to create a sequence to use as a primary key for a table, I have read about them on the mysql website but can?t seem to get them working. Do they exist for version 4.1 and if so could someone give me some sample code. If not is there anyway to automatically generate primary keys for tables in MySQL. Well, not Sequence objects like with Oracle or InterBase. You can create an auto-increment column, that increases with each new row. Check http://www.mysql.com/doc/en/CREATE_TABLE.html With regards, Martijn Tonies Database Workbench - developer tool for MySQL, InterBase, Firebird MS SQL Server. Upscene Productions http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL -- Sequences -- Do they exist
[EMAIL PROTECTED] wrote: I am looking to create a sequence to use as a primary key for a table, I have read about them on the mysql website but can?t seem to get them work= ing. Do they exist for version 4.1 and if so could someone give me some sampl= e code. If not is there anyway to automatically generate primary keys for tables in MySQL. You can specify column as AUTO_INCREMENT: http://www.mysql.com/doc/en/example-AUTO_INCREMENT.html -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL -- Sequences -- Do they exist
Hello noel.
Wednesday, December 10, 2003, 5:09:30 PM, you wrote:
nkmdi If not is there anyway to automatically generate primary keys for
nkmdi tables in MySQL.
CREATE TABLE ttt (
id int not null auto_increment primary key,
name varchar(100) not null
);
Please note auto_increment token in the example above. It tells MySQL
that id is an auto-incrementing primary key for the table. So, to
insert a row you'd just issue the following query:
INSERT INTO ttt (name) VALUES ('some name here');
INSERT INTO ttt (name) VALUES ('another name here');
or
INSERT INTO ttt (id, name) VALUES (NULL, 'some name here');
INSERT INTO ttt (id, name) VALUES (NULL 'another name here');
Anyway, you'll get the following two records after that:
1, 'some name here'
2, 'another name here'
I hope that helps.
--
See you,
Andrey.
[ [EMAIL PROTECTED] | ICQ# 114087545 | 2:5090/[EMAIL PROTECTED] ]
...Perfect guest: One who makes his host feel at home.
pgp0.pgp
Description: PGP signature
How to generate data sequences simple way?
Hello, I hope someone can help me with the following problem: I have a table containing integer counters associated with particular datetime like this: CREATE TABLE `counters` ( ... `when` datetime NOT NULL default '-00-00 00:00:00', `counter` smallint(5) unsigned NOT NULL default '0', ... ); For some purposes I need to initialize frequently the table for time sequences with constant time step e.g. since 2003-11-06 8:00 to 2003-11-06 12:00 with time step 15 minutes. It means I need to do following INSERT commands: INSERT INTO couters (..., '2003-11-06 8:00', 0, ...); INSERT INTO couters (..., '2003-11-06 8:15', 0, ...); INSERT INTO couters (..., '2003-11-06 8:30', 0, ...); ... INSERT INTO couters (..., '2003-11-06 12:00', 0, ...); Of course my C code is able to generate such command strings in one simple loop and send them to the MySQL server. But the code should be as fast as possible and I prefer to do this task by one INSERT-SELECT command like this: INSERT INTO counters SELECT ... Unfortunately I do not have any idea how to build such SELECT command that will generate the datetime sequence for me. Does anybody know how to do it? I would appreciate any hints very much. Thanks, Petr -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to generate data sequences simple way?
Hi Petr, I hope someone can help me with the following problem: I have a table containing integer counters associated with particular datetime like this: CREATE TABLE `counters` ( ... `when` datetime NOT NULL default '-00-00 00:00:00', `counter` smallint(5) unsigned NOT NULL default '0', ... ); For some purposes I need to initialize frequently the table for time sequences with constant time step e.g. since 2003-11-06 8:00 to 2003-11-06 12:00 with time step 15 minutes. It means I need to do following INSERT commands: INSERT INTO couters (..., '2003-11-06 8:00', 0, ...); INSERT INTO couters (..., '2003-11-06 8:15', 0, ...); INSERT INTO couters (..., '2003-11-06 8:30', 0, ...); ... INSERT INTO couters (..., '2003-11-06 12:00', 0, ...); Of course my C code is able to generate such command strings in one simple loop and send them to the MySQL server. But the code should be as fast as possible and I prefer to do this task by one INSERT-SELECT command like this: INSERT INTO counters SELECT ... As fast as possible? How many rows do you need to create then? Is this a process that needs to be done several times? 'Cause I can do this kind of stuff very easily with my Test Data Generator tool that comes with Database Workbench. With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird MS SQL Server. Upscene Productions http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Disabling special character sequences
Is there any means of running MySQL (3.23, mostly MyISAM tables running on Win2k Server) so that escaped character sequences (those preceded by a backslash \) are _not_ interpretted as anything other than literals? We're trying to port a large number of web applications that previously used MS SQL and Access to use MySQL Server and this has been a big headache so far - even just in migrating the data from one to the other. Thanks, Jim -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Finding holes in autoinc sequences
Richard, Say, that's really good! I'm still not used to taking advantage of those variables. Clever. Also, I think you can remove all but the rows showing gaps by adding a group by and having clause... select id,@b:=IF(id=@a+1,id,concat(@a+1,..,id-1)) range,@a:=id, @flag:=IF(@b=id,id,MISSING) from seq group by id having id range; -Original Message- From: Richard Clarke [mailto:[EMAIL PROTECTED]] Sent: Thursday, May 30, 2002 7:48 PM To: [EMAIL PROTECTED] Subject: Re: Finding holes in autoinc sequences Or another approach could be (using same example tables) mysql set @a:=0; set @b:=0; set @c:=0; mysql select id,@b:=IF(id=@a+1,id,concat(@a+1,..,id-1)),@a:=id,@flag:=IF( @b=id,id,MISS ING) from seq; ++---++--- -- --+ | id | @b:=IF(id=@a+1,id,concat(@a+1,..,id-1)) | @a:=id | @flag:=IF(@b=id,id,MISSING) | ++---++--- -- --+ | 1 | 1 | 1 | 1 | | 2 | 2 | 2 | 2 | | 3 | 3 | 3 | 3 | | 4 | 4 | 4 | 4 | | 5 | 5 | 5 | 5 | | 6 | 6 | 6 | 6 | | 7 | 7 | 7 | 7 | | 8 | 8 | 8 | 8 | | 9 | 9 | 9 | 9 | | 12 | 10..11| 12 | MISSING | | 13 | 13| 13 | 13 | | 14 | 14| 14 | 14 | | 15 | 15| 15 | 15 | | 16 | 16| 16 | 16 | | 17 | 17| 17 | 17 | | 18 | 18| 18 | 18 | | 19 | 19| 19 | 19 | | 22 | 20..21| 22 | MISSING | | 24 | 23..23| 24 | MISSING | | 25 | 25| 25 | 25 | | 26 | 26| 26 | 26 | | 27 | 27| 27 | 27 | | 28 | 28| 28 | 28 | | 29 | 29| 29 | 29 | ++---++--- -- --+ 24 rows in set (0.00 sec) To get the MISSING entries you would need to create a temporary table from that query then select where the @flag column = MISSING. Maybe this is more efficient than the join previously suggested. Maybe mysql team would let HAVING be applied to the 'variably' created rows you could then add having flag_alias = MISSING project out just the rows that are missing (saving the need for temporary tables). you could also plug the 10..11 20..21 etc statements into a perl foreach loop (if you were using perl) to automatically create the inner numbers. Ric p.s. maybe there is a way of tricking it into using a having statement to project out the MISSING rows. - Original Message - From: Kevin Fries [EMAIL PROTECTED] To: 'mos' [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Thursday, May 30, 2002 10:56 PM Subject: RE: Finding holes in autoinc sequences A simple and fast way to find the gaps is to use a self LEFT JOIN, such as: select a.id from seq a left join seq b on a.id + 1 = b.id where b.id is null; The result set will show any row in seq where there was no record with ID one greater than that record's value. You'll get back the last row, and any rows which don't have a next-higher neighbor. To get my example above to work, use: create table seq ( id int auto_increment not null primary key); insert into seq values(); ** repeat to get rows 1..30. ** delete from seq where id between 10 and 11; delete from seq where id between 20 and 21; delete from seq where id = 23; select a.id from seq a left join seq b on a.id + 1 = b.id where b.id is null; ++--+ | id | id | ++--+ | 9 | NULL | | 19 | NULL | | 22 | NULL | | 30 | NULL | ++--+ Note that this doesn't show where the gaps end. You can further enhance the query by looking backward as well...getting fancy output: select a.id, case when c.id is null and b.id is null then '' else (case when c.id is null then '' else (case when b.id is null then '' else '' end) end) end gaps from seq a left join seq b on a.id + 1 = b.id left join seq c on a.id - 1 = c.id where b.id is null or c.id is null; ++--+ | id
Re: Finding holes in autoinc sequences
Genius. I knew there must be a way of tricking Mysql into applying a having statement and hence doing the whole query in a non join based statement. I have found the variable features of mysql most useful for a query of the type, select the top x rows per group. i.e. a limit per group. This is perfect if you are doing any statistical calculations which involves gathering the top 100 (or n) rows per id. Richard - Original Message - From: Kevin Fries [EMAIL PROTECTED] To: 'Richard Clarke' [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Friday, May 31, 2002 6:07 PM Subject: RE: Finding holes in autoinc sequences Richard, Say, that's really good! I'm still not used to taking advantage of those variables. Clever. Also, I think you can remove all but the rows showing gaps by adding a group by and having clause... select id,@b:=IF(id=@a+1,id,concat(@a+1,..,id-1)) range,@a:=id, @flag:=IF(@b=id,id,MISSING) from seq group by id having id range; -Original Message- From: Richard Clarke [mailto:[EMAIL PROTECTED]] Sent: Thursday, May 30, 2002 7:48 PM To: [EMAIL PROTECTED] Subject: Re: Finding holes in autoinc sequences Or another approach could be (using same example tables) mysql set @a:=0; set @b:=0; set @c:=0; mysql select id,@b:=IF(id=@a+1,id,concat(@a+1,..,id-1)),@a:=id,@flag:=IF( @b=id,id,MISS ING) from seq; ++---++--- -- --+ | id | @b:=IF(id=@a+1,id,concat(@a+1,..,id-1)) | @a:=id | @flag:=IF(@b=id,id,MISSING) | ++---++--- -- --+ | 1 | 1 | 1 | 1 | | 2 | 2 | 2 | 2 | | 3 | 3 | 3 | 3 | | 4 | 4 | 4 | 4 | | 5 | 5 | 5 | 5 | | 6 | 6 | 6 | 6 | | 7 | 7 | 7 | 7 | | 8 | 8 | 8 | 8 | | 9 | 9 | 9 | 9 | | 12 | 10..11| 12 | MISSING | | 13 | 13| 13 | 13 | | 14 | 14| 14 | 14 | | 15 | 15| 15 | 15 | | 16 | 16| 16 | 16 | | 17 | 17| 17 | 17 | | 18 | 18| 18 | 18 | | 19 | 19| 19 | 19 | | 22 | 20..21| 22 | MISSING | | 24 | 23..23| 24 | MISSING | | 25 | 25| 25 | 25 | | 26 | 26| 26 | 26 | | 27 | 27| 27 | 27 | | 28 | 28| 28 | 28 | | 29 | 29| 29 | 29 | ++---++--- -- --+ 24 rows in set (0.00 sec) To get the MISSING entries you would need to create a temporary table from that query then select where the @flag column = MISSING. Maybe this is more efficient than the join previously suggested. Maybe mysql team would let HAVING be applied to the 'variably' created rows you could then add having flag_alias = MISSING project out just the rows that are missing (saving the need for temporary tables). you could also plug the 10..11 20..21 etc statements into a perl foreach loop (if you were using perl) to automatically create the inner numbers. Ric p.s. maybe there is a way of tricking it into using a having statement to project out the MISSING rows. - Original Message - From: Kevin Fries [EMAIL PROTECTED] To: 'mos' [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Thursday, May 30, 2002 10:56 PM Subject: RE: Finding holes in autoinc sequences A simple and fast way to find the gaps is to use a self LEFT JOIN, such as: select a.id from seq a left join seq b on a.id + 1 = b.id where b.id is null; The result set will show any row in seq where there was no record with ID one greater than that record's value. You'll get back the last row, and any rows which don't have a next-higher neighbor. To get my example above to work, use: create table seq ( id int auto_increment not null primary key); insert into seq values(); ** repeat to get rows 1..30. ** delete from seq where id between 10 and 11
Finding holes in autoinc sequences
I'm going to be tackling this problem in a few days and I wanted to bounce it off of a few MySQL heads first to see if it generates any ideas. (or sparks?g) Here's is the problem. I have an auto-inc column and rows will get deleted from the table and of course it will create a hole in the sequence which is fine. But I want to track which rows have been deleted by finding the holes. (I will probably keep track of the deleted rows as they get deleted, but occasionally I will need to verify this by scanning the table.) Example: Original squence of Rcd_Id: 1,2,3,4,5,6,7,8,9,10 After deleting rows 5 and 9 we get: Rcd_Id: 1,2,3,4,6,7,8,10 Now is there any SQL statement that I can use to quickly and efficiently find the 2 missing rows in this example? The only thing I've come up with is to write a PHP program to loop through the Rcd_Id's in order and see which ones are missing (the query would fetch 10k rows at a time so it doesn't consume too much memory). The table could get rather large (1m rows) and I need something that doesn't consume a lot of memory or time. Does anyone have any ideas? TIA Mike - 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: Finding holes in autoinc sequences
On Thu, May 30, 2002 at 03:17:53PM -0500, mos wrote: I'm going to be tackling this problem in a few days and I wanted to bounce it off of a few MySQL heads first to see if it generates any ideas. (or sparks?g) Here's is the problem. I have an auto-inc column and rows will get deleted from the table and of course it will create a hole in the sequence which is fine. But I want to track which rows have been deleted by finding the holes. (I will probably keep track of the deleted rows as they get deleted, but occasionally I will need to verify this by scanning the table.) Example: Original squence of Rcd_Id: 1,2,3,4,5,6,7,8,9,10 After deleting rows 5 and 9 we get: Rcd_Id: 1,2,3,4,6,7,8,10 Now is there any SQL statement that I can use to quickly and efficiently find the 2 missing rows in this example? The only thing I've come up with is to write a PHP program to loop through the Rcd_Id's in order and see which ones are missing (the query would fetch 10k rows at a time so it doesn't consume too much memory). The table could get rather large (1m rows) and I need something that doesn't consume a lot of memory or time. Does anyone have any ideas? TIA How about using a simple binary search? Start by finding out the max auto_increment value and total number of records. That'll tell you how many holes you are looking for. Then chop the space up and query to find the number of rows in each range of values and you'll know where to focus your efforts. That should reduce the number of records you need to read if done well. Jeremy -- Jeremy D. Zawodny, [EMAIL PROTECTED] Technical Yahoo - Yahoo Finance Desk: (408) 349-7878 Fax: (408) 349-5454 Cell: (408) 685-5936 MySQL 3.23.51: up 0 days, processed 22,868,489 queries (310/sec. avg) - 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: Finding holes in autoinc sequences
A simple and fast way to find the gaps is to use a self LEFT JOIN, such as: select a.id from seq a left join seq b on a.id + 1 = b.id where b.id is null; The result set will show any row in seq where there was no record with ID one greater than that record's value. You'll get back the last row, and any rows which don't have a next-higher neighbor. To get my example above to work, use: create table seq ( id int auto_increment not null primary key); insert into seq values(); ** repeat to get rows 1..30. ** delete from seq where id between 10 and 11; delete from seq where id between 20 and 21; delete from seq where id = 23; select a.id from seq a left join seq b on a.id + 1 = b.id where b.id is null; ++--+ | id | id | ++--+ | 9 | NULL | | 19 | NULL | | 22 | NULL | | 30 | NULL | ++--+ Note that this doesn't show where the gaps end. You can further enhance the query by looking backward as well...getting fancy output: select a.id, case when c.id is null and b.id is null then '' else (case when c.id is null then '' else (case when b.id is null then '' else '' end) end) end gaps from seq a left join seq b on a.id + 1 = b.id left join seq c on a.id - 1 = c.id where b.id is null or c.id is null; ++--+ | id | gaps | ++--+ | 1 | | | 9 | | | 12 | | | 19 | | | 22 || | 24 | | | 30 | | ++--+ Note here that there's a gap between 9 and 12, between 19 and 22, and between 22 and 24. There's also a gap before 1, and one after 30, but this just tells us where the range ends. nulled Outer joins are very handy. Kevin Fries -Original Message- From: mos [mailto:[EMAIL PROTECTED]] Sent: Thursday, May 30, 2002 1:18 PM To: [EMAIL PROTECTED] Subject: Finding holes in autoinc sequences I'm going to be tackling this problem in a few days and I wanted to bounce it off of a few MySQL heads first to see if it generates any ideas. (or sparks?g) Here's is the problem. I have an auto-inc column and rows will get deleted from the table and of course it will create a hole in the sequence which is fine. But I want to track which rows have been deleted by finding the holes. (I will probably keep track of the deleted rows as they get deleted, but occasionally I will need to verify this by scanning the table.) Example: Original squence of Rcd_Id: 1,2,3,4,5,6,7,8,9,10 After deleting rows 5 and 9 we get: Rcd_Id: 1,2,3,4,6,7,8,10 Now is there any SQL statement that I can use to quickly and efficiently find the 2 missing rows in this example? The only thing I've come up with is to write a PHP program to loop through the Rcd_Id's in order and see which ones are missing (the query would fetch 10k rows at a time so it doesn't consume too much memory). The table could get rather large (1m rows) and I need something that doesn't consume a lot of memory or time. Does anyone have any ideas? TIA Mike - 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: Finding holes in autoinc sequences
Or another approach could be (using same example tables) mysql set @a:=0; set @b:=0; set @c:=0; mysql select id,@b:=IF(id=@a+1,id,concat(@a+1,..,id-1)),@a:=id,@flag:=IF(@b=id,id,MISS ING) from seq; ++---++- --+ | id | @b:=IF(id=@a+1,id,concat(@a+1,..,id-1)) | @a:=id | @flag:=IF(@b=id,id,MISSING) | ++---++- --+ | 1 | 1 | 1 | 1 | | 2 | 2 | 2 | 2 | | 3 | 3 | 3 | 3 | | 4 | 4 | 4 | 4 | | 5 | 5 | 5 | 5 | | 6 | 6 | 6 | 6 | | 7 | 7 | 7 | 7 | | 8 | 8 | 8 | 8 | | 9 | 9 | 9 | 9 | | 12 | 10..11| 12 | MISSING | | 13 | 13| 13 | 13 | | 14 | 14| 14 | 14 | | 15 | 15| 15 | 15 | | 16 | 16| 16 | 16 | | 17 | 17| 17 | 17 | | 18 | 18| 18 | 18 | | 19 | 19| 19 | 19 | | 22 | 20..21| 22 | MISSING | | 24 | 23..23| 24 | MISSING | | 25 | 25| 25 | 25 | | 26 | 26| 26 | 26 | | 27 | 27| 27 | 27 | | 28 | 28| 28 | 28 | | 29 | 29| 29 | 29 | ++---++- --+ 24 rows in set (0.00 sec) To get the MISSING entries you would need to create a temporary table from that query then select where the @flag column = MISSING. Maybe this is more efficient than the join previously suggested. Maybe mysql team would let HAVING be applied to the 'variably' created rows you could then add having flag_alias = MISSING project out just the rows that are missing (saving the need for temporary tables). you could also plug the 10..11 20..21 etc statements into a perl foreach loop (if you were using perl) to automatically create the inner numbers. Ric p.s. maybe there is a way of tricking it into using a having statement to project out the MISSING rows. - Original Message - From: Kevin Fries [EMAIL PROTECTED] To: 'mos' [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Thursday, May 30, 2002 10:56 PM Subject: RE: Finding holes in autoinc sequences A simple and fast way to find the gaps is to use a self LEFT JOIN, such as: select a.id from seq a left join seq b on a.id + 1 = b.id where b.id is null; The result set will show any row in seq where there was no record with ID one greater than that record's value. You'll get back the last row, and any rows which don't have a next-higher neighbor. To get my example above to work, use: create table seq ( id int auto_increment not null primary key); insert into seq values(); ** repeat to get rows 1..30. ** delete from seq where id between 10 and 11; delete from seq where id between 20 and 21; delete from seq where id = 23; select a.id from seq a left join seq b on a.id + 1 = b.id where b.id is null; ++--+ | id | id | ++--+ | 9 | NULL | | 19 | NULL | | 22 | NULL | | 30 | NULL | ++--+ Note that this doesn't show where the gaps end. You can further enhance the query by looking backward as well...getting fancy output: select a.id, case when c.id is null and b.id is null then '' else (case when c.id is null then '' else (case when b.id is null then '' else '' end) end) end gaps from seq a left join seq b on a.id + 1 = b.id left join seq c on a.id - 1 = c.id where b.id is null or c.id is null; ++--+ | id | gaps | ++--+ | 1 | | | 9 | | | 12 | | | 19 | | | 22 || | 24 | | | 30 | | ++--+ Note here that there's a gap between 9 and 12, between 19 and 22, and between 22 and 24. There's also a gap before 1, and one after 30, but this just tells us where the range ends. nulled Outer joins are very handy. Kevin Fries -Original Message- From: mos [mailto:[EMAIL PROTECTED]] Sent: Thursday, May 30, 2002 1:18 PM To: [EMAIL PROTECTED] Subject: Finding holes in autoinc sequences I'm going to be tackling this problem in a few days and I wanted to bounce it off of a few MySQL heads first
Features (Sequences, views, triggers/rules)
I am currently migrating my DB and associated apps to MySQL from Postgres,
and I am running into a couple of limitations that I couldn't figure out
how to work around from the docs, mostly stemming from sequences, views,
and triggers. I am currently using the stock RH7.1 MySQL 3.23.36,
please let me know if there are better solutions in 4.0.
1. Is there any way to create a global auto_increment variable? In
Postgres, I would have done this by creating a global sequence:
CREATE SEQUENCE global_id_seq;
and then defaulted the primary keys to the next value of the sequence:
CREATE TABLE onetable (
onetable_id INT PRIMARY KEY DEFAULT nextval('global_id_seq')
);
I know that this is not necessarily the correct way to do things, but I
am using an outside app which is stupid enough to need this, and I can't
change the app.
2. I know that MySQL does not yet have views, but I was wondering if there
was
a way to work around this in the DB. That same stupid application
(NetViz) connects to the DB through ODBC and graphically displays the
data in the DB, in my case, a network topology. I have a table full of
hosts and their associated information (name, ip, OS, etc.), a table full
of all the different types of software that can be running in the network
with its associated info (vendor, version, serial, etc.), and a mapping
table to create a joint primary key out of the two foriegn keys from the
software and hosts tables to show what host is running what software. My
problem is that NetViz does not support many-to-many heirarchy mappings,
and I have (in Postgres) created a view which pretty much just joins the
map table and the software table to form a many-to-one mapping, which
NetViz uses just fine. I then used rules (Postgres specific, I think,
similar to triggers) to make the view writable by NetViz.
Thanks
-Mike
-
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: does MySql support Procedures, Events and Sequences
Hi. On Tue, Nov 20, 2001 at 09:58:46AM +0530, [EMAIL PROTECTED] wrote: Hi, I am developing an application which shows Meta Data of the MySql database in Tree Structure. In this i am displaying nodes like Tables and its childs are Tables in the database, Indexes and its child nodes are indexes on all tables etc. I am confused weather MySql support Procedures, It doesn't support Stored Procedures. Events I am not sure what you mean with Events, but for every meaning I could assume, the answer is: MySQL doesn't support it. and Sequences. Have a look at AUTO_INCREMENT in the manual. If there is a support i can also include these nodes in my Tree. Please respond to this as it is high priority for me. It's always, isn't it? Bye, Benjamin. -- [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
Re: does MySql support Procedures, Events and Sequences
Hi, I am developing an application which shows Meta Data of the MySql database in Tree Structure. In this i am displaying nodes like Tables and its childs are Tables in the database, Indexes and its child nodes are indexes on all tables etc. I am confused weather MySql support Procedures, Events and Sequences. If there is a support i can also include these nodes in my Tree. Please respond to this as it is high priority for me. Thanks Regards, Omkaram Sangem iNuCom (India) Ltd Secunderabad:53 Phones:+91-40-7893817/7812959 Fax:+91-40-7809071 Email: [EMAIL PROTECTED] www.inucom.com Quality is Everything - 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
Counting Sequences Solution
Hello, A solution to my problem: For an athlete's first entry in the database I need a column value of 1. The second performance entry of the same athlete would have a value of 2. And so on. has been posted by Paul Dubois: ALTER TABLE tbl_name ADD seqnum INT UNSIGNED NOT NULL AUTO_INCREMENT, ADD PRIMARY KEY (name, seqnum) This worked perfectly and was very fast on a table with 2.6 million records and 179,00 athletes. Many thanks. Bruce - 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
Counting Sequences Clarified
Hello, Thank's for your interest Paul. I did a poor job of explaining my problem. Here is another go: For an athlete's first entry in the database I need a column value of 1. The second performance entry of the same athlete would have a value of 2. And so on. I need to apply this retrospectively to ten years of performance records. Ongoing updates are not an issue at present. Thanks Bruce - 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: Counting Sequences Clarified
Bruce, Why not just determine this number when you do a query? Why do you need to have it be stored in the database? It's easy to create a Perl (or probably PHP, but I really don't know PHP) script to fill in such a column, too, but you would need to manually maintain that. And right now I don't understand the problem well enough to convince me that such maintenance would be worthwhile ... Tom Haapanen [EMAIL PROTECTED] -Original Message- From: Bruce Collins [mailto:[EMAIL PROTECTED]] Sent: Tuesday, 02 October, 2001 07:04 To: [EMAIL PROTECTED] Subject: Counting Sequences Clarified Hello, Thank's for your interest Paul. I did a poor job of explaining my problem. Here is another go: For an athlete's first entry in the database I need a column value of 1. The second performance entry of the same athlete would have a value of 2. And so on. I need to apply this retrospectively to ten years of performance records. Ongoing updates are not an issue at present. Thanks Bruce - 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: Counting Sequences Clarified
At 9:04 PM +1000 10/2/01, Bruce Collins wrote: Hello, Thank's for your interest Paul. I did a poor job of explaining my problem. Here is another go: For an athlete's first entry in the database I need a column value of 1. The second performance entry of the same athlete would have a value of 2. And so on. I need to apply this retrospectively to ten years of performance records. Ongoing updates are not an issue at present. Thanks Bruce If you have a column identifies each athlete, say, by name, you can add an AUTO_INCREMENT column and create a primary key that combines the two columns. Then records will be auto-numbered, individually for each athlete name. This assumes a version of MySQL = 3.22.25. ALTER TABLE tbl_name ADD seqnum INT UNSIGNED NOT NULL AUTO_INCREMENT, ADD PRIMARY KEY (name, seqnum) name should have been declared NOT NULL as well. -- 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
Counting Sequences
Hello, I have a mySQL database with a large table of athletic performances where every athlete has a row for every performance. My problem is to make a column which is a sequential count of each athlete's performances. The table is in chronological order. Have been trying with PHP but I am very much a beginner. Thanks, Bruce. - 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: Counting Sequences
At 12:23 PM +1000 10/2/01, Bruce Collins wrote: Hello, I have a mySQL database with a large table of athletic performances where every athlete has a row for every performance. My problem is to make a column which is a sequential count of each athlete's performances. The table is in chronological order. Have been trying with PHP but I am very much a beginner. Thanks, Bruce. What do you mean by sequential count? You want to know how many records you have for each athlete? SELECT name, COUNT(*) FROM table GROUP BY name will give you that. If that's not what you mean, please specify. -- 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
sequences or auto_increment column????
Does anybody know which approach is better with mysql: Create sequences for tables or an auto_increment columns??? and why??? Siomara _ Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp - 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: sequences or auto_increment column????
I prefer to use Auto_Increment for ease of use. In addition, I don't think that MySQL supports sequences outright (like you may be used to with Oracle). There are some workarounds availabe within the MySQL manual, do a search with sequences. Good Luck, Dennis ** Beridney Computer Services http://www.beridney.com - Original Message - From: Siomara Pantarotto [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, July 27, 2001 10:04 AM Subject: sequences or auto_increment column Does anybody know which approach is better with mysql: Create sequences for tables or an auto_increment columns??? and why??? Siomara - 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
My SQL Database and escape sequences
Can some one let me know all possible ways to provide escape characters in MYSQL inserts. I am using C API. Regards, Ravi Do You Yahoo!? Get your free @yahoo.co.uk address at http://mail.yahoo.co.uk or your free @yahoo.ie address at http://mail.yahoo.ie - 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: My SQL Database and escape sequences
At 5:49 PM +0100 5/10/01, VVM Ravikumar Sarma Chengalvala wrote: Can some one let me know all possible ways to provide escape characters in MYSQL inserts. I am using C API. If you're asking how to escape special characters: http://www.mysql.com/doc/m/y/mysql_escape_string.html http://www.mysql.com/doc/m/y/mysql_real_escape_string.html If you're asking what the allowable escape sequences are: http://www.mysql.com/doc/S/t/String_syntax.html Regards, Ravi -- 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
Re: ID numbers: auto-increment or sequences ?
On Wed, 11 Apr 2001 14:42:47 +0200, Gunnar von Boehn [EMAIL PROTECTED] wrote: with MySQL 3.23+ you can now assign the auto_increment start value, when creating the table: AUTO_INCREMENT = 1 For identification of each record in our database we use an 'autovalue'-column. It seems that this was a rather bad idea. As we are now planning to run two individual databases. In both of them records shall be written. Later we plan to merge them together again. But how can we do this? With the 'autovalue'-ids we may end with record having the same ids. Can I somehow say that in database (1) the autovalues should start with 1 and in database (2) maybe with 1 ? Mike(mickalo)Blezien Thunder Rain Internet Publishing Providing Internet Solutions that work! http://www.thunder-rain.com Tel: 1(225) 686-2002 = - 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: ID numbers: auto-increment or sequences ?
"Gunnar von Boehn" [EMAIL PROTECTED] wrote: Can I somehow say that in database (1) the autovalues should start with 1 and in database (2) maybe with 1 ? Yes. See the previous post from Mike Blezien. What he didn't say is that yes, you can have two identically structured tables with different AUTO_INCREMENT values set at table creation time and then later insert the records into a third table and retain the incremented values. -- Steve Werby President, Befriend Internet Services LLC http://www.befriend.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
ID numbers: auto-increment or sequences ?
Hi, For identification of each record in our database we use an 'autovalue'-column. It seems that this was a rather bad idea. As we are now planning to run two individual databases. In both of them records shall be written. Later we plan to merge them together again. But how can we do this? With the 'autovalue'-ids we may end with record having the same ids. Can I somehow say that in database (1) the autovalues should start with 1 and in database (2) maybe with 1 ? With 'postgres' I could use a sequence for that. Can mysql the same or do I have to build a sequence table on my own? If I have to build my own sequence table, how do I handle the locking? thanks for any help 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
