[SOLVED] using longtext for all strings

[bobbinsbro]

hello.

 

i'm creating a CMS for an online store. i was required to create a mechanism that will allow the admins of the site to upload excel worksheets with product info to populate a DB. i basically create+populate tables by parsing the text in these files.

 

in order to create mysql tables from the worksheets, in need to recognize the data-types of each column in the excel files. the method i will use to do this will be to require each column-title in the excel worksheets to begin with datatype codes that i will set. i need to keep these codes simple and as few as possible but without limiting functionality and without bloating the DB size (by setting column datatypes to byte sizes much bigger than their content).

 

so i intend to divide the common mysql types into parent types that the website will need:

prices - mysql DECIMAL

date/time - mysql DATE/TIME

integers - mysql INT

boolean - mysql BIT(1) or TINYINT(1)

text - mysql LONGTEXT

 

now here is my question:

is there any problem with using LONGTEXT in all columns that store strings, regardless of actual length? i read on the mysql website that LONGTEXT is treated as a very long VARCHAR, so i was hoping that inserting the word "hello" into a LONGTEXT column wont result in lots of wasted bytes...?

 

things that will be stored as strings will include product names in different char-sets (utf-8 and latin-1), description, notes, product colors, etc.

 

i'm using mysql server v. 5.0.67 - community. i'm not sure what version the website server will end up having, but i assume it will be 5.0.X - enterprise.

 

thanx in advance.

[fenway]

Don't use LONGTEXT... VARCHAR in MYSQL5 can store up to 65K, which is usually sufficient.

[bobbinsbro]

could you please tell me what the difference is & why VARCHAR is better? and would it be a bad idea to set all text columns so VARCHAR(66560)?

[fenway]

MySQL handles them quite differently... and VARCHAR acts like CHAR in many temporary operations, so space does matter for performance.  That being said, if you need to store large amounts reguarly, use VARCHAR(65k).

[bobbinsbro]

thanx for your help