3、MySQL的数据类型介绍

上一章节内容主要介绍了一些MySQL中对表和库的增删改查操作，对于上一章中对与表中字段的一些定义进行说明，本章主要介绍关于数据库类型的一些定义。

一、概述：

MySQL数据库中的数据大致可以分为以下几类，从而实现数据库在操作的时候对不同类型的处理。对数据库分类的优点主要有以下几种：

1、使系统能够根据数据类型来操作数据

2、预防数据运算的时候出错

通过分类，可以将每个类型不特定的行为联系在一起执行这些行为时，数据分类将有效避免不同类型数据之间的错误，做场见的是字符串数字相加。

3、更见有效的利用空间。

4、定义数据类型实际是定义表中的列

二、数据库类型的分类

数据库的分类可以分为数值类型，字符型，日期型、文本型TEXT及复合型。

2.1 数值型

2.1.1 整型

tinyint(-127~128之间的整数)

smallint（短整型：(-32768~32767)数值占用2字节， 用带符号的二进制整数形式存储）

mediumint

auto_increment(自增长类型)

row num

int (整型默认为11个字节)

bigint(大整型)

Population

举例：

创建表包含以上类型，且每个字段中均输入99999999数值，并查看

mysql> insert into mydata values(99999999,999999999,99999999,999999999,999999999);
ERROR 1264 (22003): Out of range value for column 'id1' at row 1
提示id1，报错
mysql> select * from mydata;
+------+--------+--------+-----------+-----------+
| id1  | id2    | id3    | id4       | id5       |
+------+--------+--------+-----------+-----------+
|  127 | -32768 |  65535 | 999999999 | 999999999 |
|  127 | -32768 | 655356 | 999999999 | 999999999 |
+------+--------+--------+-----------+-----------+

在MySQL5.5及以上版本之后各个字段类型数字超出长度限制则均不能创建成功；

mysql> insert into mydata(id1) values('NIHAO');
ERROR 1366 (HY000): Incorrect integer value: 'NIHAO' for column 'id1' at row 1

类型不同的数据也限制执行插入操作

类型直接均可使用int(n)这里的n则是对整型数据的长度进行限制，n的最大值为255

2.2 整型修饰符：

unsigned 无符号整数，修饰符：规定字段只能保存正的数据。其可以增大这个字段正整数的支持范围。

zerofill 规定0填充(默认是空格)填补输出的值，使用这个值可以防止MySQL存储负值。

例如：超时会员卡编号

No 0000000000000001
mysql> create table mydata2(id int(10) unsigned zerofill);
Query OK, 0 rows affected (0.14 sec)

mysql> insert into mydata2 values(1);
Query OK, 1 row affected (0.01 sec)
mysql> select * from mydata2;
+------------+
| id         |
+------------+
| 0000000001 |
+------------+
1 row in set (0.00 sec)
mysql>

2.2 浮点型数值

float(单精度)，double(双精度)，decimal(精度确定)

float和dobule是浮点数，数值越大与不精确

Decimal是定点数，常用来存储财务工资方面，具备高精度

float(5,2):表示字段有限长度是5位，其中整数部分为3位，小数部分为2位，小数点超出部分则执行四舍五入，其占用4个字节

double：8个字节村粗

decimal：用来存储精度的小数，消耗空间，运算较慢。可以使用bigint替代。

8762435.543 mm=x*1000 = 8762435543
select mm/1000 from tb_name;
mysql> create table mydata3(tb float(4,1),td double(5,2),tc decimal(5,2));
Query OK, 0 rows affected (0.09 sec)
mysql> insert into mydata3 values(45.6789,45.6789,45.6789);
Query OK, 1 row affected, 1 warning (0.08 sec)
mysql> select * from mydata3;
+------+-------+-------+
| tb   | td    | tc    |
+------+-------+-------+
| 45.7 | 45.68 | 45.68 |
+------+-------+-------+
1 row in set (0.00 sec)
mysql>

三、字符串类型

3.1字符串类型比较

char(n)

n:存储固定长度，在括号中自定长度其最大值为255；

varchar(n)

n:存储变长，在括号内指定最大长度其最大值为255，如果大于255则需要使用TEXT类型

TEXT

TEXT类型存放最大长度为65535个字符串。

3.2 三种类型优缺点：

char较varchar占用空间，但效率高

char：固定长度，插入数据必须小于指定的长度否则自动截断(适用于mysql版本低于5.5的版本，大等于该版本的将不能插入数据)。

varchar:长度动态，会导致数据迁移增加IO压力

字符串类型修饰符：

binary:区分字符大小写。

举例：

mysql> create table mychar1(ca char(10),va varchar(10));
mysql> insert into mychar1 values('1234567890111','1234567890111');
ERROR 1406 (22001): Data too long for column 'ca' at row 1
字符过长无法插入报错

不同存储引擎使用不同用法：

MyISAM表:建议使用char类型

InnoDB表：由于其数据内部存储格式对固定长度的数据行和可变长度的数据行不加区分，素有使用varchar可以减少磁盘空间用量和尖山磁盘I/O。

TEXT类型：

TEXT类型及其子类型用于存储比较长的非二进制字符串：

TEXT不区分大小写

四、复合类型

ENUM：枚举类型，只能取一个

SET：set类型可以取多个

例子：

Query OK, 1 row affected (0.00 sec)

mysql> create table mychar(sex enum('M','W'));
Query OK, 0 rows affected (0.11 sec)
mysql> insert into mychar values('M');
Query OK, 1 row affected (0.01 sec)
mysql> create table mychar2(type set('a','b','c','d'));
Query OK, 0 rows affected (0.14 sec)
mysql> insert into mychar2 values('a,b,d');

mysql> select * from mychar2;
+-------+
| type  |
+-------+
| a,b,d |
+-------+

说明：enmu：最多有65535个元素，从1开始索引为0，表示错误值或nouu，

五、时间日期型：

5.1 date类型

格式:YYYY-MM-DD

支持范围:1000-01-01到9999-12-31

mysql> create table mydate(dd date);

Query OK, 0 rows affected (0.06 sec)

mysql> insert into mydate values('2017-09-10'),(20180910);

5.2 time类型

mysql> create table mydate1(tt time);
Query OK, 0 rows affected (0.15 sec)
mysql> insert into mydate1 values('100101');
Query OK, 1 row affected (0.01 sec)
mysql> select * from mydate1;
+----------+
| tt       |
+----------+
| 10:01:01 |
+----------+
1 row in set (0.00 sec)

5.3 year类型

00-69：转为2000-2069 70-79转为：1970-1999

1970Unix诞生记为计算机元年

例子：

mysql> create table mydate2(yy year);
Query OK, 0 rows affected (0.14 sec)
mysql> insert into mydate2 values('2015'),(01),(9),(69),(2080);
Query OK, 5 rows affected (0.08 sec)
Records: 5  Duplicates: 0  Warnings: 0
mysql> select * from mydate2;
+------+
| yy   |
+------+
| 2015 |
| 2001 |
| 2009 |
| 2069 |
| 2080 |
+------+
5 rows in set (0.00 sec)

5.4 datetime类型和time stamp类型

datetime类型timestamp类型

datetime类型能保存大范围的值，从1001年到9999年，精度为秒，它把日期和时间封装到格式为YYYY-MM-DD HH:MM:SS的整数中，与时区无关,使用8个字节的存储空间

timestamp类型保存了从1970年1月1日午夜（格林尼治时间） 以来的秒数

它和linux时间戳相同，timestamp只使用了4个字节的存储空间，因此它的范围比datetime小的多，只能表示从1970-2038，通常我们还是用它的，提高空间利用率

举例：

mysql> create table mydate3(dt datetime);
Query OK, 0 rows affected (0.21 sec)
mysql> insert into mydate3 values('11111111111111')
    -> ;
Query OK, 1 row affected (0.00 sec)
mysql> select * from mydate3;
+---------------------+
| dt                  |
+---------------------+
| 1111-11-11 11:11:11 |
+---------------------+
1 row in set (0.00 sec)
插入当前时间
mysql> insert into mydate4 values(now());
Query OK, 1 row affected (0.01 sec)