mySQL Data Types
There are quite a few different data types that can be used with mySQL databases. Selecting the most appropriate data type for each field will ensure that the data is stored in the most efficient manner and can also be accessed efficiently.
We'll consider the various data types in groups since where it isn't obvious which exact data type to choose you will most likely be considering those in a small group.
Integer Data Types
- bigint - between -9223372036854775808 and 9223372036854775807 if signed or between 0 and 18446744073709551615 if unsigned.
- int - between -2147483648 and 2147483647 if signed or between 0 and 4294967295 if signed.
- mediumint - between -8388608 and 8388607 if signed and between 0 and 16777215 if signed.
- smallint - between -32768 and 32767 if signed or between 0 and 65535 if unsigned.
- tinyint - between -128 and 127 if signed or between 0 and 256 if unsigned.
Decimal Data Types
- decimal or numeric - the only mon-integer numeric data type that maintains precision (in earlier versions of mySQL these were strings rather than floating point). You specify the number of significant digits and the number of decimal places and the number is stored in a way that ensures that the exact value is maintained.
Floating Point Data Types
- double or real - between -1.7976931348623157e+308 and -2.225070738585072014e-308,0, and between 2.225070738585072014e-308 and 1.7976931348623157e+308. Note that numbers are converted to the closest binary equivalent.
- float - between -3.402823466e+38 and -1.175494351e-38,0, and between 1.175494351e-38 and 3.402823466e+38. Note that numbers are converted to the closest binary equivalent.
String Data Types
- longtext - theoretically can store up to 4294967295 characters of text.
- mediumtext - can store up to 16777215 characters of text.
- text - can store up to 65535 characters of text.
- tinytext - can store up to 255 characters of text.
- varchar - can store up to 65535 characters of text (255 characters in older versions) provided it fits within the maximum row length.
Note that the char data types are stored within the table while text data types are stored separately and referenced from the table.
Binary Data Types
- binary - stores the specified number of bytes between 1 and 255.
- blob - can store up to 65535 bytes.
- longblob - theoretically can store up to 4294967295 bytes.
- mediumblob - can store up to 16777215 bytes.
- tinyblob - can store up to 255 bytes.
- varbinary - can store up to 65535 bytes provided it fits within the maximum row length.
Note that the binary data types are stored within the table while blob data types are stored separately and referenced from the table.
Date Data Types
- date - stores the year, month and day where the year is between 1000 and 9999.
- datetime - as for date but also stores hours, minutes and seconds.
- time - stores hours, minutes and seconds.
- timestamp - used to track table modifications, by inserting NULL into the field the current date and time is recorded, also whenever the row is updated the current date and time is automatically stored in the first datetime field. Typically you would have two such fields, the first for last updated and the second for created.
- year - store a four digit year between 1900 and 2155 or a 2 digit year between 1970 and 2069.
- bit - stores a bitmap of up to 7 or 8 bits.
- enum - do not use.
- set - do not use.
This article written by Stephen Chapman, Felgall Pty Ltd.