Professional Web Applications Themes

Column format for storing GUID? - MySQL

Hello, What would be the best way of storing a GUID/UUID in a column, for efficient indexing and lookup? varchar(32)? int? Thanks, -Steve...

  1. #1

    Default Column format for storing GUID?

    Hello,
    What would be the best way of storing a GUID/UUID in a column, for
    efficient indexing and lookup?
    varchar(32)? int?
    Thanks,

    -Steve

    estebanjang@gmail.com Guest

  2. #2

    Default Re: Column format for storing GUID?

    On 8 Mar 2007 16:46:54 -0800, com wrote: 

    Two ints. Unless you REALLY KNOW that GUID is going to be used equally
    often as UUID. (In my own work, user ID is important far more often than
    group ever comes up. Your application may vary from this.)

    --
    85. I will not use any plan in which the final step is horribly complicated,
    e.g. "Align the 12 Stones of Power on the sacred altar then activate the
    medallion at the moment of total eclipse." Instead it will be more along
    the lines of "Push the button." --Peter Anspach's Evil Overlord List
    Peter Guest

  3. #3

    Default Re: Column format for storing GUID?

    On Mar 8, 6:54 pm, "Peter H. Coffin" <com> wrote: 
    >
    > Two ints. Unless you REALLY KNOW that GUID is going to be used equally
    > often as UUID. (In my own work, user ID is important far more often than
    > group ever comes up. Your application may vary from this.)
    >
    > --
    > 85. I will not use any plan in which the final step is horribly complicated,
    > e.g. "Align the 12 Stones of Power on the sacred altar then activate the
    > medallion at the moment of total eclipse." Instead it will be more along
    > the lines of "Push the button." --Peter Anspach's Evil Overlord List[/ref]

    Ah... by "GUID" I meant 128-bit Globally Unique Identifier (the same
    thing as Universally Unique Identifier), not Group ID.

    So, the question is what is the conventional way that people store 128-
    bit IDs in their database tables? (I'm new to database design, so)
    One could convert these to hexadecimal representation and store in a
    varchar(32) column, but I don't know that that's an efficient use of
    the space/comparison. I could split 128-bit into two BIGINT columns,
    but I really don't want to use 2 columns for a single ID.

    How do database experts store GUIDs in database tables? I plan to
    index these GUIDs.

    -Steve

    estebanjang@gmail.com Guest

  4. #4

    Default Re: Column format for storing GUID?

    com wrote: 

    If you can afford the extra work for assembling/dissembling - use two
    BIGINT UNSIGNED columns. Otherwise DECIMAL(39) would fit as well and
    allow you to input/output the number as a whole.


    XL
    --
    Axel Schwenke, Support Engineer, MySQL AB

    Online User Manual: http://dev.mysql.com/doc/refman/5.0/en/
    MySQL User Forums: http://forums.mysql.com/
    Axel Guest

  5. #5

    Default Re: Column format for storing GUID?

    On Fri, 9 Mar 2007 11:13:34 +0100, Axel Schwenke
    <de> wrote:
     
    >
    >If you can afford the extra work for assembling/dissembling - use two
    >BIGINT UNSIGNED columns. Otherwise DECIMAL(39) would fit as well and
    >allow you to input/output the number as a whole.
    >
    >
    >XL[/ref]
    Given that GUIDs are most often written as a sequence of hexadeciamal
    digits "seperated" by hyphens, isn't storing these as anything other
    than "char" going to mean that "the extra work for
    assembling/dissembling" will always be required?

    Just a thought
    Lee


    From Wikipedia

    Guids are most commonly written in text as a sequence of hexadecimal
    digits such as:

    3F2504E0-4F89-11D3-9A0C-0305E82C3301

    This text notation follows from the data structure defined above. The
    sequence is

    1. Data1 (8 characters)
    2. Hyphen
    3. Data2 (4 characters)
    4. Hyphen
    5. Data3 (4 characters)
    6. Hyphen
    7. Initial two items from Data4 (4 characters)
    8. Hyphen
    9. Remaining six items from Data4 (12 characters)

    Often braces are added to enclose the above format, as such:

    {3F2504E0-4F89-11D3-9A0C-0305E82C3301}


    Lee Guest

  6. #6

    Default Re: Column format for storing GUID?

    Lee Peedin <rr.com> wrote: 
    >>
    >>If you can afford the extra work for assembling/dissembling - use two
    >>BIGINT UNSIGNED columns. Otherwise DECIMAL(39) would fit as well and
    >>allow you to input/output the number as a whole.
    >>
    >>[/ref]
    > Given that GUIDs are most often written as a sequence of hexadeciamal
    > digits "seperated" by hyphens, isn't storing these as anything other
    > than "char" going to mean that "the extra work for
    > assembling/dissembling" will always be required?[/ref]

    Hmm. I read about that GUID stuff now and the "hex-with-hyphens" format
    looks quite arbitrary to me. Since it is basically a 128-bit number,
    I would prefer to store it as such.
     

    Of course one could store it as CHAR(36) or even CHAR(38) - but that
    would mean:

    - waste of storage space, resulting in
    - performance impact
    and last not least
    - possibility to have invalid GUIDs - we need to check integrity


    The "silver bullet" would be a native 128-bit INTEGER type and a GUID
    data format a'la SELECT CONVERT(uid_col AS GUID) FROM ...


    XL
    --
    Axel Schwenke, Support Engineer, MySQL AB

    Online User Manual: http://dev.mysql.com/doc/refman/5.0/en/
    MySQL User Forums: http://forums.mysql.com/
    Axel Guest

  7. #7

    Default Re: Column format for storing GUID?

    what operations do you need to do on it?

    until there is a native GUID format, it seems like char(36) would be a
    good choice, given that MySQL's UUID() function returns a 36-character
    text format (hex w/ hyphens), and I can't see why there would be any
    other operations on it besides generation & comparison

    Jason Guest

  8. #8

    Default Re: Column format for storing GUID?

    Jason Sachs wrote: 

    It's simple. Comparison on a 128 bit integer is much faster than
    comparison on a 36 (or 38) byte character stringl

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

  9. #9

    Default Re: Column format for storing GUID?

    On Mar 9, 6:32 am, Axel Schwenke <de> wrote: 
    > [/ref]
    > [/ref]

    >
    > Hmm. I read about that GUID stuff now and the "hex-with-hyphens" format
    > looks quite arbitrary to me. Since it is basically a 128-bit number,
    > I would prefer to store it as such.

    >
    > Of course one could store it as CHAR(36) or even CHAR(38) - but that
    > would mean:
    >
    > - waste of storage space, resulting in
    > - performance impact
    > and last not least
    > - possibility to have invalid GUIDs - we need to check integrity
    >
    > The "silver bullet" would be a native 128-bit INTEGER type and a GUID
    > data format a'la SELECT CONVERT(uid_col AS GUID) FROM ...
    >
    > XL
    > --
    > Axel Schwenke, Support Engineer, MySQL AB
    >
    > Online User Manual:http://dev.mysql.com/doc/refman/5.0/en/
    > MySQL User Forums: http://forums.mysql.com/[/ref]

    Thanks. Any chance that MySQL will provide this "silver bullet" at
    some point? ;-)

    Looking through MSDN, Microsoft seems to have UNIQUEINDENTIFIER data
    type. This is a 16-byte identifier.
    Combine that with NEWID( ) function and ROWGUIDCOL constraint, and I
    have exactly what I am looking for.

    -Steve

    estebanjang@gmail.com Guest

Similar Threads

  1. How can I format a number column in a datagrid
    By Arne in forum ASP.NET Data Grid Control
    Replies: 4
    Last Post: January 6th, 02:37 PM
  2. Weird problem of storing the data format
    By rick in forum ASP Database
    Replies: 1
    Last Post: January 13th, 01:34 PM
  3. How to format TIMESTAMP column??
    By Blobby J Blobdom in forum PHP Development
    Replies: 3
    Last Post: October 20th, 08:23 AM
  4. How to format a boolean column to show YES/NO
    By Jos in forum ASP.NET Data Grid Control
    Replies: 1
    Last Post: August 6th, 11:29 AM

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