MySQL - 序列

更新于 2025/6/18 19:52:17

MySQL 中的序列
检索 AUTO_INCREMENT 值
重新编号现有序列
使用客户端程序的序列

序列是一系列整数，从 1 开始，每个连续值加 1。这些序列通常用于数据库中，因为许多应用程序要求表中的每一行都具有唯一标识符，而序列提供了一种生成此类值的简便方法。

MySQL 中的序列

MySQL 没有内置序列功能，但以 AUTO_INCREMENT 列的形式提供了替代方案，其用途类似。

在 MySQL 中，AUTO_INCREMENT 属性用于为列自动生成唯一的整数值(序列)。默认情况下，此序列的初始值为 1，每添加一行，序列值加 1。

语法

以下是 MySQL 中 AUTO_INCREMENT 属性的语法 -

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

示例

在下面的示例中，我们创建了一个名为"CUSTOMERS"的表，并为该表的"ID"列定义了 AUTO_INCREMENT 属性 -

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

现在，让我们向上面创建的表中插入一些记录 -

INSERT INTO CUSTOMERS VALUES 
(NULL, 'Ramesh', 32, 'Ahmedabad', 2000.00),
(NULL, 'Khilan', 25, 'Delhi', 1500.00),
(NULL, 'Kaushik', 23, 'Kota', 2000.00),
(NULL, 'Chaitali', 25, 'Mumbai', 6500.00),
(NULL, 'Hardik', 27, 'Bhopal', 8500.00),
(NULL, 'Komal', 22, 'Hyderabad', 4500.00),
(NULL, 'Muffy', 24, 'Indore', 10000.00);

输出

我们可以在下面显示的表格中看到"ID"列中的值自动递增 -

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
5	Hardik	27	Bhopal	8500.00
6	Komal	22	Hyderabad	4500.00
7	Muffy	24	Indore	10000.00

检索 AUTO_INCREMENT 值

要在 MySQL 中获取 AUTO_INCREMENT 值，您可以使用 LAST_INSERT_ID() SQL 函数。此函数可用于任何可以发出 SQL 语句的客户端。或者，在 PERL 和 PHP 脚本中，可以使用特定函数来检索最后一条记录的自动递增值。

PERL 示例

您可以使用 mysql_insertid 属性访问查询生成的 AUTO_INCREMENT 值。此属性可以通过数据库句柄或语句句柄访问，具体取决于您执行查询的方式。

以下示例通过数据库句柄引用它 -

$dbh->do ("INSERT INTO insect (name,date,origin)
VALUES('moth','2001-09-14','windowsill')");
my $seq = $dbh->{mysql_insertid};

PHP 示例

执行生成 AUTO_INCREMENT 值的查询后，您可以使用 mysql_insert_id() 命令检索该值 -

mysql_query ("INSERT INTO insect (name,date,origin)
VALUES('moth','2001-09-14','windowsill')", $conn_id);
$seq = mysql_insert_id ($conn_id);

对现有序列重新编号

在某些情况下，您可能需要对表中的记录重新排序，尤其是在删除了大量记录的情况下。如果您的表通过连接与其他表相关，则在重新排序时务必小心。

如果您确定无法避免对 AUTO_INCREMENT 列进行重新排序，则可以从表中删除该 AUTO_INCREMENT 列，然后重新添加。

示例

以下示例展示了如何使用此技术对表中的 id 值 进行重新编号。

ALTER TABLE CUSTOMERS DROP id;
ALTER TABLE CUSTOMERS
ADD id INT UNSIGNED NOT NULL AUTO_INCREMENT FIRST,
ADD PRIMARY KEY (id);

输出

以下是获得的输出 -

Query OK, 0 rows affected (0.10 sec)
Records: 0  Duplicates: 0  Warnings: 0

以特定值启动序列

默认情况下，MySQL 从 1 开始序列，但您可以在创建表时指定其他初始值。

示例

以下示例演示了如何在创建表时从 100 开始序列 -

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

输出

上述代码的输出如下所示 -

Query OK, 0 rows affected (0.04 sec)

或者，您可以先创建表，然后使用 ALTER TABLE 命令设置初始序列值，如下所示 -

ALTER TABLE CUSTOMERS AUTO_INCREMENT = 100;

使用客户端程序创建序列

我们也可以使用客户端程序创建序列。

语法

PHP NodeJS Java Python

要通过 PHP 程序在表的某个列上创建序列，我们需要在使用 mysqli 函数 query() 创建表时为特定列指定 auto_increment，如下所示 -

$sql = "CREATE TABLE insect (id INT UNSIGNED NOT NULL AUTO_INCREMENT,PRIMARY KEY (id),name VARCHAR(30) NOT NULL,date DATE NOT NULL,origin VARCHAR(30) NOT NULL)";
$mysqli->query($sql);

要通过 JavaScript 程序在表的列上创建序列，我们需要在使用 mysql2 库的 query() 函数创建表时为特定列指定 auto_increment，如下所示 -

sql = "CREATE TABLE insect (id INT UNSIGNED NOT NULL AUTO_INCREMENT,PRIMARY KEY (id),name VARCHAR(30) NOT NULL,date DATE NOT NULL,origin VARCHAR(30) NOT NULL)";
con.query(sql);

要通过 Java 程序在表的列上创建序列，我们需要在使用 JDBC 函数 execute() 创建表时为特定列指定 auto_increment，如下所示 -

String sql = "CREATE TABLE insect (id INT UNSIGNED NOT NULL AUTO_INCREMENT,PRIMARY KEY (id),name VARCHAR(30) NOT NULL,date DATE NOT NULL,origin VARCHAR(30) NOT NULL)";
statement.execute(sql);

要通过 Python 程序在表的列上创建序列，我们需要在使用 MySQL Connector/Python 的 execute() 函数创建表时为特定列指定 auto_increment，如下所示:-

create_table_query = "CREATE TABLE insect (id INT UNSIGNED NOT NULL AUTO_INCREMENT, PRIMARY KEY (id), name VARCHAR(30) NOT NULL, date DATE NOT NULL, origin VARCHAR(30) NOT NULL)"
cursorObj.execute(create_table_query)

示例

以下是程序−

PHP NodeJS Java Python

$dbhost = 'localhost';
$dbuser = 'root';
$dbpass = 'password';
$db = 'TUTORIALS';
$mysqli = new mysqli($dbhost, $dbuser, $dbpass, $db);
if ($mysqli->connect_errno) {
    printf("Connect failed: %s
", $mysqli->connect_error);
    exit();
}
//printf('Connected successfully.
');
//create table with autoincrement sequene
$sql = "CREATE TABLE insect (id INT UNSIGNED NOT NULL AUTO_INCREMENT,PRIMARY KEY (id),name VARCHAR(30) NOT NULL,date DATE NOT NULL,origin VARCHAR(30) NOT NULL)";
if($mysqli->query($sql)){
    printf("Table created successfully....!
");
}
//let's insert some record
$sql = "INSERT INTO insect (id,name,date,origin) VALUES(NULL,'housefly','2001-09-10','kitchen')";
if($mysqli->query($sql)){
    printf("First record inserted successfully...!
");
}
$sql = "INSERT INTO insect (id,name,date,origin) VALUES(NULL,'millipede','2001-09-10','driveway')";
if($mysqli->query($sql)){
    printf("Second record inserted successfully...!
");
}
$sql = "INSERT INTO insect (id,name,date,origin) VALUES(NULL,'grasshopper','2001-09-10','front yard')";
if($mysqli->query($sql)){
    printf("Third record inserted successfully...!
");
}
printf("Table records: 
");
$sql = "SELECT * FROM insect";
if($result = $mysqli->query($sql)){
    printf("Table record: 
");
    while($row = mysqli_fetch_array($result)){
        printf("Id: %d, Name: %s, Date: %s, Origin %s",
        $row['id'],
        $row['name'],
        $row['date'],
        $row['origin']);
        printf("
");
    }
}
if($mysqli->error){
    printf("Error message: ", $mysqli->error);
}
$mysqli->close();

输出

获得的输出如下所示 -

Table created successfully....!
First record inserted successfully...!
Second record inserted successfully...!
Third record inserted successfully...!
Table records:
Table record:
Id: 1, Name: housefly, Date: 2001-09-10, Origin kitchen
Id: 2, Name: millipede, Date: 2001-09-10, Origin driveway
Id: 3, Name: grasshopper, Date: 2001-09-10, Origin front yard

var mysql = require('mysql2');
var con = mysql.createConnection({
host:"localhost",
user:"root",
password:"password"
});
//连接到 MySQL
 con.connect(function(err) {
 if (err) throw err;
//   console.log("Connected successfully...!");
//   console.log("--------------------------");
 sql = "USE TUTORIALS";
 con.query(sql);
 //create table
 sql = "CREATE TABLE insect (id INT UNSIGNED NOT NULL AUTO_INCREMENT,PRIMARY KEY (id),name VARCHAR(30) NOT NULL,date DATE NOT NULL,origin VARCHAR(30) NOT NULL)";
 con.query(sql, function(err, result){
    if (err) throw err;
    console.log("Table created successfully....!");
    });
//now let's insert some record
sql = "INSERT INTO insect (id,name,date,origin) VALUES(NULL,'housefly','2001-09-10','kitchen')";
con.query(sql, function(err, result){
    if (err) throw err;
    console.log("First record inserted successfully...!");
    });
sql = "INSERT INTO insect (id,name,date,origin) VALUES(NULL,'millipede','2001-09-10','driveway')";
con.query(sql, function(err, result){
    if (err) throw err;
    console.log("Second record inserted successfully...!");
    });
sql = "INSERT INTO insect (id,name,date,origin) VALUES(NULL,'grasshopper','2001-09-10','front yard')";
    con.query(sql, function(err, result){
    if (err) throw err;
    console.log("Third record inserted successfully...!");
    });
sql = "SELECT * FROM INSECT";
con.query(sql, function(err, result){
console.log("Table records(with ID auto_increment sequence).");
    if (err) throw err;
    console.log(result);
    });
});

输出

获得的输出如下所示 -

Table created successfully....!
First record inserted successfully...!
Second record inserted successfully...!
Third record inserted successfully...!
Table records(with ID auto_increment sequence).
[
  {
    id: 1,
    name: 'housefly',
    date: 2001-09-09T18:30:00.000Z,
    origin: 'kitchen'
  },
  {
    id: 2,
    name: 'millipede',
    date: 2001-09-09T18:30:00.000Z,
    origin: 'driveway'
  },
  {
    id: 3,
    name: 'grasshopper',
    date: 2001-09-09T18:30:00.000Z,
    origin: 'front yard'
  }
]

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class Sequence {
   public static void main(String[] args) {
      String url = "jdbc:mysql://localhost:3306/TUTORIALS";
      String user = "root";
      String password = "password";
      ResultSet rs;
      try {
         Class.forName("com.mysql.cj.jdbc.Driver");
            Connection con = DriverManager.getConnection(url, user, password);
            Statement st = con.createStatement();
            //System.out.println("Database connected successfully...!");
            //create table
            String sql = "CREATE TABLE insect (id INT UNSIGNED NOT NULL AUTO_INCREMENT,PRIMARY KEY (id),name VARCHAR(30) NOT NULL,date DATE NOT NULL,origin VARCHAR(30) NOT NULL)";
            st.execute(sql);
            System.out.println("Table 'insect' created successfully...!");
            //lets insert some records into the table
            String sql1 = "INSERT INTO insect (id,name,date,origin) VALUES(NULL,'housefly','2001-09-10','kitchen'), (NULL,'millipede','2001-09-10','driveway'), (NULL,'grasshopper','2001-09-10','front yard')";
            st.execute(sql1);
            System.out.println("Records inserted successfully...!");
            //lets print table records
            String sql2 = "SELECT * FROM INSECT";
            rs = st.executeQuery(sql2);
            System.out.println("Table records: ");
            while(rs.next()) {
               String id = rs.getString("id");
               String name = rs.getString("name");
               String date = rs.getString("date");
               String origin = rs.getString("origin");
               System.out.println("Id: " + id + ", Name: " + name + ", Date: " + date + ", Origin: " + origin);
            }
      }catch(Exception e) {
         e.printStackTrace();
      }
   }
}

输出

获得的输出如下所示 -

Table 'insect' created successfully...!
Records inserted successfully...!
Table records: 
Id: 1, Name: housefly, Date: 2001-09-10, Origin: kitchen
Id: 2, Name: millipede, Date: 2001-09-10, Origin: driveway
Id: 3, Name: grasshopper, Date: 2001-09-10, Origin: front yard

import mysql.connector
# 连接到 MySQL
con = mysql.connector.connect(
    host="localhost",
    user="root",
    password="password",
    database="tut"
)
# 创建游标对象
cursorObj = con.cursor()
# 创建表
create_table_query = """
CREATE TABLE insect (
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
PRIMARY KEY (id),
name VARCHAR(30) NOT NULL,
date DATE NOT NULL,
origin VARCHAR(30) NOT NULL
)
"""
cursorObj.execute(create_table_query)
print("Table 'insect' is created successfully!")
# 插入记录
sql = "INSERT INTO insect (name, date, origin) VALUES (%s, %s, %s)"
values = [
    ('housefly', '2001-09-10', 'kitchen'),
    ('millipede', '2001-09-10', 'driveway'),
    ('grasshopper', '2001-09-10', 'front yard')
]
cursorObj.executemany(sql, values)
print("Data inserted into the 'insect' table.")
con.commit()
# 检索记录
cursorObj.execute("SELECT * FROM insect")
records = cursorObj.fetchall()
# 打印记录
print("Table records(with ID auto_increment sequence).")
for record in records:
    print(record)
# 关闭连接
cursorObj.close()
con.close()

输出

获得的输出如下所示 -

Table 'insect' is created successfully!
Data inserted into the 'insect' table.
Table records(with ID auto_increment sequence).
(1, 'housefly', datetime.date(2001, 9, 10), 'kitchen')
(2, 'millipede', datetime.date(2001, 9, 10), 'driveway')
(3, 'grasshopper', datetime.date(2001, 9, 10), 'front yard')

❮ MySQL 事务详解 MySQL 处理重复项 ❯

MySQL 8 教程

MySQL 8 数据库

MySQL 8 用户

MySQL 8 表

MySQL 8 查询

MySQL 8 视图

MySQL 8 索引

MySQL 运算符和子句

MySQL 连接

MySQL 键

MySQL 触发器

MySQL 8 数据类型

MySQL 正则表达式

MySQL 全文搜索

MySQL8 函数和运算符

MySQL 8 其他概念

MySQL 8 实用资源

MySQL - 序列

MySQL 中的序列

语法

示例

输出

检索 AUTO_INCREMENT 值

PERL 示例

PHP 示例

对现有序列重新编号

示例

输出

以特定值启动序列

示例

输出

使用客户端程序创建序列

语法

示例

输出

输出

输出

输出

颜色选择器

读后有收获微信请站长喝咖啡

错误报告

您的建议:

感谢您的帮助！