SQL: Numeric Data types

Numeric types are used to store integer, real values. Below table summarizes all the integer types supported by SQL.

 

Data Type	Signed Range	Unsigned Range
tinyint	−128 to 127	0 to 255
smallint	−32,768 to 32,767	0 to 65,535
mediumint	−8,388,608 to 8,388,607	0 to 16,777,215
int	−2,147,483,648 to 2,147,483,647	0 to 4,294,967,295
bigint	−2^63 to 2^63 - 1	0 to 2^64 - 1

 

How to specify unsigned integer?

Using ‘unsigned’ keyword, you can specify unsigned integer.

 

Example

CREATE TABLE t1(

         a int,

         b int unsigned

);

 

mysql> CREATE TABLE t1(
    -> a int,
    -> b int unsigned
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> 
mysql> DESCRIBE t1;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| a     | int          | YES  |     | NULL    |       |
| b     | int unsigned | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

 

Real Data types

Below table summarizes real data types.

 

Data type	Description
float(size, d)	Size specifies the total number of allowable digits both to the left and to the right of the decimal point and the number of digits after the decimal point is specified in the d parameter.
double(size, d)	Size specifies the total number of allowable digits both to the left and to the right of the decimal point and the number of digits after the decimal point is specified in the d parameter.

 

Can I specify unsigned floating point types?

Yes

 

mysql> CREATE TABLE t2(
    -> a int,
    -> b int unsigned,
    -> c float(10, 5),
    -> d float(10, 5) unsigned
    -> );
Query OK, 0 rows affected, 3 warnings (0.02 sec)

mysql> 
mysql> DESCRIBE t2;
+-------+----------------------+------+-----+---------+-------+
| Field | Type                 | Null | Key | Default | Extra |
+-------+----------------------+------+-----+---------+-------+
| a     | int                  | YES  |     | NULL    |       |
| b     | int unsigned         | YES  |     | NULL    |       |
| c     | float(10,5)          | YES  |     | NULL    |       |
| d     | float(10,5) unsigned | YES  |     | NULL    |       |
+-------+----------------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

 

  

Previous                                                    Next                                                    Home