30

I have a field as follows in MySQL: Type: Text Length: 0 Decimals: 0

And when I try to insert data around the size of 4 pages of MS Word, Coldfusion errors with: Data Too Long from the DB.

I thought TEXT data type was able to expand and handle this size of data? What am I missing and what can I do?

wallyk
  • 56,922
  • 16
  • 83
  • 148
AnApprentice
  • 108,152
  • 195
  • 629
  • 1,012

2 Answers2

49

The type TEXT is limited to 2^16 bytes, or 65536 bytes. Try using the type LONGTEXT instead. It can hold values up to 2^32 bytes in length.

Mark Byers
  • 811,555
  • 193
  • 1,581
  • 1,452
4

Text extracted from:

MySQL 5.1 Reference Manual :: 10 Data Types :: 10.1 Data Type Overview :: 10.1.3 Overview of String Types

TEXT[(M)] [CHARACTER SET charset_name] [COLLATE collation_name]

A TEXT column with a maximum length of 65,535 (2^16 – 1) characters.

The effective maximum length is less if the value contains multi-byte characters. Each TEXT value is stored using a two-byte length prefix that indicates the number of bytes in the value.

An optional length M can be given for this type. If this is done, MySQL creates the column as the smallest TEXT type large enough to hold values M characters long.

I think you'd better use BLOB for that column.

MySQL 5.0 Reference Manual :: 10 Data Types :: 10.4 String Types :: 10.4.3 The BLOB and TEXT Types

Leniel Maccaferri
  • 100,159
  • 46
  • 371
  • 480