Professional Web Applications Themes

How to personalise an auto_increment reference field ? - MySQL

Hi, I would like to personalise my auto_increment reference field. Example : INSERT INTO my_table (data_field) VALUES ("value"); Normal way : First insert will result on id_field = 1 and data_field = "value". Next insert will result on id_field = 2 and data_field = "value". Next insert will result on id_field = 3 and data_field = "value". etc. Wanted way : First insert will result on id_field = 100 and data_field = "value". Next insert will result on id_field = 200 and data_field = "value". Next insert will result on id_field = 300 and data_field = "value"....

  1. #1

    Default How to personalise an auto_increment reference field ?

    Hi,

    I would like to personalise my auto_increment reference field.

    Example :
    INSERT INTO my_table (data_field) VALUES ("value");

    Normal way :
    First insert will result on id_field = 1 and data_field = "value".
    Next insert will result on id_field = 2 and data_field = "value".
    Next insert will result on id_field = 3 and data_field = "value".
    etc.

    Wanted way :
    First insert will result on id_field = 100 and data_field = "value".
    Next insert will result on id_field = 200 and data_field = "value".
    Next insert will result on id_field = 300 and data_field = "value".
    Baptiste Pillot Guest

  2. #2

    Default Re: How to personalise an auto_increment reference field ?

    why?
    Baptiste Pillot wrote:
    > Hi,
    >
    > I would like to personalise my auto_increment reference field.
    >
    > Example :
    > INSERT INTO my_table (data_field) VALUES ("value");
    >
    > Normal way :
    > First insert will result on id_field = 1 and data_field = "value".
    > Next insert will result on id_field = 2 and data_field = "value".
    > Next insert will result on id_field = 3 and data_field = "value".
    > etc.
    >
    > Wanted way :
    > First insert will result on id_field = 100 and data_field = "value".
    > Next insert will result on id_field = 200 and data_field = "value".
    > Next insert will result on id_field = 300 and data_field = "value".
    strawberry Guest

  3. #3

    Default Re: How to personalise an auto_increment reference field ?

    The reason why is more complicated to explain ;-). I will try to be simple.

    I'd like to have an application installed into 2 complete
    apache+php+mysql servers.
    Mysql database will be on the 2 servers.
    Server 1 will te to server 2
    At the same time server 2 will te to server 1 !

    This works well, but when users access the application on server 1 on 2
    at the same time, the new records may have the same id and this will
    result on a database tion crash, because two inserts will use the
    same id.
    The simpliest way I've found to avoid that is to use an id suffix :
    - add a 01 on queue of each reference on server 1
    - add a 02 on queue of each reference on server 2
    Then all the records on server 1 will be identifiant as 101, 201, 301,
    etc. and so.

    I can do that in Php, but if I could do that directly into SQL (with
    stored procedures by example) this could be more efficient, perhaps. I'd
    like to try.

    If you know other ways to te in realtime each other 2 or more 3
    servers, I could be interested ;-).

    > why?
    > Baptiste Pillot wrote:
    > > Hi,
    > >
    > > I would like to personalise my auto_increment reference field.
    > >
    > > Example :
    > > INSERT INTO my_table (data_field) VALUES ("value");
    > >
    > > Normal way :
    > > First insert will result on id_field = 1 and data_field = "value".
    > > Next insert will result on id_field = 2 and data_field = "value".
    > > Next insert will result on id_field = 3 and data_field = "value".
    > > etc.
    > >
    > > Wanted way :
    > > First insert will result on id_field = 100 and data_field = "value".
    > > Next insert will result on id_field = 200 and data_field = "value".
    > > Next insert will result on id_field = 300 and data_field = "value".
    Baptiste Pillot Guest

  4. #4

    Default Re: How to personalise an auto_increment reference field ?

    Baptiste Pillot wrote:
    > Hi,
    >
    > I would like to personalise my auto_increment reference field.
    >
    > Example :
    > INSERT INTO my_table (data_field) VALUES ("value");
    >
    > Normal way :
    > First insert will result on id_field = 1 and data_field = "value".
    > Next insert will result on id_field = 2 and data_field = "value".
    > Next insert will result on id_field = 3 and data_field = "value".
    > etc.
    >
    > Wanted way :
    > First insert will result on id_field = 100 and data_field = "value".
    > Next insert will result on id_field = 200 and data_field = "value".
    > Next insert will result on id_field = 300 and data_field = "value".
    Provided that you are running MySQL 5.0.x, you can fiddle with
    auto_increment_increment and auto_increment_offset to get your desired result.

    I wrote an article that covers this subject in detail, with examples.
    [url]http://www.onlamp.com/pub/a/onlamp/2006/04/20/advanced-mysql-tion.html[/url]

    ciao
    gmax

    --
    _ _ _ _
    (_|| | |(_|>< The Data Charmer
    _|
    [url]http://datacharmer.org/[/url]
    Giuseppe Maxia Guest

  5. #5

    Default Re: How to personalise an auto_increment reference field ?

    Baptiste Pillot wrote:
    > Hi,
    >
    > I would like to personalise my auto_increment reference field.
    >
    > Example :
    > INSERT INTO my_table (data_field) VALUES ("value");
    >
    > Normal way :
    > First insert will result on id_field = 1 and data_field = "value".
    > Next insert will result on id_field = 2 and data_field = "value".
    > Next insert will result on id_field = 3 and data_field = "value".
    > etc.
    >
    > Wanted way :
    > First insert will result on id_field = 100 and data_field = "value".
    > Next insert will result on id_field = 200 and data_field = "value".
    > Next insert will result on id_field = 300 and data_field = "value".
    Some other ways:

    1) Change your auto_increment_offset on one server to some large value (i.e.
    100,000,000) so there's no overlap.
    2) Get fancy and set the auto_increment_offset to an odd number on one system
    (i.e. 11) and an even number on the other system (i.e. 12) and set
    auto_increment_increment to 2.
    3) have another column with the system id and the primary key consist of the
    autoincrement column plus the system id.

    I prefer the first way. Easy and straightforward.


    --
    ==================
    Remove the "x" from my email address
    Jerry Stuckle
    JDS Computer Training Corp.
    [email]jstucklexattglobal.net[/email]
    ==================
    Jerry Stuckle Guest

  6. #6

    Default Re: How to personalise an auto_increment reference field ?

    Jerry Stuckle wrote:
    > Baptiste Pillot wrote:
    >> Hi,
    >>
    >> I would like to personalise my auto_increment reference field.
    >>
    >> Example :
    >> INSERT INTO my_table (data_field) VALUES ("value");
    >>
    >> Normal way :
    >> First insert will result on id_field = 1 and data_field = "value".
    >> Next insert will result on id_field = 2 and data_field = "value".
    >> Next insert will result on id_field = 3 and data_field = "value".
    >> etc.
    >>
    >> Wanted way :
    >> First insert will result on id_field = 100 and data_field = "value".
    >> Next insert will result on id_field = 200 and data_field = "value".
    >> Next insert will result on id_field = 300 and data_field = "value".
    >
    > Some other ways:
    >
    > 1) Change your auto_increment_offset on one server to some large
    > value (i.e. 100,000,000) so there's no overlap.
    > 2) Get fancy and set the auto_increment_offset to an odd number on
    > one system (i.e. 11) and an even number on the other system (i.e. 12)
    > and set auto_increment_increment to 2.
    > 3) have another column with the system id and the primary key
    > consist of the autoincrement column plus the system id.
    >
    > I prefer the first way. Easy and straightforward.
    I was going to suggest the third way. Then there is a field for creating
    system and a field for record ID.


    Paul Lautman Guest

  7. #7

    Default Re: How to personalise an auto_increment reference field ?

    Paul Lautman wrote:
    > Jerry Stuckle wrote:
    >
    >>Baptiste Pillot wrote:
    >>
    >>>Hi,
    >>>
    >>>I would like to personalise my auto_increment reference field.
    >>>
    >>>Example :
    >>>INSERT INTO my_table (data_field) VALUES ("value");
    >>>
    >>>Normal way :
    >>>First insert will result on id_field = 1 and data_field = "value".
    >>>Next insert will result on id_field = 2 and data_field = "value".
    >>>Next insert will result on id_field = 3 and data_field = "value".
    >>>etc.
    >>>
    >>>Wanted way :
    >>>First insert will result on id_field = 100 and data_field = "value".
    >>>Next insert will result on id_field = 200 and data_field = "value".
    >>>Next insert will result on id_field = 300 and data_field = "value".
    >>
    >>Some other ways:
    >>
    >> 1) Change your auto_increment_offset on one server to some large
    >>value (i.e. 100,000,000) so there's no overlap.
    >> 2) Get fancy and set the auto_increment_offset to an odd number on
    >>one system (i.e. 11) and an even number on the other system (i.e. 12)
    >>and set auto_increment_increment to 2.
    >> 3) have another column with the system id and the primary key
    >>consist of the autoincrement column plus the system id.
    >>
    >>I prefer the first way. Easy and straightforward.
    >
    >
    > I was going to suggest the third way. Then there is a field for creating
    > system and a field for record ID.
    >
    >
    The only problem with that is you now have another column - which really isn't
    necessary and can be confusing. You can't just search on a single ID any more.


    --
    ==================
    Remove the "x" from my email address
    Jerry Stuckle
    JDS Computer Training Corp.
    [email]jstucklexattglobal.net[/email]
    ==================
    Jerry Stuckle Guest

  8. #8

    Default Re: How to personalise an auto_increment reference field ?

    Jerry Stuckle a écrit :
    > Paul Lautman wrote:
    >> Jerry Stuckle wrote:
    >>
    >>> Baptiste Pillot wrote:
    >>>
    >>>> Hi,
    >>>>
    >>>> I would like to personalise my auto_increment reference field.
    >>>>
    >>>> Example :
    >>>> INSERT INTO my_table (data_field) VALUES ("value");
    >>>>
    >>>> Normal way :
    >>>> First insert will result on id_field = 1 and data_field = "value".
    >>>> Next insert will result on id_field = 2 and data_field = "value".
    >>>> Next insert will result on id_field = 3 and data_field = "value".
    >>>> etc.
    >>>>
    >>>> Wanted way :
    >>>> First insert will result on id_field = 100 and data_field = "value".
    >>>> Next insert will result on id_field = 200 and data_field = "value".
    >>>> Next insert will result on id_field = 300 and data_field = "value".
    >>>
    >>> Some other ways:
    >>>
    >>> 1) Change your auto_increment_offset on one server to some large
    >>> value (i.e. 100,000,000) so there's no overlap.
    >>> 2) Get fancy and set the auto_increment_offset to an odd number on
    >>> one system (i.e. 11) and an even number on the other system (i.e. 12)
    >>> and set auto_increment_increment to 2.
    >>> 3) have another column with the system id and the primary key
    >>> consist of the autoincrement column plus the system id.
    >>>
    >>> I prefer the first way. Easy and straightforward.
    >>
    >>
    >> I was going to suggest the third way. Then there is a field for
    >> creating system and a field for record ID.
    >>
    >
    > The only problem with that is you now have another column - which
    > really isn't necessary and can be confusing. You can't just search on
    > a single ID any more.
    >
    >
    I totally agree with Jerry for this last point, multiple-id-columns in
    all my tables will be confusing a lot !
    Baptiste Pillot Guest

  9. #9

    Default Re: How to personalise an auto_increment reference field ?

    Jerry Stuckle a écrit :
    > Baptiste Pillot wrote:
    >> Hi,
    >>
    >> I would like to personalise my auto_increment reference field.
    >>
    >> Example :
    >> INSERT INTO my_table (data_field) VALUES ("value");
    >>
    >> Normal way :
    >> First insert will result on id_field = 1 and data_field = "value".
    >> Next insert will result on id_field = 2 and data_field = "value".
    >> Next insert will result on id_field = 3 and data_field = "value".
    >> etc.
    >>
    >> Wanted way :
    >> First insert will result on id_field = 100 and data_field = "value".
    >> Next insert will result on id_field = 200 and data_field = "value".
    >> Next insert will result on id_field = 300 and data_field = "value".
    >
    > Some other ways:
    >
    > 1) Change your auto_increment_offset on one server to some large
    > value (i.e. 100,000,000) so there's no overlap.
    > 2) Get fancy and set the auto_increment_offset to an odd number on
    > one system (i.e. 11) and an even number on the other system (i.e. 12)
    > and set auto_increment_increment to 2.
    > 3) have another column with the system id and the primary key
    > consist of the autoincrement column plus the system id.
    >
    > I prefer the first way. Easy and straightforward.
    >
    >
    But with database tion the auto_increment will not stay into its
    lowers values.
    I think it works about like this :
    server 1 inserts record 1 -> auto_increment is 2
    server 2 inserts record 100000001 (at the same time) -> auto_increment
    is 100000002.
    auto_increment is transmitted to server 1, into the tion process !
    next server 1 insert : record will be id with 100000002 ->
    auto_increment is 100000003
    not good. The problem is to keep auto_increments at their
    server-associated position on each server.
    Baptiste Pillot Guest

  10. #10

    Default Re: How to personalise an auto_increment reference field ?

    Giuseppe Maxia a écrit :
    > Baptiste Pillot wrote:
    >
    >> Hi,
    >>
    >> I would like to personalise my auto_increment reference field.
    >>
    >> Example :
    >> INSERT INTO my_table (data_field) VALUES ("value");
    >>
    >> Normal way :
    >> First insert will result on id_field = 1 and data_field = "value".
    >> Next insert will result on id_field = 2 and data_field = "value".
    >> Next insert will result on id_field = 3 and data_field = "value".
    >> etc.
    >>
    >> Wanted way :
    >> First insert will result on id_field = 100 and data_field = "value".
    >> Next insert will result on id_field = 200 and data_field = "value".
    >> Next insert will result on id_field = 300 and data_field = "value".
    >>
    >
    > Provided that you are running MySQL 5.0.x, you can fiddle with
    > auto_increment_increment and auto_increment_offset to get your desired result.
    >
    > I wrote an article that covers this subject in detail, with examples.
    > [url]http://www.onlamp.com/pub/a/onlamp/2006/04/20/advanced-mysql-tion.html[/url]
    >
    > ciao
    > gmax
    >
    >
    I've read your article, and... this seems to be exactly what I wanted to
    do ! Thanks a lot. This will help better than anything else. I think I
    have to read Mysql 5 full doentation, lot of things to explore...
    Great, those auto_increment_increment and auto_increment_offset features !


    Baptiste Pillot Guest

  11. #11

    Default Re: How to personalise an auto_increment reference field ?

    Baptiste Pillot wrote:
    > Jerry Stuckle a écrit :
    >
    >> Baptiste Pillot wrote:
    >>
    >>> Hi,
    >>>
    >>> I would like to personalise my auto_increment reference field.
    >>>
    >>> Example :
    >>> INSERT INTO my_table (data_field) VALUES ("value");
    >>>
    >>> Normal way :
    >>> First insert will result on id_field = 1 and data_field = "value".
    >>> Next insert will result on id_field = 2 and data_field = "value".
    >>> Next insert will result on id_field = 3 and data_field = "value".
    >>> etc.
    >>>
    >>> Wanted way :
    >>> First insert will result on id_field = 100 and data_field = "value".
    >>> Next insert will result on id_field = 200 and data_field = "value".
    >>> Next insert will result on id_field = 300 and data_field = "value".
    >>
    >>
    >> Some other ways:
    >>
    >> 1) Change your auto_increment_offset on one server to some large
    >> value (i.e. 100,000,000) so there's no overlap.
    >> 2) Get fancy and set the auto_increment_offset to an odd number on
    >> one system (i.e. 11) and an even number on the other system (i.e. 12)
    >> and set auto_increment_increment to 2.
    >> 3) have another column with the system id and the primary key
    >> consist of the autoincrement column plus the system id.
    >>
    >> I prefer the first way. Easy and straightforward.
    >>
    >>
    > But with database tion the auto_increment will not stay into its
    > lowers values.
    > I think it works about like this :
    > server 1 inserts record 1 -> auto_increment is 2
    > server 2 inserts record 100000001 (at the same time) -> auto_increment
    > is 100000002.
    > auto_increment is transmitted to server 1, into the tion process !
    > next server 1 insert : record will be id with 100000002 ->
    > auto_increment is 100000003
    > not good. The problem is to keep auto_increments at their
    > server-associated position on each server.
    Why would it? The auto_increment settings are system variables - not data to be
    ted.

    And if they were ted changing auto_increment_increment would be replaced
    also - so your first idea wouldn't work.



    --
    ==================
    Remove the "x" from my email address
    Jerry Stuckle
    JDS Computer Training Corp.
    [email]jstucklexattglobal.net[/email]
    ==================
    Jerry Stuckle Guest

  12. #12

    Default Re: How to personalise an auto_increment reference field ?

    Jerry Stuckle a écrit :
    > Baptiste Pillot wrote:
    >> Jerry Stuckle a écrit :
    >>
    >>> Baptiste Pillot wrote:
    >>>
    >>>> Hi,
    >>>>
    >>>> I would like to personalise my auto_increment reference field.
    >>>>
    >>>> Example :
    >>>> INSERT INTO my_table (data_field) VALUES ("value");
    >>>>
    >>>> Normal way :
    >>>> First insert will result on id_field = 1 and data_field = "value".
    >>>> Next insert will result on id_field = 2 and data_field = "value".
    >>>> Next insert will result on id_field = 3 and data_field = "value".
    >>>> etc.
    >>>>
    >>>> Wanted way :
    >>>> First insert will result on id_field = 100 and data_field = "value".
    >>>> Next insert will result on id_field = 200 and data_field = "value".
    >>>> Next insert will result on id_field = 300 and data_field = "value".
    >>>
    >>>
    >>> Some other ways:
    >>>
    >>> 1) Change your auto_increment_offset on one server to some large
    >>> value (i.e. 100,000,000) so there's no overlap.
    >>> 2) Get fancy and set the auto_increment_offset to an odd number on
    >>> one system (i.e. 11) and an even number on the other system (i.e.
    >>> 12) and set auto_increment_increment to 2.
    >>> 3) have another column with the system id and the primary key
    >>> consist of the autoincrement column plus the system id.
    >>>
    >>> I prefer the first way. Easy and straightforward.
    >>>
    >>>
    >> But with database tion the auto_increment will not stay into
    >> its lowers values.
    >> I think it works about like this :
    >> server 1 inserts record 1 -> auto_increment is 2
    >> server 2 inserts record 100000001 (at the same time) ->
    >> auto_increment is 100000002.
    >> auto_increment is transmitted to server 1, into the tion
    >> process !
    >> next server 1 insert : record will be id with 100000002 ->
    >> auto_increment is 100000003
    >> not good. The problem is to keep auto_increments at their
    >> server-associated position on each server.
    >
    > Why would it? The auto_increment settings are system variables - not
    > data to be ted.
    >
    > And if they were ted changing auto_increment_increment would be
    > replaced also - so your first idea wouldn't work.
    >
    >
    >
    Perhaps I should test it again, i don't remember what exactly was the
    problem with it. I will try this again when I will test... The
    auto_increment_increment+auto_increment_offset solution seems to be the
    best, I keep it !
    Baptiste Pillot Guest

Similar Threads

  1. Why is reference.vb hierarchally linked to reference.map?
    By Richard Myers in forum ASP.NET Web Services
    Replies: 1
    Last Post: May 6th, 04:37 PM
  2. Defined text field in form -> subject field in e-mail
    By olaka in forum Dreamweaver AppDev
    Replies: 0
    Last Post: March 14th, 02:20 PM
  3. Replies: 0
    Last Post: September 7th, 06:53 PM
  4. Replies: 1
    Last Post: August 29th, 07:02 AM
  5. Have two fields reference the same field
    By William W in forum Microsoft Access
    Replies: 1
    Last Post: August 1st, 04:54 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139