Re: GUID storage
Nowhere in this discussion was this question. Is it in the plans to have a 128 bit numeric column type for MySQL? If so, in what kind of time frame? If not, why not? Character arrays are obviously, but they are probably not the best way to get optimal performance. Native support for a 128 bit number, usable as a UUID, would be better. - ray On May 12, 2004, at 3:07 PM, Jeremy Zawodny wrote: On Wed, May 12, 2004 at 02:50:55PM -0700, Larry Lowry wrote: Well I'm trying to move to MySQL from the MS SQL Server world. Most data elements are easy except for the uniqueidentifier. In the MySQL world what is the preferred/best way to store a uniqueidentifier? The easiest would just be a char(36). If you have unique ids that are 36 characters, then use a char(36). That seems like the obvious thing to do. Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ [book] High Performance MySQL -- http://highperformancemysql.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: GUID storage
In the last episode (Jun 28), Ray Kiddy said: Is it in the plans to have a 128 bit numeric column type for MySQL? If so, in what kind of time frame? If not, why not? I don't know of any 32-bit compiler that provides a 128-bit integer type, which means for most platforms mysql would have to require a bignum library (openssl or libgmp) to support it in any meaningful fashion, and it wouldn't be very fast. Character arrays are obviously, but they are probably not the best way to get optimal performance. Native support for a 128 bit number, usable as a UUID, would be better. You're not doing math on those UUIDs in mysql, are you? A CHAR(16) BINARY field to store a raw UUID in sounds pretty optimal to me. -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: GUID storage
Hi! On May 12, Larry Lowry wrote: A uniqueidentifier in MS SQL is basically a guid. I am generating them via System.Guid.NewGuid().ToString(N) in the Dot Net framework which now returns me a string of 32 characters (hex). Internally I understand it is a 128-bit integer. As an option I could store that in MySql. What data type would that be? How would one convert to binary char(16)? In what documentation would I find this? I do not know enough yet to write a UDF. In 4.1.2 you can use function UNHEX(): UNHEX(REPLACE(uuid, '-', '')) will convert uuid to 16-byte string There's no easy way to do a reverse conversion yet. HEX() will do, but you'll lose dashes. Regards, Sergei -- __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Sergei Golubchik [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Senior Software Developer /_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
GUID storage
Well I'm trying to move to MySQL from the MS SQL Server world. Most data elements are easy except for the uniqueidentifier. In the MySQL world what is the preferred/best way to store a uniqueidentifier? The easiest would just be a char(36). Thanks. Larry Lowry
Re: GUID storage
Larry Lowry wrote: Well I'm trying to move to MySQL from the MS SQL Server world. Most data elements are easy except for the uniqueidentifier. In the MySQL world what is the preferred/best way to store a uniqueidentifier? The easiest would just be a char(36). Thanks. Larry Lowry In this case I would say easiest is best. There is no specific field for uniqueidentifier. Mike Hillyer -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: GUID storage
On Wed, May 12, 2004 at 02:50:55PM -0700, Larry Lowry wrote: Well I'm trying to move to MySQL from the MS SQL Server world. Most data elements are easy except for the uniqueidentifier. In the MySQL world what is the preferred/best way to store a uniqueidentifier? The easiest would just be a char(36). If you have unique ids that are 36 characters, then use a char(36). That seems like the obvious thing to do. Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ [book] High Performance MySQL -- http://highperformancemysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: GUID storage
In the last episode (May 12), Larry Lowry said: Well I'm trying to move to MySQL from the MS SQL Server world. Most data elements are easy except for the uniqueidentifier. In the MySQL world what is the preferred/best way to store a uniqueidentifier? The easiest would just be a char(36). What is a uniqueidentifier? 36 chars sounds like a uuid, in which case a char(36) is probably the most transparent. If you convert them to raw form they will fit in a binary char(16), though. You could even write uuid_to_string and uuid_from_string UDFs to simplify conversion. -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: GUID storage
A uniqueidentifier in MS SQL is basically a guid. I am generating them via System.Guid.NewGuid().ToString(N) in the Dot Net framework which now returns me a string of 32 characters (hex). Internally I understand it is a 128-bit integer. As an option I could store that in MySql. What data type would that be? How would one convert to binary char(16)? In what documentation would I find this? I do not know enough yet to write a UDF. Larry Lowry - Original Message - From: Dan Nelson [EMAIL PROTECTED] To: Larry Lowry [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Wednesday, May 12, 2004 3:10 PM Subject: Re: GUID storage In the last episode (May 12), Larry Lowry said: Well I'm trying to move to MySQL from the MS SQL Server world. Most data elements are easy except for the uniqueidentifier. In the MySQL world what is the preferred/best way to store a uniqueidentifier? The easiest would just be a char(36). What is a uniqueidentifier? 36 chars sounds like a uuid, in which case a char(36) is probably the most transparent. If you convert them to raw form they will fit in a binary char(16), though. You could even write uuid_to_string and uuid_from_string UDFs to simplify conversion. -- Dan Nelson [EMAIL PROTECTED] -- 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: GUID storage
In the last episode (May 12), Larry Lowry said: A uniqueidentifier in MS SQL is basically a guid. I am generating them via System.Guid.NewGuid().ToString(N) in the Dot Net framework which now returns me a string of 32 characters (hex). Internally I understand it is a 128-bit integer. As an option I could store that in MySql. What data type would that be? How would one convert to binary char(16)? In what documentation would I find this? I do not know enough yet to write a UDF. You'll need to build your own INSERT statement, since I don't know of any way to convert an arbitrary hex string to a binary with mysql functions (although you can use the HEX() function to go the other way). What you can do is insert a hex value directly into a binary field. Say your generated uuid was cb0217f9-a47b-11d8-89f3-0080ad08fb4f: UPDATE mytable SET myfield=CAST(0xcb0217f9a47b11d889f30080ad08fb4f as INARY); It must be exactly like this, not within quotes. You also might want to try just setting your uniqueid field directly using bind variables/ placeholders. If ODBC is smart enough, it will realize that both source and destination are 16 bytes and just pass the raw binary value without any bin-hex-bin conversion. Another option is to convert the guid to a raw string yourself with .net, and pass that to mysql. -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
