On this page:
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.
decimal(<precision>, [<scale>]) [zerofill]
For storing floating-point numbers where precision is critical, such as monetary values.
double(<size> [<digits>]) [zerofill]
For double-precision floating-point numbers. This is for storing very large floating-point numbers.
float(<size>, <digits>) [zerofill]
A single-precision floating-point number, 4 bytes. For storing small floating-point numbers.
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)
tinyint(<size>) [auto_increment] [unsigned] [zerofill]
A whole number, 1 byte, with a maximum range of -128 to 127 (unsigned: 0 to 255)
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(<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(<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.
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).
Fixed length – for storing strings that won't vary much in size.
Range of 0 to 255, stores that amount in bytes
Variable length – for storing strings that will vary in size.
Range of 0 to 255, stores that amount in bytes, plus 1 byte
Variable-length storage for most text values
Range of 0 to 65,535 characters.
Variable-length storage for short text values
Range of 0 to 255 characters, like
Variable-length storage for medium-sized text values
Range of 0 to 16,777,215 characters.
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).
Binary Large OBject.
The binary form of
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(<value1>, <value2>, ...)
Stores one value of your predefined list of possible strings (value1, value2, ...).
Maximum number of elements is 65,535.
If you define a field as
set(<value1>, <value2>, ...)
Maximum number of elements is 64
If you define a field as
Range of years 1000 to 9999
Stores date and time from years 1000 to 9999
Stores time from midnight
Stores a timestamp from years 1970 to 2037 (the "unix year")
If you insert a NULL value into a
If you modify any field in a row that has a
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/