MySQL Data Types

JavaScript Editor Js editor     Website development 



Main Page

Previous Page
Next Page

MySQL Data Types

Selecting the proper column type for your tables is key to a successful database. Tables B.7, B.8, and B.9 define the different string, number, and other types you can use, along with how much space they will take up on the server's hard drive. When choosing a type for each column, you should use the most efficient (i.e., the most size-frugal) data type in terms of the largest possible value for the column.

Table B.7. Here are most of the available numeric column types for use with MySQL databases. For FLOAT, DOUBLE, and DECIMAL, the Length argument is the maximum total number of digits, and the Decimals argument dictates the number of that total to be found after the decimal point. (As of MySQL 5.0.3, the size of DECIMAL column is based upon a formula.)

MySQL Numeric Types

Type

Size

Description

TINYINT[Length]

1 byte

Range of 128 to 127 or 0 to 255 unsigned.

SMALLINT[Length]

2 bytes

Range of 32,768 to 32,767 or 0 to 65535 unsigned.

MEDIUMINT[Length]

3 bytes

Range of 8,388,608 to 8,388,607 or 0 to 16,777,215 unsigned.

INT[Length]

4 bytes

Range of 2,147,483,648 to 2,147,483,647 or 0 to 4,294,967,295 unsigned.

BIGINT[Length]

8 bytes

Range of 9,223,372,036,854,775,808 to 9,223,372,036,854,775,807 or 0 to 18,446,744,073,709,551,615 unsigned.

FLOAT[Length, Decimals]

4 bytes

A small number with a floating decimal point.

DOUBLE[Length, Decimals]

8 bytes

A large number with a floating decimal point.

DECIMAL[Length, Decimals]

Length + 1 or Length + 2 bytes

A DOUBLE with a fixed decimal point.


Table B.8. Here are the most common column types for storing text in a MySQL database.

MySQL Text Types

Type

Size

Description

CHAR[Length]

Length bytes

A fixed-length field from 0 to 255 characters long.

VARCHAR(Length)

String length + 1 or 2 bytes

A fixed-length field from 0 to 255 characters long (65,535 characters long as of MySQL 5.0.3).

TINYTEXT

String length + 1 bytes

A string with a maximum length of 255 characters.

TEXT

String length + 2 bytes

A string with a maximum length of 65,535 characters.

MEDIUMTEXT

String length + 3 bytes

A string with a maximum length of 16,777,215 characters.

LONGTEXT

String length + 4 bytes

A string with a maximum length of 4,294,967,295 characters.

BINARY[Length]

Length bytes

Similar to CHAR but stores binary data.

VARBINARY[Length]

Data length + 1 bytes

Similar to VARCHAR but stores binary data.

TINYBLOB

Data length + 1 bytes

Stores binary data with a maximum length of 255 bytes.

BLOB

Data length + 2 bytes

Stores binary data with a maximum length of 65,535 bytes.

MEDIUMBLOB

Data length + 3 bytes

Stores binary data with a maximum length of 16,777,215 bytes.

LONGBLOB

Data length + 4 bytes

Stores binary data with a maximum length of 4,294,967,295 bytes.

ENUM

1 or 2 bytes

Short for enumeration, which means that each column can have one of several possible values.

SET

1, 2, 3, 4, or 8 bytes

Like ENUM except that each column can have more than one of several possible values.


Table B.9. These are the available date and time column types for MySQL.

MySQL Date and Time Types

Type

Size

Description

DATE

3 bytes

In the format of YYYY-MM-DD

DATETIME

8 bytes

In the format of YYYY-MM-DD HH:MM:SS

TIMESTAMP

4 bytes

In the format of YYYYMMDDHHMMSS; acceptable range ends in the year 2037

TIME

3 bytes

In the format of HH:MM:SS

YEAR

1 byte

In the format of YYYY, with a range from 1901 to 2155


Further, I should mention that MEDIUMINT, SET, ENUM, as well as the different-sized BLOB and TEXT column types are all MySQL-specific extensions of the SQL defaults. Finally, when it comes to defining columns, remember that any column type can be NULL or NOT NULL, integers can be UNSIGNED, and any number can be ZEROFILL. A column can also be defined as having a DEFAULT value, should a value not otherwise be supplied for it.


Previous Page
Next Page


JavaScript Editor Js editor     Website development


©