MySQL批量新增可以返回对应的ID吗|极客教程

MySQL批量新增可以返回对应的ID吗

在日常的开发过程中，我们经常会遇到需要批量新增数据的情况。对于 MySQL 数据库来说，它提供了多种方式来批量新增数据，但是默认情况下并不能直接返回对应的 ID。本文将详细探讨 MySQL 批量新增操作是否能够返回对应的 ID ，以及如何实现这个功能。

1. MySQL的批量新增

在 MySQL 中，我们可以使用 INSERT INTO 语句来插入一条或多条数据到表中，实现批量新增的功能。

INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...),
       (value1, value2, ...),
       ...

上述语句中，table_name 是要插入数据的目标表名，(column1, column2, ...) 是目标表的列名，(value1, value2, ...) 是要插入的具体数值。

下面是一个示例：

INSERT INTO users (name, age, email)
VALUES ('Alice', 25, 'alice@example.com'),
       ('Bob', 30, 'bob@example.com'),
       ('Charlie', 35, 'charlie@example.com');

上述代码会将三条用户数据同时插入到名为 users 的表中。

2. MySQL的自增主键

通常情况下，我们在数据库中为每个表都会定义一个自增的主键字段，用来唯一标识每条数据。对于这样的自增主键，MySQL 提供了 AUTO_INCREMENT 关键字来实现。

在创建表时，我们可以为某一列指定 AUTO_INCREMENT 属性，这样当新增一条数据时，MySQL 会自动为该列生成一个唯一的递增值。

下面是一个创建带有自增主键的表的示例：

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50),
    age INT,
    email VARCHAR(100)
);

在上述示例中，id 列被指定为自增主键，并且为其指定了 PRIMARY KEY 约束，这样每次新增数据时，id 列会自动递增并生成唯一的值。

3. MySQL的批量新增返回ID的解决方案

默认情况下，MySQL 并不会直接返回批量新增的数据对应的 ID。但是我们可以借助 MySQL 的一些特性和函数来实现这个功能。

3.1 利用`LAST_INSERT_ID`函数

在 MySQL 中，LAST_INSERT_ID() 函数可以用来获取上一次插入操作生成的自增主键值。因此，我们可以在批量新增数据后立即调用该函数来获取对应的 ID。

具体步骤如下：

在插入数据前，先调用SELECT LAST_INSERT_ID()语句，获取当前的自增主键值。
在插入数据后，再次调用SELECT LAST_INSERT_ID()语句，获取批量新增数据对应的自增主键值。

下面是一个示例代码：

import mysql.connector

# 连接数据库
cnx = mysql.connector.connect(user='username', password='password',
                              host='localhost', database='database_name')

# 创建游标对象
cursor = cnx.cursor()

# 获取当前的自增主键值
cursor.execute("SELECT LAST_INSERT_ID()")

# 将结果赋值给变量 last_id
last_id = cursor.fetchone()[0]

# 批量插入数据
data = [('Alice', 25, 'alice@example.com'),
        ('Bob', 30, 'bob@example.com'),
        ('Charlie', 35, 'charlie@example.com')]

query = "INSERT INTO users (name, age, email) VALUES (%s, %s, %s)"

cursor.executemany(query, data)

# 获取批量新增数据对应的自增主键值
cursor.execute("SELECT LAST_INSERT_ID()")

# 读取结果
result = cursor.fetchall()
new_ids = [id[0] for id in result]

# 提交操作并关闭连接
cnx.commit()
cursor.close()
cnx.close()

# 输出新增数据对应的ID
print(new_ids)

执行上述代码，会输出新增数据对应的 ID。

3.2 使用`INSERT INTO ... SELECT`语句

另一种方法是使用 INSERT INTO ... SELECT 语句实现批量新增并返回对应的 ID。

具体步骤如下：

创建一个临时表，用于保存新增数据的临时结果。
将要新增的数据插入到临时表中，同时使用 LAST_INSERT_ID() 函数获取对应的自增主键值。
从临时表中获取最后插入的自增主键值，并将结果返回。

下面是一个示例代码：

import mysql.connector

# 连接数据库
cnx = mysql.connector.connect(user='username', password='password',
                              host='localhost', database='database_name')

# 创建游标对象
cursor = cnx.cursor()

# 创建临时表
cursor.execute("CREATE TEMPORARY TABLE temp_users (id INT AUTO_INCREMENT PRIMARY KEY,
                                                 name VARCHAR(50),
                                                 age INT,
                                                 email VARCHAR(100))")

# 批量插入并返回对应的自增主键值
data = [('Alice', 25, 'alice@example.com'),
        ('Bob', 30, 'bob@example.com'),
        ('Charlie', 35, 'charlie@example.com')]

query = "INSERT INTO temp_users (name, age, email) SELECT * FROM (SELECT %s, %s, %s) AS tmp"

cursor.executemany(query, data)

# 获取最后插入的自增主键值
cursor.execute("SELECT id FROM temp_users ORDER BY id DESC LIMIT %s", (len(data),))

# 读取结果
result = cursor.fetchall()
new_ids = [id[0] for id in result]

# 提交操作并关闭连接
cnx.commit()
cursor.close()
cnx.close()

# 输出新增数据对应的ID
print(new_ids)

执行上述代码，会输出新增数据对应的 ID。