首页 > > 网络编程 > Mysql >

MySQL之数据类型

2018-06-18 00:14:55来源：未知 阅读 ()

新老客户大回馈,云服务器低至5折

MySQL提供了多种数据类型，主要包括数值型、字符串型和日期时间类型。本次博客就来谈谈MySQL中常用的数据类型吧（版本：mysql-5.7.19）！

 

数值类型

MySQL支持所有标准SQL中数值类型，具体见下表：

 数值类型是由范围的，如果超出数值的范围就会发生“out of range”的错误提示，所以在选择数值类型的时候，一定要结合实际的情况去选择。

 

 1.整数类型

对于整数类型的数值类型还支持在小括号内指定显示的宽度，例如int(5)就表示当数值宽度小于5位的时候，在数字前面填满宽度，如果不指定显示的宽度，默认为int(11)。
举例： 
mysql> create table t1(num1 int(5),num2 int);
Query OK, 0 rows affected (0.04 sec)

mysql> desc  t1;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| num1  | int(5)  | YES  |     | NULL    |       |
| num2  | int(11) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
2 rows in set (0.01 sec)
mysql> insert into t1 values(1,1);
Query OK, 1 row affected (0.00 sec)
mysql> select * from t1;
+------+------+
| num1 | num2 |
+------+------+
|    1 |    1 |
+------+------+
1 row in set (0.00 sec)
以上例子会发现当不指定显示宽度的时候，默认为int(11)。
下面介绍一个小知识点，也就是zerofill，就是在数字位数不够的空间用字符“0”填满。
mysql> alter table t1 modify num1 int(5) zerofill;
Query OK, 1 row affected (0.07 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> alter table t1 modify num2 int zerofill;
Query OK, 1 row affected (0.08 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> select * from t1;
+-------+------------+
| num1  | num2       |
+-------+------------+
| 00001 | 0000000001 |
+-------+------------+
1 row in set (0.00 sec)

当为一个列指定为zerofill的时候，则MySQL自动为该列添加无符号属性，那么就可以取得较大的上限值，可以见前面的表格。

mysql> insert into t1  values(111111,1);
Query OK, 1 row affected (0.00 sec)

mysql> select * from t1;
+--------+------------+
| num1   | num2       |
+--------+------------+
|  00001 | 0000000001 |
| 111111 | 0000000001 |
+--------+------------+
2 rows in set (0.00 sec)
mysql> desc t1;
+-------+---------------------------+------+-----+---------+-------+
| Field | Type                      | Null | Key | Default | Extra |
+-------+---------------------------+------+-----+---------+-------+
| num1  | int(5) unsigned zerofill  | YES  |     | NULL    |       |
| num2  | int(10) unsigned zerofill | YES  |     | NULL    |       |
+-------+---------------------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

如上面的例子，即使我们插入的数值为111111，也没有报错，且默认的显示长度变为了10，这里一定要注意的是，括号内的只是显示长度，无符号的int的最大值为4294967295，我们来测试一下就知道了：

mysql> insert into t1 values(4294967295,4294967295);
Query OK, 1 row affected (0.00 sec)

mysql> select * from t1;
+------------+------------+
| num1       | num2       |
+------------+------------+
|      00001 | 0000000001 |
|     111111 | 0000000001 |
| 4294967295 | 4294967295 |
+------------+------------+
3 rows in set (0.00 sec)

mysql> insert into t1 values(4294967296,4294967295);
ERROR 1264 (22003): Out of range value for column 'num1' at row 1
mysql> insert into t1 values(4294967295,4294967296);
ERROR 1264 (22003): Out of range value for column 'num2' at row 1

当数值超过4294967295的时候就会报错了。

整数类型还有另外一个属性：anto_increment，在需要产生唯一的标识符的时候，可以利用这个属性，这个属性只属于整数类型，默认情况下，anto_increment的值从1开始，每次增加一，一个表中最多只能有一个auto_incerment列，对于任何想要使用auto_incerment的列，应该定义为not null，并且定义为primary key，定义方式如下：

create table tablename(id int auto_increment not null primary key);

举例：

mysql> create table t2(id int auto_increment not null primary key,name varchar(10));
Query OK, 0 rows affected (0.04 sec)

mysql> insert into t2(name) values('Frank'),('Rose');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from t2;
+----+-------+
| id | name  |
+----+-------+
|  1 | Frank |
|  2 | Rose  |
+----+-------+
2 rows in set (0.00 sec)

mysql> desc t2;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| name  | varchar(10) | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

 

 2.小数类型

对于小数类型，MySQL定义了两种方式：浮点类型和定点类型，浮点类型也可以使用(M,D)这种用法，但是这是不标准的用法，不建议这么做，浮点类型往往在存储较长的小数的时候，由于硬件的限制，可能导致数值的不精确。decimal是以字符串的形式存储的，比较适合较长的小数存储，默认是以decimal(10,0)来进行操作的。
举例： 
mysql> create table t3(num1 float(4,2),num2 double(4,2),num3 decimal(4,2));
Query OK, 0 rows affected (0.43 sec)

mysql> insert into t3(num1,num2,num3) values(1.26,1.26,1.26);
Query OK, 1 row affected (0.00 sec)

mysql> select * from t3;
+------+------+------+
| num1 | num2 | num3 |
+------+------+------+
| 1.26 | 1.26 | 1.26 |
+------+------+------+
1 row in set (0.00 sec)
mysql> insert into t3(num1,num2,num3) values(1.266,1.266,1.266);
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> show warnings;
+-------+------+-------------------------------------------+
| Level | Code | Message                                   |
+-------+------+-------------------------------------------+
| Note  | 1265 | Data truncated for column 'num3' at row 1 |
+-------+------+-------------------------------------------+
1 row in set (0.00 sec)

mysql> select * from t3;
+------+------+------+
| num1 | num2 | num3 |
+------+------+------+
| 1.26 | 1.26 | 1.26 |
| 1.27 | 1.27 | 1.27 |
+------+------+------+
2 rows in set (0.00 sec)
当小数位超过指定的标度的时候，就会进行四舍五入的操作，但是decimal列会出现告警的信息，浮点类型则不会。
这里将例子的数值类型都改为默认的情况再存储值： 
mysql> alter table t3 modify num1 float;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table t3 modify num2 double;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table t3 modify num3 decimal;
Query OK, 4 rows affected, 4 warnings (0.12 sec)
Records: 4  Duplicates: 0  Warnings: 4
mysql> desc t3;
+-------+---------------+------+-----+---------+-------+
| Field | Type          | Null | Key | Default | Extra |
+-------+---------------+------+-----+---------+-------+
| num1  | float         | YES  |     | NULL    |       |
| num2  | double        | YES  |     | NULL    |       |
| num3  | decimal(10,0) | YES  |     | NULL    |       |
+-------+---------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
mysql> select * from t3;  #会发现浮点型存储的值没有发生改变，而decimal的直接就被截断了，因为浮点型如果不写精度和标度，则会安装实际精度显示。
+------+------+------+
| num1 | num2 | num3 |
+------+------+------+
| 1.26 | 1.26 |    1 |
| 1.27 | 1.27 |    1 |
| 1.27 | 1.27 |    1 |
| 1.27 | 1.27 |    1 |
+------+------+------+
4 rows in set (0.00 sec)

 

 3.位类型

 用于存放位字段值，bit(M)可以存放多位二进制，M的范围为1-64，对于位字段，直接使用select是看不到结果的，可以使用bin()或者hex()函数读取。

mysql> create table t4(id bit(1));
Query OK, 0 rows affected (0.04 sec)

mysql> desc t4;
+-------+--------+------+-----+---------+-------+
| Field | Type   | Null | Key | Default | Extra |
+-------+--------+------+-----+---------+-------+
| id    | bit(1) | YES  |     | NULL    |       |
+-------+--------+------+-----+---------+-------+
1 row in set (0.00 sec)

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

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

mysql> select bin(id),hex(id) from t4;
+---------+---------+
| bin(id) | hex(id) |
+---------+---------+
| 1       | 1       |
+---------+---------+
1 row in set (0.00 sec)

我们来试试插入数值2：

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

出错了，因为2的二进制码是"10"，必须有2bit才能存储，修改过后查看：

mysql> alter table t4 modify id bit(2);
Query OK, 1 row affected (0.08 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> insert into t4  values(2);
Query OK, 1 row affected (0.00 sec)

mysql> select bin(id),hex(id) from t4;
+---------+---------+
| bin(id) | hex(id) |
+---------+---------+
| 1       | 1       |
| 10      | 2       |
+---------+---------+
2 rows in set (0.00 sec)

 

 字符串类型

 MySQL提供了多种字符数据的存储类型，具体见下表：

 

 1.char和varchar类型

char和varchar主要是用来存储较短的字符串，二者的主要区别在于存储方式的不同，char列的长度为创建表时声明的长度，而varchar列中的值为可变长字符串。在检索的时候，char会删除尾部的空格，而varchar则保留这些空格。

举个例子：

mysql> create table space(s1 varchar(4),s2 char(4));
Query OK, 0 rows affected (0.04 sec)

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

mysql> select length(s1),length(s2) from space;
+------------+------------+
| length(s1) | length(s2) |
+------------+------------+
|          4 |          2 |
+------------+------------+
1 row in set (0.00 sec)

根据以上例子可以看到，char列在检索的时候已经被删除了。

对比：

char存储定长的数据很方便且索引的效率很好，比如定义了char(4)，那么不论你的数据是否达到了4个字节，都要占用4个字节的1空间；

varchar，存储变长的数据，但存储的效率没有char高且检索的效率也会低很多，varchar类的实际长度+1，原因是这一个字节用于保存实际使用了多大的长度。

总结：从节约空间的角度去看使用varchar更好，但是如果从存储效率和索引效率上来看，char更合适。

 

 2.enum类型

enum也叫枚举类型，它的值范围需要在创建表时通过枚举方式显示指定，最多允许有65535个成员：

举例：

mysql> create table t6(g enum('green','red','blue','yellow'));
Query OK, 0 rows affected (0.04 sec)

mysql> insert into t6 values('Green'),('red'),('hello');
ERROR 1265 (01000): Data truncated for column 'g' at row 3
mysql>
mysql> insert into t6 values('Green'),('red'),('blue'),(NULL);
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select * from t6;
+-------+
| g     |
+-------+
| green |
| red   |
| blue  |
| NULL  |
+-------+
4 rows in set (0.00 sec)
从上面的例子可以看出，enum类型是忽略大小写的，且在插入超出范围内的值会报错。枚举类型值允许从集合中取出单个值，不能一次性取多个值。
 

3.set类型

 set和enum类似，里面最多可以有64个成员。但是set和enum不同的之处就是，set一次可以选取多个成员。

mysql> create table t7(s set('green','red','blue','yellow'));
Query OK, 0 rows affected (0.04 sec)
mysql> desc t7;
+-------+------------------------------------+------+-----+---------+-------+
| Field | Type                               | Null | Key | Default | Extra |
+-------+------------------------------------+------+-----+---------+-------+
| s     | set('green','red','blue','yellow') | YES  |     | NULL    |       |
+-------+------------------------------------+------+-----+---------+-------+
1 row in set (0.00 sec)

mysql>
mysql> insert into t7 values('Green,red'),('yellow,blue,red');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql>
mysql> select * from t7;
+-----------------+
| s               |
+-----------------+
| green,red       |
| red,blue,yellow |
+-----------------+
2 rows in set (0.00 sec)
mysql> insert into t7 values('yellow,red,red');
Query OK, 1 row affected (0.00 sec)

mysql> select * from t7;
+-----------------+
| s               |
+-----------------+
| green,red       |
| red,blue,yellow |
| red,yellow      |
+-----------------+
3 rows in set (0.00 sec)

通过以上例子可以看出，set也是不区分大小写的，如果在插入信息的有重复的值，将只取一次。成员范围外的值在插入表的时候会报错。

 

时间和日期类型 

 MySQL有很多的数据类型用于表示时间和日期，见下表：

 

 每一个时间和日期的数据类型都有其范围，如果超出其范围，系统会提示错误，并以零值来存储。不同日期的零值的表示如下表：

 

 1.date、time和datetime

最常见的三种时间和日期的数据类型了。 

举例：

mysql> create table t8(d date,t time,dt datetime);
Query OK, 0 rows affected (0.04 sec)

mysql> insert into t8 values(now(),now(),now()); #使用now函数插入当前的时间和日期
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> select * from t8;
+------------+----------+---------------------+
| d          | t        | dt                  |
+------------+----------+---------------------+
| 2017-09-17 | 21:06:59 | 2017-09-17 21:06:59 |
+------------+----------+---------------------+
1 row in set (0.00 sec)

通过上面的例子可以看出，datetime是date和time的组合。


 2.timestamp

 timestamp也是用来表示日期和时间的，但是和datetime是有所不同的，我们一起看一下吧！

mysql> create table t1(ts timestamp);
Query OK, 0 rows affected (0.03 sec)

mysql> desc t1;
+-------+-----------+------+-----+-------------------+-----------------------------+
| Field | Type      | Null | Key | Default           | Extra                       |
+-------+-----------+------+-----+-------------------+-----------------------------+
| ts    | timestamp | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+-------+-----------+------+-----+-------------------+-----------------------------+
1 row in set (0.00 sec)
mysql> insert into t1 values(null);
Query OK, 1 row affected (0.00 sec)
mysql> select * from t1;
+---------------------+
| ts                  |
+---------------------+
| 2017-09-17 21:23:59 |
+---------------------+
1 row in set (0.00 sec)

系统给timestamp自动创建了默认值CURRENT_TIMESTAMP（系统时间），当插入了一个null的时候，t1中正确的插入当前的系统时间。一个表中只允许存在一个timestamp字段。

timestamp还有一个重要的特性就是与时区相关：

mysql> create table t2 (ts timestamp,t datetime);
Query OK, 0 rows affected (0.04 sec)

mysql> show variables like 'time_zone';  #查看当前的时区
+---------------+--------+
| Variable_name | Value  |
+---------------+--------+
| time_zone     | SYSTEM |
+---------------+--------+
1 row in set, 1 warning (0.01 sec)

mysql> insert into t2 values(now(),now());
Query OK, 1 row affected (0.00 sec)

mysql> select * from t2;
+---------------------+---------------------+
| ts                  | t                   |
+---------------------+---------------------+
| 2017-09-17 21:31:15 | 2017-09-17 21:31:15 |
+---------------------+---------------------+
1 row in set (0.00 sec)

SYSTEM为当前的时区，也就东八区，现在将时区改为东九区试试：

mysql> set time_zone='+9:00';
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t2;
+---------------------+---------------------+
| ts                  | t                   |
+---------------------+---------------------+
| 2017-09-17 22:31:15 | 2017-09-17 21:31:15 |
+---------------------+---------------------+
1 row in set (0.00 sec)

结果发现使用timestamp比使用datetime快一个小时，这一点是需要注意的，另外，timestamp的最大取值也就到2038年的某有天，所以不建议存放比较久远的日期和时间。

 

 3.year

 year主要是用来记录年份，当时间只需要记录年的时候，year比date更节省空间，year的范围为1901-2155年。

mysql> create table t3 (y year);
Query OK, 0 rows affected (0.04 sec)

mysql> desc t3;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| y     | year(4) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
1 row in set (0.00 sec)

mysql> insert into t3 values(2017);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t3 values(20);
Query OK, 1 row affected (0.00 sec)

mysql> select * from t3;
+------+
| y    |
+------+
| 2017 |
| 2020 |
+------+
2 rows in set (0.00 sec)
通过以上例子可以发现，year也支持两位字符串表示年份：范围为"00"到"99"： 
"00" ~ "69" 范围被转换成 2000 ~ 2069年
"70" ~ "99" 范围被转换成 1970 ~ 1999年

 

 总结：在学习MySQL的时候，应该对数据类型的用途、占用空间、表示范围等都要有一定的了解，这样才能在实际运用中选择适合的数据类型，用较小的存储空间换来较高的数据库的性能。

 

 参考书籍：《深入浅出MySQL》，写的非常不错，推荐！

标签：

版权申明：本站文章部分自网络，如有侵权，请联系：west999com@outlook.com
特别注意：本站所有转载文章言论不代表本站观点，本站所提供的摄影照片，插画，设计作品，如需使用，请与原作者联系，版权归原作者所有

上一篇：数据库系统概论(2)——Chap. 2 关系数据库基础

下一篇：关于Navicat Premium中即使是成功的执行了一条Sql语句仍然报错的

相关文章

IDC资讯： 主机资讯 注册资讯 托管资讯 vps资讯 网站建设

网站运营： 建站经验 策划盈利 搜索优化 网站推广 免费资源

网站联盟： 联盟新闻 联盟介绍 联盟点评 网赚技巧

行业资讯： 搜索引擎 网络游戏 电子商务 广告传媒

网络编程： Asp.Net编程 Asp编程 Php编程 Xml编程 Access Mssql Mysql 其它

服务器技术： Web服务器 Ftp服务器 Mail服务器 Dns服务器 安全防护

软件技巧： 其它软件 Word Excel Powerpoint Ghost Vista QQ空间 QQ FlashGet 迅雷

网页制作： FrontPages Dreamweaver Javascript css photoshop fireworks Flash

程序设计： Java技术 C/C++ VB delphi

网络知识： 网络协议 网络安全 网络管理 组网方案 Cisco技术

操作系统： Win2000 WinXP Win2003 Mac OS Linux FreeBSD

热门词条
最新资讯
热门关注
热门标签