CODEWITHSUNDEEP
mysqldatabase
CyberJunkie
CyberJunkie

Reputation: 22674

Maximum length for MySQL type text

I'm creating a form for sending private messages and want to set the maxlength value of a textarea appropriate to the max length of a text field in my MySQL database table. How many characters can a type text field store?

If a lot, would I be able to specify length in the database text type field as I would with varchar?

Upvotes: 545

Views: 668036

Answers (8)

evilReiko
evilReiko

Reputation: 20473

Type Approx. Length Exact Max. Length Allowed
TINYTEXT 256 Bytes 255 characters
TEXT 64 Kilobytes 65,535 characters
MEDIUMTEXT 16 Megabytes 16,777,215 characters
LONGTEXT 4 Gigabytes 4,294,967,295 characters

Basically, it's like:

"Exact Max. Length Allowed" = "Approx. Length" in bytes - 1

Note: If using multibyte characters (like Arabic, where each Arabic character takes 2 bytes), the column "Exact Max. Length Allowed" for TINYTEXT can hold be up to 127 Arabic characters (Note: space, dash, underscore, and other such characters, are 1-byte characters).

Upvotes: 163

fyr
fyr

Reputation: 20859

See for maximum numbers: http://dev.mysql.com/doc/refman/5.0/en/storage-requirements.html

TINYBLOB, TINYTEXT       L + 1 bytes, where L < 2^8    (255 Bytes)
BLOB, TEXT               L + 2 bytes, where L < 2^16   (64 Kilobytes)
MEDIUMBLOB, MEDIUMTEXT   L + 3 bytes, where L < 2^24   (16 Megabytes)
LONGBLOB, LONGTEXT       L + 4 bytes, where L < 2^32   (4 Gigabytes)

L is the number of bytes in your text field. So the maximum number of chars for text is 216-1 (using single-byte characters). Means 65 535 chars(using single-byte characters).

UTF-8/MultiByte encoding: using MultiByte encoding each character might consume more than 1 byte of space. For UTF-8 space consumption is between 1 to 4 bytes per char.

Upvotes: 871

Rohit.007
Rohit.007

Reputation: 3502

For the MySql version 8.0.

Numeric Type Storage Requirements

Data Type       Storage Required
TINYINT         1 byte
SMALLINT        2 bytes
MEDIUMINT       3 bytes
INT, INTEGER    4 bytes
BIGINT          8 bytes
FLOAT(p)        4 bytes if 0 <= p <= 24, 8 bytes if 25 <= p <= 53
FLOAT           4 bytes
DOUBLE, REAL    8 bytes
DECIMAL(M,D), NUMERIC(M,D)  Varies; see following discussion
BIT(M)  approximately (M+7)/8 bytes

Values for DECIMAL (and NUMERIC) columns are represented using a binary format that packs nine decimal (base 10) digits into four bytes. Storage for the integer and fractional parts of each value are determined separately. Each multiple of nine digits requires four bytes, and the “leftover” digits require some fraction of four bytes. The storage required for excess digits is given by the following table.

Date and Time Type Storage Requirements For TIME, DATETIME, and TIMESTAMP columns, the storage required for tables created before MySQL 5.6.4 differs from tables created from 5.6.4 on. This is due to a change in 5.6.4 that permits these types to have a fractional part, which requires from 0 to 3 bytes.

Data Type   Storage Required Before MySQL 5.6.4   Storage Required as of MySQL 5.6.4
YEAR        1 byte                                1 byte
DATE        3 bytes                               3 bytes
TIME        3 bytes                               3 bytes + fractional seconds storage
DATETIME    8 bytes                               5 bytes + fractional seconds storage
TIMESTAMP   4 bytes                               4 bytes + fractional seconds storage

As of MySQL 5.6.4, storage for YEAR and DATE remains unchanged. However, TIME, DATETIME, and TIMESTAMP are represented differently. DATETIME is packed more efficiently, requiring 5 rather than 8 bytes for the nonfractional part, and all three parts have a fractional part that requires from 0 to 3 bytes, depending on the fractional seconds precision of stored values.

Fractional Seconds Precision    Storage Required
0                               0 bytes
1, 2                            1 byte
3, 4                            2 bytes
5, 6                            3 bytes

For example, TIME(0), TIME(2), TIME(4), and TIME(6) use 3, 4, 5, and 6 bytes, respectively. TIME and TIME(0) are equivalent and require the same storage.

For details about internal representation of temporal values, see MySQL Internals: Important Algorithms and Structures.

String Type Storage Requirements In the following table, M represents the declared column length in characters for nonbinary string types and bytes for binary string types. L represents the actual length in bytes of a given string value.

Data Type                    Storage Required
CHAR(M)                      The compact family of InnoDB row formats optimize storage for variable-length character sets. See COMPACT Row Format Characteristics. Otherwise, M × w bytes, <= M <= 255, where w is the number of bytes required for the maximum-length character in the character set.
BINARY(M)                    M bytes, 0 <= M <= 255
VARCHAR(M), VARBINARY(M)     L + 1 bytes if column values require 0 − 255 bytes, L + 2 bytes if values may require more than 255 bytes
TINYBLOB, TINYTEXT           L + 1 bytes, where L < 28
BLOB, TEXT                   L + 2 bytes, where L < 216
MEDIUMBLOB, MEDIUMTEXT       L + 3 bytes, where L < 224
LONGBLOB, LONGTEXT           L + 4 bytes, where L < 232
ENUM('value1','value2',...)  1 or 2 bytes, depending on the number of enumeration values (65,535 values maximum)
SET('value1','value2',...)   1, 2, 3, 4, or 8 bytes, depending on the number of set members (64 members maximum)

Upvotes: 1

sohel shaikh
sohel shaikh

Reputation: 102

TEXT is a string data type that can store up to 65,535 characters. But still if you want to store more data then change its data type to LONGTEXT

ALTER TABLE name_tabel CHANGE text_field LONGTEXT CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL;

Upvotes: 2

Sachith Wickramaarachchi
Sachith Wickramaarachchi

Reputation: 5862

TINYTEXT 256 bytes
TEXT 65,535 bytes ~64kb
MEDIUMTEXT 16,777,215 bytes ~16MB
LONGTEXT 4,294,967,295 bytes ~4GB

TINYTEXT is a string data type that can store up to to 255 characters.

TEXT is a string data type that can store up to 65,535 characters. TEXT is commonly used for brief articles.

LONGTEXT is a string data type with a maximum length of 4,294,967,295 characters. Use LONGTEXT if you need to store large text, such as a chapter of a novel.

Upvotes: 27

Cristian Oana
Cristian Oana

Reputation: 1718

TINYTEXT: 256 bytes
TEXT: 65,535 bytes
MEDIUMTEXT: 16,777,215 bytes
LONGTEXT: 4,294,967,295 bytes

Upvotes: 144

SCC
SCC

Reputation: 509

How many characters can a type text field store?

According to Documentation You can use maximum of 21,844 characters if the charset is UTF8

If a lot, would I be able to specify length in the db text type field as I would with varchar?

You dont need to specify the length. If you need more character use data types MEDIUMTEXT or LONGTEXT. With VARCHAR, specifieng length is not for Storage requirement, it is only for how the data is retrieved from data base.

Upvotes: 9

Blindy
Blindy

Reputation: 67380

Acording to http://dev.mysql.com/doc/refman/5.0/en/storage-requirements.html, the limit is L + 2 bytes, where L < 2^16, or 64k.

You shouldn't need to concern yourself with limiting it, it's automatically broken down into chunks that get added as the string grows, so it won't always blindly use 64k.

Upvotes: 21

Related Questions