Mysql列的类型定义详解——史上最详细教程(含实战)-Toy模板网

这篇具有很好参考价值的文章主要介绍了Mysql列的类型定义详解——史上最详细教程(含实战)。希望对大家有所帮助。如果存在错误或未考虑完全的地方，请大家不吝赐教，您也可以点击"举报违法"按钮提交疑问。

前言

一、整形类型

1.整数类型的附带属性

(1).类型名称后面的小括号

(2).unsigned

(3).auto_increment

二、浮点数和定点数类型

1.类型表

2.三种类型的区别

(1).区别

(2).忠告

三、字符型

1.CHAR 类型和 VARCHAR 类型

(1).字符串字符(M)

(2).实战类型

2.TEXT类型

(1).类型表

(2).特别注意

(3).实战建议

(4).实战练习

3.ENUM 和 SET类型

(1).ENUM类型

(2).SET类型

四、日期和事件类型

1.类型表

2.类型简介

五、二进制类型

1.类型表

2.类型

(1).BINARY和VARBINARY

(2).BIT类型

(3)BLOB类型

3.实战建议

总结

✨✨✨大家好，我是会飞的鱼-blog，今天我来给大家介绍一下Mysql，有不足之处，请大家多多指教。感谢大家支持！！！

前言

1）采用26字母和0-9的自然数加上下互相 ‘_’ 组成，命名简洁明确，多个单词用下划线 ‘_’ 隔开

2）全部小写命名，尽量避免出现大写

3）字段必须填写描述信息

4）禁止使用数据库关键字

5）字段名称一般采用名词或动宾短语

6）采用字段的名字必须是易于理解，一般不超过三个英文单词

7）在命名表的列时，不要重复表的名称（如：在 user 表中，出现 user_name 字段）

8）字段命名使用完整名称

一、整形类型

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

Mysql列的类型定义详解——史上最详细教程(含实战)

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

(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

(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 具有自增长属性

二、浮点数和定点数类型

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

1.类型表

Mysql列的类型定义详解——史上最详细教程(含实战)

2.三种类型的区别

(1).区别

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)。

(2).忠告

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

三、字符型

1.CHAR 类型和 VARCHAR 类型

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

(1).字符串字符(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，这样即可有效节约系统的空间。

值	CHAR(4)	存储字节数	VARCHAR(4)	存储字节数
‘’	‘’	4	‘’	1
‘ab’	‘ab’	4	‘ab’	3
‘abcd’	‘abcd’	4	‘abcd’	5
‘abcdefgh’	-	#插入失败	-	#插入失败

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 后面屏蔽的空格原型毕露

(2).实战类型

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

2.TEXT类型

(1).类型表

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

Mysql列的类型定义详解——史上最详细教程(含实战)

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

(2).特别注意

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个中文字符);  #插入成功

(3).实战建议

1、 char长度固定，即每条数据占用等长字节空间；适合用在身份证号码、手机号码等定。超过255字节的只能用varchar或者text；

2、 varchar可变长度，可以设置最大长度；适合用在长度可变的属性。

3、 text不设置长度，当不知道属性的最大长度时，适合用text，能用varchar的地方不用text；

4、如果都可以选择，按照查询速度： char最快， varchar次之，text最慢。

(4).实战练习

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

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

mysql> create table userinfo (

 id int(11) unsigned NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT ‘主键’,

 name   varchar(64) DEFAULT NULL COMMENT ‘姓名’,

 mobile  char(11)  DEFAULT NULL COMMENT ‘手机号码’,

 address varchar(128) DEFAULT NULL COMMENT ‘居住地址’,

 description text  DEFAULT NULL COMMENT ‘个人简介-不知道具体的范围，不常更新用text’,

 sex     char(1)  DEFAULT NULL COMMENT ‘性别 - 男或女’,

 age     tinyint unsigned DEFAULT 0 COMMENT ‘年龄’,

 idno    char(18) DEFAULT NULL COMENT ‘身份证号码’

);

3.ENUM 和 SET类型

(1).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为默认值。

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

四、日期和事件类型

日期与时间类型是为了方便在数据库中存储日期和时间而设计的，数据库有多种表示日期和时间的数据类型。其中，YEAR类型表示年，DATE类型表示日期，TIME类型表示时间，DATETIME和TIMESTAMP表示日期和时间。

1.类型表

下面从这5种日期与时间类型的字节数、取值范围和零值等方面进行对比，如下表所示。

Mysql列的类型定义详解——史上最详细教程(含实战)

2.类型简介

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

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

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()) );

五、二进制类型

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

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

1.类型表

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

Mysql列的类型定义详解——史上最详细教程(含实战)

2.类型

(1).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 ;  #以十六进制的方式显示

(2).BIT类型

其中，“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 ;  #以二进制的方式显示字段值

(3)BLOB类型

Mysql列的类型定义详解——史上最详细教程(含实战)

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

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

3.实战建议

1、 binary长度固定，即每条数据占用等长字节空间；保存长度不超过255字节的二进制数据；

2、 varbinary可变长度，可以设置最大长度,最大长度65535；适合用在长度可变的二进制数据;

3、 blob不设置长度，当不知道属性的最大长度时，适合用blob，能用varbinary的地方不用blob；

4、如果都可以选择，按照查询速度： binary最快， varbinary次之，blob最慢。