MySQL supports various data types like
i) numeric
ii) Date and times
iii) Text/Binary
iv) Data Features
Numeric data types consists of integers (whole numbers), fixed point types , floating point types , bit values.
Whole Numbers :-
TINYINT, SMALLINT, MEDIUMINT, INT, or BIGINT.
- These require 8, 16, 24, 32, and 64 bits of storage space.
- Can store values from −2(N–1) to 2(N–1)–1 for signed and 0 to 2(N)–1 for unsigned.
where N is the number of bits of storage space they use.
- Integer types can optionally have the UNSIGNED attribute,
Storage and usage of various integer types,
Type
|
Storage
|
Minimum Value
|
Maximum Value
|
(Bytes)
|
(Signed/Unsigned)
|
(Signed/Unsigned)
| |
TINYINT
|
1
|
-128
|
127
|
0
|
255
| ||
SMALLINT
|
2
|
-32768
|
32767
|
0
|
65535
| ||
MEDIUMINT
|
3
|
-8388608
|
8388607
|
0
|
16777215
| ||
INT
|
4
|
-2147483648
|
2147483647
|
0
|
4294967295
| ||
BIGINT
|
8
|
-9223372036854775808
|
9223372036854775807
|
0
|
18446744073709551615
|
(* 1 Byte - 8 Bits)
What is INT(m)?
‘m’ determines the display width. The display width does not constrain the range of values that can be stored in the column. Nor does it prevent values wider than the column display width from being displayed correctly.
We often think why we provide int(10) and int(11) in some tables.
The reason is int(10) should be assigned for unsigned data type and int(11) for signed data type 10 digits for the number and 1 for sign (+/-) ex (-1234567890) / (+1234567890) .
MySQL will assign the display width of its own based on the data type declaration ie. signed or unsigned.
Note : If no sign is specified mysql will create with signed data type for integers.
Ex:
Below are two tables created using signed and unsigned integer type.
CREATE TABLE `tbl_one` (
`id` int signed NOT NULL AUTO_INCREMENT,
`name` varchar(50) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB;
CREATE TABLE `tbl_two` (
`id` int unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(50) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB;
What is the length of each integer type?
Each integer data type can store a certain length of data.
Type
|
Digits
|
TINYINT
|
3
|
SMALLINT
|
5
|
MEDIUMINT
|
7
|
INT
|
10
|
BIGINT
|
19
|
On any integer data type column (not an primary/unique key) if a record is inserted exceeding the maximum value mysql will throw an warning and the last maximum value supported by the data type will be inserted to the table.
For Ex:
Below is the table structure which has tiny int.
CREATE TABLE `tbl_tiny` (
`id` tinyint(4) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
This is an signed data type column. It can store a maximum value of 127.
If any value more than 127 is inserted mysql will throw an warning and it will insert the maximum value for that query.
From above we can see values greater than 127 ie, 128/1000 were thrown with warnings and maximum value was inserted to the table .
It applies the same for all integer data types.
For a primary/unique key column.
Primary key column,
CREATE TABLE `tbl_tiny` (
`id` tinyint signed NOT NULL AUTO_INCREMENT,
`name` varchar(50) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB;
mysql> show create table tbl_tiny\G
*************************** 1. row ***************************
Table: tbl_tiny
Create Table: CREATE TABLE `tbl_tiny` (
`id` tinyint(4) NOT NULL AUTO_INCREMENT,
`name` varchar(50) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=127 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
mysql> select count(*) from tbl_tiny;
+----------+
| count(*) |
+----------+
| 126 |
+----------+
1 row in set (0.00 sec)
mysql> insert into tbl_tiny (name) values ('bad');
ERROR 1062 (23000): Duplicate entry '127' for key 'PRIMARY'
mysql>
Unique key column,
mysql> show create table tbl_tiny2\G
*************************** 1. row ***************************
Table: tbl_tiny2
Create Table: CREATE TABLE `tbl_tiny2` (
`id` tinyint(4) NOT NULL,
`name` varchar(50) NOT NULL,
UNIQUE KEY `id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
mysql> select count(*) from tbl_tiny2;
+----------+
| count(*) |
+----------+
| 126 |
+----------+
1 row in set (0.00 sec)
mysql> insert into tbl_tiny2 (id,name) values (127,'bad');
Query OK, 1 row affected (0.01 sec)
mysql> insert into tbl_tiny2 (id,name) values (128,'bad');
ERROR 1264 (22003): Out of range value for column 'id' at row 1
mysql>
mysql> insert into tbl_tiny2 (id,name) values (1280,'bad');
ERROR 1264 (22003): Out of range value for column 'id' at row 1
mysql>
mysql> select count(*) from tbl_tiny2;
+----------+
| count(*) |
+----------+
| 127 |
+----------+
1 row in set (0.00 sec)
So data types has to be mentioned based on our requirement.
