MySQL: TEXT vs. VARCHAR Performance What is the difference between VARCHAR and TEXT in MySQL?
Tuesday, February 12, 2019

MySQL: TEXT vs. VARCHAR Performance What is the difference between VARCHAR and TEXT in MySQL?

MySQL Tutorial

Home / Basic MySQL Tutorial / The Basics Of MySQL TEXT Data Type

The Basics Of MySQL TEXT Data Type

Summary: in this tutorial, you will learn how to use MySQL TEXT for storing text data in the database table.

Introduction to MySQL TEXT data type

MySQL TEXT data type Besides CHAR and VARCHAR character types, MySQL provides us with TEXT type that has more features which CHAR and VARCHAR cannot cover.

The TEXT is useful for storing long-form text strings that can take from 1 byte to 4 GB. We often find the TEXT data type for storing article body in news sites, product description in e-commerce sites.

Different from CHAR and VARCHAR, you don’t have to specify a storage length when you use a TEXT type for a column. In addition, MySQL does not remove or pad spaces when retrieve or insert text data like CHAR and VARCHAR.

Note that the TEXT data is not stored in the database server’s memory, therefore, whenever you query TEXT data, MySQL has to read from it from the disk, which is much slower in comparison with CHAR and VARCHAR.

MySQL provides four TEXT types: TINYTEXT, TEXT, MEDIUMTEXT, and LONGTEXT.

The following shows the size of each TEXT type with the assumption that we are using a character set that take 1 byte to store a character

TINYTEXT – 1 Byte (255 characters)

The maximum characters that TINYTEXT can store is 255 ( 2^8 = 256, 1 byte overhead).

You should use TINYTEXT for column that require less than 255 characters, has inconsistent length, and does not require sorting such as excerpt of a blog post, summary of an article, etc.

See the following example:

1
2
3
4
5
CREATE TABLE articles (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255),
    summary TINYTEXT
);

In this example, we created a new table named articles that has summary column with the data type is TINYTEXT.

TEXT – 64KB (65,535 characters)

The TEXT data type can hold up to 64 KB that is equivalent to 65535 (2^16 – 1) characters. TEXT also requires 2 byte overhead.

The TEXT can hold the body of an article. Consider the following example:

1
2
3
ALTER TABLE articles
ADD COLUMN body TEXT NOT NULL
AFTER summary;

In this example, we added the body column with TEXT data type to the articles table using the ALTER TABLE statement.

MEDIUMTEXT – 16MB (16,777,215 characters)

The MEDIUMTEXT can hold up to 16MB text data that is equivalent to 16,777,215 characters. It requires 3 bytes overhead.

The MEDIUMTEXT is useful for storing quite large text data like text of a book, white papers, etc. For example:

1
2
3
4
5
CREATE TABLE whitepapers (
    id INT AUTO_INCREMENT PRIMARY KEY,
    body MEDIUMTEXT NOT NULL,
    published_on DATE NOT NULL
);

LONGTEXT – 4GB (4,294,967,295 characters)

The LONGTEXT can store text data up to 4 GB, which is a lot. It requires 4 bytes overhead.

In this tutorial, you have learned how to use various MySQL TEXT data types for storing text in database tables.

  • Was this tutorial helpful ?
  • Yes     No
Previous Tutorial: The Essential Guide to MySQL VARCHAR Data Type
Next Tutorial: MySQL Temporary Table

MySQL Quick Start

  • What Is MySQL?
  • Install MySQL Database Server
  • Download MySQL Sample Database
  • Load Sample Database

MySQL Data Manipulation

  • MySQL SELECT
  • MySQL DISTINCT
  • MySQL WHERE
  • MySQL AND
  • MySQL OR
  • MySQL LIMIT
  • MySQL IN
  • MySQL BETWEEN
  • MySQL LIKE
  • MySQL ORDER BY
  • MySQL Alias
  • MySQL Join
  • MySQL INNER JOIN
  • MySQL LEFT JOIN
  • MySQL RIGHT JOIN
  • MySQL CROSS JOIN
  • MySQL Self Join
  • MySQL GROUP BY
  • MySQL HAVING
  • MySQL ROLLUP
  • MySQL Subquery
  • MySQL UNION
  • MySQL MINUS
  • MySQL INTERSECT
  • MySQL INSERT
  • MySQL INSERT INTO SELECT
  • MySQL Insert On Duplicate Key Update
  • MySQL INSERT IGNORE
  • MySQL LAST_INSERT_ID Function
  • MySQL UPDATE
  • MySQL UPDATE JOIN
  • MySQL DELETE
  • MySQL DELETE JOIN
  • MySQL ON DELETE CASCADE
  • MySQL REPLACE

MySQL Data Definition

  • MySQL Select Database
  • MySQL CREATE DATABASE
  • MySQL DROP DATABASE
  • Manage Databases
  • MySQL Table Types
  • MySQL Data Types
  • MySQL CREATE TABLE
  • MySQL Primary Key
  • MySQL Foreign Key
  • MySQL UNIQUE Constraint
  • MySQL CHECK Constraint
  • MySQL NOT NULL Constraint
  • MySQL Sequence
  • MySQL ALTER TABLE
  • MySQL ADD COLUMN
  • MySQL DROP COLUMN
  • MySQL RENAME TABLE
  • MySQL DROP TABLE
  • MySQL Temporary Table
  • MySQL TRUNCATE TABLE

MySQL Data Types

  • BIT
  • BOOLEAN
  • CHAR
  • DATE
  • DATETIME
  • DECIMAL
  • ENUM
  • INT
  • JSON
  • TIME
  • TIMESTAMP
  • VARCHAR

MySQL Globalization

  • MySQL Character Set
  • MySQL Collation

MySQL Import  Export

  • Import a CSV File Into a Table
  • Export a Table to a CSV File

MySQL Programming Interfaces

  • PHP MySQL Tutorial
  • Node.js MySQL Tutorial
  • Python MySQL Tutorial
  • Perl MySQL Tutorial
  • MySQL JDBC Tutorial

Other Tutorials

  • MySQL Administration
  • MySQL Full-Text Search
  • MySQL Cheat Sheet
  • MySQL Books and Video Training
  • MySQL Hosting
  • MySQL Resources

Recent MySQL Tutorials

  • MySQL SHOW PROCESSLIST
  • Selecting a MySQL Database Using USE Statement
  • MySQL DROP DATABASE
  • MySQL CREATE DATABASE
  • MySQL INSERT INTO SELECT
  • MySQL ABS Function
  • MySQL MOD Function
  • MySQL ROLLUP
  • MySQL TRUNCATE Function
  • MySQL CEIL Function

About MySQL Tutorial Website

MySQLTutorial.org is a website dedicated to MySQL database. We regularly publish useful MySQL tutorials to help web developers and database administrators learn MySQL faster and more effectively.

All MySQL tutorials are practical and easy-to-follow, with SQL script and screenshots available. More About Us

Site Links

  • About Us
  • Contact Us
  • Request a Tutorial
  • Privacy Policy

Stack Overflow



  1. Log In
    Sign Up

  2. current community


    • Stack Overflow

      help
      chat

    • Meta Stack Overflow

    your communities

    Sign up or log in to customize your list.

    more stack exchange communities

    company blog

    • Tour

      Start here for a quick overview of the site

    • Help Center

      Detailed answers to any questions you might have

    • Meta

      Discuss the workings and policies of this site

    • About Us

      Learn more about Stack Overflow the company

    • Business

      Learn more about hiring developers or posting ads with us

By using our site, you acknowledge that you have read and understand our Cookie Policy , Privacy Policy , and our Terms of Service .

Difference between VARCHAR and TEXT in mysql [closed]

Ask Question


up vote
170
down vote

favorite

33

When we create a table in mysql with a VARCHAR column, we have to set the length for it. But for TEXT type we don’t have to provide the length.

What are the differences between VARCHAR and TEXT?

mysql

share | improve this question

edited Feb 12 ’15 at 18:55

AbcAeffchen

8,198123352

asked Aug 14 ’14 at 5:45

user3840485

91521521

closed as off-topic by Phil , Hanky Panky , user3414693, Bojangles , CRABOLO Aug 25 ’14 at 3:41

  • This question does not appear to be about programming within the scope defined in the help center .

If this question can be reworded to fit the rules in the help center , please edit the question .

add a comment  | 

1 Answer
1

active

oldest

votes


up vote
332
down vote

accepted

TL;DR

TEXT

  • fixed max size of 65535 characters (you cannot limit the max size)
  • takes 2 + c bytes of disk space, where c is the length of the stored string.
  • cannot be part of an index

VARCHAR(M)

  • variable max size of M characters
  • M needs to be between 1 and 65535
  • takes 1 + c bytes (for M ≤ 255) or 2 + c (for 256 ≤ M ≤ 65535) bytes of disk space where c is the length of the stored string
  • can be part of an index

More Details

TEXT has a fixed max size of 2¹⁶-1 = 65535 characters.
VARCHAR has a variable max size M up to M = 2¹⁶-1.
So you cannot choose the size of TEXT but you can for a VARCHAR.

The other difference is, that you cannot put an index (except for a fulltext index) on a TEXT column.
So if you want to have an index on the column, you have to use VARCHAR. But notice that the length of an index is also limited, so if your VARCHAR column is too long you have to use only the first few characters of the VARCHAR column in your index (See the documentation for CREATE INDEX ).

But you also want to use VARCHAR, if you know that the maximum length of the possible input string is only M, e.g. a phone number or a name or something like this. Then you can use VARCHAR(30) instead of TINYTEXT or TEXT and if someone tries to save the text of all three “Lord of the Ring” books in your phone number column you only store the first 30 characters 🙂

Edit: If the text you want to store in the database is longer than 65535 characters, you have to choose MEDIUMTEXT or LONGTEXT, but be careful: MEDIUMTEXT stores strings up to 16 MB, LONGTEXT up to 4 GB. If you use LONGTEXT and get the data via PHP (at least if you use mysqli without store_result), you maybe get a memory allocation error, because PHP tries to allocate 4 GB of memory to be sure the whole string can be buffered. This maybe also happens in other languages than PHP.

However, you should always check the input (Is it too long? Does it contain strange code?) before storing it in the database.

Notice: For both types, the required disk space depends only on the length of the stored string and not on the maximum length.
E.g. if you use the charset latin1 and store the text “Test” in VARCHAR(30), VARCHAR(100) and TINYTEXT, it always requires 5 bytes (1 byte to store the length of the string and 1 byte for each character). If you store the same text in a VARCHAR(2000) or a TEXT column, it would also require the same space, but, in this case, it would be 6 bytes (2 bytes to store the string length and 1 byte for each character).

For more information have a look at the documentation .

Finally, I want to add a notice, that both, TEXT and VARCHAR are variable length data types, and so they most likely minimize the space you need to store the data. But this comes with a trade-off for performance. If you need better performance, you have to use a fixed length type like CHAR. You can read more about this here .

share | improve this answer

edited Jul 27 ’17 at 8:40

answered Aug 14 ’14 at 6:02

AbcAeffchen

8,198123352

  • Can you elaborate on what’s the difference between a "fixed max size" and a "variable max size"?
    –  DemiImp
    Dec 6 ’16 at 2:47

  • @DemiImp I’m not sure what I can add. What do you want to know?
    –  AbcAeffchen
    Dec 7 ’16 at 0:06

  • 1

    @AbcAeffchen I believe I found out what you meant. Basically a "fixed max size" means that you can’t set the size at all – it’s always 65535 even if you don’t want to support a size that large. Which means that TEXT is sort of shorthand for a VARCHAR field with a max size of 2¹⁶-1, disregarding the indexing issue. Did I understand right?
    –  DemiImp
    Jan 2 ’17 at 8:47

  • 1

    @DemiImp That is correct. I thought that this is what I wrote in the first two lines. I will add your explanation as a note.
    –  AbcAeffchen
    Jan 2 ’17 at 8:55

  • 2

    What is the advantage of TEXT over VARCHAR?
    –  Solomon Ucko
    Jul 22 at 20:34

 | 
show 2 more comments

Not the answer you’re looking for? Browse other questions tagged mysql or ask your own question .

asked

4 years, 4 months ago

viewed

112,410 times

active

1 year, 4 months ago

Linked

-1

What’s the point in setting max size for varchar() in mysql?

-2

PostgreSQL – can not get more records than 65535

780

MySQL: Large VARCHAR vs. TEXT?

4

MySQL: column size limit

1

MySQL Workbench TEXT() Column Parameter

-1

error when create mysql table with php

0

MySQL varchar field selection and effect on buffer pool usage

Related

789

How do you set a default value for a MySQL Datetime column?

1048

Which MySQL data type to use for storing boolean values

977

How to output MySQL query results in CSV format?

757

How do I specify unique constraint for multiple columns in MySQL?

648

Finding duplicate values in MySQL

539

Differences between INDEX, PRIMARY, UNIQUE, FULLTEXT in MySQL?

297

What’s the difference between VARCHAR and CHAR?

780

MySQL: Large VARCHAR vs. TEXT?

346

Maximum length for MySQL type text

664

TINYTEXT, TEXT, MEDIUMTEXT, and LONGTEXT maximum storage sizes

Hot Network Questions

  • Error "Missing \endcsname inserted" when including PDF using \includegraphics

  • Why do I have to keep on changing my fit?

  • Draw different curves with different scales on TikZ

  • Is it possible to type μ on the bash command line without copying and pasting?

  • Have spelling reformers ever proposed rewriting "-ti-" as "-zi-" when it is pronounced that way?

  • Combining first two letters from first name and first two letters from last name

  • Book about old man storing magical power into plates

  • 9sums – a logical deduction puzzle

  • What is the meaning of this gesture in the British parliament?

  • Through which number we can remove the question mark?

  • O(·) is not a function, so how can a function be equal to it?

  • Is Voyager 2 capable of proving the existence of Oort cloud?

  • Is it possible that the return ticket of a round-trip is counterfeit?

  • Is it a good idea to push new employees to prove themselves in their first 90 days?

  • What is the cmp.addValueProvider() feature designed for?

  • What to teach after scales and chords

  • Our product is ruining users privacy, without telling them

  • Why didn’t Loki use the Tesseract?

  • How much water should I let my dog drink?

  • My cat is not eating or drinking anything after the death of the fellow cat

  • How should I approach reverse engineering this text encoding?

  • What should I choose, Life or Nirvana?

  • Please help identify this physics apparatus!

  • Use columns 1 and 2 to populate column 3

more hot questions

lang-sql

Stack Overflow works best with JavaScript enabled

Skip to content

  • Search

InTechgrity

Technology Powering The Web

MySQL data types: CHAR, VARCHAR, INT, TEXT #Part-1

Swashata 1
This entry is part 1 of 5 in the series MySQL DataTypes
wp-content/uploads/2012/06/mysql-datatype-series-logo.png
MySQL DataTypes
  • MySQL data types: CHAR, VARCHAR, INT, TEXT #Part-1
  • MySQL date and time DataTypes Overview: DATE, TIME, DATETIME, TIMESTAMP, YEAR & Zero Values #Part2.1
  • Automatically insert Current Date and Time in MySQL table #Part – 2.2
  • MySQL: Working with date time arithmetic #Part 2.3.1
  • MySQL FLOAT vs DEC: working with fraction and decimal [DEC]

MySQL datatypes Previously, we have seen how to create and use database and tables from MySQL terminal . Now while creating a table, we have discussed briefly on datatypes. Datatypes are necessary to define the types of data inside every column. They can be simple Characters, text, integer or others. MySQL comes with plenty of datatypes to work with. Defining them properly will not only stop you from entering wrong data, but also will save space on your disc.

For example, consider a table where you have a field for phone_number. Obviously you can store the phone number in text format [like “1234567890”], but restricting it only to integer type will not only avoid accidental inputs, but also save your server disc space.

So, let us see what types of datatypes we have in MySQL and their default usage… In this part, we shall see three major types of data. We shall talk about date-time in the next post, and other datatypes in the third part of this series.

#1: Character or CHAR:

One of the most common datatype in MySQL. Here are the syntaxes and common usage…

Description:

CHAR or CHARACTER stands for fixed length characters [TEXT]. Suppose you have a column where you are to store strings of “N” character length only. Then you can specify the datatype as CHAR(N), where N is the number of characters [1,2,3,4,5…… and so].

Syntax and Usage:

It is to be set while creating a table. To set a column having CHAR datatype, use the following command…

CREATE TABLE table_name
(
column_name CHAR(N),
column_name CHAR(n)
);

It can also be used in ALTER TABLE command. We shall discuss this later on that particular chapter.

For example, you are going to create a table, named personal_info having columns, do_you_have_pets, are_you_student where each of the columns can have values either Y [for YES] or N [for NO]. So it will be a wise idea to set the datatypes of each columns to CHAR(1). So the command here will be:

CREATE TABLE personal_info
(
do_you_have_pets CHAR(1),
are_you_student CHAR(1)
);

Obviously Y and N being 1 length character we have used CHAR(1)

#2: Variable Character or VARCHAR:

Description:

Holds text data having variable character range. The range is defined inside the datatype. It is best used for holding data like email, name, place, country etc, where the data is short text, and it can be of variable length. We set the datatype as VARCHAR(N) where N is the range of the string length. For example, if we have used
VARCHAR(200) then the range of the string will be from 0 to 200.

Depending on your version of MySQL, the length can be 255 characters before the version 5.0.3 and up to 65,535 characters in MySQL 5.0.3 or later.

Syntax and Usage:

The default usage is while creating a table…

CREATE TABLE table_name
(
column_name VARCHAR(N),
column_name VARCHAR(n)
);

For example, say you want to create a table client_info having columns first_name, last_name, email. Clearly, all the three columns should contain text data. So the ideal way of creating them will be:

CREATE TABLE client_info
(
first_name VARCHAR(100),
last_name VARCHAR(60),
email VARCHAR(60)
);

Clearly, the range of first_name is 0-100, last_name is 0-60 and same for email.

Integers or TINYINT, SMALLINT, MEDIUMINT, INT, BIGINT :

Description:

Greatly used to store numbers. Can store positive or negative numbers… Different datatypes can store different range of number. It is given in the following table:

DataTypeRange
TINYINT-128 to 127
SMALLINT-32768 to 32767
MEDIUMINT-8388608 to 8388607
INT-2147483648 to 2147483647
BIGINT-9223372036854775808 to 9223372036854775807

Syntax and Usage:

To be used with CREATE TABLE or ALTER TABLE to set the datatype of a specific column.

CREATE TABLE contact_detail
(
name VARCHAR(100),
phone_number BIGINT,
area_code SMALLINT,
pin_code INT
);

Special Suffix:

Add the suffix “UNSIGNED” after any of the datatype to set the range from 0 to the according value. For example,

ALTER TABLE contact_detail MODIFY
phone_number BIGINT UNSIGNED;

Will make the phone_number column range from 0 to 9223372036854775807. It can also be used with CREATE TABLE.

Texts or TEXT, MEDIUMTEXT and LONGTEXT:

Description:

Good on storing large texts. The three datatypes differ by the range of supported character. It is given below…

DataTypeRange of string length
TEXT0 to 65535
MEDIUMTEXT0 to 16777215
LONGTEXT0 to 4294967295

Syntax and Usage:

Ideally used with columns storing long text messages like emails, feedbacks, posts etc. To be used with CREATE TABLE or ALTER TABLE in the following manner…

CREATE TABLE msg_store
(
name VARCHAR(100),
email_message LONGTEXT,
feedback_message MEDIUMTEXT,
comment TEXT
);

That was all. In the next part of this post, we shall see some other datatypes. Do give your feedback on this article. If you face any trouble, then feel free to a
sk us!

header image credit: http://everythingmysql.ning.com/

Series Navigation

MySQL date and time DataTypes Overview: DATE, TIME, DATETIME, TIMESTAMP, YEAR & Zero Values #Part2.1 »

1 Comment

  1. Pingback: Storing PHP arrays objects in a file, database – serialize unserialize

Comments are closed.

Sponsors


Recommend on Google

  • RSS Feed
  • Pinterest
  • Google Plus
Download Freebies »»

Recent Projects

  • WP Category Post List version 2 released
  • iTg Sexy Horizontal 1 tier Dropdown menu
  • WP Simple Event Management Plugin by iTg – Manage college fests and events
  • WP Category Post List Plugin – List your posts filtered by categories with thumbnails

Browse more Projects »

WordPress Stuff

  • Add dynamic Login Logout n Site Admin button to WordPress using WP API
  • The concept behind making an Interactive WordPress Custom Page
  • Remove External scripts & styles from WordPress Admin Page
  • Get eForm – WordPress Form Builder at 50% discount
  • Is manual WordPress Migration still relevant?

Browse more WordPress stuff »