Page 1 of 1

large text bits

Posted: Tue Mar 30, 2004 8:21 am
by magicrobotmonkey
what would be the best way to store large chiunks of text in a mysql database? right now I'm using varchar(255) which i'm not happy with because its just not big enough. I tried to use blob or text or whatever and couldn't work it out.

Posted: Tue Mar 30, 2004 8:27 am
by twigletmac
I would use a TEXT field. What were the issues you had?

Mac

Posted: Tue Mar 30, 2004 9:57 am
by magicrobotmonkey
how big can text fields be? and is it just a straight up insert and select or do you have to play with the data?

Posted: Tue Mar 30, 2004 11:23 am
by twigletmac
I haven't found a limit for a TEXT field yet :) (check the manual for a more diffinitive answer :P ). You don't have to play with the data to get it in - you could think of it as a very big VARCHAR field in many respects.

Mac

Posted: Tue Mar 30, 2004 11:49 am
by magicrobotmonkey
hmmm... ok. it was a while ago when i was trying to do it. i probably just messed something up. thanks

Posted: Tue Mar 30, 2004 11:55 am
by delorian
Additionaly, text field type provides you with mysql full-text search ability. Check the manual http://www.mysql.com/doc/en/BLOB.html

Posted: Sun May 16, 2004 10:00 am
by magmy
Below is a trick that I discovered for obtaining varchar texts larger than 255 characters when fetching rows using mssql. Let us say that you have a table T with a field F of type varchar(2000). A typical select statement for obtaining F from T looks like this:

Code: Select all

$result = mssql_query("select F from T", $connection);
$row = mssql_fetch_row($result);
Here $row[0] will be limited to a maximum of 255 characters (at least in my version of the mssql library).

My modified query is:

Code: Select all

$result = mssql_query("select convert(text,F) as F from T, $connection);
This makes the DB-library believe that the character field is actually a text field, and the 255 character limit is removed.

I hope this helps someone :)

Posted: Mon May 17, 2004 5:08 am
by leenoble_uk
You should tailor the text field as best you can. I usually get away with using MEDIUMTEXT for my column definition.
I think there's an overhead if you use an unnecessarily large column definition. For the same reason you don't use VARCHAR(255) for every column as it is a waste of space work out what realistically is the maximum text field you would need. It's all in the MySQL manual how many bytes you can store in each.

Posted: Mon May 17, 2004 5:11 pm
by tim
i dont know if it has been stated or not (to lazy to read)

but read about the BLOB attriubute