Primer – Column Types part 2

November 28, 2009
 |  No Comments

MySQL Text data type

Text can be fixed length (char) or variable length strings. Also, text comparisions can be case sensitive or insensitive depending on the type you choose.

  • CHAR(x): where x can range from 1 to 255.
  • VARCHAR(x): x ranges from 1 – 255
  • TINYTEXT: small text, case insensitive
  • TEXT: slightly longer text, case insensitive
  • MEDIUMTEXT: medium size text, case insensitive
  • LONGTEXT: really long text, case insensitive
  • TINYBLOB: Blob means a Binary Large OBject. You should use blobs for case sensitive searches.
  • BLOB: slightly larger blob, case sensitive.
  • MEDIUMBLOB: medium sized blobs, case sensitive.
  • LONGBLOB: really huge blobs, case sensitive.
  • ENUM: Enumeration data type have fixed values and the column can take only one value from the given set. The values are placed in parenthesis following ENUM declaration. An example, is the marital status column we encountered in employee_per table. 
    		m_status ENUM("Y", "N")
    Thus, m_status column will take only Y or N as values. If you specify any other value with the INSERT statement, MYSQL will not return an error, it just inserts a NULLvalue in the column.
  • SET: An extension of ENUM. Values are fixed and placed after the SET declaration; however, SET columns can take multiple values from the values provided. Consider a column with the SET data type as
    		hobbies SET ("Reading", "Surfing", "Trekking", "Computing")
    You can have 0 or all the four values in the column. 
    		INSERT tablename (hobbies) values ("Surfing", "Computing");