mysql数据类型和字段属性原理与用法详解

时间：2021-12-23 05:48:59

本文实例讲述了mysql数据类型和字段属性。分享给大家供大家参考，具体如下：

本文内容：

  • 数据类型
    • 数值类型
      • 整数型
      • 浮点型
      • 定点型
    • 日期时间类型
    • 字符串类型
    • 补充：
      • 显示宽度与zerofll
      • 记录长度
  • 字段属性
    • 空\不为空值：null、not null
    • 主键：primary key
    • 唯一键：unique key
    • 自增长：auto_increment
    • 默认值：default
    • 字段描述：comment
    • 补充：
      • 复合键

首发日期：2018-04-08

数据类型：

mysql的数据类型就是存储数据的类型。

数值类型：

  • 整数类型：tinyint,smallint,mediumint,integer,bigint
    • 默认是有符号，如果需要使用无符号的数据类型要在后面加"unsigned"
类型 简写 字节大小 （signed）范围 （unsigned）范围 备注
整数型：          
tinyint   1 0～255 -128～127  
smallint   2 0～65535 -32768～32767  
mediumint   3 0～16777215 -8388608～8388607  
integer int 4 0～4294967295 -2147483648～2147483647  
bigint   8 0～264-1 -263～263-1  
  • 小数类型：
    • 浮点类型：float(size,d),double(size,d)
      • size是数值的最大位数，d是小数点右侧的位数（即使你没输够那么多位也会补全那么多位）。
      • float的精度为6～7位（依据情况不同？毕竟机器是以二进制存储的），double的精度为14～15位
      • 整数部分的个数为size-d，直接存入的值的个数不能多于这个，但如果浮点数四舍五入导致整数进位而溢出超过最大位数的，系统允许成立（某些版本貌似已经不允许了，所以一般小数位也不要超过）。
      • 很多时候没必要关注两个浮点的数值范围大小（是极大的），注重点应该是它的精度。
      • mysql数据类型和字段属性原理与用法详解mysql数据类型和字段属性原理与用法详解
    • 定点型：decimal(size,d)
      • size是数值的最大位数，最大为65，d是小数点右侧的最大位数，最大为30。
      • 定点型不允许因为小数部分四舍五入导致整数部分长度超出范围。
      • decimal是不会损失精度的。可以理解成使用“字符串”存储（mysql权威指南这么说）。
      • mysql数据类型和字段属性原理与用法详解
    • 直接不带括号的数据类型float、double、decimal代表没有小数部分

时间日期类型：

时间日期类型包括date,time,datetime,timestamp,year;

  • datetime:时间日期，格式是yyyy-mm-dd hh:ii:ss,表示的范围是从1000到9999年（有些版本已经允许0-9999？不确定）；
    • mysql数据类型和字段属性原理与用法详解
  • date:日期，格式是yyyy-mm-dd ，表示的范围是从1000到9999年
    • mysql数据类型和字段属性原理与用法详解
  • time:时间段，格式是hh:ii:ss, 指定的时间在某个区间之间，有正负。表示的范围 -838:59:59~838:59:59，这代表某个日期的前后时间范围【如果你仅仅想要用来表示24小时内的时间，可以忽略它的时间范围】
    • mysql数据类型和字段属性原理与用法详解
  • timestamp:格式是yyyy-mm-dd hh:ii:ss，表示的范围从1970-01-01 00:00:00到2038-01-19 03:14:07 【一旦该行记录被修改，那么timestamp会修改成当前时间。】
    • mysql数据类型和字段属性原理与用法详解
  • year:年份，表示的范围1901-2156
    • mysql数据类型和字段属性原理与用法详解
    • 原本有两种格式，year(2)和year(4)，但5.7中移除了year(2)。
     

字符串类型  ：

    • set  :集合字符串。将一个个特定的字符串放到集合里面。

      • 定义了集合之后，不能插入集合元素之外的字符串，（可以插入多个符合条件的字符串）
      • 集合字符串实际上也是仅仅存储数值，系统自动转换成对应的字符串。
      • 集合中每一个元素对应一个二进制位，被选中的为1，没选中的为0，左边为二进制中的低位，右边为高位，从右到左得到二进制结果
?
1
2
3
4
5
6
7
create table my_set(
hobby set('football','basketball','pingpong','computer')
)charset utf8;
 
insert into my_set values('basketball,computer');
insert into my_set values(3);-- 3=0011
insert into my_set values(5);-- 5=0101

mysql数据类型和字段属性原理与用法详解

    • enum  :枚举字符串

      • 有点类似set,但这里只能插入一个字符串（set类型的字段的值可以是多个set元素，但enum只能是其中一个元素）
      • 将可能出现的数据列举出来，实际存储的数据只能是列举出来的数据。
      • 使用枚举有利于统一数据，以及节省存储空间（枚举只是存储数值，系统转换成字符串）。
      • 枚举元素实际上按定义的顺序标号，从1开始，所以可以直接插入枚举元素对应的数值。
      • 如:
?
1
2
3
create table my_enum(
gender enum('male','female','unknown')
)charset utf8;

mysql数据类型和字段属性原理与用法详解

  • blob  :二进制数据字符串

    • 存储二进制数据（少用）

  • text  :文字字符串

    • 存储文字，当字符大于255时，一般使用text;

  • varchar  :变长字符串

    • varchar(l),l代表字符长度，单位为字符，最大长度为65536。在实际存储中需要1到2个字节来存储长度（1还是2由实际大小决定，216=35536,<=255的一般一个字节），在使用时根据定义的大小来分配空间，但会根据实际需要使用的缩小空间（比如规定60大小，实际只有30，那么实际空间就是31～32）。
    • 当l大于255时，建议使用text，而不是varchar;
    • mysql数据类型和字段属性原理与用法详解

  • char  :定长字符串

    • char(l),l代表字符长度，单位为字符，最大长度为255，不使用l时默认为1
    • 不同环境中根据字符大小，实际占有的空间不同；在utf8环境下，char(4)所用的字节数为4*3=12；

补充：

    • 显示宽度 与填充 ：

      • 在列类型后面显示的数值，代表数据最终显示的最少位数（包含符号）。
      • 一般不够显示宽度的数据都会采用空格填充，也可以使用zerofill来使用零填充（零填充会导致数据类型自动变成无符号的）。
      • tiny(3)的空格填充效果：mysql数据类型和字段属性原理与用法详解，zerofill效果：mysql数据类型和字段属性原理与用法详解
?
1
2
3
4
5
6
create table myint(t1 tinyint(3));
drop table myint;
insert into myint values(127),(1);
select * from myint;
alter table myint modify t1 tinyint(3) zerofill;
select * from myint;

  • 不要轻易的使用最大限度来存储字符串，因为涉及记录长度问题  ：

    • mysql中规定任何一条数据最长不能超过65535个字节
    • 如果有任何一个字段允许为空，那么系统会自动从整个记录中保存一个字节来存储null；
    • text文本不占用记录长度，额外存储。
    • utf8中varchar的最大限度：65535-2（需要两个字节来存储长度）  /3=21844，所以最大字符数为21844
    • gbk中varchar的最大限度:65535-2（需要两个字节来存储长度） /2=32766

字段属性：

字段属性是字段除数据类型外的属性，一般有空\不为空值、主键、唯一键、自增长、默认值、描述等属性。

空\不为空值：null、not null

  • 字段的数据默认情况下是允许为空的，比如说一条人的信息记录中可以没有邮箱（或许有些人考虑用“空字符串”来代表），我们允许可以不填入数据的字段可以设置为null；但比如说某些必填数据，我们不想填入的时候留空，可以设置这个字段为not null
  • 允许为空时的数据，空的数据显示为null：mysql数据类型和字段属性原理与用法详解
  • 不为空的字段，插入null时，会报错：mysql数据类型和字段属性原理与用法详解
  • null\not null属性的定义方式(因为默认允许空，所以主要关注not null)：mysql数据类型和字段属性原理与用法详解；当然也可以通过修改字段的形式来给字段定义not null属性

主键：primary key

  • 主键用于唯一标识每一条记录（每个人都有自己唯一的身份证）
  • 每一张表只能有一个主键。
  • 因为唯一标识，所以主键字段的数据不能为空，并且主键字段的数据值不能重复
  • 主键也是一种索引，可以提高查找速率。
  • 主键的定义：
    • 1.定义表结构的时候定义成字段的属性。mysql数据类型和字段属性原理与用法详解mysql数据类型和字段属性原理与用法详解
    • 2.修改表结构，增加一个带主键的字段。
    • 3.修改表结构，给某一字段增加主键属性。mysql数据类型和字段属性原理与用法详解
  • 主键的用途：主键的主要用途是用来唯一标识每一条记录，比如我们想将两个表（学生表跟老师表）的多对多关系封装到一个表中，这个抽取的映射字段一般都是各自的主键。mysql数据类型和字段属性原理与用法详解；并且由于主键的数据的不可重复性，也用来约束数据的唯一性。
  • 主键的删除：
    ?
    1
    alter table 表名 drop primary key;

唯一键：unique key

  • 唯一键的功能与主键有点类型，但不同的是主键只能有一个，唯一键可以有多个，而且唯一键的字段的数据允许为空。
  • 唯一键可以约束字段，使得字段的数据不能重复
  • 如果唯一键同时也有not null,并且表中没有主键的话，在desc查看表结构中会显示成主键
  • 如果唯一键也不允许为空，那么功能与主键相同
  • 唯一键的定义方法可以参考主键的。
  • 唯一键的删除：
    ?
    1
    alter table 表名 drop index 唯一键名; -- 唯一键默认使用字段名来定义名字

自增长：auto_increment

  • 自增长的功能是可以使某个字段的数据随着记录的插入而进行增长（不给这个字段插入数据的情况下）
  • 自增长的前提是这个字段必须是一个“索引”，比如主键、唯一键
  • 自增长的前提这个字段的数据类型是一个数值型的，（如果给了float,也不会增长成小数，而仅仅是整数）
  • 一个表只能有一个自增长。
  • 只有不给值，或者给null的情况下，才能正确自增长；如果某一次自增长失败了，那么下一次会从当前字段的最大值开始继续自增长。
  • 自增长的定义方法：mysql数据类型和字段属性原理与用法详解；当然也可以通过修改字段的形式来给字段定义not null属性
  • 自增长的修改：
    • 修改增长速度：set auto_increment = 值;
    • 修改下次增长的值：alter table 表名 auto_increment = 值; 【修改的值只能变大，不能变小，因为可能导致数据重复问题】
  • 自增长的查看：show vairable like "auto_increment%";
  • 自增长的删除：利用alter来删除，alter修改字段属性的时候如果不带上原来的属性就会被删掉：

默认值：default

  • 默认值的功能是当我们不给一个字段赋值的时候，使用默认值作为数据，比如不选择性别的时候，默认使用“保密”；
  • 默认值的定义方式：mysql数据类型和字段属性原理与用法详解
  • mysql数据类型和字段属性原理与用法详解

字段描述：comment

  • 字段描述是用来描述字段的，能在查看数据表创建语句的时候显示出来（不会再select结果中显示出来），可以帮助我们了解某一个字段的意义。
  • 定义方式：mysql数据类型和字段属性原理与用法详解
  • mysql数据类型和字段属性原理与用法详解

补充：

  • 复合键：可以将多个字段组成一个键，比如学生可以选多门课，但学生号跟课程号一起的时候应该是唯一的，这里使用主键来限制这个情况
    • 定义方法（复合以主键为例）：mysql数据类型和字段属性原理与用法详解
    • 数据约束（以上面的表为例）：mysql数据类型和字段属性原理与用法详解

 

希望本文所述对大家MySQL数据库计有所帮助。

原文链接：https://www.cnblogs.com/progor/p/8745690.html

标签：

相关文章