MySQL - 列属性

ゞ浴缸里的玫瑰 2022-05-06 11:57 208阅读 0赞

数据表中，真正约束字段的是数据类型。但是数据类型的约束很单一，需要有一些额外的约束，来更加保证数据的合法性。

--------------------

**查看表数据结构**

*  红色表示数据类型；
 *  蓝色表示列属性；

![70][]

**列属性有很多，大致如下：**

NOT NULL/NULL,   
DEFAULT,   
PRIMARY KEY,   
UNIKUE KEY,   
AUTO\_INCREMENT,   
COMMENT

--------------------

## **【1】空属性** ##

其有两个值：null(默认值) 和 not null (不为空) ；

虽然默认的数据库基本字段为空，但是生产环境中尽可能保证字段都不为空。空数据没有意义，且无法参加运算。

select 1+'hello',null,1+null;

![70 1][]

--------------------

## **【2】列描述** ##

comment：描述，没有实际含义，为列增加文字说明，会随着表创建一起储存起来。

--------------------

## **【3】默认值** ##

default：某一种数据会经常性的出现某个具体的值，可以在一开始指定好，用户可以在使用的时候选择性的使用默认值或插入新数据。

create table my_default(
    name VARCHAR(20) not null unique key ,
    gender varchar(4) DEFAULT '男'
    )charset utf8;
    
    desc my_default;
    
    insert into my_default values('tom',null);--gender为null
    
    insert into my_default values('lily',DEFAULT);-- default，gender取默认值
    
    select * from my_default;

![70 2][]

*  **当不对gender列进行插入的时候，gender将会使用默认值**

insert into my_default (name) VALUE ('lir');--指定列，gender取默认值
    
    select * from my_default;

![70 3][]

**可见，指定列 与 values使用default 两种方式都可以使具有默认值的列，在插入数据的时候使用默认值。**

--------------------

## **【4】主键** ##

primary key ：一张表**只能有一个字段**可以使用对应的键，用来唯一的约束该字段里面的数据，不能重复。**主键本身一定不为空，且不允许重复。**

**给表增加主键，大体分为三种：**

**① 创建表的时候在字段后面使用 primary key关键字；**

create table my_default(
    id int PRIMARY KEY,--使用关键字
    name VARCHAR(20) not null unique key ,
    gender varchar(4) DEFAULT '男'
    )charset utf8;

**② 在创建表的时候，在所有的字段之后使用 primary key(主键的字段 列表)来创建主键(如果有多个字段作为主键，称之为复合主键)**

create table my_pri(
    number char(10),
    course char(10),
    score TINYINT,
    PRIMARY KEY(number,course)--复合主键
    )charset utf8;
    
    desc my_pri;

![70 4][]

**测试如下：**

insert into my_pri VALUES('001','IT','10');--插入成功
    insert into my_pri VALUES('001','IT','10');--插入失败
    
    insert into my_pri VALUES('001','IT2','10');--插入成功

![70 5][]

**③ 创建表之后，为表追加主键。有两种方式，第一位修改表字段属性；第二直接追加。**

第一种方式示例如下：

alter table table_name add primary key(column) ;
    
    --alter TABLE my_pri add PRIMARY KEY(number,course);--增加复合主键
    
    --alter table my_pri add CONSTRAINT pk_score PRIMARY KEY(score)--使用关键字CONSTRAINT 且指定主键名字pk_score 
    
    --alter table my_pri add CONSTRAINT  PRIMARY KEY(score)--使用关键字CONSTRAINT 且使用默认名字

删除主键：

alter table my_pri drop PRIMARY KEY;
    --不能根据主键名字删除，因为只有一个主键

**【主键分类】**

在实际创建表的过程中，很少使用真实的业务 数据作为主键字段(业务主键，如课程号，学号)；大部分时候是使用逻辑性字段(字段没有什么业务含义，值是什么都没有关系)，将这种字段主键称之为逻辑主键。

--------------------

【5】自动增长  
| - - |   
auto\_increment：当对应的字段，不给值 || 给默认值 || 给null 值，会自动的被系统触发。

系统会从当前字段中已有的最大值进行+1操作，得到一个新的不同的字段，作为下一次插入数据是自增长字段的值。若手动修改了auto\_increment，且为向上修改，那么下一次值为auto\_increment！

自增长通常跟主键搭配。

自增长特定：

① 任何一个字段要做自增长前提必须是一个索引；   
② 自增长字段必须是整型数字；

| -   
测试如下

create table my_auto(
    id int auto_increment ,
    name varchar(20)
    )charset utf8;
    --失败

![70 6][]

| - **说明自增长字段必须是key（索引）**\- |

正确如下：

create table my_auto(
    id int primary key auto_increment ,
    name varchar(20)
    )charset utf8;

![70 7][]

**插入数据**

insert into my_auto  values(null,'tom');
    
    insert into my_auto  values('','tom2');--插入失败
    
    insert into my_auto  values('null','tom3');--插入失败
    
    insert into my_auto  values(DEFAULT,'tom4');

其中，第二、三将会插入失败–主键不能为空：

![70 8][]

第一、四插入null 或者默认值，将会自动增长并插入：

![70 9][]

**如果自增长对应的字段输入了值，那么自增长失效。下一次插入数据，仍旧会获取 最大值+1：**

insert into my_auto  values(5,'tom5');
    insert into my_auto  values(default,'tom6');
    
    select * from my_auto;

![70 10][]

**【修改自增长】**

自增长如果是设计到字段改变，必须先删除自增长，后增加(一张表只能有一个自增长)；

修改当前自增长已存在的值，修改只能比最大值大，不能小(小不生效)。

语法如下：

alter table table_name auto_increment = 值 ;

**测试 向下修改：**

alter table my_auto auto_increment=3;--现在数据表已存在最大值7
    
    show create table my_auto;
    
    CREATE TABLE `my_auto` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `name` varchar(20) DEFAULT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8
    
    --此时AUTO_INCREMENT=8 ，故向下修改无效

**测试 向上修改：**

alter table my_auto auto_increment=10;
    
    show create table my_auto;
    
    CREATE TABLE `my_auto` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `name` varchar(20) DEFAULT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8
    
    --可以向上修改

插入数据测试：

insert into my_auto VALUES(null,'Lucy');
    
    select * from my_auto ;

![70 11][]

**此时id为10！！！**

**演示到这里，有没有一个疑问：为什么自增长是从1开始？**

我们知道，所有系统的表现(如字符集，校对集)都是由系统内部的变量进行控制的。

**查看自增长对应的变量：`show VARIABLES LIKE '%auto_increment%'`**

auto_increment_increment 1 --步长值
    auto_increment_offset    1 --起始值

![70 12][]

**可以修改变量实现不同的效果。但是修改是对整个数据库进行修改而不是单张表，且修改是会话级别，关闭并重新打开链接，变量恢复默认值**

set auto_increment_increment = 2;
    
    show VARIABLES LIKE '%auto_increment%';

![70 13][]

**变量修改后，在第二次插入数据时候生效：**

*  **因为修改变量并不会立即更新表选项的auto\_increment，当再插入数据后，会更新auto\_increment ,此时检测到步长已经改变，故之后插入数据会使用新的步长值！！**

insert into my_auto values(null,'Lucy12');
    insert into my_auto values(null,'Lucy12');
    
    select * from my_auto;

![70 14][]

--------------------

【删除自增长】

自增长不能通过drop消除，只能通过modify column；

需要注意的是，如果自增长对应的字段为主键，那么修改 列属性的时候，不要再加primary key ; 会被系统认为又定义一个主键，将会提示”Multiple primary key defined”

alter table my_auto modify id int primary key;
    --错误，会被认为又定义一个主键;
    
    alter table my_auto modify id int ;
    --正确，且不会丢失掉 id 的主键

![70 15][]

--------------------

【6】唯一键  
一张表往往有很多字段需要唯一性，数据不能重复；但是一张表只能有一个字段为主键，那么唯一键(unique key)，就可以解决表中有多个字段需要唯一性约束的问题。

唯一键默认值允许自动为空，而且可以多个不同字段为空—空字段不参与唯一性比较。

【增加唯一键】

分为创建表和创建后两种方式：

① 创建表时(又分两种)：

--第一种，字段增加
    create table my_unique(
    name varchar(4) unique key,
    number varchar(20) unique key
    )charset utf8;
    
    --第二种，字段末尾增加
    create table my_unique(
    name varchar(4) ,
    number varchar(20) ,
    unique key name(name),--指定唯一约束名字
    unique key(number)--使用默认名字
    
    )charset utf8;

![70 16][]

② 创建表后(又分两种)：

-- 创建表，不带唯一约束
    create table my_unique(
    name varchar(4) ,
    number varchar(20) 
    )charset utf8;

--第一种，modify column
    alter table my_unique modify column name varchar(5) UNIQUE KEY;
    
    --第二种 add constraint unique key
    
    alter TABLE my_unique add CONSTRAINT num_uk UNIQUE KEY(number);
    --指定唯一约束名字为num_uk

**【删除唯一约束】**

alter table my_unique drop index num_uk;
    
    --根据指定约束名字删除，若创建的时候未指定名字，默认使用列名作为唯一约束名字。

CREATE TABLE `my_unique` (
      `name` varchar(5) DEFAULT NULL,
      `number` varchar(20) DEFAULT NULL,
      UNIQUE KEY `num_uk` (`number`),--唯一约束名字为num_uk
      UNIQUE KEY `name` (`name`)--唯一约束名字默认为列名
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8

下面演示一个有趣的现象：

--创建表
    create table my_unique(
    name varchar(4) not null,--注意这里 not null
    number varchar(20) 
    )charset utf8;
    
    --增加唯一约束
    alter table my_unique modify column name  varchar(5) not NULL UNIQUE KEY;
    
    --查看表结构
    desc my_unique;

![70 17][]

**注意key那一列，显示 PRI 不是UNI！！！**

**解释如下：该表没有主键；刚好是一个不为空的唯一键，性质和主键一样。故MySQL无法判断，显示了PRI。当表中已经存在主键，则会正常显示为UNI**

--------------------

**【复合唯一约束】**

--第一种创建方式
    create table my_unique(
    name varchar(4) not null,
    number varchar(20) ,
    unique KEY na_um_un(name,number)--字段末尾创建，使用指定名
    )charset utf8;
    
    desc my_unique;

![70 18][]

*  插入数据

insert into my_unique values('tom','1001');
    
    insert into my_unique values('tom','1001');

*  提示数据重复–约束名字为 na\_um\_un(创建时候指定的名字)

![70 19][]

--第二种创建方式
    
    --创建空表,不带约束
    create table my_unique(
    name varchar(4) not null,
    number varchar(20) 
    )charset utf8;
    
    --增加复合约束
    alter TABLE my_unique add CONSTRAINT num_na_uk UNIQUE KEY(number,name);
    
    --查看表结构
    desc my_unique;

![70 20][]

*  删除指定名字复合约束

alter table my_unique drop index num_na_uk;

*  删除默认名字复合约束
 *   *  若不指定名字，那么复合约束名字为第一个字段名字

alter TABLE my_unique add CONSTRAINT UNIQUE KEY(number,name);
    
    --查看表创建语句
    show create TABLE my_unique;
    
    CREATE TABLE `my_unique` (
      `name` varchar(4) NOT NULL,
      `number` varchar(20) DEFAULT NULL,
      UNIQUE KEY `number` (`number`,`name`)--这里，名字为number
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    
    --删除复合约束
    alter table my_unique drop index number;

**另外，唯一键同样是索引的类型，故唯一键的添加删除可以如下：**

ALTER TABLE `weight_num_curr_detail`
    DROP INDEX `unque_store_brand` ;
    ADD UNIQUE INDEX `unque_store_brand` (`store_code`, `brand_code`, `year_num`) USING BTREE ;

--------------------

本文转载于：[https://blog.csdn.net/J080624/article/details/71703297][https_blog.csdn.net_J080624_article_details_71703297]

[70]: /images/20220506/a9525aa15d53416f97ac305f9fa90f42.png
[70 1]: /images/20220506/7ab227bae760473fa314b3d4173ae629.png
[70 2]: /images/20220506/97d5a875d7f34d12b86ec865b6cf40a5.png
[70 3]: /images/20220506/f4392a77f44f4fe380da70b979b15aac.png
[70 4]: /images/20220506/cd050412d8fa456a81771303aea9972c.png
[70 5]: /images/20220506/5dcfda872f454aa4a77a6a55df4c9f70.png
[70 6]: /images/20220506/36d9104445b54879a4aad718d9d2fbf3.png
[70 7]: /images/20220506/aba01e4efcad47a7a91415d892872b5d.png
[70 8]: /images/20220506/b1f276fd5eaa4b319585af538912d762.png
[70 9]: /images/20220506/2b3d08c9897948bfb1dd9d4ee8c99366.png
[70 10]: /images/20220506/bc5dea69eb91416bb2b92fe82950b1db.png
[70 11]: /images/20220506/af53104ae60543b382ecdfa27d6084a3.png
[70 12]: /images/20220506/8f29c5ddaf634cf490c71e058ce3b272.png
[70 13]: /images/20220506/e413fef2b96d4171a5515c962e2d22af.png
[70 14]: /images/20220506/55dd7a125867431d975126a76aff8ca3.png
[70 15]: /images/20220506/70a4f415707f4e34a0acfd27b77093a3.png
[70 16]: /images/20220506/a2259d97bd9144a788eec6d18cfc65bd.png
[70 17]: /images/20220506/852ae3c853d04ecdad5c2dea82475213.png
[70 18]: /images/20220506/7f811837449c4a7fb62a873925dafde6.png
[70 19]: /images/20220506/5b72b2d61179433e8e7817ba0f769d7f.png
[70 20]: /images/20220506/bf5a53e28a5b47d08c7ab13edebf73d5.png
[https_blog.csdn.net_J080624_article_details_71703297]: https://blog.csdn.net/J080624/article/details/71703297