SQLServer 中自增(identity)用法

一、含义

identity表示该字段的值会自动更新，不需要我们维护，通常情况下我们不可以直接给identity修饰的字符赋值，否则编译时会报错。

二、语法

列名数据类型约束 identity(m,n)

m表示的时初始值，n表示的是每次插入时自动增加的值

要么同时指定m和n的值，要么m和n都不指定(默认m和n 等于1)，不能只写其中一个。

三、实例演示

1、不指定m和n的值

-- 创建图书信息表
CREATE TABLE dbo.book
(
    bookId BIGINT NOT NULL PRIMARY KEY IDENTITY, -- primary key column
    bookname NVARCHAR(50)  NOT NULL, --书名
    author NVARCHAR(50) NOT NULL--作者
);
--插入书籍信息
INSERT INTO dbo.book VALUES
('数据库原理与应用教程--SQL Server2008','尹志宇，郭晴'),
('设计模式其实很简单','刘径舟，刘玉华');

--查看图书表中的信息
SELECT * FROM dbo.book;

最后输出结果：

identity不指定m、n时，默认=1,因此每次插入时主键自动加1。

2、指定m和n的值

-- 创建图书信息表
CREATE TABLE dbo.book
(
    bookId BIGINT NOT NULL PRIMARY KEY IDENTITY(5,3), -- primary key column
    bookname NVARCHAR(50)  NOT NULL, --书名
    author NVARCHAR(50) NOT NULL--作者
);
--插入书籍信息
INSERT INTO dbo.book VALUES
('数据库原理与应用教程--SQL Server2008','尹志宇，郭晴'),
('设计模式其实很简单','刘径舟，刘玉华');
DELETE FROM dbo.book where bookId=8;
INSERT INTO dbo.book VALUES
('数据库原理与应用教程--SQL Server2008','尹志宇，郭晴'),
('设计模式其实很简单','刘径舟，刘玉华');
--查看图书表中的信息
SELECT * FROM dbo.book;

最后输出结果：

3、删除一条记录并继续插入

-- 创建图书信息表
CREATE TABLE dbo.book
(
    bookId BIGINT NOT NULL PRIMARY KEY IDENTITY(5,3), -- primary key column
    bookname NVARCHAR(50)  NOT NULL, --书名
    author NVARCHAR(50) NOT NULL--作者
);
--插入书籍信息
INSERT INTO dbo.book VALUES
('数据库原理与应用教程--SQL Server2008','尹志宇，郭晴'),
('设计模式其实很简单','刘径舟，刘玉华');
DELETE FROM dbo.book where bookId=8;
INSERT INTO dbo.book VALUES
('数据库原理与应用教程--SQL Server2008','尹志宇，郭晴'),
('设计模式其实很简单','刘径舟，刘玉华');
--查看图书表中的信息
SELECT * FROM dbo.book;

最后输出结果：

删除之后，identity的自增机制并不会返回，而是继续加下去。例如删除的bookid为8的记录，新插入的记录不会是8，而是13。

4、更新identity的值

dbcc checkident(tablename,ressed,n)

-- 创建图书信息表
CREATE TABLE dbo.book
(
    bookId BIGINT NOT NULL PRIMARY KEY IDENTITY(5,3), -- primary key column
    bookname NVARCHAR(50)  NOT NULL, --书名
    author NVARCHAR(50) NOT NULL--作者
);
--插入书籍信息
INSERT INTO dbo.book VALUES
('数据库原理与应用教程--SQL Server2008','尹志宇，郭晴'),
('设计模式其实很简单','刘径舟，刘玉华');
DELETE FROM dbo.book where bookId=8;
dbcc checkident(dbo.book,reseed,5)
--查看图书表中的信息
INSERT INTO dbo.book VALUES
('数据库原理与应用教程--SQL Server2008','尹志宇，郭晴'),
('设计模式其实很简单','刘径舟，刘玉华');
SELECT * FROM dbo.book;

最后输出结果：

identity的自增机制进行修改，本例用作identity初始值改换，即插入下一条记录后可以就是(5+3)，后续亦可依次加3.

5、自行插入identity值

语法：

SET IDENTITY_INSERT tablename ON

INSERT INTO tablename(column1,column2,column3,) VALUES(value1,value2,value3)

SER IDENTITY_INSERT tablename OFF

注意：插入时必须指定identity修饰的字段名

-- 创建图书信息表
CREATE TABLE dbo.book
(
    bookId BIGINT NOT NULL PRIMARY KEY IDENTITY(5,3), -- primary key column
    bookname NVARCHAR(50)  NOT NULL, --书名
    author NVARCHAR(50) NOT NULL--作者
);
--插入书籍信息
INSERT INTO dbo.book VALUES
('数据库原理与应用教程--SQL Server2008','尹志宇，郭晴'),
('设计模式其实很简单','刘径舟，刘玉华');


--identity开启插入模式
SET IDENTITY_INSERT dbo.book ON;


--必须指定identity 修饰的Column 名
INSERT INTO dbo.book(bookId,bookname,author)
VALUES
(10,'数据库原理与应用教程--SQL Server2008','尹志宇，郭晴');

--identity关闭插入模式
SET IDENTITY_INSERT dbo.book OFF;

--插入书籍信息
INSERT INTO dbo.book VALUES
('数据库原理与应用教程--SQL Server2008','尹志宇，郭晴'),
('设计模式其实很简单','刘径舟，刘玉华');


SELECT * FROM dbo.book;

最后输出结果：

identity开启自行插入模式，即没有约束限制，用户可以自行插入想要插入的数字。当identity的插入模式关闭后，之后插入的记录，identity会自行寻找记录中最大的一个数进行自动累加。