large text bits

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
magicrobotmonkey
Forum Regular
Posts: 888
Joined: Sun Mar 21, 2004 1:09 pm
Location: Cambridge, MA

large text bits

Post 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.
User avatar
twigletmac
Her Royal Site Adminness
Posts: 5371
Joined: Tue Apr 23, 2002 2:21 am
Location: Essex, UK

Post by twigletmac »

I would use a TEXT field. What were the issues you had?

Mac
magicrobotmonkey
Forum Regular
Posts: 888
Joined: Sun Mar 21, 2004 1:09 pm
Location: Cambridge, MA

Post 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?
User avatar
twigletmac
Her Royal Site Adminness
Posts: 5371
Joined: Tue Apr 23, 2002 2:21 am
Location: Essex, UK

Post 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
magicrobotmonkey
Forum Regular
Posts: 888
Joined: Sun Mar 21, 2004 1:09 pm
Location: Cambridge, MA

Post by magicrobotmonkey »

hmmm... ok. it was a while ago when i was trying to do it. i probably just messed something up. thanks
User avatar
delorian
Forum Contributor
Posts: 223
Joined: Sun May 04, 2003 5:20 pm
Location: Olsztyn, Poland

Post 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
magmy
Forum Newbie
Posts: 1
Joined: Sun May 16, 2004 10:00 am

Post 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 :)
leenoble_uk
Forum Contributor
Posts: 108
Joined: Fri May 03, 2002 10:33 am
Location: Cheshire
Contact:

Post 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.
User avatar
tim
DevNet Resident
Posts: 1165
Joined: Thu Feb 12, 2004 7:19 pm
Location: ohio

Post by tim »

i dont know if it has been stated or not (to lazy to read)

but read about the BLOB attriubute
Post Reply