mysql数值类型

一、Integer Types (Exact Value) - INTEGER, INT, SMALLINT,TINYINT, MEDIUMINT, BIGINT

Description:
INTEGER[(M)] [UNSIGNED] [ZEROFILL]

分为有符号型和无符号型，默认为有符号型，有符号型要用1bit去存储符号，无符号型则不用。

取值范围如下表所示：

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

 

int(integer)   integer表示显示的宽度：

 

mysql> create table int_t1(
    -> id int zerofill);
Query OK, 0 rows affected (0.02 sec)

mysql> insert into int_t1 values('23232');
Query OK, 1 row affected (0.00 sec)

mysql> select * from int_t1;
+------------+
| id         |
+------------+
| 0000023232 |
+------------+
1 row in set (0.00 sec)

mysql> create table int_t2( id int(6) zerofill);
Query OK, 0 rows affected (0.02 sec)

mysql> insert into int_t2 values('1234');
Query OK, 1 row affected (0.01 sec)

mysql> select * from int_t2;
+--------+
| id     |
+--------+
| 001234 |
+--------+
1 row in set (0.00 sec)

从以上演示可以看出若填充的数值长度不够，则用0显示。

 

二、DECIMAL:

Description:

DECIMAL[(M[,D])] [UNSIGNED] [ZEROFILL]

 

M-------所有位数，可以省略，,默认为10，最大值为65

D------小数部分位数，可以省略，最大值为30

 

examp：DECIMAL(5,2)  column range from-999.99 to 999.99

 

DECIMAL=DECIMAL(10)=DECIMAL(10,0)

 

DECIMAL在mysql中以二进制方式存储，9个数字占4个字节

 

三、BIT(n)  （n为1--64,存贮所需字节数为2的N次方-1）

mysql> create table bit_t2(
    -> id bit(4));
Query OK, 0 rows affected (0.02 sec)

mysql> insert into bit_t2 values(100);
ERROR 1406 (22001): Data too long for column 'id' at row 1
mysql> insert into bit_t2 values(16);
ERROR 1406 (22001): Data too long for column 'id' at row 1
mysql> insert into bit_t2 values(15);
Query OK, 1 row affected (0.00 sec)

mysql> insert into bit_t2 values(b'1110');
Query OK, 1 row affected (0.02 sec)

mysql> insert into bit_t2 values(b'11111');
ERROR 1406 (22001): Data too long for column 'id' at row 1

以上示例说明bit(4)存储的最大十进制数为15，二进制数为b'1111'

mysql> create table bit_t1(
    -> bt bit(10));
Query OK, 0 rows affected (0.02 sec)

mysql> insert into bit_t1 values(0),(1),(2);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from bit_t1;
+------+
| bt   |
+------+
|      |
|     |
|     |
+------+
3 rows in set (0.00 sec)

 

bt+0---->十进制显示

mysql> select bt,bt+0 from bit_t1;
+------+------+
| bt   | bt+0 |
+------+------+
|      |    0 |
|     |    1 |
|     |    2 |
+------+------+
3 rows in set (0.00 sec)

 

bin(bt)---->二进制显示

mysql> select bt,bt+0,bin(bt) from bit_t1;
+------+------+---------+
| bt   | bt+0 | bin(bt) |
+------+------+---------+
|      |    0 | 0       |
|     |    1 | 1       |
|     |    2 | 10      |
+------+------+---------+
3 rows in set (0.02 sec)

mysql> insert into bit_t1 values(b'1111');
Query OK, 1 row affected (0.00 sec)

mysql> select bt,bt+0,bin(bt) from bit_t1;
+------+------+---------+
| bt   | bt+0 | bin(bt) |
+------+------+---------+
|      |    0 | 0       |
|     |    1 | 1       |
|     |    2 | 10      |
|     |   15 | 1111    |
+------+------+---------+
4 rows in set (0.00 sec)

mysql> select * from bit_t1 where bt=1111;
Empty set (0.00 sec)

mysql> select * from bit_t1 where bin(bt)=1111
    -> ;
+------+
| bt   |
+------+
|     |
+------+
1 row in set (0.00 sec)

mysql> delete from bit_t1 where bin(bt)=1111;
Query OK, 1 row affected (0.00 sec)

mysql> select bt,bt+0,bin(bt) from bit_t1;
+------+------+---------+
| bt   | bt+0 | bin(bt) |
+------+------+---------+
|      |    0 | 0       |
|     |    1 | 1       |
|     |    2 | 10      |
+------+------+---------+
3 rows in set (0.00 sec)

 

四、FLOAT, DOUBLE

FLOAT(n)：n最大值为24，当超过24时，会自动变为更高精度类型

example:

mysql> create table flo_t(
    -> id float(10),
    -> id2 float(24),
    -> id3 float(25));
Query OK, 0 rows affected (0.04 sec)

mysql> desc flo_t;
+-------+--------+------+-----+---------+-------+
| Field | Type   | Null | Key | Default | Extra |
+-------+--------+------+-----+---------+-------+
| id    | float  | YES  |     | NULL    |       |
| id2   | float  | YES  |     | NULL    |       |
| id3   | double | YES  |     | NULL    |       |
+-------+--------+------+-----+---------+-------+
3 rows in set (0.01 sec)