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	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');`

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.

All content not copyright by anyone else is copyright © 2003–2010 James Walker.
License for use is the GNU Free Documentation License. Find it:
here in the License directory
or at the Free Software Foundation, www.fsf.org

This content is copyright to the author stated on the page.

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