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

本文实例讲述了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,直接存入的值的个数不能多于这个,但如果浮点数四舍五入导致整数进位而溢出超过最大位数的,系统允许成立(某些版本貌似已经不允许了,所以一般小数位也不要超过)。
      • 很多时候没必要关注两个浮点的数值范围大小(是极大的),注重点应该是它的精度。
    • 定点型:decimal(size,d)
      • size是数值的最大位数,最大为65,d是小数点右侧的最大位数,最大为30
      • 定点型不允许因为小数部分四舍五入导致整数部分长度超出范围。
      • DECIMAL是不会损失精度的。可以理解成使用“字符串”存储(MYSQL权威指南这么说)。
    • 直接不带括号的数据类型float、double、decimal代表没有小数部分

时间日期类型:

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

  • Datetime:时间日期,格式是YYYY-MM-DD HH:II:SS,表示的范围是从1000到9999年(有些版本已经允许0-9999?不确定);

  • Date:日期,格式是YYYY-MM-DD ,表示的范围是从1000到9999年

  • Time:时间段,格式是HH:II:SS, 指定的时间在某个区间之间,有正负。表示的范围 -838:59:59~838:59:59,这代表某个日期的前后时间范围【如果你仅仅想要用来表示24小时内的时间,可以忽略它的时间范围】
  • Timestamp:格式是YYYY-MM-DD HH:II:SS,表示的范围从1970-01-01 00:00:00到2038-01-19 03:14:07 【一旦该行记录被修改,那么timestamp会修改成当前时间。】
  • Year:年份,表示的范围1901-2156
    • 原本有两种格式,year(2)和year(4),但5.7中移除了year(2)。

字符串类型  :

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

      • 定义了集合之后,不能插入集合元素之外的字符串,(可以插入多个符合条件的字符串)
      • 集合字符串实际上也是仅仅存储数值,系统自动转换成对应的字符串。
      • 集合中每一个元素对应一个二进制位,被选中的为1,没选中的为0,左边为二进制中的低位,右边为高位,从右到左得到二进制结果
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

    • enum  :枚举字符串

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

  • blob  :二进制数据字符串

    • 存储二进制数据(少用)
  • text  :文字字符串

    • 存储文字,当字符大于255时,一般使用text;
  • varchar  :变长字符串

    • varchar(L),L代表字符长度,单位为字符,最大长度为65536。在实际存储中需要1到2个字节来存储长度(1还是2由实际大小决定,216=35536,<=255的一般一个字节),在使用时根据定义的大小来分配空间,但会根据实际需要使用的缩小空间(比如规定60大小,实际只有30,那么实际空间就是31~32)。
    • 当L大于255时,建议使用Text,而不是varchar;
  • char  :定长字符串

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

补充:

    • 显示宽度 与填充 :

      • 在列类型后面显示的数值,代表数据最终显示的最少位数(包含符号)。
      • 一般不够显示宽度的数据都会采用空格填充,也可以使用zerofill来使用零填充(零填充会导致数据类型自动变成无符号的)。
      • tiny(3)的空格填充效果:,zerofill效果:
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:
  • 不为空的字段,插入null时,会报错:
  • null\not null属性的定义方式(因为默认允许空,所以主要关注not null):;当然也可以通过修改字段的形式来给字段定义not null属性

主键:primary key

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

    alter table 表名 drop primary key;

唯一键:unique key

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

    alter table 表名 drop index 唯一键名; -- 唯一键默认使用字段名来定义名字

自增长:auto_increment

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

默认值:default

  • 默认值的功能是当我们不给一个字段赋值的时候,使用默认值作为数据,比如不选择性别的时候,默认使用“保密”;
  • 默认值的定义方式:

字段描述:comment

  • 字段描述是用来描述字段的,能在查看数据表创建语句的时候显示出来(不会再select结果中显示出来),可以帮助我们了解某一个字段的意义。
  • 定义方式:

补充:

  • 复合键:可以将多个字段组成一个键,比如学生可以选多门课,但学生号跟课程号一起的时候应该是唯一的,这里使用主键来限制这个情况

    • 定义方法(复合以主键为例):
    • 数据约束(以上面的表为例):

更多关于MySQL相关内容感兴趣的读者可查看本站专题:《MySQL查询技巧大全》、《MySQL事务操作技巧汇总》、《MySQL存储过程技巧大全》、《MySQL数据库锁相关技巧汇总》及《MySQL常用函数大汇总》

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

(0)

相关推荐

  • MySQL日期数据类型、时间类型使用总结

    MySQL 日期类型:日期格式.所占存储空间.日期范围 比较. 日期类型        存储空间       日期格式                 日期范围 ------------ ---------   --------------------- ----------------------------------------- datetime       8 bytes   YYYY-MM-DD HH:MM:SS   1000-01-01 00:00:00 ~ 9999-12-31

  • 深入了解mysql的4种常用、重要的数据类型

    为了实现这一目标,它需要我们在数据库系统的常用数据类型有充分的认识. 下面我们来看一下mysql的重要数据类型 1.数值类型可以划分为数值类型:整型,浮点十进制类型. 所谓的"十进制"是指decimal和numeric,他们是同一类型的.严格地说,它不是一个数字类型,因为他们实际上是作为字符串存储,每一个数字值(包括小数点)占一个字节的存储空间,所以这种类型的消耗大量的存储空间,但它的优点是它的价值不会失去做浮点数计算精度,它更适合一些计算精度要求高,如价格计算.浮动取决于不同精度的类

  • MySQL优化之表结构优化的5大建议(数据类型选择讲的很好)

    殊不知,在N年前被奉为"圣经"的数据库设计3范式早就已经不完全适用了.这里我整理了一些比较常见的数据库表结构设计方面的优化技巧,希望对大家有用. 由于MySQL数据库是基于行(Row)存储的数据库,而数据库操作 IO 的时候是以 page(block)的方式,也就是说,如果我们每条记录所占用的空间量减小,就会使每个page中可存放的数据行数增大,那么每次 IO 可访问的行数也就增多了.反过来说,处理相同行数的数据,需要访问的 page 就会减少,也就是 IO 操作次数降低,直接提升性能

  • MySQL 数据类型 大全

    复制代码 代码如下: 其中的长度以字节为单位 名称 长度 用法 TINYINT(M) BIT,BOOL,BOOLEAN 1 如果为无符号数,可以存储从0到255的数; 否则可以存储从-128到127的数. SMALLINT(M) 2 如果为无符号数,可以存储从0到65535的数; 否则可以存储从-32768到32767的数. MEDIUMINT(M) 3 如果为无符号数,可以存储从0到16777215的数;否则可以存储从-8388608到8388607的数 INT(M) INTEGER(M) 4

  • mysql 数据类型TIMESTAMP

    在mysql中timestamp数据类型是一个比较特殊的数据类型,他可以自动在你不使用程序更新情况下只要你更新了记录timestamp会自动更新时间 通常表中会有一个Create date 创建日期的字段,其它数据库均有默认值的选项.MySQL也有默认值timestamp,但在MySQL中,不仅是插入就算是修改也会更新timestamp的值! 这样一来,就不是创建日期了,当作更新日期来使用比较好! 因此在MySQL中要记录创建日期还得使用datetime 然后使用NOW() 函数完成! 1: 如

  • MariaDB(MySQL)创建、删除、选择及数据类型使用详解

    一.MariaDB简介(MySQL简介略过) MariaDB数据库管理系统是MySQL的一个分支,主要由开源社区在维护,采用GPL授权许可 MariaDB的目的是完全兼容MySQL,包括API和命令行,使之能轻松成为MySQL的代替品.在存储引擎方面,使用XtraDB(英语:XtraDB)来代替MySQL的InnoDB. MariaDB由MySQL的创始人Michael Widenius(英语:Michael Widenius)主导开发,他早前曾以10亿美元的价格,将自己创建的公司MySQL A

  • MySQL数据类型varchar详解

    1.varchar(N)的逻辑意义从MySQL4.1开始,varchar (N)中的N指的是该字段最多能存储多少个字符(characters),不是字节数.不管是一个中英文字符或者数字.或者一个汉字,都当做一个字符.在4.1之前,N表示的是最大存储的字节数(bytes).2.varchar(N)到底能存多长的数据在mysql reference manual上,varchar最多能存储65535个字节的数据.varchar 的最大长度受限于最大行长度(max row size,65535byte

  • Mysql支持的数据类型(列类型总结)

    一.数值类型 Mysql支持所有标准SQL中的数值类型,其中包括严格数据类型(INTEGER,SMALLINT,DECIMAL,NUMBERIC),以及近似数值数据类型(FLOAT,REAL,DOUBLE PRESISION),并在此基础上进行扩展. 扩展后增加了TINYINT,MEDIUMINT,BIGINT这3种长度不同的整形,并增加了BIT类型,用来存放位数据. 整数类型        字节       范围(有符号)      范围(无符号)          用途 TINYINT  

  • MySQL数据类型和常用字段属性总结

    前言 好比C++中,定义int类型需要多少字节,定义double类型需要多少字节一样,MySQL对表每个列中的数据也会实行严格控制,这是数据驱动应用程序成功的关键.M前言 好比C++中,定义int类型需要多少字节,定义double类型需要多少字节一样,MySQL对表每个列中的数据也会实行严格控制,这是数据驱动应用程序成功的关键.MySQL提供了一组可以赋给表中各个列的数据类型,每个类型都强制数据满足为该数据类型预先确定的一组规则,例如大小.类型及格式. 这里先总结数据类型.MySQL中的数据类型

  • MySQL 数据类型 详解

    1.整型 MySQL数据类型 含义(有符号) tinyint(m) 1个字节 范围(-128~127) smallint(m) 2个字节 范围(-32768~32767) mediumint(m) 3个字节 范围(-8388608~8388607) int(m) 4个字节 范围(-2147483648~2147483647) bigint(m) 8个字节 范围(+-9.22*10的18次方) 取值范围如果加了unsigned,则最大值翻倍,如tinyint unsigned的取值范围为(0~25

随机推荐