db - MySQL - MySQL的约束和索引

db - MySQL - MySQL的约束和索引

世界上并没有完美的程序，但是我们并不因此而沮丧，因为写程序就是一个不断追求完美的过程。

文章目录

• MySQL的约束和索引
• • 主键约束
  • 唯一约束
  • 外键约束
  • 非空约束
  • 默认值约束
  • 索引
  • 自增列

MySQL的约束和索引 主键约束

• 特点

  • 唯一
  • 非空
  • 索引

• 语法

  • create table (
    id int primary key
    );

  • create table (
    id int,
    mobile char(11),
    primary key (id, mobile)
    );

  • alter table add primary key (col1, col2, …);

• 使用

  • 建表t_3，同时指定id为主键

  create table `t_3` (
      `id` int primary key
  );
  

  • 建表t_4，同时指定id和mobile为主键

  create table `t_4` (
      `id` int,
      `mobile` char(11),
      primary key (`id`, `mobile`)
  );
  

  • 建表t_5，后续添加主键id和name

  create table `t_5` (
      `id` int,
      `name` varchar(255)
  );
  
  alter table `t_5` add primary key (`id`, `name`);
  

唯一约束

• 特点

  • 唯一
  • 可为空
  • 索引

• 语法

  • create table (
    name varchar(255) unique
    );

  • create table (
    name varchar(255),
    unique index (name)
    );

  • alter table add unique (col1, col2, …);

• 使用

  • 创建表t_6，字段id使用主键约束，字段name使用唯一约束

  create table `t_6` (
      `id` int primary key,
      `name` varchar(255) unique
  );
  

  • 创建表t_7，指定唯一约束name

  create table `t_7` (
      `id` int primary key,
      `name` varchar(255),
      unique index (name)
  );
  

  • 创建表t_8，然后添加唯一约束name

  create table `t_8` (
      `id` int primary key,
      `name` varchar(255)
  );
  
  alter table `t_8` add unique (name);
  

外键约束

• 特点

  • 约束两表之间的关系

• 语法

  • create table (
    id int primary key,
    sub_id int,
    name varchar(255),
    foreign key (sub_id) references t_sub (id);
    );

  • alter table add foreign key (sub_id) references t_sub (id);

  • alter table drop foreign key ;

• 使用

  • 创建t_9和t_10表，并将t_9的主键作为t_10的外键

  create table `t_9` (
      id int primary key
  );
  
  create table `t_10` (
      `id` int primary key,
      `t_9_id` int,
      foreign key (`t_9_id`) references `t_9` (`id`)
  );
  
  show create table `t_10`;
  

  • 创建t_11表，然后添加t_9主键为外键

  create table `t_11` (
      `id` int primary key,
      `t_9_id` int
  );
  
  alter table `t_11` add foreign key (`t_9_id`) references `t_9` (`id`);
  
  show create table `t_11`;
  

  • 删除外键约束

  alter table `t_11` drop foreign key `t_11_ibfk_1`;
  
  show create table `t_11`;
  

非空约束

• 特点

  • 限制字段非空

• 语法

  • create table (
    id int primary key,
    name varchar(255) not null
    );

• 使用

  • 创建t_12，name设置为非空

  create table `t_12` (
      id int primary key,
      name varchar(255) not null
  );
  
  describe `t_12`;
  

默认值约束

• 特点

  • 为字段设置默认值

• 语法

  • create table (
    id int primary key,
    name varchar(255) default ‘hello’;
    );

• 使用

  • 创建t_13，name默认值为hello

  create table `t_13` (
      id int primary key,
      name varchar(255) default 'hello'
  );
  
  describe `t_13`;
  

索引

• 特点

  • 为字段添加索引以提高查询速度

• 语法

  • create table (
    id int primary key,
    name varchar(255),
    index (name)
    );

  • create index on (col1, col2, …);

• 使用

  • 创建t_14, 为name创建索引

  create table `t_14` (
      `id` int primary key,
      `name` varchar(255),
      index (`name`)
  );
  
  show create table `t_14`;
  

  • 创建t_15，然后为name和mobile创建联合索引

  create table `t_15` (
      `id` int primary key,
      `name` varchar(255),
      `mobile` char(11)
  );
  
  create index t_15_idx_name_mobile on `t_15` (`name`, `mobile`);
  
  show create table `t_15`;
  

自增列

• 特点

  • 字段自增

• 语法

  • create table (
    id int primary key auto_increment
    );

  • alter table modify auto_increment;

• 使用

  • 由于高并发及数据库集群等情况的存在，目前自增列做主键可能并不太推荐了，主键生成策略主要有UUID和雪花算法等
  • 创建t_16, 指定id为自增键

  create table `t_16` (
      `id` int primary key auto_increment
  );
  
  describe `t_16`;
  

  • 创建t_17, 然后设置id为自增键

  create table `t_17` (
      `id` int primary key
  );
  
  alter table `t_17` modify `id` int auto_increment;
  
  describe `t_17`;