Home > Computer & I'net > MySQL Database Data Types...

Browse by title articles:

What is hot:

MySQL Database Data Types

CREATE TABLE

	Now you see it ...
	How to create an Outlook Express Backup
	THEAD, TFOOT & TBODY
	Functions - Return Values

MySQL Database Data Types

MySQL Database Data Types

by David Stanley

Once you have your table data organized, the next step is to figure out the data type. There are three main types : text, numbers, and Dates/Times. Choosing the column types specifies what information can or can't be stored in a table cell. Using the most correct option for each column is important as it may affect the database's overall performance.

TEXT TYPES

CHAR( )	A fixed section from 0 to 255 characters long.
VARCHAR( )	A variable section from 0 to 255 characters long.
TINYTEXT	A string with a maximum length of 255 characters.
TEXT	A string with a maximum length of 65535 characters.
BLOB	A string with a maximum length of 65535 characters.
MEDIUMTEXT	A string with a maximum length of 16777215 characters.
MEDIUMBLOB	A string with a maximum length of 16777215 characters.
LONGTEXT	A tring with a maximum length of 4294967295 characters.
LONGBLOB	A tring with a maximum length of 4294967295 characters.

The ( ) brackets allow you to enter a maximum number of characters will be used in the column.

VARCHAR(20)

CHAR and VARCHAR are the most widely used types. CHAR is a fixed length string and is mainly used when the data is not going to vary much in it's length. VARCHAR is a variable length string and is mainly used when the data may vary in length.

CHAR may be faster for the database to process considering the fields stay the same length down the column. VARCHAR may be a bit slower as it calculates each field down the column, but it saves on memory space. Which one to ultimatly use is up to you.

Using both a CHAR and VARCHAR option in the same table, MySQL will automatically change the CHAR into VARCHAR for compatability reasons.

BLOB stands for Binary Large OBject. Both TEXT and BLOB are variable length types that store large amounts of data. They are similar to a larger version of VARCHAR. These types can store a large piece of data information, but they are also processed much slower.

NUMBER TYPES

TINYINT( )	-128 to 127 normal 0 to 255 UNSIGNED.
SMALLINT( )	-32768 to 32767 normal 0 to 65535 UNSIGNED.
MEDIUMINT( )	-8388608 to 8388607 normal 0 to 16777215 UNSIGNED.
INT( )	-2147483648 to 2147483647 normal 0 to 4294967295 UNSIGNED.
BIGINT( )	-9223372036854775808 to 9223372036854775807 normal 0 to 18446744073709551615 UNSIGNED.
FLOAT	A small number with a floating decimal point.
DOUBLE( , )	A large number with a floating decimal point.
DECIMAL( , )	A DOUBLE stored as a string , allowing for a fixed decimal point.

The integer types have an extra option called UNSIGNED. Normally, the integer goes from an negative to positive value. Using an UNSIGNED command will move that range up so it starts at zero instead of a negative number.

DATE TYPES

DATE	YYYY-MM-DD.
DATETIME	YYYY-MM-DD HH:MM:SS.
TIMESTAMP	YYYYMMDDHHMMSS.
TIME	HH:MM:SS.

MISC TYPES

ENUM ( )	Short for ENUMERATION which means that each column may have one of a specified possible values.
SET	Similar to ENUM except each column may have more than one of the specified possible values.

ENUM is short for ENUMERATED list. This column can only store one of the values that are declared in the specified list contained in the ( ) brackets.

ENUM('y','n')

You can list up to 65535 values in an ENUM list. If a value is inserted that is not in the list, a blank value will be inserted.

SET is similar to ENUM except SET may contain up to 64 list items and can store more than one choice.

-----------------
Article by David Stanley. Visit his site http://www.htmlite.com. Reprinted with permission.

Articles