Page 1 of 1
Most flexible data type
Posted: Tue Oct 09, 2007 12:21 am
by JellyFish
What is the most flexible data type for MySQL? What can hold the most variety of data, while at the same time being able to hold the most amount of data?
Posted: Tue Oct 09, 2007 12:42 am
by Christopher
That's a couple of questions in one. The TEXT and BLOB types can hold the most data. I would think that string types would be more flexible than numeric types because they can also hold numbers that can be converted back to numerics.
Posted: Tue Oct 09, 2007 1:15 am
by mrkite
blobs can hold any data.
But blobs and text can't be indexed. Text can be fulltext indexed, but that's about it. I don't believe you can create any index on blobs.
Blobs aren't used much. Anywhere a blob would be appropriate, saving a file on disk and storing its filename in mysql is more appropriate.
Posted: Tue Oct 09, 2007 2:08 pm
by JellyFish
mrkite wrote:blobs can hold any data.
But blobs and text can't be indexed. Text can be fulltext indexed, but that's about it. I don't believe you can create any index on blobs.
Blobs aren't used much. Anywhere a blob would be appropriate, saving a file on disk and storing its filename in mysql is more appropriate.
What's indexed?
Posted: Tue Oct 09, 2007 2:51 pm
by onion2k
mrkite wrote:Blobs aren't used much. Anywhere a blob would be appropriate, saving a file on disk and storing its filename in mysql is more appropriate.
The result of AES_ENCRYPT() should be stored in a blob. That's extremely useful
Posted: Tue Oct 09, 2007 7:20 pm
by califdon
You shouldn't really think in terms of "flexible". Certain data types are appropriate for different kinds of data, that's all. It would be bad database design to try to store different kinds of data (say, dates and telephone numbers) in the same field of a table, because the underlying concepts of relational databases expect data in any field of a table to be consistent. If it's not, you're really asking for trouble, later on. So the proper question is, what is the appropriate data type for a particular field.
Posted: Wed Oct 10, 2007 12:27 am
by Josh1billion
JellyFish wrote:mrkite wrote:blobs can hold any data.
But blobs and text can't be indexed. Text can be fulltext indexed, but that's about it. I don't believe you can create any index on blobs.
Blobs aren't used much. Anywhere a blob would be appropriate, saving a file on disk and storing its filename in mysql is more appropriate.
What's indexed?
An index is a field that has the "primary" (key) attribute. That "primary" attribute means that each record must have a unique value for that field, so this is usually used for "id" fields.
Posted: Wed Oct 10, 2007 4:10 am
by mrkite
Josh1billion wrote:
An index is a field that has the "primary" (key) attribute. That "primary" attribute means that each record must have a unique value for that field, so this is usually used for "id" fields.
Primary key is just one type of index. Anything that you're going to use in a where statement should be indexed.
For example, if you say "select * from table where age=32;" If age doesn't have an index, mysql has to scan the entire table looking for matches. If you have a million rows in that table, it has to look through every single row and check for a match. If you add an index to age, then mysql creates a btree and instead of a million rows, it only has to look at roughly 6 rows (depending on how well-balanced the tree is).
btw, it looks like mysql does allow you to index text and blobs, but you must specify a prefix length.. meaning that only the first X bytes of the field will be indexed.
Posted: Wed Oct 10, 2007 4:28 am
by Benjamin
To answer the original question: BLOB