Reference:SQL/Data Types
From CoderGuide
Contents |
MySQL/SQL Data Types
Note: Not all of these may be present in your SQL server, but they are in MySQL. Other names may be valid as well. For instance, MySQL will treat "LONG VARCHAR" as the MEDIUMTEXT type. Your server may have other types. Consult your manual.
Numeric Types:
Integers:
If you remember from Math, integers are whole numbers. These may either be signed or unsigned values. The default is signed integers. Pay close attention to the valid range of these data types.
| Type Name | Data type | Signed Range | Unsigned Range |
|---|---|---|---|
| TINYINT | an 8-bit integer | -128 to 127 | 0 to 255 |
| SMALLINT | 16-bit integer | -32,768 to 32,767 | 0 to 65,535 |
| MEDIUMINT | 24-bit integer | -8,388,608 to 8,388,607 | 0 to 16,777,215 |
| INT | 32-bit integer | -2,147,483,648 to 2,147,483,647 | 0 to 4,294,967,295 |
| BIGINT | 64-bit integer | -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807 | 0 to 18,446,744,073,709,551,615 |
Values taken from dev.mysql.com. MySQL supports the use of INT(number) to specify the maximum expected display width of the type-- IT DOES NOT EFFECT THE STORAGE RANGE OF THE TYPE. Also, if you wish to zero pad your number, you can use add ZEROFILL. For example, if you use INT(4) ZEROFILL, and the value is 4, MySQL wil display 0004.
Other numeric types:
| Name | Data Type |
|---|---|
| FLOAT | (see note) a floating point number. The exact range varies from architecture and compilers. This generally would be the same range as the Float type in C/C++ for the compiler that was used to compile MySQL/WhateverSQL on your system. |
| DOUBLE | (see note)another floating point number type, with more significant digits than FLOAT. The last digit is estimated, and is always either 0 or even (traditionally, though this can vary between systems). if you had 27.23 as a double, the software would store it as 27.230. If you ran out of significant digits, then you'll see the effects of double precision. |
| DECIMAL | Stores a number up to 65 digits in length, can be constrained with DECIMAL(M,D), where M is the total number of digits allowed, and D is the number allowed to the right of the decimal point. MySQL 5.1 stores DECIMAL values as binary data, earlier versions store it as an ASCII string. D and M may or may not be required. MySQL versions prior to 3.23.6 require both, while versions after will use zero for D if it's omitted, and ten for M if it is omitted. |
| NUMERIC | Essentially the same as DECIMAL |
| BIT | A bit field type. BIT(b), where b indicates the number of bits. Default is 1. Bit fields are binary data, specified bt b'bits'. Exampe: b'1001' |
Note: Float and Double types should never be used to store money values, as these number types are approximates, and not the actual value. Either used fixed point integers (i.e. 10000 would be $100.00), or use DECIMAL which stores a number as a string in MySQL versions prior to 5.1. The ideal method is to store the number as a bigint fixed point type.
Character/Text types
| Type Name | Data type |
|---|---|
| CHAR | fixed length string, left padded with spaces. Trailing spaces are stripped off on retrieval. |
| VARCHAR | variable length string that uses no more memory than required. MySQL currently strips off trailing spaces when the string is stored, which is different from the ANSI SQL standard. |
| TINYTEXT | for storing text/string (254 bytes) |
| TEXT | also for storing text (64 Kilobytes) |
| MEDIUMTEXT | for storing more text (16 Megabytes) |
| LONGTEXT | for storing a lot of text (4 Gigabytes) |
| ENUM | An Enumeration type. Columns may only be assigned to the values specified in the enumeration. In other words, ENUM('a','b','Zippidy-Doo-Day') would only allow for the field to contain "a","b", or "Zippidy-Do-Day", anything else would result in an error. Another more common example would be ENUM('T','F'), or ENUM('M','F') |
| SET | For storing a set of information. It's similar to ENUM, except that the field may contain any, or all predefined values. These sets are separated by commas.So, to set all the values for SET("Alpha","Beta","Gamma") use the string 'Alpha,Beta,Gamma'. |
Storing Binary Data
The BLOB (Binary Large OBject) type is used for storing binary data. It is similar to the TEXT type, except the data is treated as binary, not text. Both TEXT and BLOB types have the same storage limits.
| Type Name | Data Type | Storage Size |
|---|---|---|
| TINYBLOB | For storing binary data | 28-1 bytes (254 bytes) |
| BLOB | For storing binary data | 216-1 bytes (64 Kilobytes) |
| MEDIUMBLOB | For storing binary data | 224-1 bytes (16 Megabytes) |
| LONGBLOB | For storing binary data | 232-1 bytes (4 Gigabytes) |
BLOB types can not be assigned a default value.
Date/Time types
For the purpose of the table below:
CC -- Century (so, if the year was 1990, 19 would be CC)
YY -- Year (if the year was 1990, 90 would be YY)
MM -- The month
DD -- The day of the month
hh -- Hours
mm -- Minutes
ss -- Seconds
| Type Name | Data Type |
|---|---|
| DATE | Date in CCYY-MM-DD |
| TIME | Time in hh:mm:ss |
| DATETIME | Date/Time in CCYY-MM-DD hh:mm:ss |
| TIMESTAMP | Date/Time in CCYYMMDDhhmmss |
| YEAR | The year in CCYY |
Other Types
| Type Name | Data Type |
|---|---|
| For true/false values. The ISO standard sets this as being an optional type, and there is some debate on how to handle NULL values (ISO allows for NULL/UNKNOWN). Currently BOOLEAN type is supported by MySQL but maps these values to 1 for true and 0 for false, PostgreSQL follows the ISO standard except doesn't allow for the UNKNOWN literal. BOOLEAN is not supported by Microsoft SQL (MSSQL), DB2, or Oracle. |
