Most of the tables in my db have data of type varchar and length set as 36 used to fill natively created guids. Is varchar the most suitable data type to store guid values or any other type in mysql ? plz explain
1
-
3How does your GUIDs look like?Tobias– Tobias2011-01-12 14:55:35 +00:00Commented Jan 12, 2011 at 14:55
Add a comment
|
5 Answers
You may want to check out PROCEDURE ANALYSE. It can help to suggest the appropriate data types based on what is already in your table.
Here is a blog post that also talks about PROCEDURE ANALYSE and might help as well.
Comments
That's a good question. I have seen in many places that they use varchar for different kinds of IDs that looks like numbers. So why not use integer? Well, in my experience, there are three main cases:
Although they are number, they might be too big. For example, you wouldn't be able to use integer (even the 64-bit big integer) for a number with 32 digits (the maximum for 64-bit unsigned integer is 20 digits).
Some number have special formatting. For example, a phone number might look like this: +44(12)3456789. Now obviously, the +, (, and ), are not part of the number and you can call the number without them, but this is the usual format, so with an integer variable, you will not be able to store this.
In the future you might change your IDs to include letters, so it is a good practice to allow letters from the beginning to avoid having to change millions of records later.
Hope that helps.
Comments
If your GUIDs are always length 36 and not null, use CHAR instead of VARCHAR. It will save space because VARCHAR must store a length prefix for each row.
Also, if all of your columns are fixed length, it will increase performance in some cases since the database can more efficiently scan rows.
Comments
It all depends on what you mean by "most suitable".
The advantages of varchar(36) is simplicity. You can stored the GUID as is, and read it back easily.
But if you are worried about the amount of disk space that the column takes up, then you have a few choices:
varchar(36) = 37 bytes char(36) = 36 bytes char(32) = 32 bytes (just remove the hyphens) binary(16) = 16 bytes (remove the hyphens and unhex the GUID to get a binary string)
So if you are worried about disk space you will find that binary(16) is much better than varchar(36).
Here's an example of getting a binary(16) string from a GUID in MySQL:
unhex(replace(uuid(),'-',''))
Comments
I mean, varchar allows for every character to be used and is typically prevented from overflow like the number datatypes. So...well, if you expect your GUIDs to contain large values or values outside of numerical characters, then you're golden.
