类型

整形类型

整数类型是数据库中最基本的数据类型。标准SQL中支持INTEGER和SMALLINT这两种数据类型。MySQL数据库除了支持这两种类型以外，还扩展支持了TINYINT、MEDIUMINT和BIGINT。下表从不同整数类型的字节数、取值范围等方面进行对比。

类型名称后面的小括号   指定显示宽度（并不是该类型占用字节数）。如果不显示指定宽度则默认为tinyint(3)、 smallint(5)、 mediumint(8)、 int(11) 和 bigint(20)。一般配合 zerofill 使用，顾名思义，zerofill 就是用“0”填充的意思，也就是在数字位数不够的空间用字符“0”填满。

mysql> use school;   #选择数据库school mysql> create table class6(class_id integer(5) zerofill, class_name varchar(128), class_teacher varchar(64) );         #创建表class6

 

UNSIGNED（无符号）  如果需要在字段里面保存非负数或者需要较大的上限值时，可以用此选项，它的取值范围是正常值的下限取 0，上限取原值的 2 倍，例如，tinyint 有符号范围是-128～+127，而无符号范围是 0～255。如果一个列指定为 zerofill，则 MySQL 自动为该列添加 UNSIGNED 属性。 

mysql> use school;   #选择数据库school

mysql> create table class6(id integer unsigned , name varchar(128), teacher varchar(64) );         #创建表class6

mysql> create table class7(id integer zerofill , name varchar(128), teacher varchar(64) );         #创建表class7, id类型为 int unsigned

 AUTO_INCREMENT  在需要产生唯一标识符或顺序值时，可利用此属性，这个属性只用于整数类型。AUTO_INCREMENT 值一般从 1 开始，每行增加 1。 一个表中最多只能有一个 AUTO_INCREMENT列 。对于任何想要使用 AUTO_INCREMENT 的列，应该定义为 NOT NULL，并定义为 PRIMARY KEY 或定义为 UNIQUE 键。 例如，可按下列任何一种方式定义 AUTO_INCREMENT 列： 

 浮点类型和定点数类型

数据表中用浮点数类型和定点数类型来表示小数。浮点数类型包括单精度浮点数（FLOAT型）和双精度浮点数（DOUBLE型）。定点数类型就是DECIMAL型。下面从这三种类型的字节数、取值范围等方面进行对比，如下表所示。

• MySQL 浮点型和定点型可以用类型名称后加（M，D）来表示，M表示该值的总共长度，D表示小数点后面的长度，M和D又称为精度和标度，如float(7,4)的 可显示为-999.9999，MySQL保存值时进行四舍五入，如果插入999.00009，则结果为999.0001。对于decimal,M是最大位数（精度），范围是1到65。可不指定，默认值是10。D是小数点右边的位数（小数位）。范围是0到30，并且不能大于M，可不指定，默认值是0。
• FLOAT和DOUBLE在不指定精度时，默认会按照实际的精度来显示，而DECIMAL在不指定精度时，默认整数为10，小数为0,即(10, 0)。

忠告: 当我们需要存储小数，并且有精度要求，比如存储金额时，通常会考虑使用DECIMAL字段类型!!!

日期和时间类型

日期与时间类型是为了方便在数据库中存储日期和时间而设计的，数据库有多种表示日期和时间的数据类型。其中，YEAR类型表示年，DATE类型表示日期，TIME类型表示时间，DATETIME和TIMESTAMP表示日期和时间。下面从这5种日期与时间类型的字节数、取值范围和零值等方面进行对比，如下表所示。

每种日期与时间类型都有一个有效范围。如果插入的值超过了这个范围，系统就会报错，并将零值插入到数据库中。不同的日期与时间类型均有不同的零值.插入日期时间时，日期年月日和时间时分秒可以使用 “: - _ / ”中的任意字符来分隔,如果单独插入时间，则会报错！！！

CURDATE()  - 获得当前的DATE, 可直接插入DATE 类型中.

NOW() - 获得当前的DATETIME, 可直接插入DATETIME 和TIMESTAMP类型中.

TIME() - 获取参数给定时间串中的时分秒，可直接插入TIME 类型中.

YEAR() - 获取参数给定时间串中的年份，可直接插入YEAR类型中.

MONTH() 、DAY()、HOUR()、MINUTE()、SECOND() 获取参数给定时间串中的月、日、时、分、秒值.

字符串类型

1. CHAR类型和VARCHAR类型

CHAR类型和VARCHAR类型都在创建表时指定了最大长度，其基本形式如下：

    字符串类型（M）

其中，“字符串类型”参数指定了数据类型为CHAR类型还是VARCHAR类型；M参数指定了该字符串的最大长度为M。例如，CHAR(4)就是数据类型为CHAR类型，其最大长度为4。

CHAR类型的长度是固定的，在创建表时就指定了。其长度可以是0~255的任意值。例如，CHAR(100)就是指定CHAR类型的长度为100(100个字符)。CHAR 存储值时，它们会用空格右填充到指定的长度。

CREATE TABLE students_char (id INT PRIMARY KEY AUTO_INCREMENT,name CHAR(10)
);

INSERT INTO students_char (name) VALUES ('Alice');
INSERT INTO students_char (name) VALUES ('Bob');
INSERT INTO students_char (name) VALUES ('Charlie');

VARCHAR类型的长度是可变的，在创建表时指定了最大长度。定义时，其最大值可以取0~65535之间的任意值。指定VARCHAR类型的最大值以后，其长度可以在0到最大长度之间。例如，VARCHAR(100)的最大长度是100，但是不是每条记录都要占用100个字节，而是在这个最大值范围内使用多少就分配多少。VARCHAR类型实际占用的空间为字符串的实际长度加1或2，这样即可有效节约系统的空间。

mysql> create table char_example(e_char char(5), v_char varchar(5)); #创建数据库表

mysql> insert into char_example values('12345','12345');  #正常插入数据

mysql> insert into char_example values('1 2  ','1 2  ');   #char类型会屏蔽后面隐藏的空格，varchar 不会

mysql>select concat('(',e_char, ')'), concat('(',v_char, ')')  from char_example ; #让char 后面屏蔽的空格原型毕露 

• 1、 char一定会使用指定的空间，varchar是根据数据来定空间
• 2、 char的数据查询效率比varchar高：varchar是需要通过后面的记录数来计算
• 3、  如果确定数据一定是占指定长度，那么使用char类型；
• 4、  如果不确定数据到底有多少，那么使用varchar类型；
• 5、  如果数据长度超过255个字符而在65535之内，直接使用varchar
• 6、  如果字符串尾部要保留空格，必须选择varchar

TEXT类型

TEXT类型是一种特殊的字符串类型，包括TINYTEXT、TEXT、MEDIUMTEXT和LONGTEXT，其长度和存储空间的对比下表所示：

 各种TEXT类型的区别在于允许的长度和存储空间不同。因此，在这几种TEXT类型中，根据需求选取既能满足需要又节省空间的类型即可。

特别注意：

1. 以上各类型无须指定长度！

允许的长度是指实际存储的字节数，而不是实际的字符个数，比如假设一个中文字符占两个字节， 那么TEXT 类型可存储 65535/2 = 32767  个中文字符，而varchar(100)可存储100个中文字 符，实际占200个字节，但varchar(65535) 并不能存储65535个中文字符，因为已超出表达范围.

相信大家有疑惑，text类型和varchar的类型问题？

VARCHAR 类型

• 存储方式：VARCHAR 类型根据实际存储的字符数来分配空间，并额外存储一个或两个字节来表示字符串的长度。
• 字符数 vs 字节数：VARCHAR 类型的长度是以字符数为单位指定的。例如，VARCHAR(100) 表示最多可以存储 100 个字符。
• 多字节字符：如果使用多字节字符编码（如 UTF-8），每个字符可能占用多个字节。例如，UTF-8 编码下，一个中文字符通常占用 3 个字节，因此 VARCHAR(100) 最多可以存储 100 个字符，即 300 个字节。

TEXT 类型

• 存储方式：TEXT 类型根据实际存储的字节数来分配空间。
• 最大长度：TEXT 类型的最大长度是以字节为单位的。例如，TEXT 类型最多可以存储 65535 字节。
• 多字节字符：如果使用多字节字符编码，TEXT 类型的最大字符数会根据每个字符占用的字节数来计算。例如，UTF-8 编码下，一个中文字符占用 3 个字节，因此 TEXT 类型最多可以存储 65535 / 3 ≈ 21845 个中文字符。

mysql> use test;   #选择数据库test

mysql> create table text_example(e_text tinytext, v_char varchar(255)); #创建数据库表，e_text 可存储255个字节，v_char可存储255个字符   

mysql> insert into char_example values(90个中文字符,90个中文字符);  #插入失败，utfmb4 用3个字节表示一个中文汉字,会超出tinytext 保存范围

mysql> insert into char_example values(80个中文字符,100个中文字符);  #插入成功 

• 1、 char长度固定， 即每条数据占用等长字节空间；适合用在身份证号码、手机号码等定。超过255字节的只能用varchar或者text；
• 2、 varchar可变长度，可以设置最大长度；适合用在长度可变的属性。
• 3、 text不设置长度， 当不知道属性的最大长度时，适合用text， 能用varchar的地方不用text；
• 4、 如果都可以选择，按照查询速度： char最快， varchar次之，text最慢。

实战练习

创建一个用户信息表，用来存储用户的如下信息：姓名，手机号码，家庭地址，个人简介，性别，年龄，身份证号。数据库表名可定义为userinfo, 同时增加一个列id 用来作为每一条记录的唯一标识，并设为主键，自加！

ENUM类型

ENUM类型又称为枚举类型。在创建表时，ENUM类型的取值范围以列表的形式指定，其基本形式如下：

    属性名  ENUM('值1',  '值2', …, '值n')

其中，“属性名”参数指字段的名称，“值n”参数表示列表中的第n个值。ENUM类型的值只能取列表中的一个元素。其取值列表中最多能有65535个值。如果数据值列表在255个以内，那么一个字节就够，如果超过255但是小于65535，那么系统采用两个字节保存。列表中的每个值独有一个顺序排列的编号，MySQL中存入的是这个编号，而不是列表中的值。默认编号从1开始！Enum(‘男’,’女’,‘选择保密’)  # ‘男’=>1  ‘女’=>2  ‘选择保密’=> 3

mysql> create table enum_example (e_enum enum('男','女','选择保密') ); #创建表 

mysql> insert into enum_example values('男');  #插入记录,必须是enum 选项中的值

mysql> insert into enum_example values(1);  #插入记录可以用数值表示

mysql>select e_enum + 0  from enum_example; #查询enum 选项对应的整数值

如果ENUM类型加上了NOT NULL属性，其默认值为取值列表的第一个元素。如果不加NOT NULL属性，ENUM类型将允许插入NULL，而且NULL为默认值。

set类型

在创建表时，SET类型的取值范围就以列表的形式指定了，其基本形式如下：

    属性名  SET('值1',  '值2', …, '值n')

其中，属性名参数指字段的名称，“值n”参数表示列表中的第n个值，这些值末尾的空格将会被系统直接删除。其基本形式与ENUM类型一样。SET类型的值可以取列表中的一个元素或者多个元素的组合。取多个元素时，不同元素之间用逗号隔开。SET类型的值最多只能是由64个元素构成的组合。

mysql> use test;   #选择数据库test

mysql> create table set_example (interest set('足球','追剧','篮球','撩妹') ); #创建表 

mysql> insert into set_example values( '足球,撩妹' ); #插入记录,必须是enum 选项中的值

mysql> insert into enum_example values(9);  #插入相应位效果等同，9 =>1001 选择1,4

mysql>select interest+0 from set_example;  #以整数的方式查询

 二进制类型

二进制数据和文本数据在mysql 中的最大区别在于：

1. 二进制类型存储原始的二进制数据(如图片，视频，exe文件等)。文本类型(TEXT)用来存储字符字符串(如由英文字符、中文字符或其它语言字符组成的字符串)。
2. 二进制类型没有字符集，并且排序和比较基于列值字节的数值。而TEXT类型有字符集，并且根据字符集的校对规则对值进行排序和比较。

二进制类型是存储二进制数据的数据类型，包括BINARY、VARBINARY、BIT、TINYBLOB、BLOB、MEDIUMBLOB和LONGBLOB。二进制类型之间的对比如下表所示。

BINARY和VARBINARY类型

BINARY类型和VARBINARY类型都是在创建表时指定最大长度，其基本形式如下：

    类型 (M)   

其中，“字符串类型”参数指定数据类型为BINARY类型还是VARBINARY类型；M参数指定该二进制数的最大字节长度为M。这与CHAR类型和VARCHAR类型相似。例如，BINARY(10)就是指数据类型为BINARY类型，其最大长度为10。

1. BINARY类型的长度是固定的，在创建表时就指定了，不足最大长度的空间由″\0″补全。例如，BINARY(50)就是指定BINARY类型的长度为50。

VARBINARY类型的长度是可变的，在创建表时指定了最大的长度，其长度可以在0到最大长度之间，在这个最大值范围。

mysql> use test;   #选择数据库test

mysql> create table bin_example(e_bin  binary(5),e_varbin varbinary(5)); #创建表 

mysql> insert into bin_example values( 'ab','ab'); #插入记录,可以是普通字符串

mysql> insert into bin_example values( b'0110000101100010',b'0110000101100010'); #插入记录,可以是二进制，与上例等同

mysql> select * from bit_example ;  #以十六进制的方式显示         

BIT类型

BIT类型在创建表时指定最大长度，其基本形式如下：

    BIT（M）

其中，“M”指定该二进制数的最大存储长度为M，M的最大值为64。例如，BIT(4)就是指数据类型为BIT类型，长度为4。若字段的类型BIT(4)存储的数据是0~15，因为变成二进制之后15的值为1111，则其长度为4。如果插入的值为16，其二进制数为10000，长度为5，超过了最大长度，因此大于16的数是不能插入BIT(4)类型字段中的。

操作要点:

1. 插入数据时，使用 b’位串’的方式插入相应值!
2. 查询时,可以使用 bin() 、oct() 、hex() 函数讲字段的值转成相应的二进制、八进制和十六进制.

BLOB类型

1.以上各类型无须指定长度！

2.允许的长度是指实际存储的字节数，不用考虑字符编码。

• 1、 binary长度固定， 即每条数据占用等长字节空间；保存长度不超过255字节的二进制数据；
• 2、 varbinary可变长度，可以设置最大长度,最大长度65535；适合用在长度可变的二进制数据;
• 3、 blob不设置长度， 当不知道属性的最大长度时，适合用blob， 能用varbinary的地方不用blob；
• 4、 如果都可以选择，按照查询速度： binary最快， varbinary次之，blob最慢。