Sql 简明教程

SQL - Auto Increment

SQL 自动增量用于自动向表的列中添加唯一的顺序值。

The SQL Auto Increment is used to automatically add unique sequential values into a column of a table.

我们通常在创建表时定义列上的自动增量。当我们在表中插入新记录时，将向其添加唯一值。

We usually define the Auto Increment on a column while creating a table. And when we insert new records into the table, the unique values are added to them.

不同的 RDBMS 以不同的方式支持自动增量特性。

Different RDBMS support the Auto Increment feature in different ways.

Auto Increment in MySQL

在 MySQL 中，可以使用名为 AUTO_INCREMENT 的属性向表的列添加自动增量特性。

In MySQL, you can add the auto-increment feature to a column of a table using the attribute named AUTO_INCREMENT.

默认情况下，当我们在列上定义 AUTO_INCREMENT 属性时，唯一值将从“1”生成；而对于我们输入表中的每条新记录，列中的值将增加 1。因此，插入的第一条记录将具有值 1，第二条记录将具有值 2，依此类推。

By default, when we define the AUTO_INCREMENT attribute on a column, the unique values are generated from "1"; and for each new record we enter into the table, the values in the column will increment by 1. Thus, the first record inserted will have a value of 1, the second record will have a value of 2, and so on.

Syntax

以下是向 MySQL 表的列中添加 AUTO_INCREMENT 属性的语法 -

Following is the syntax to add AUTO_INCREMENT attribute to a column of a table in MySQL −

CREATE TABLE table_name(
   column1 datatype AUTO_INCREMENT,
   column2 datatype,
   column3 datatype,
   .....
   columnN datatype
);

Example

在查询中，我们创建一个名为 CUSTOMERS 的表，并将 AUTO_INCREMENT 添加到名为 ID 的列 -

In the query to we are creating a table named CUSTOMERS and adding the AUTO_INCREMENT to the column named ID −

CREATE TABLE CUSTOMERS(
   ID INT PRIMARY KEY AUTO_INCREMENT,
   NAME VARCHAR (20) NOT NULL,
   AGE INT NOT NULL,
   ADDRESS CHAR (25),
   SALARY DECIMAL (18, 2)
);

现在，让我们使用 INSERT 语句向 CUSTOMERS 表中插入值 -

Now, let us insert values into the CUSTOMERS table using the INSERT statement −

INSERT INTO CUSTOMERS (NAME, AGE, ADDRESS, SALARY) VALUES
("Ramesh", 32, "Ahmedabad", 2000.00),
("Khilan", 25, "Delhi", 1500.00),
("Kaushik", 23, "Kota", 2000.00),
("Chaitali", 25, "Mumbai", 6500.00);

Verification

要验证这一点，您需要使用 SELECT 查询检索 CUSTOMERS 的内容，如下所示 -

To verify this, you need to retrieve the contents of the CUSTOMERS using the SELECT query as −

SELECT * FROM CUSTOMERS;

Output

以下是上述查询的输出，您可以在此处观察到 ID 值是自动生成的 -

Following is the output of the above query, here you can observe that the ID values are generated automatically −

ID

NAME

AGE

ADDRESS

SALARY

1

Ramesh

32

Ahmedabad

2000.00

2

Khilan

25

Delhi

1500.00

3

Kaushik

23

Kota

2000.00

4

Chaitali

25

Mumbai

6500.00

AUTO_INCREMENT on Existing Columns

MySQL 还允许您使用 ALTER TABLE 语句对现有表实现 AUTO_INCREMENT 属性。

MySQL also allows you to implement the AUTO_INCREMENT attribute on an existing table, using the ALTER TABLE statement.

以下查询开始在 CUSTOMERS 表 CUSTOMERS 中从 5 对 ID 值进行递增 -

Following query starts incrementing the ID values from 5 in the CUSTOMERS table CUSTOMERS −

ALTER TABLE CUSTOMERS AUTO_INCREMENT = 100;

现在，让我们插入更多记录，以查看 ID 值是否已自动递增。

Now, let us insert more records to see if the ID values are auto incremented.

INSERT INTO CUSTOMERS(NAME, AGE, ADDRESS, SALARY) VALUES
("Hardik", 27, "Bhopal", 8500.00),
("Komal", 22, "MP", 4500.00),
("Muffy", 24, "Indore", 10000.00);

要查看上述表格数据，我们使用以下 SELECT 查询：

To view the above table data, we use the following SELECT query −

SELECT * FROM CUSTOMERS;

Output

上述查询的输出如下所示。它显示了自动递增功能。ID 值从 100 开始为新插入记录获取 ID 值。

The output of the above query is shown below. It shows the auto increment in action. We are getting the ID values of the newly inserted records begins at 100.

ID

NAME

AGE

ADDRESS

SALARY

1

Ramesh

32

Ahmedabad

2000.00

2

Khilan

25

Delhi

1500.00

3

Kaushik

23

Kota

2000.00

4

Chaitali

25

Mumbai

6500.00

100

Hardik

27

Bhopal

8500.00

101

Komal

22

Hyderabad

4500.00

102

Muffy

24

Indore

10000.00

Auto Increment in SQL Server

在 SQL Server 中，没有直接命令/查询来执行自动递增。相反，我们使用 IDENTITY() 属性。该属性类似于 MySQL 中的 AUTO_INCREMENT 属性。它自动生成唯一、连续的数字，并且主要在 PRIMARY KEY 约束中使用。

In SQL Server, there is no direct command/query to perform Auto Increment. Instead, we use the IDENTITY() property. This property works similar to the AUTO_INCREMENT attribute in MySQL. It generates unique, sequential numbers automatically and it is mostly used on the PRIMARY KEY constraint.

Syntax

以下是 SQL Server 中 IDENTITY() 属性的基本语法：

Following is the basic syntax of IDENTITY() property in SQL Server −

CREATE TABLE table_name (
   column1 datatype IDENTITY [(seed, increment)],
   column2 datatype,
   column3 datatype,
   .....
   columnN datatype
);

该属性接受两个参数。它们描述如下：

This property accepts two parameters. The same are described below:

  1. seed: It sets the starting value for the auto-incrementing column.

  2. increment: It specifies how much the value increases by for each new row.

Example

在以下示例中，我们在名为 CUSTOMERS 的表的 ID 列上使用 IDENTITY() 属性：

In the following example, we are using the IDENTITY() property on the ID column of table named CUSTOMERS −

CREATE TABLE CUSTOMERS(
   ID INT PRIMARY KEY IDENTITY(1,1),
   NAME VARCHAR (20) NOT NULL,
   AGE INT NOT NULL,
   ADDRESS CHAR (25),
   SALARY DECIMAL (18, 2)
);

创建表后，我们使用以下查询插入一些记录：

After creating the table, we are inserting some records using the following query −

INSERT INTO CUSTOMERS(NAME, AGE, ADDRESS, SALARY) VALUES
('Ramesh', 32, 'Ahmedabad', 2000.00),
('Khilan', 25, 'Delhi', 1500.00),
('Kaushik', 23, 'Kota', 2000.00),
('Chaitali', 25, 'Mumbai', 6500.00);

要查看表格数据，我们使用以下 SELECT 查询：

To view the table data, we use the following SELECT query −

SELECT * FROM CUSTOMERS;

Output

以下是对上述查询的输出，其中 ID 值自动生成：

Following is an output of the above query, where ID values are generated automatically −

ID

NAME

AGE

ADDRESS

SALARY

1

Ramesh

32

Ahmedabad

2000.00

2

Khilan

25

Delhi

1500.00

3

Kaushik

23

Kota

2000.00

4

Chaitali

25

Mumbai

6500.00