MySQL5.7-数据类型和约束条件

系列文章目录

一、数据类型

在MySQL中，每个字段都必须指定数据类型，且以后存入的值必须就是该类型。

• 创建数据表的语法：
  先了解一下创建表的语法，有助于理解后续内容：

  CREATE TABLE 表名(
     字段名 字段类型(宽度) 若干约束条件，
     # 如：字段名为name，类型为CHAR，宽度为10，约束条件为该字段的值不能为空
     name CHAR(10) NOT NULL,
     ……
  );
  

  宽度在不同的类型有不同的作用，上述例子中CHAR(10)代表最多存10个字符。其他类型会在后面讲到。

• 整型：

  类型	大小	范围（有符号）	范围（无符号）
  TINYINT	1 bytes	(-128，127)	(0，255)
  SMALLINT	2 bytes	(-32 768，32 767)	(0，65 535)
  MEDIUMINT	3 bytes	(-8 388 608，8 388 607)	(0，16 777 215)
  INT或INTEGER	4 bytes	(-2 147 483 648，2 147 483 647)	(0，4 294 967 295)
  BIGINT	8 bytes	(-9,223,372,036,854,775,808，9 223 372 036 854 775 807)	(0，18 446 744 073 709 551 615)

  注意：整型的宽度指显示宽度，即显示的位数。如：int(8)类型的字段，如果设置了ZEROFILL约束，且数值不足8位，就用0补充左边不足的位数。如果超过8位，就正常显示，对存储没有影响。

• 浮点型：

  类型	大小	范围（有符号）	范围（无符号）
  FLOAT	4 bytes	(-3.402 823 466 E+38，-1.175 494 351 E-38)，0，(1.175 494 351 E-38，3.402 823 466 351 E+38)	0，(1.175 494 351 E-38，3.402 823 466 E+38)
  DOUBLE	8 bytes	(-1.797 693 134 862 315 7 E+308，-2.225 073 858 507 201 4 E-308)，0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)	0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)
  DECIMAL	对于从、DECIMAL(M,D) ，如果M>D，为M+2否则为D+2	依赖于M和D的值	依赖于M和D的值

  浮点类型的宽度是指总共的位数，和小数所占的位数。如DECIMAL(20,12)代表总共20位，小数占其中的12位。

  精确度从小到大为：FLOAT < DOUBLE < DECIMAL。

• 字符型：

  类型	大小	用途
  CHAR	0-255 bytes	定长字符串，超过宽度报错，不足用空格补全，显示时会去除这些空格
  VARCHAR	0-65535 bytes	变长字符串，超过宽度报错，不足宽度，有几个存几个
  TINYBLOB	0-255 bytes	不超过 255 个字符的二进制字符串
  TINYTEXT	0-255 bytes	短文本字符串
  BLOB	0-65 535 bytes	二进制形式的长文本数据
  TEXT	0-65 535 bytes	长文本数据
  MEDIUMBLOB	0-16 777 215 bytes	二进制形式的中等长度文本数据
  MEDIUMTEXT	0-16 777 215 bytes	中等长度文本数据
  LONGBLOB	0-4 294 967 295 bytes	二进制形式的极大文本数据
  LONGTEXT	0-4 294 967 295 bytes	极大文本数据

• 日期和时间类型：

  类型	大小	范围	格式	用途
  DATE	3 bytes	1000-01-01/9999-12-31	YYYY-MM-DD	日期值
  TIME	3 bytes	‘-838:59:59’/‘838:59:59’	HH:MM:SS	时间值或持续时间
  YEAR	1 bytes	1901/2155	YYYY	年份值
  DATETIME	8 bytes	1000-01-01 00:00:00/9999-12-31 23:59:59	YYYY-MM-DD HH:MM:SS	混合日期和时间值
  TIMESTAMP	4 bytes	1970-01-01 00:00:00/2038结束时间是第 2147483647 秒，北京时间 2038-1-19 11:14:07，格林尼治时间 2038年1月19日 凌晨 03:14:07	YYYYMMDD HHMMSS	混合日期和时间值，时间戳

• 枚举类型和集合类型：

  类型	格式	用途
  ENUM	ENUM(选项a,选项b,选项c,选项d)	多选一
  SET	SET(选项a,选项b,选项c,选项d)	多选多

  只能写括号中的选项，不能写括号里没有的。

二、约束条件

• 约束条件的概念：

  约束条件是在创建表时，对字段的值的一些限制，比如：字段的值能否为空、是否允许重复等。
  约束条件可以写多个。

• 常见的约束条件：

  • NOT NULL：

    该字段的值不能为空。

  • UNSIGNED：

    无符号，按照正数处理。

  • ZAROFILL：

    用0填充不足显示宽度的位数。如，显示宽度为8，数值为123，则显示：00000123。

  • DEFAULT：

    设置字段的默认值，比如默认18岁：age TINYINT DEFAULT 18。

  • UNIQUE：

    • 单列唯一：该列不允许出现重复的值，直接写在字段的后面；
    • 联合唯一：多个字段的值，形成的组合不能重复；写法为：在所有字段定义的最后面单独写上UNIQUE(字段名,字段名……)。

  • AUTO_INCREMENT：

    使整数类型的字段值自动增长，每次插入记录时，都在上一个值的基础上加1，然后自动保存到表中，默认从1开始。

    **自增的字段只会一直增加，不会回退。**比如，最后一个记录的自增字段值为3，即便将其删除，下次添加记录时，依旧会取3的下一个值，也就是4。

    使用TRUNCATE 表名可以清空表并重置主键。

    一张表只能有一个自增长列，通常只用于主键。

  • PRIMARY KEY：

    主键约束，效果等价于NOT NULL+UNIQUE，用来设置主键。主键是一个列或者列的组合，其值能唯一地标识表中的每一行。

    如果不手动设置主键，InnoDB会按字段定义顺序，找第一个非空且唯一的字段作为主键，若果找不到，会自动创建一个隐藏的主键（无法使用，也不能提高查询速度）。

    联合主键：使用多个字段的组合作为主键，在所有字段定义的最后面单独写上PRIMARY KEY(字段名,字段名……)。

  • FOREIGN KEY：

    外键约束。用于在多表关联时，建立关系，比较复杂，在下一篇详细解释。

下一篇