mysql 自增长sql mysql 自增长

一、MySQL约束概述

在 MySQL 中，约束是指对表中数据的一种约束，能够确保数据库中数据的正确性和有效性。

在 MySQL 中，主要支持以下 6 种约束：

• 主键约束
• 唯一约束
• 检查约束
• 非空约束
• 默认值约束
• 外键约束

二、主键约束

主键(PRIMARY KEY)的完整称呼是“主键约束”，是 MySQL 中使用最为频繁的约束。
主键分为单字段主键和多字段联合主键，
使用主键应注意以下几点：

• 每个表只能定义一个主键。
• 主键值必须唯一标识表中的每一行，且不能为 NULL，即表中不可能存在有相同主键值的两行数据。这是唯一性原则。
• 一个字段名只能在联合主键字段表中出现一次。
• 联合主键不能包含不必要的多余字段。当把联合主键的某一字段删除后，如果剩下的字段构成的主键仍然满足唯一性原则，那么这个联合主键是不正确的。这是最小化原则。

(1)创建表时设置主键约束

在 CREATE TABLE 语句中，通过 PRIMARY KEY 关键字来指定主键。

在定义字段的同时指定主键，该字段即为主键。

PRIMARY KEY [默认值]

实例：

CREATE TABLE `NewTable` (`id`  int NOT NULL PRIMARY KEY,`usename`  varchar(50) NULL ,`phone`  varchar(11) NULL ,);

或单独一行定义主键

[CONSTRAINT ] PRIMARY KEY [字段名]

单独一行定义可以设置联合主键，主键由多个字段联合组成。

PRIMARY KEY [字段1，字段2，…,字段n]

实例：

CREATE TABLE `NewTable` (`id`  int NOT NULL ,`usename`  varchar(50) NULL ,`phone`  varchar(11) NULL ,PRIMARY KEY (`id`));

(2)在修改表时添加主键约束

主键约束不仅可以在创建表的同时创建，也可以在修改表时添加。但是需要注意的是，设置成主键约束的字段中不允许有空值和重复。格式如下：

ALTER TABLE  ADD PRIMARY KEY();

实例：

ALTER TABLE `student`ADD PRIMARY KEY (`s_id`);

(3)删除主键约束

删除主键约束的语法格式如下：

ALTER TABLE  DROP PRIMARY KEY;

实例：

ALTER TABLE `student`DROP PRIMARY KEY;

若修改主键时，已有主键存在，需先删除原有的主键。

ALTER TABLE `student`DROP PRIMARY KEY,ADD PRIMARY KEY (`s_id`, `s_name`);

三、MySQL AUTO_INCREMENT：主键自增长

在 MySQL 中，当主键定义为自增长后，这个主键的值就不再需要用户输入数据了，而由数据库系统根据定义自动赋值。每增加一条记录，主键会自动以相同的步长进行增长。

通过给字段添加 AUTO_INCREMENT 属性来实现主键自增长

字段名 数据类型 AUTO_INCREMENT

• 默认情况下，AUTO_INCREMENT 的初始值是 1，每新增一条记录，字段值自动加 1。
• 一个表中只能有一个字段使用 AUTO_INCREMENT 约束，且该字段必须有唯一索引，以避免序号重复(即为主键或主键的一部分)。
• AUTO_INCREMENT 约束的字段必须具备 NOT NULL 属性。
• AUTO_INCREMENT 约束的字段只能是整数类型(TINYINT、SMALLINT、INT、BIGINT 等)。
• AUTO_INCREMENT 约束字段的最大值受该字段的数据类型约束，如果达到上限，AUTO_INCREMENT 就会失效。

实例：

CREATE TABLE `NewTable` (`id`  int NOT NULL AUTO_INCREMENT ,`name`  varchar(30) NULL ,PRIMARY KEY (`id`));

可以在表选项里指定自增字段初始值，AUTO_INCREMENT=初始值。

CREATE TABLE `NewTable` (`id`  int NOT NULL AUTO_INCREMENT ,`nickname`  varchar(255) NULL ,PRIMARY KEY (`id`))ENGINE=InnoDBAUTO_INCREMENT=100;

四、唯一约束

MySQL 唯一约束(Unique Key)是指所有记录中字段的值不能重复出现。

唯一约束与主键约束相似的是它们都可以确保列的唯一性。不同的是，唯一约束在一个表中可有多个，并且设置唯一约束的列允许有空值，但是只能有一个空值。而主键约束在一个表中只能有一个，且不允许有空值。比如，在用户信息表中，为了避免表中用户名重名，可以把用户名设置为唯一约束。

(1)创建表时设置唯一约束

使用UNIQUE 关键字指定唯一约束。

UNIQUE

实例：

CREATE TABLE `NewTable` (`id`  int NOT NULL AUTO_INCREMENT ,`nickname`  varchar(255) NULL UNIQUE ,PRIMARY KEY (`id`),);

(2)唯一约束与唯一索引的区别

• 1、唯一约束和唯一索引，都可以实现列数据的唯一，列值可以有null。
• 2、创建唯一约束，会自动创建一个同名的唯一索引，该索引不能单独删除，删除约束会自动删除索引。唯一约束是通过唯一索引来实现数据的唯一。
• 3、创建一个唯一索引，这个索引就是独立，可以单独删除。
• 4、如果一个列上想有约束和索引，且两者可以单独的删除。可以先建唯一索引，再建同名的唯一约束。
• 5、如果表的一个字段，要作为另外一个表的外键，这个字段必须有唯一约束(或是主键)，如果只是有唯一索引，就会报错。

创建唯一索引语法格式如下：

UNIQUE INDEX `索引名称` (`字段名称`)

实例：

CREATE TABLE `NewTable` (`id`  int NOT NULL AUTO_INCREMENT ,`nickname`  varchar(255) NULL ,PRIMARY KEY (`id`),UNIQUE INDEX `nickname` (`nickname`) );

(3)修改表时添加唯一约束

修改表时添加唯一约束的语法格式如下:

ALTER TABLE  ADD [CONSTRAINT]  UNIQUE [index] ();

实例：

ALTER TABLE `student`ADD UNIQUE INDEX `name` (`s_name`) ;

(4) 删除唯一约束

在 MySQL 中删除唯一约束的语法格式如下：

ALTER TABLE  DROP INDEX ;

实例：

ALTER TABLE `student`DROP INDEX `name`;

五、非空约束

MySQL 非空约束(NOT NULL)指字段的值不能为空。对于使用了非空约束的字段，如果用户在添加数据时没有指定值，数据库系统就会报错。

(1)创建表时设置非空约束

创建表时可以使用 NOT NULL 关键字设置非空约束，具体的语法格式如下：

NOT NULL;

实例：

CREATE TABLE `NewTable` (`id`  int NOT NULL AUTO_INCREMENT ,`nickname`  varchar(255) NOT NULL ,PRIMARY KEY (`id`),);

(2)修改表时添加非空约束

修改时，该字段上已有的所有约束仍要包含在语句中，否则将被改变。修改表时设置非空约束的语法格式如下：

ALTER TABLE CHANGE COLUMN    NOT NULL ;#不修改字段名称，则用 MODIFY COLUMN ALTER TABLE MODIFY COLUMN   NOT  NULL ;

(3)删除非空约束

删除非空约束，只要把字段的not null 改为null 即可，该字段上已有的所有约束仍要包含在语句中，否则将被改变。

修改表时删除非空约束的语法格式如下：

ALTER TABLE CHANGE COLUMN    NULL ;#不修改字段名称，则用 MODIFY COLUMN ALTER TABLE MODIFY COLUMN   NULL ;

实例：

ALTER TABLE `student`CHANGE COLUMN `s_name` `s_name1`  varchar(20) NULL DEFAULT '';ALTER TABLE `student`MODIFY COLUMN `s_name`  varchar(20) NULL DEFAULT '';

六、默认值约束

默认值(Default)的完整称呼是“默认值约束(Default Constraint)”，用来指定某列的默认值。在表中插入一条新记录时，如果没有为某个字段赋值，系统就会自动为这个字段插入默认值。

(1)创建表时设置默认值约束

创建表时可以使用 DEFAULT 关键字设置默认值约束，具体的语法格式如下：

DEFAULT ;

CREATE TABLE `NewTable` (`id`  int NOT NULL AUTO_INCREMENT ,`nickname`  varchar(255) NULL DEFAULT '' ,PRIMARY KEY (`id`));

(2)修改表时添加默认值约束

修改表时添加默认值约束的语法格式如下：

ALTER TABLE CHANGE COLUMN    DEFAULT  ;# 不修改字段名称，则用 MODIFY COLUMN ALTER TABLE MODIFY COLUMN   DEFAULT  ;

CHANGE COLUMN和MODIFY COLUMN 语法里，不修改的约束仍要写入语句中，否则将修改。

(3)删除默认值约束

删除默认值约束。只要把DEFAULT设置为 NULL

ALTER TABLE CHANGE COLUMN      DEFAULT NULL;#ALTER TABLE MODIFY COLUMN    DEFAULT NULL;

七、MySQL检查约束(CHECK)

MySQL 检查约束(CHECK)是用来检查数据表中字段值有效性的一种手段。

检查约束使用 CHECK 关键字，具体的语法格式如下：

CHECK

表达式”指的就是 SQL 表达式，用于指定需要检查的限定条件。

在更新表数据的时候，系统会检查更新后的数据行是否满足 CHECK 约束中的限定条件。MySQL 可以使用简单的表达式来实现 CHECK 约束，也允许使用复杂的表达式作为限定条件，例如在限定条件中加入子查询。

• 基于列的 CHECK 约束：将 CHECK 约束子句置于表中某个列的定义之后
• 基于表的 CHECK 约束：将 CHECK 约束子句置于所有列的定义以及主键约束和外键定义之后，该约束可以同时对表中多个列设置限定条件

修改表时添加检查约束

修改表时设置检查约束的语法格式如下：

ALTER TABLE tb_name ADD CONSTRAINT  CHECK()

删除检查约束

修改表时删除检查约束的语法格式如下：

ALTER TABLE  DROP CONSTRAINT ;

八、MySQL外键约束(FOREIGN KEY)

MySQL 外键约束(FOREIGN KEY)是表的一个特殊字段，经常与主键约束一起使用。对于两个具有关联关系的表而言，相关联字段中主键所在的表就是主表(父表)，外键所在的表就是从表(子表)。

外键用来建立主表与从表的关联关系，为两个表的数据建立连接，约束两个表中数据的一致性和完整性。

主表删除某条记录时，从表中与之对应的记录也必须有相应的改变。一个表可以有一个或多个外键，外键可以为空值，若不为空值，则每一个外键的值必须等于主表中主键的某个值。

定义外键时，需要遵守下列规则：

• 主表必须已经存在于数据库中，或者是当前正在创建的表。如果是后一种情况，则主表与从表是同一个表，这样的表称为自参照表，这种结构称为自参照完整性。
• 必须为主表定义主键。
• 主键不能包含空值，但允许在外键中出现空值。也就是说，只要外键的每个非空值出现在指定的主键中，这个外键的内容就是正确的。
• 在主表的表名后面指定列名或列名的组合。这个列或列的组合必须是主表的主键或候选键。
• 外键中列的数目必须和主表的主键中列的数目相同。
• 外键中列的数据类型必须和主表主键中对应列的数据类型相同。

(1)创建表时设置外键约束

通过 FOREIGN KEY 关键字来指定外键

[CONSTRAINT ] FOREIGN KEY 字段名 [，字段名2，…]REFERENCES  主键列1 [，主键列2，…]

(2)修改表时添加外键约束

外键约束也可以在修改表时添加，但是添加外键约束的前提是：从表中外键列中的数据必须与主表中主键列中的数据一致或者是没有数据。

在修改数据表时添加外键约束的语法格式如下：

ALTER TABLE  ADD CONSTRAINT FOREIGN KEY() REFERENCES  ();

(3)删除外键约束

外键一旦删除，就会解除主表和从表间的关联关系。

删除外键约束的语法格式如下所示：

ALTER TABLE  DROP FOREIGN KEY ;

九、MySQL查看表中的约束

在 MySQL 中可以使用 SHOW CREATE TABLE 语句来查看表中的约束。

SHOW CREATE TABLE ;