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]
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: 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]
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]
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
