MySQL - 数据类型

类型定义

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.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 

 

　　1.2 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

 

 　　1.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 字符串类型

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

 

 　　4.3 ENUM 和SET类型

　　ENUM类型

　　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;　　　　　　　　　　　　　　　　　#以整数的方式查询

 

5. 二进制类型

二进制数据和文本数据在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 ;  　　　　　　　　　　　　#以二进制的方式显示字段值 

 

 

BLOB类型

 

 

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

使用注意：

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

 

6. Jason 类型

(挖坑)

 

 

7.Spatial 数据类型

(挖坑)

 

 

===================================================================================================================