列的类型定义

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

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

整数类型的附带属性
(1)类型名称后面的小括号 指定显示宽度（并不是该类型占用字节数）。如果不显示指定宽度则默认为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

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

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

mysql> use school; #选择数据库school
mysql> create table class8(id integer auto_increment PRIMARY KEY , name varchar(128), teacher varchar(64) ); #创建表class8， id 具有自增长属性
mysql> create table class9(id integer auto_increment UNIQUE , name varchar(128), teacher varchar(64) ); #创建表class9， id 具有自增长属性

2.浮点类型

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

三种类型的区别：
.float数值类型用于表示单精度浮点数值，而double数值类型用于表示双精度浮点数值，float和double都是浮点型，而decimal是定点型；

mysql> use school; #选择数据库school
mysql> create table class10 (f1 float, do1 double, de1 decimal); #创建表class10

.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字段类型!!！

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

每种日期与时间类型都有一个有效范围。如果插入的值超过了这个范围，系统就会报错，并将零值插入到数据库中。不同的日期与时间类型均有不同的零值.

插入日期时间时，日期年月日和时间时分秒可以使用 “: - _ / ”中的任意字符来分隔,如果单独插入时间，则会报错！！！

mysql> use test; #选择数据库test
mysql> create table date_example (e_date date, e_datetime datetime, e_timestamp
timestamp, e_time time, e_year year); #创建表dt_example
mysql> insert into date_example values(‘2020-5-9’, ‘2020-5-9 15:01:00’, ‘2020-05-09 15:01:00’, ‘15:56:01’, 2011);
mysql> insert into date_example values(‘2020_5/9’, ‘2020:5-9 15/01-00’, ‘2020:05/09 15-01_00’, ‘15:56:01’, ‘2011’); #效果同上面插入语句

也可以通过mysql 本身提供的时间函数获取：

常用的时间函数:
CURDATE() - 获得当前的DATE, 可直接插入DATE 类型中.
NOW() - 获得当前的DATETIME, 可直接插入DATETIME 和TIMESTAMP类型中.
TIME() - 获取参数给定时间串中的时分秒，可直接插入TIME 类型中.
YEAR() - 获取参数给定时间串中的年份，可直接插入YEAR类型中.
MONTH() 、DAY()、HOUR()、MINUTE()、SECOND() 获取参数给定时间串中的月、日、时、分、秒值.

mysql> use test; #选择数据库test
mysql> insert into date_example values(CURDATE(), NOW(), NOW(), time(NOW()), YEAR(NOW()) );

4.字符串类型

1. CHAR类型和VARCHAR类型
   CHAR类型和VARCHAR类型都在创建表时指定了最大长度，其基本形式如下：
   字符串类型（M）
   其中，“字符串类型”参数指定了数据类型为CHAR类型还是VARCHAR类型；M参数指定了该字符串的最大长度为M。例如，CHAR(4)就是数据类型为CHAR类型，其最大长度为4。
   CHAR类型的长度是固定的，在创建表时就指定了。其长度可以是0~255的任意值。例如，CHAR(100)就是指定CHAR类型的长度为100。CHAR 存储值时，它们会用空格右填充到指定的长度。
   VARCHAR类型的长度是可变的，在创建表时指定了最大长度。定义时，其最大值可以取0~65535之间的任意值。指定VARCHAR类型的最大值以后，其长度可以在0到最大长度之间。例如，VARCHAR(100)的最大长度是100，但是不是每条记录都要占用100个字节，而是在这个最大值范围内使用多少就分配多少。VARCHAR类型实际占用的空间为字符串的实际长度加1或2，这样即可有效节约系统的空间。

mysql> use test; #选择数据库test
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

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

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

特别注意：
1.以上各类型无须指定长度！

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

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最慢。

5.ENUM 和SET类型
ENUM类型又称为枚举类型。在创建表时，ENUM类型的取值范围以列表的形式指定，其基本形式如下：
属性名 ENUM(‘值1’, ‘值2’, …, ‘值n’)
其中，“属性名”参数指字段的名称，“值n”参数表示列表中的第n个值。ENUM类型的值只能取列表中的一个元素。其取值列表中最多能有65535个值。如果数据值列表在255个以内，那么一个字节就够，如果超过255但是小于65535，那么系统采用两个字节保存。列表中的每个值独有一个顺序排列的编号，MySQL中存入的是这个编号，而不是列表中的值。默认编号从1开始！
Enum(‘男’,’女’,‘选择保密’) # ‘男’=>1 ‘女’=>2 ‘选择保密’=> 3
mysql> use test; #选择数据库test
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; #以整数的方式查询

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

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

BINARY和VARBINARY类型
BINARY类型和VARBINARY类型都是在创建表时指定最大长度，其基本形式如下：
类型 (M)
其中，“字符串类型”参数指定数据类型为BINARY类型还是VARBINARY类型；M参数指定该二进制数的最大字节长度为M。这与CHAR类型和VARCHAR类型相似。例如，BINARY(10)就是指数据类型为BINARY类型，其最大长度为10。
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)类型字段中的。

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

mysql> use test; #选择数据库test
mysql> create table bit_example (b bit(8) ); #创建表
mysql> insert into bit_example values( b’10110111’ ); #插入记录,可以是二进制位
mysql> insert into bit_example values( ‘a’ ); #插入记录,可以是字符，但不能超出字节长度
mysql> select bin(b) from bit_example ; #以二进制的方式显示字段值

1.以上各类型无须指定长度！
2.允许的长度是指实际存储的字节数，不用考虑字符编码。

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