Results 1 to 3 of 3

Thread: how to create sequence in MYSQL

  1. #1
    Contributing Member
    Join Date
    Sep 2006
    Answers
    962

    how to create sequence in MYSQL

    please send me the syntax for creating the sequence in mysql and how that sequence be linked to different fields of different tables.


  2. #2
    Expert Member
    Join Date
    Apr 2007
    Answers
    500

    Re: how to create sequence in MYSQL

    In Oracle sequences are often used to maintain a unique series of numbers for an ID field. Sequences are independant of any table and can be used to keep a value unique across a number of tables. In fact they are not even restricted to use in tables.

    MySQL doesn't currently support sequences. However it does have an auto increment value which can be applied to a primary key of a table. This is done during the table creation.

    mysql> create table seq_test (id int primary key auto_increment ,name
    varchar(30));
    Query OK, 0 rows affected (0.06 sec)

    As the name suggests the value is assigned automatically, this is in contrast to Oracle where we have to call the nextval function of the sequence to return the value when we need it.

    So if we perform an insert against the table but do not specifiy a value for the auto_increment column it's assigned automatically.

    mysql> insert into seq_test (name) values ('Dave');
    Query OK, 1 row affected ( 0.03 sec)
    mysql> select * from seq_test;

    However we can override the assignment using a value of our choosing.

    mysql> insert into seq_test (id,name) values (100,'John');
    Query OK, 1 row affected (0.05 sec)
    mysql> select * from seq_test;


    The sequence will then start from this new higher point.

    mysql> insert into seq_test (name) values ('Penny');
    Query OK, 1 row affected (0.02 sec)
    mysql> select * from seq_test;

    We can insert records with values lower than the current highest value but these will be subject to the normal rules for a primary key (no duplicates) and will not effect the next number assigned automatically.

    If all of the records are deleted from a table the sequence is not reset.

    mysql> delete from seq_test;
    Query OK, 3 rows affected (0.01 sec)
    mysql> insert into seq_test (name) values ('garry');
    Query OK, 1 row affected (0.03 sec)
    mysql> select * from seq_test
    To do this you need to truncate the table like so.

    mysql> truncate table seq_test;
    Query OK, 1 row affected (0.03 sec)
    mysql> insert into seq_test (name) values ('garry');
    Query OK, 1 row affected (0.03 sec)
    mysql> select * from seq_test

    Alternatively you can reset the sequence using an alter table command.

    mysql> alter table seq_test auto_increment = 100;
    Query OK, 1 row affected (0.27 sec)Records: 1 Duplicates: 0 Warnings: 0
    This method could also be used to assign a higher number to start the sequence rather than starting with 1 by calling the alter table straight after the table creation.

    So in summary auto_increment offers a great way of assigning a unique value automatically to a table. However what it doesn't allow when compared with an Oracle Sequence is different increment values, ability to use across a number of tables and the option to assign in a reverse order.

    I hope This will help u............


  3. #3
    Junior Member
    Join Date
    Nov 2012
    Answers
    1

    Re: how to create sequence in MYSQL

    HI Sireesha,

    Thanks for the post...

    Is there any way to have sequences in mysql for strings?

    I have an ID of Varchar and I want to have a auto generated sequence for that . Is it possible?

    Thanks
    uday


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
About us
Applying for a job can be a stressful and frustrating experience, especially for someone who has never done it before. Considering that you are competing for the position with a at least a dozen other applicants, it is imperative that you thoroughly prepare for the job interview, in order to stand a good chance of getting hired. That's where GeekInterview can help.
Interact