mysql数据类型介绍
一，数值类型
| 整数类型 | 字节 | 最小值 | 最大值 |
|---|---|---|---|
| tinyint | 1 | 有符号-128 无符号 0 |
有符号 127 (2^7-1) 无符号 255 (2^8-1) |
| smallint | 2 | 有符号-32768 无符号 0 |
有符号 32767 (2^15-1) 无符号 65535 (2^16-1) |
| mediumint | 3 | 有符号 -8388608 无符号0 |
有符号 8388608 (2^23-1) 无符号 1677215 (2^24-1) |
| int | 4 | 有符号 -2147483648 无符号 0 |
有符号 2147483648 (2^31-1) 无符号 4294967295 (2^32-1) |
| bigint | 8 | 有符号 -9223372036854775808 无符号 0 |
有符号 9223372036854775808 (2^63-1) 无符号 18446744073709551615 (2^64-1) |
对于整数数据，我们通常在类型名称后面的小括号内制定显示宽度，如int(5)表示当数值宽度小于5位的时候在数字前面填满宽度，如不显示指定宽度则默认为int(11)。一般配合zerofill使用，其作用就是在数字位数不够的空间用字符'0'填充。
好，下面我们通过几个例子来具体看一下整型中的一些需要注意的问题:
(1) 在表t1中创建两个字段status1和status2，指定宽度为tinyint(2)和tinyint(3):
mysql> create table t1(status1 tinyint(2),status2 tinyint(3)); Query OK, 0 rows affected (0.45 sec) mysql> desc t1; +---------+------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------+------------+------+-----+---------+-------+ | status1 | tinyint(2) | YES | | NULL | | | status2 | tinyint(3) | YES | | NULL | | +---------+------------+------+-----+---------+-------+ 2 rows in set (0.00 sec)
在status1和status2中都插入数据1,发现没神么异常
insert into t1(status1,status2)values(1,1); Query OK, 1 row affected (0.06 sec) mysql> select status1,status2 from t1; +---------+---------+ | status1 | status2 | +---------+---------+ | 1 | 1 | +---------+---------+ 1 row in set (0.00 sec)
我们修改status1和status2字段，加入zerofill参数,：
mysql> alter table t1 modify status1 tinyint(2) zerofill; Query OK, 1 row affected (0.99 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql> alter table t1 modify status2 tinyint(3) zerofill; Query OK, 1 row affected (0.95 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql> desc t1; +---------+------------------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------+------------------------------+------+-----+---------+-------+ | status1 | tinyint(2) unsigned zerofill | YES | | NULL | | | status2 | tinyint(3) unsigned zerofill | YES | | NULL | | +---------+------------------------------+------+-----+---------+-------+ 2 rows in set (0.00 sec) mysql> select status1,status2 from t1; +---------+---------+ | status1 | status2 | +---------+---------+ | 01 | 001 | +---------+---------+ 1 row in set (0.00 sec)这时，我们可以看到tinyint(2)和tinyint(3)之间的区别了，他们零填充的宽度不一样。那我们设置了宽度之后，如果插入的数据大于宽度会不会报错呢？答案是：不会。
mysql> insert into t1(status1,status2)values(100,100); Query OK, 1 row affected (0.06 sec) mysql> select status1,status2 from t1; +---------+---------+ | status1 | status2 | +---------+---------+ | 01 | 001 | | 100 | 100 | +---------+---------+ 2 rows in set (0.00 sec)
这里我们设置status1的宽度是2，但并不妨碍我们插入数据100。
注：通过上述测试我们可以知道，在数据库中整型数据类型，在数据精度这个方面来讲，和我们设置的宽度并没有关系，整型的精度是固定的（上表）。
（2） 上面我们在加入zerfill属性的时候，发现自动为该字段添加了unsigned属性。接下来我们来了解一下unsigned属性。
所有的整型都有一个unsigned（无符号）属性，如果需要在字段里保存非负数，或者较大的上限时可以用此属性，他的取值范围是下限取0，上限是原值的2倍，如果我们指定一个列zerofill属性，则会自动添加unsigned属性。
我们创建表t2，创建字段status1 tinyint(3)，插入数据200，发现报错了，因为数据库默认tinyint是有符号的，而有符号tinyint的取值范围为[-128,127]，故越界了。
mysql> create table t2(status1 tinyint(3)); Query OK, 0 rows affected (0.48 sec) mysql> insert into t2(status1)values(200); ERROR 1264 (22003): Out of range value for column 'status1' at row 1
我们在t2中，再添加字段status2 tinyint(3)，并设置无符号属性unsinged，插入数据200，此时发现没有问题，因为tinyint无符号的范围是[0,255]
mysql> alter table t2 add column status2 tinyint(3) unsigned; Query OK, 0 rows affected (0.63 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> insert into t2(status2)values(200); Query OK, 1 row affected (0.07 sec)
(3) auto_increment 自增属性
在需要产生唯一标识符或顺序值时，可以利用这个属性，他只用于整型类型。auto_increment 指一般从1开始，每增加一行加1，在插入null到一个auto_increment列时，mysql会插入一个比该列中当前最大值大1的值。
注：一个表中最多只能有一个auto_increment列，且应该定义为not_null ，并定义primary_key或unique键。
常用的定义方式：(这边直接在表t2中添加一个自增字段，创建表时定义也类似)
mysql> alter table t2 add column id int auto_increment not null primary key; Query OK, 0 rows affected (0.85 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc t2; +---------+---------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +---------+---------------------+------+-----+---------+----------------+ | status1 | tinyint(3) | YES | | NULL | | | status2 | tinyint(3) unsigned | YES | | NULL | | | id | int(11) | NO | PRI | NULL | auto_increment | +---------+---------------------+------+-----+---------+----------------+ 3 rows in set (0.00 sec)
| 浮点数类型 | 字节 | 最小值 | 最大值 |
|---|---|---|---|
| float | 4 | +- 1.175494351E-38 | +-3.402823466E+38 |
| double | 8 | +-2.225073858072014E-308 | +- 1.7976931348623157E+308 |
| 定点数类型 | 字节 | 描述 |
|---|---|---|
| dec(m,d), decimal(m,d) |
m+2 | 最大值范围和double相同，给定decimal的有效取值范围由m和d决定 |
对于小数，有两种方式：浮点数和定点数。浮点数包括float(单精度)和double（双精度），而定点数则只有decimal，定点数在mysql内部以字符串形式存放，比浮点数更精确，适合用来表示货比等精度高的数据。
浮点数和定点数都可以名称后加(m,d)来表示，m（精度）表示一共显示几位，d（标度）表示小数点后面的位数，但一般我们对于浮点数类型，不进行这样的定义，他在数据库迁移的时候可能会出现一些问题。float和double在不指定精度的时候，默认会按照实际的精度（有实际的硬件和操作系统决定）来显示，而decimal在不指定精度时，默认整数位是10，小数位为0
好，下面我们来看一下decimal的定义吧：
在表t2中添加字段sum decimal(5,2)，并添加数据1.23，运行没有问题:
mysql> alter table t2 add column sum decimal(5,2); Query OK, 0 rows affected (0.89 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> select * from t2; +---------+---------+----+------+ | status1 | status2 | id | sum | +---------+---------+----+------+ | NULL | 200 | 1 | NULL | +---------+---------+----+------+ 1 row in set (0.00 sec) mysql> update t2 set sum =1.23 where id=1; Query OK, 1 row affected (0.07 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from t2; +---------+---------+----+------+ | status1 | status2 | id | sum | +---------+---------+----+------+ | NULL | 200 | 1 | 1.23 | +---------+---------+----+------+ 1 row in set (0.00 sec)
接着我们插入一条数据1.234,这时我们看到有一个警告，并且1.234损失了精度，变成了1.23
mysql> insert into t2(sum)values(1.234); Query OK, 1 row affected, 1 warning (0.06 sec) mysql> select * from t2; +---------+---------+----+------+ | status1 | status2 | id | sum | +---------+---------+----+------+ | NULL | 200 | 1 | 1.23 | | NULL | NULL | 3 | 1.23 | +---------+---------+----+------+ 2 rows in set (0.00 sec)
| 位类型 | 字节 | 最小值 | 最大值 |
|---|---|---|---|
| bit(m) | 1~8 | bit(1) | bit(64) |
位类型用来存放位字段，m范围为1~64，默认为1。
好，下面我们来看几个例子：
（1）创建delflag bit(1)字段，插入数据1，发现当我们用select * 查询的时候，得不到插入的数据，用bin()函数可以得到值，个人认为：数据库插入bit类型字段后，在取出时并没有直接为我们转换成十进制，而像整型，浮点型都是给我们自动转换了，所以会出现这种情况。
mysql> alter table t2 add column delflag bit(1); Query OK, 0 rows affected (0.70 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc t2; +---------+---------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +---------+---------------------+------+-----+---------+----------------+ | status1 | tinyint(3) | YES | | NULL | | | status2 | tinyint(3) unsigned | YES | | NULL | | | id | int(11) | NO | PRI | NULL | auto_increment | | sum | decimal(5,2) | YES | | NULL | | | delflag | bit(1) | YES | | NULL | | +---------+---------------------+------+-----+---------+----------------+ 5 rows in set (0.00 sec) mysql> select * from t2; +---------+---------+----+------+---------+ | status1 | status2 | id | sum | delflag | +---------+---------+----+------+---------+ | NULL | 200 | 1 | 1.23 | NULL | | NULL | NULL | 3 | 1.23 | NULL | +---------+---------+----+------+---------+ 2 rows in set (0.00 sec) mysql> update t2 set delflag=1 where id=1; Query OK, 1 row affected (0.04 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select delflag from t2; +---------+ | delflag | +---------+ | | | NULL | +---------+ 2 rows in set (0.00 sec) mysql> select bin(delflag) from t2; +--------------+ | bin(delflag) | +--------------+ | 1 | | NULL | +--------------+ 2 rows in set (0.00 sec)
（2）我们向delflag字段插入2，发现插入失败，因为越界了，bit(1)最多只能存储到1
mysql> update t2 set delflag=2 where id=1; ERROR 1406 (22001): Data too long for column 'delflag' at row 1
二，日期，时间类型
| 日期和时间类型 | 字节 | 最小值 | 最大值 | 零值表示 |
|---|---|---|---|---|
| date | 4 | 1000-01-01 | 9999-12-31 | 0000-00-00 |
| datetime | 8 | 1000-01-01 00:00:00 | 9999-12-31 23:59:59 | 0000-00-00 00:00:00 |
| timestamp | 4 | 19700101080001 | 2038年的某个时刻 | 00000000000000 |
| time | 3 | -838:59:59 | 838:59:59 | 00:00:00 |
| year | 1 | 1901 | 2155 | 0000 |
（1） date,time ,datetime是三种最常用的日期类型
下面就直接举几个例子吧：
创建表t3, 字段d date,t time,dt datetime，并用now()函数插入当前日期:
mysql> create table t3(d date,t time,dt datetime); Query OK, 0 rows affected (0.34 sec) mysql> desc t3; +-------+----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+----------+------+-----+---------+-------+ | d | date | YES | | NULL | | | t | time | YES | | NULL | | | dt | datetime | YES | | NULL | | +-------+----------+------+-----+---------+-------+ 3 rows in set (0.01 sec) mysql> insert into t3 values(now(),now(),now()); Query OK, 1 row affected, 1 warning (0.08 sec) mysql> select d,t,dt from t3; +------------+----------+---------------------+ | d | t | dt | +------------+----------+---------------------+ | 2016-09-27 | 07:11:36 | 2016-09-27 07:11:36 | +------------+----------+---------------------+ 1 row in set (0.00 sec)
可以发现datetime是date和time的组合。
（2） year 年份
他主要用来记录只需要存年份的数据，比date更节省空间。
mysql> alter table t3 add column y year; Query OK, 0 rows affected (0.93 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc t3; +-------+----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+----------+------+-----+---------+-------+ | d | date | YES | | NULL | | | t | time | YES | | NULL | | | dt | datetime | YES | | NULL | | | y | year(4) | YES | | NULL | | +-------+----------+------+-----+---------+-------+ 4 rows in set (0.00 sec) mysql> insert into t3(y)values(now()); Query OK, 1 row affected (0.04 sec) mysql> select y from t3; +------+ | y | +------+ | NULL | | 2016 | +------+ 2 rows in set (0.00 sec)
(3) datestamp
如果需要经常插入或更新日期为当前系统时间，则通常使用datestamp，timestamp值返回后显示为“YY-MM-DD HH:MM:SS”格式的字符串，显示宽度为19个字符串，如果想要获得数字值，则应在timestamp列添加"+0"。
在表t3中添加字段ts timestamp:
mysql> alter table t3 add column ts timestamp; Query OK, 0 rows affected (0.80 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc t3; +-------+-----------+------+-----+-------------------+-----------------------------+ | Field | Type | Null | Key | Default | Extra | +-------+-----------+------+-----+-------------------+-----------------------------+ | d | date | YES | | NULL | | | t | time | YES | | NULL | | | dt | datetime | YES | | NULL | | | y | year(4) | YES | | NULL | | | ts | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP | +-------+-----------+------+-----+-------------------+-----------------------------+ 5 rows in set (0.00 sec)
我们看到系统自动给了这个字段一个默认值，current_timestamp(系统日期)，我们插入null 看看结果：
mysql> insert into t3(ts) values(null); Query OK, 1 row affected (0.05 sec) mysql> select * from t3; +------------+----------+---------------------+------+---------------------+ | d | t | dt | y | ts | +------------+----------+---------------------+------+---------------------+ | 2016-09-27 | 07:11:36 | 2016-09-27 07:11:36 | NULL | 2016-09-27 07:33:41 | | NULL | NULL | NULL | 2016 | 2016-09-27 07:33:41 | | NULL | NULL | NULL | NULL | 2016-09-27 07:36:01 | +------------+----------+---------------------+------+---------------------+ 3 rows in set (0.00 sec)
发现插入了这条数据的系统日期（最后一次更改这天数据的时间）。
timestamp还有一个重要的特点，就是和时区相关。当插入日期的时候，会先转换为本地时区，而从数据库取出时同样会转换为本地时区，这样两个不同时区的用户看到的同一日期可能不同。
下面我们来看一下差别：
a, 首先，创建表t4，字段 tm1(datestamp),tm2(date)
mysql> create table t4(tm1 timestamp not null default current_timestamp,tm2 datetime default null); Query OK, 0 rows affected (0.51 sec) mysql> desc t4; +-------+-----------+------+-----+-------------------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-----------+------+-----+-------------------+-------+ | tm1 | timestamp | NO | | CURRENT_TIMESTAMP | | | tm2 | datetime | YES | | NULL | | +-------+-----------+------+-----+-------------------+-------+ 2 rows in set (0.00 sec)
b,然后我们来看一下当前时区：
mysql> show variables like 'time_zone'; +---------------+--------+ | Variable_name | Value | +---------------+--------+ | time_zone | SYSTEM | +---------------+--------+ 1 row in set (0.03 sec)可以发现，时区值为system，这个默认值和主机的时区值是是一致的，我们在中国所以是东八区
c，用now（）插入当前时间
mysql> insert intot4(tm1,tm2)values(now(),now()); ERROR 1146 (42S02): Table 'test1.intot4' doesn't exist mysql> insert into t4(tm1,tm2)values(now(),now()); Query OK, 1 row affected (0.06 sec) mysql> select tm1,tm2 from t4; +---------------------+---------------------+ | tm1 | tm2 | +---------------------+---------------------+ | 2016-09-28 19:07:37 | 2016-09-28 19:07:37 | +---------------------+---------------------+ 1 row in set (0.00 sec)这时我们发现两个值一样，下面我们来修改一下时区：
mysql> set time_zone='+9:00'; Query OK, 0 rows affected (0.00 sec) mysql> select tm1,tm2 from t4; +---------------------+---------------------+ | tm1 | tm2 | +---------------------+---------------------+ | 2016-09-28 20:07:37 | 2016-09-28 19:07:37 | +---------------------+---------------------+ 1 row in set (0.00 sec)
当我们将时区改为东九区的时候，发现两个时间差了一小时
三，字符串类型
| 字符串类型 | 字节 | 描述及存储需求 |
|---|---|---|
| char(m) | m | m为0~255之间的整数 |
| varchar(m) | m为0~65535之间的整数，值的长度+1字节 | |
| tinyblob | 允许长度0~255字节，值的长度+1字节 | |
| blob | 允许长度0~65535字节，值的长度+2字节 | |
| mediumblob | 允许长度0~16772150字节，值的长度+3字节 | |
| longblob | 允许长度0~4294967295字节，值得长度+4 | |
| tinytext | 允许长度0~255字节，值的长度+2字节 | |
| text | 允许长度0~65535字节，值的长度+2字节 | |
| mediumtext | 允许长度0~167772150字节，值的长度+3个字节 | |
| longtext | 允许长度0~4294967295字节，值的长度+4字节 | |
| varbinary(m) | 允许长度0～m个字节的变长字节字符串，值的长度+1个字节 | |
| binary(m) | m | 允许长度0～m个字节的定长字节字符串 |
（1） char 和varchar类型
区别： 第一，char 列的长度固定，为创建表时声明的长度，长度可以为0~255的任何值，而varchar列中的值为可变长字符串。第二，char列删除了尾部的空格，而varchar保留这些空格。
注：可变与不可变解释：CHAR的长度是固定的，varchar长度是可以变化的，固定与可变是针对存储介质（硬盘）来说的。假如：CHAR和varchar的默认长度都设为10，两个字段都分别写入“abc”，CHAR 损耗了硬盘10字节 = “abc”长度 + 7个空字符,varchar损耗了硬盘 3字节 = “abc”长度，设定默认值n（假如是 10 ） 则该字段内能写入的字符串长度最大只能为 10。
mysql> create table t5(v varchar(5),c char(5));
Query OK, 0 rows affected (0.39 sec)
mysql> insert into t5(v,c)values('ab ','ab ');
Query OK, 1 row affected (0.06 sec)
mysql> select length(v),length(c) from t5;
+-----------+-----------+
| length(v) | length(c) |
+-----------+-----------+
| 4 | 2 |
+-----------+-----------+
1 row in set (0.00 sec)
（2） binary和varbinary类型
binary和varbinary类似于char和varchar,不同的是他们包含二进制字符串而不包含非二进制字符串。
mysql> create table t6(c binary(3));
Query OK, 0 rows affected (0.36 sec)
mysql> desc t6;
+-------+-----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-----------+------+-----+---------+-------+
| c | binary(3) | YES | | NULL | |
+-------+-----------+------+-----+---------+-------+
1 row in set (0.00 sec)
mysql> insert into t6(c)values('a');
Query OK, 1 row affected (0.06 sec)
mysql> select c,hex(c),c='a',c='a\0',c='a\0\0' from t6;
+------+--------+-------+---------+-----------+
| c | hex(c) | c='a' | c='a\0' | c='a\0\0' |
+------+--------+-------+---------+-----------+
| a | 610000 | 0 | 0 | 1 |
+------+--------+-------+---------+-----------+
1 row in set (0.00 sec)
当c='a\0\0'时，显示的是1（表示和数据库数据相同了），即说明binary值在最后通过填充零字节以达到指定字段定义的长度。
四，enum 枚举类型
枚举类型的值范围需要在创建表的时候通过枚举方式显示指定，对于1~255个成员的枚举需要1个字节存储，对于255~65535个成员，需要2个字节存储，最多允许有65535个成员。
好，下面我们创建一张表t7，字段 enum('m','f')，即含有m和f的枚举类
mysql> create table t7(e enum('m','f'));
Query OK, 0 rows affected (0.38 sec)
mysql> desc t7;
+-------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------------+------+-----+---------+-------+
| e | enum('m','f') | YES | | NULL | |
+-------+---------------+------+-----+---------+-------+
1 row in set (0.01 sec)
mysql> insert into t7(e) values('M'),('1'),('F'),(null),('f');
Query OK, 5 rows affected (0.06 sec)
Records: 5 Duplicates: 0 Warnings: 0
mysql> select t from t7;
ERROR 1054 (42S22): Unknown column 't' in 'field list'
mysql> select e from t7;
+------+
| e |
+------+
| m |
| m |
| f |
| NULL |
| f |
+------+
5 rows in set (0.00 sec)
通过上面的例子，我们可以看到：
第一，enum是忽略大小写的，如第三个数据我们插入的是F，结果在数据库存的是我们定义的小写f
第二，像‘1’，‘2’，1,2虽然没有定义，但他会表示如1和‘1’会插入m，'2'和2则插入f，即他们在枚举类对应位置的值，但当插入3的时候就会报错
第三，当插入没有定义的枚举类型时，会报错。
五，set 集合
set和enum类型非常类似，也是一个字符串对象（包括插入不在集合内数据会报错都类似），里面可以包含0~64个成员。根据成员的不同，存储上也有所不同。
1~8成员的集合，占1个字节
9~16成员的集合，占2个字节
17~24成员的集合，占3个字节
25~32成员的集合，占4个字节
33~64成员的集合，占8字节
和enum区别 ： set和enum除了存储之外，最大的区别在于，set类型一次可以选取多个成员，而enum只能选择一个。
mysql> alter table t7 modify s set('a','b','c','d');
Query OK, 2 rows affected (1.24 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> desc t7;
+-------+----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------------------+------+-----+---------+-------+
| e | enum('m','f') | YES | | NULL | |
| s | set('a','b','c','d') | YES | | NULL | |
+-------+----------------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> select * from t7;
+------+------+
| e | s |
+------+------+
| m | NULL |
| m | NULL |
+------+------+
2 rows in set (0.00 sec)
mysql> update t7 set s=('a,b') where e='m';
Query OK, 2 rows affected (0.05 sec)
Rows matched: 2 Changed: 2 Warnings: 0
mysql> select * from t7;
+------+------+
| e | s |
+------+------+
| m | a,b |
| m | a,b |
+------+------+
2 rows in set (0.00 sec)
- 上一篇：MySQL系列教程mySQL集群
- 下一篇：Hadoop1中Task运行过程
相关文章
图文推荐
- 文章
- 推荐
- 热门新闻