mysql datatypes summary

Notes

This is a summary only. For details, gotchas, and version-specific info, refer to your friendly manual.

An item in [] means that it is optional.

An item in <> means that you should substitute some value there.

The most commonly used data types are marked like this.

numeric

decimal

decimal(<precision>, [<scale>]) [zerofill]

For storing floating-point numbers where precision is critical, such as monetary values.

synonyms: dec, numeric

double

double(<size> [<digits>]) [zerofill]

For double-precision floating-point numbers. This is for storing very large floating-point numbers.

synonyms: double precision, real

float

float(<size>, <digits>) [zerofill]

A single-precision floating-point number, 4 bytes. For storing small floating-point numbers.

int

int(<size>) [auto_increment] [unsigned] [zerofill]

A whole number, 4 bytes, with a maximum range of -2,147,483,648 to 2,147,483,647 (unsigned: 0 to 4,294,967, 295)

synonyms: integer

tinyint

tinyint(<size>) [auto_increment] [unsigned] [zerofill]

A whole number, 1 byte, with a maximum range of -128 to 127 (unsigned: 0 to 255)

smallint

smallint(<size>) [auto_increment] [unsigned] [zerofill]

A whole number, 3 bytes, with a maximum range of -32,768 to 32,767 (unsigned: 0 to 65,535)

mediumint

mediumint(<size>) [auto_increment] [unsigned] [zerofill]

A whole number, 3 bytes, with a maximum range of -8,388,608 to 8,388,607,647 (unsigned: 0 to 16,777,215)

bigint

bigint(<size>) [auto_increment] [unsigned] [zerofill]

The largest integer type. A whole number, 8 bytes, with a maximum range that is so big that i don't want to type it. Doubtful that any of us mortals will need to use this data type.

string

Regular text datatypes sort and compare "in a case-insensitive fashion in accordance with the sorting rules for the default character set."
binary datatypes store and compare data strictly byte-by-byte (and so, case-sensitive).

char

char(<size>) [binary]

Fixed length – for storing strings that won't vary much in size.

Range of 0 to 255, stores that amount in bytes

synonyms: character, national char, national character

 
varchar

varchar(<size>) [binary]

Variable length – for storing strings that will vary in size.

Range of 0 to 255, stores that amount in bytes, plus 1 byte

synonyms: character varying, national varchar

 
text

text

Variable-length storage for most text values

Range of 0 to 65,535 characters.

tinytext

tinytext

Variable-length storage for short text values

Range of 0 to 255 characters, like varchar

mediumtext

mediumtext

Variable-length storage for medium-sized text values

Range of 0 to 16,777,215 characters.

longtext

longtext

Variable-length storage for very large text values

Maximum is theoretically more than 4GB, but practically (due to memory available and mysql protocol limits, would be less).

blob

blob

Binary Large OBject.

The binary form of text

Also the tinyblob, mediumblob, and longblob types which correspond to tinytext, mediumtext, and longtext

special_string

Useful when you know exactly what strings you want, and also uses less space in the database because the fields are stored as an index number, not as the strings.

Sorting therefore happens according to the index, not the value of the string.

enum

enum(<value1>, <value2>, ...)

Stores one value of your predefined list of possible strings (value1, value2, ...).

Maximum number of elements is 65,535.

Example: If you define a field as enum('orange', 'mango', 'apple', 'litchee')
then you can put in the field only one of those values:
insert into my_table (fruits) values ('apple');

set

set(<value1>, <value2>, ...)

Similar to enum(), but set() can store more than one value of your predefined list of possible strings.

Maximum number of elements is 64

Example: If you define a field as set('orange', 'mango', 'apple', 'litchee')
then you can put in the field one or more of those values, like:
insert into my_table (fruits) values ('orange, apple');

date

date

date

Format: YYYY-MM-DD
Example: 2006-09-23

Range of years 1000 to 9999

datetime

datetime

Format: YYYY-MM-DD hh:mm:ss
Example: 2006-09-23 01:15:23

Stores date and time from years 1000 to 9999

time

time

Format: hh:mm:ss
Example: 01:15:23

Stores time from midnight (00:00:00) to one second before midnight (23:59:59)

timestamp

timestamp (<size>)

Format: YYYYMMDDhhmmss
Example: 20060923011523

Stores a timestamp from years 1970 to 2037 (the "unix year")

If you insert a NULL value into a timestamp column, the current date and time are inserted instead of the NULL.

If you modify any field in a row that has a timestamp column, the first timestamp column will be automatically updated with the current date and time.

It would be great to have Tibetan and Chinese translations of this page. Can you help? Contact us! http://tibetangeeks.com/contact/