Data Types

In previous examples, you may have noticed that three of the table’s fields were given the data type of VARCHAR, and one was given the type CHAR. The term VARCHAR stands for VARiable length CHARacter string and the command takes a numeric value that tells MySQL the maximum length allowed to a string stored in this field.
 
This data type is very useful, as MySQL can then plan the size of databases and perform lookups and searches more easily. The downside is that if you ever attempt to assign a string value longer than the length allowed, it will be truncated to the maximum length declared in the table definition.
 
The year field, however, has more predictable values, so instead of VARCHAR we use the more efficient CHAR(4) data type. The parameter of 4 allows for four bytes of data, supporting all years from -999 to 9999. You could, of course, just store two-digit values for the year, but if your data is going to still be needed in the following century, or may otherwise wrap around, it will have to be sanitized first—much like the “millennium
bug” that would have caused dates beginning on January 1, 2000, to be treated as 1900 on many of the world’s biggest computer installations.
 
Both CHAR and VARCHAR accept text strings and impose a limit on the size of the field. The difference is that every string in a CHAR field has the specified size. If you put in a smaller string, it is padded with spaces. A VARCHAR field does not pad the text; it lets the size of the field vary to fit the text that is inserted. But VARCHAR requires a small amount of overhead to keep track of the size of each value. So CHAR is slightly more efficient if the sizes are similar in all records, whereas VARCHAR is more efficient if sizes can vary a lot and get large. In addition, the overhead causes access to VARCHAR data to be slightly slower than to CHAR data.

The CHAR data type

Following table lists the CHAR data types. All these types offer a parameter that sets the maximum (or exact) length of the string allowed in the field. As the table shows, each type also has a built-in maximum.

Data type Bytes used Examples
CHAR(n) Exactly n (n<255) CHAR(5) "Hello" uses 5 bytes CHAR(57) “New York” uses 57 bytes
VARCHAR(n) Up to n (<= 65535) VARCHAR(100) “Greetings” uses 9 bytes VARCHAR(7) “Morning” uses 7 bytes

The BINARY data type

The BINARY data type is used for storing strings of full bytes that do not have an associated character set. For example, you might use the BINARY data type to store a GIF image.

Data type Bytes used Examples
BINARY(n) or BYTE(n) Exactly n (n<255)

As CHAR but contains binary data

VARBINARY(n) Up to n (<= 65535) As VARCHAR but contains binary data

The TEXT and VARCHAR data types

The differences between TEXT and VARCHAR are small:
• Prior to version 5.0.3, MySQL would remove leading and trailing spaces from VARCHAR fields.
• TEXT fields cannot have default values.
• MySQL indexes only the first n characters of a TEXT column (you specify n when you create the index).
What this means is that VARCHAR is the better and faster data type to use if you need to search the entire contents of a field. If you will never search more than a certain number of leading characters in a field, you should probably use a TEXT data type.

Data type Bytes used Examples
TINYTEXT(n) Up to n (<= 255) Treated as a string with a character set
TEXT(n) Up to n (<= 65535) Treated as a string with a character set
MEDIUMTEXT(n) Up to n (<= 16777215) Treated as a string with a character set
LONGTEXT(n) Up to n (<= 4294967295) Treated as a string with a character set

The BLOB data type

The term BLOB stands for Binary Large OBject and therefore, as you would think, the BLOB data type is most useful for binary data in excess of 65,536 bytes in size. The main other difference between the BLOB and BINARY data types is that BLOBs cannot have default values see following Table.

Data type Bytes used Examples
TINYBLOB(n) Up to n (<= 255) Treated as binary data—no character set
BLOB(n) Up to n (<= 65535) Treated as binary data—no character set
MEDIUMBLOB(n) Up to n (<= 16777215) Treated as binary data—no character set
LONGBLOB(n) Up to n (<= 4294967295) Treated as binary data—no character set

Numeric data types

MySQL supports various numeric data types from a single byte up to double-precision floating-point numbers. Although the most memory that a numeric field can use up is eight bytes, you are well advised to choose the smallest data type that will adequately handle the largest value you expect. Your databases will be small and quickly accessible.
 
Following Table lists the numeric data types supported by MySQL and the ranges of values they can contain. In case you are not acquainted with the terms, a signed number is one with a possible range from a negative value, through zero, to a positive one, and an unsigned one has a value ranging from zero to a positive one. They can both hold the same number of values—just picture a signed number as being shifted halfway to the left so that half its values are negative and half are positive. Note that floating-point values (of any precision) may only be signed.

Data type Bytes used Minimum Value (Signed/Unsigned) Maximum Value (Signed/Unsigned)
TINYINT 1

-128

0

127 255
SMALLINT 2 -327680 32767 65535
MEDIUMINT 3 −8388608
0
8388607
16777215
INT or INTEGER 4 −2147483648
0
2147483647
4294967295
BIGINT 8 −9223372036854775808
0
9223372036854775807
18446744073709551615
FLOAT 4

−3.402823466E+38 (no unsigned)

3.402823466E+38
(no unsigned)
DOUBLE or REAL 8 -1.7976931348623157E+308
(no unsigned)
1.7976931348623157E+308
(no unsigned)

To specify whether a data type is signed or unsigned, use the UNSIGNED qualifier. The following example creates a table called tablename with a field in it called fieldname of the data type UNSIGNED INTEGER:

CREATE TABLE tablename (fieldname INT UNSIGNED);

When creating a numeric field, you can also pass an optional number as a parameter, like this:

CREATE TABLE tablename (fieldname INT(4));

But you must remember that, unlike BINARY and CHAR data types, this parameter does not indicate the number of bytes of storage to use. It may seem counterintuitive, but what the number actually represents is the display width of the data in the field when it is retrieved. It is commonly used with the ZEROFILL qualifier like this:

CREATE TABLE tablename (fieldname INT(4) ZEROFILL);

What this does is cause any numbers with a width of less than four characters to be padded with one or more zeros, sufficient to make the display width of the field four. characters long. When a field is already of the specified width or greater no padding takes place.