SQL Data Types

SQL Numeric Data Types

SQL Date and Time Data Types

SQL Character and String Data Types

Note: These data types are for character streams. They should not be used with Unicode data.

Difference between VARCHAR and TEXT

In MySQL, TEXT can store bigger amounts of data up to a maximum of 4 gigabytes (2³²−1232−1) characters, whereas VARCHAR can store strings of variable length up to 65,535 characters.

A VARCHAR can be part of an index whereas a TEXT field requires you to specify a prefix length, which can be part of an index.

VARCHAR is stored inline with the table (at least for the MyISAM storage engine), making it potentially faster when the size is reasonable. Of course, how much faster depends on both your data and your hardware. Meanwhile, TEXT is stored off table with the table having a pointer to the location of the actual storage.

Using a TEXT column in a sort will require the use of a disk-based temporary table, as the MEMORY (HEAP) storage engine.

VARCHAR and TEXT are both PostgreSQL character data types. VARCHAR(N) is an alias for CHARACTER VARYING(N), a data type that accepts text data. N is a positive integer and represents the maximum number of characters the type can store in length. In other words, a VARCHAR(N) column can store a string of up to N characters. When trying to store a longer string, PostgreSQL raises the following error:

Value too long for type character varying(N)

As required by the SQL standard, if the characters in excess are all spaces, the string will be truncated to the maximum length and no error will be thrown.

Instead, when casting a value to CHARACTER VARYING(N) or VARCHAR(N), the over-length string will simply be truncated to the first N characters without raising an error. Again, this is a rule imposed by the SQL standard. The length N is optional and must be an integer greater than zero and smaller than or equal to 10485760. When N is omitted, VARCHAR is equivalent to TEXT.

TEXT is a data type to store strings of any length. Specifically, it is PostgreSQL's native string data type as most built-in functions operating on strings accept or return TEXT values. Keep in mind that TEXT is not in the SQL standard data type, but most DMBS technologies provide it.

The characters that can be stored in either of these data types are determined by the database character set, which is chosen when creating the database. To avoid slowing down access to columns with short values, very long strings are stored in background tables. In any case, the longest character string that can be stored in VARCHAR or TEXT is about 1 GB.

The way that TEXT and VARCHAR are stored in the database is another difference between them. Given that VARCHAR columns hold data in a variable-length format, the amount of storage space needed for each item depends on how long it is. Contrarily, because TEXT columns are stored in a static-length format, the amount of storage space needed for each value is constant.

Due to these variations, one data type may be better suited for a certain use case than the other. For storing short to medium-length strings like names, locations, and descriptions, VARCHAR is typically a preferable option. For storing greater amounts of textual data, such as articles, comments, and messages, TEXT is a preferable option.

There are performance differences between TEXT and VARCHAR in addition to differences in the volume of data they can contain. VARCHAR typically performs better than TEXT because it needs less storage and provides quicker access to the data. When handling higher amounts of data, this speed benefit may be lost.

SQL Unicode Character and String Data Types

Note: These data types are not supported in MySQL databases.

SQL Binary Data Types

SQL Miscellaneous Data Types