MySQL data types: numeric
MySQL supports 2 types of numbers:
- Integers with no fractional parts
- Floating-point or fixed-point numbers which may have fractional parts
Integers can be specified in either hexadecimal or decimal format. Hexadecimal values are treated as strings by default and can’t be used in scientific notation. Scientific notation is indicated by an integer or floating-point number followed by an ‘e’ or ‘E’ and then either an ‘-’ or a ‘+’, for example:
1.34E+12 or 43.27e-1
Any number can be expressed as a positive or negative value by having a ‘-’ or ‘+’ sign in front of it. Floating-point numbers can be put into integer columns but will be rounded to the nearest integer. Similarly, integers can be put into in floating or fixed-point columns and will be given a fractional part of zero.
Choosing numeric data types
Decide on the range of values needed to represent your data then select the smallest type that will do the job. This will save storage space and improve processing efficiency.
If strict mode is enabled, an error will occur when entering values out of the range. Out of range values will be truncated if strict mode is not enabled. Truncation is determined by the range of the data type and not its display width. Floating-point and fixed-point columns are rounded to the number of decimals specified.
Numeric data types
|
Type name |
Meaning |
|
TINYINT |
Very small integer |
|
SMALLINT |
Small integer |
|
MEDIUMINT |
Medium-sized integer |
|
INT |
Standard integer |
|
BIGINT |
Large integer |
|
FLOAT |
Single-precision floating-point number |
|
DOUBLE |
Double-precision floating-point number |
|
DECIMAL |
Fixed-point number |
|
BIT |
Bit field |
Integers
Include:
- TINYINT
- SMALLINT
- MEDIUMINT
- INT
- BIGINT
These vary in the values they represent as well as the storage space needed. Integers can be UNSIGNED (only positive) which shifts the range upwards and begins at 0. When defining the integer column you can optionally include a display size (from 1 to 255). This is the number of characters needed to display the integer and does not affect the storage in any way. If no size is given, a default (size of longest integer for that type) size is assigned.
Floating-point and fixed-point
Floating-point types include:
- Float
- Double - also know as DOUBLE PRECISION
Fixed-point includes:
- Decimal - also known as NUMERIC and FIXED
There is a positive maximum and minimum range as well as a negative maximum and minimum range for floating-point types. Both floating-point and fixed-point types can be defined as UNSIGNED (only positive). The range is not shifted upward, only positive values are now allowed. They both also allow you to indicate the number of significant digits (M - a value from 1 to 255) and decimal places (D - a value from 0 to 30) to display. These are optional:
- FLOAT and DOUBLE - Your hardware then determines the precision of the display
- DECIMAL - If D omitted it defaults to 0, if M omitted it defaults to 10
The DECIMAL values are stored as strings and have a fixed number of decimals which means that they are not rounded off (good for currency values). This applies only to storage and retrieval as calculations can use floating-point operations.
BIT data type
Bit data types hold bit-field values. You can include an optional column width when defining a BIT column.
Numeric data type attributes
- UNSIGNED - Disallows negative values. Can be used with all types except BIT. In INTEGERS it shifts the range up, starting at zero (does not increase the range). This effectively doubles your range. With FLOATING-POINT and FIXED-POINT the range is not shifted up. The upper limit remains the same but the negative range is eliminated.
- SIGNED - Can apply to all numeric types but has no effect because it is the default anyway.
- ZEROFILL - For all numeric types except BIT. It pads the displayed value with zeros to the make up the display width. Specifying ZEROFILL automatically makes the column UNSIGNED.
- AUTO_INCREMENT - Only for integers and only one per column per table. The column must be defined as NOT NULL and must be indexed and also be UNSIGNED. Used to generate unique identifiers beginning at 1 (unless you insert NULL in the column) and increases by 1 for each new row. The storage engine determines whether the deleted rows AUTO_INCREMENTED values will be used or not.
- NULL/NOT NULL - The default is NULL.
Useful information
|
Type specification |
Range |
|
TINYINT[(M)] |
Signed:-128 to 127; Unsigned: 0 - 255 |
|
SMALLINT[(M)] |
Signed: -32768 to 32767; Unsigned: 0 to 65535 |
|
MEDIUMINT[(M)] |
Signed: -8388608 to 8388607; Unsigned: 0 to 16777215 |
|
INT[(M)] |
Signed: -2147683648 to 2147483647: Unsigned: 0 to 4294967295 |
|
BIGINT[(M)] |
Signed: -9223372036854775808 to 9223372036854775807; Unsigned 0 to 18446744073709551615 |
|
FLOAT[(M,D)] |
Min non-zero: ? 1.175494351E-38; Max non-zero: ? 3.402823466E+38 |
|
DOUBLE[(M,D)] |
Min non-zero: ? 2.2250738585072014E-308; Max non-zero: ? 1.7976931348623157E+308 |
|
DECIMAL([M[,D]]) |
Varies -depends on values of M and D |
|
BIT[(M)] |
|
|
Type specification |
Storage required |
|
TINYINT[(M)] |
1 Byte |
|
SMALLINT[(M)] |
2 Bytes |
|
MEDIUMINT[(M)] |
3 Bytes |
|
INT[(M)] |
4 Bytes |
|
BIGINT[(M)] |
8 Bytes |
|
FLOAT[(M,D)] |
4 Bytes |
|
DOUBLE[(M,D)] |
8 Bytes |
|
DECIMAL([M[,D]]) |
M + 2 Bytes |
|
BIT[(M)] |
Varies - depending on M |
Other data types
- String data types
- Date and time data types
- Spatial data types
These will be covered in later articles.
Sorry, there are no related posts but check these out
Comments
Pingback from MySQL general security guidelines. Part 1 | Independent Digital
Time: February 29, 2008, 10:31 am
[...] both string data values as well numeric data. Use single quotes around the numeric [...]
Pingback from Independent Digital » MySQL: query optimization
Time: June 5, 2007, 3:20 pm
[...] use the shortest possible data types (have a look at the articles: MySQL: choosing data types and MySQL data types: numeric) for the columns. This will result in smaller tables and shorter indexes that will process faster. [...]