11.12 管理多重并发auto_increment数值
mysql> SHOW VARIABLES LIKE '%innodb_autoinc_lock_mode%';
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| innodb_autoinc_lock_mode | 2 |
+--------------------------+-------+
1 row in set, 1 warning (0.03 sec)
查看我们本地的mysql数据库服务器是采用的那种方式
若值为0，AUTO-INC锁方式；
若值为1，轻量级锁方式；
若值为2，两种方式混着来（就是插入记录数量确定的情况下使用轻量级锁，不确定时采用AUTO-INC锁方式，AUTO-INC锁是InnoDB存储引擎专门用来管理自增长列（AUTO_INCREMENT列）值分配的一种内部锁机制。它的主要目的是在向表中插入新记录时，确保每个新记录的自增列都能获得唯一的、连续的值。）。
https://blog.csdn.net/weixin_45701550/article/details/106751381/
mysql> CREATE TABLE `game` (
-> `id` int(10) unsigned not null auto_increment comment '主键',
-> `game_name` varchar(128) not null default '' comment '名称',
-> `status` tinyint(1) not null default 0 comment '状态',
-> primary key (`id`) USING BTREE,
-> key `ix_status` (`status`) USING BTREE
-> ) ENGINE=InnoDB;
Query OK, 0 rows affected, 2 warnings (0.08 sec)
mysql> select auto_increment from information_schema.tables where table_schema='ad' and table_name='game';
Empty set (0.01 sec)
mysql> alter table game auto_increment=5;
Query OK, 0 rows affected (0.04 sec)
Records: 0 Duplicates: 0 Warnings: 0
(1)当插入一个主键为nul,0或者没有明确指定的记录时
mysql> -- Correct insert statements for your current table structure
mysql> INSERT INTO game (game_name, status) VALUES ('game01', 1);
Query OK, 1 row affected (0.01 sec)
mysql> INSERT INTO game (game_name, status) VALUES ('game02', 2);
Query OK, 1 row affected (0.01 sec)
mysql> INSERT INTO game (game_name, status) VALUES ('game03', 3);
Query OK, 1 row affected (0.01 sec)
mysql> select * from game;
+----+-----------+--------+
| id | game_name | status |
+----+-----------+--------+
| 5 | game01 | 1 |
| 6 | game02 | 2 |
| 7 | game03 | 3 |
+----+-----------+--------+
3 rows in set (0.00 sec)
auto_increment的起始值为5.
(2)当明确指定主键时
如果指定的主键小于auto_increment时，auto_increment不发生变化
mysql> SELECT AUTO_INCREMENT -> FROM information_schema.TABLES -> WHERE TABLE_SCHEMA = 'ad' AND TABLE_NAME = 'game'; Empty set (0.00 sec) mysql> SHOW TABLE STATUS LIKE 'game'; +------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+--------------------+----------+----------------+---------+ | Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment | +------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+--------------------+----------+----------------+---------+ | game | InnoDB | 10 | Dynamic | 3 | 5461 | 16384 | 0 | 16384 | 0 | 8 | 2025-07-03 19:56:21 | 2025-07-03 19:57:58 | NULL | utf8mb4_0900_ai_ci | NULL | | | +------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+--------------------+----------+----------------+---------+ 1 row in set (0.02 sec)
插入一个主键比当前auto_increment小的值
mysql> INSERT INTO game (game_name, status) VALUES ('game04', 4);
Query OK, 1 row affected (0.01 sec)
mysql> select * from game;
+----+-----------+--------+
| id | game_name | status |
+----+-----------+--------+
| 5 | game01 | 1 |
| 6 | game02 | 2 |
| 7 | game03 | 3 |
| 8 | game04 | 4 |
+----+-----------+--------+
4 rows in set (0.00 sec)
mysql> SHOW TABLE STATUS LIKE 'game';
+------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+--------------------+----------+----------------+---------+
| Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment |
+------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+--------------------+----------+----------------+---------+
| game | InnoDB | 10 | Dynamic | 3 | 5461 | 16384 | 0 | 16384 | 0 | 8 | 2025-07-03 19:56:21 | 2025-07-03 19:57:58 | NULL | utf8mb4_0900_ai_ci | NULL | | |
+------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+--------------------+----------+----------------+---------+
1 row in set (0.00 sec)
如果指定的主键大于auto_increment时，auto_increment会发生变化
mysql> INSERT INTO game (game_name, status) VALUES ('game11', 11);
Query OK, 1 row affected (0.01 sec)
mysql> SELECT `AUTO_INCREMENT`
-> FROM `information_schema`.`TABLES`
-> WHERE `TABLE_SCHEMA` = DATABASE() AND `TABLE_NAME` = 'game';
+----------------+
| AUTO_INCREMENT |
+----------------+
| 8 |
+----------------+
1 row in set (0.00 sec)
mysql> SHOW CREATE TABLE game;
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| game | CREATE TABLE `game` (
`id` int unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
`game_name` varchar(128) NOT NULL DEFAULT '' COMMENT '名称',
`status` tinyint(1) NOT NULL DEFAULT '0' COMMENT '状态',
PRIMARY KEY (`id`) USING BTREE,
KEY `ix_status` (`status`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
验证 AUTO_INCREMENT 调整
测试 1：不指定 id，让 MySQL 自增
sql
INSERT INTO game (game_name, status) VALUES ('game12', 12); -- id 会自动赋值为 10
SELECT * FROM game ORDER BY id DESC LIMIT 1; -- 检查最新记录的 id
SHOW CREATE TABLE game; -- 查看 AUTO_INCREMENT 是否变成 11
测试 2：手动指定 id，观察 AUTO_INCREMENT 变化
sql
INSERT INTO game (id, game_name, status) VALUES (100, 'game100', 100); -- 强制插入 id=100
SHOW CREATE TABLE game; -- AUTO_INCREMENT 应该变成 101
为什么 information_schema.TABLES 不准确？
information_schema 是元数据视图，可能存在缓存，更新会有延迟。
更可靠的方式：
sql
SHOW TABLE STATUS LIKE 'game'; -- 查看 Auto_increment 列
或
SHOW CREATE TABLE game; -- 直接显示 AUTO_INCREMENT 值
mysql> INSERT INTO game (game_name, status) VALUES ('game12', 12);
Query OK, 1 row affected (0.01 sec)
mysql> set @saved_id = last_insert_id();
Query OK, 0 rows affected (0.00 sec)
关键点解析：
1. INSERT 语句：
o 没有指定 id 值，因此 MySQL 会自动使用当前的 AUTO_INCREMENT 值（10）作为 id。
o 插入后，AUTO_INCREMENT 会自动增加到 11。
2. LAST_INSERT_ID()：
o 这个函数返回的是 当前连接 最后插入的自增 ID 值（即 10）。
o 你将其保存到用户变量 @saved_id 中，可以在后续 SQL 中使用。
3. 验证操作：
o 查看刚插入的记录：
o mysql> SELECT * FROM game WHERE id = @saved_id;
o +----+-----------+--------+
o | id | game_name | status |
o +----+-----------+--------+
o | 10 | game12 | 12 |
o +----+-----------+--------+
o 1 row in set (0.00 sec)
o 检查 AUTO_INCREMENT 是否更新：
mysql> SHOW CREATE TABLE game; -- 应该显示 AUTO_INCREMENT=11
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| game | CREATE TABLE `game` (
`id` int unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
`game_name` varchar(128) NOT NULL DEFAULT '' COMMENT '名称',
`status` tinyint(1) NOT NULL DEFAULT '0' COMMENT '状态',
PRIMARY KEY (`id`) USING BTREE,
KEY `ix_status` (`status`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)注意事项：
• LAST_INSERT_ID() 是 连接会话级 的，不同客户端会话不会互相影响。
• 如果插入失败或回滚，LAST_INSERT_ID() 的值可能不会更新。
11.13 使用auto_increment值将表进行关联
记一次SQL优化过程
CREATE TABLE invoice (
Inv_id int unsigned NOT NULL AUTO_INCREMENT,
PRIMARY KEY (Inv_id),
Date date NOT NULL
);
CREATE TABLE inv_item (
Inv_id int unsigned NOT NULL,
INDEX (Inv_id),
Qty int,
Description varchar(40)
);
-- Insert new invoice (let auto-increment handle the ID)
INSERT INTO invoice (inv_id, date)
VALUES (NULL, CURDATE());
-- Insert items for this invoice
INSERT INTO inv_item (inv_id, qty, description)
VALUES (LAST_INSERT_ID(), 1, 'hammer');
INSERT INTO inv_item (inv_id, qty, description)
VALUES (LAST_INSERT_ID(), 3, 'nails, box');
INSERT INTO inv_item (inv_id, qty, description)
VALUES (LAST_INSERT_ID(), 12, 'bandage');
或者 下列方法
START TRANSACTION;
-- Insert invoice and save the ID
INSERT INTO invoice (date) VALUES (CURDATE());
SET @new_inv_id = LAST_INSERT_ID();
-- Insert all items
INSERT INTO inv_item (inv_id, qty, description) VALUES
(@new_inv_id, 1, 'hammer'),
(@new_inv_id, 3, 'nails, box'),
(@new_inv_id, 12, 'bandage');
COMMIT;
mysql> select * from invoice;
+--------+------------+
| inv_id | date |
+--------+------------+
| 4 | 2022-02-14 |
+--------+------------+
1 row in set (0.00 sec)
mysql> select * from inv_item;
+--------+---------+------+-------------+
| inv_id | item_id | qty | description |
+--------+---------+------+-------------+
| 4 | 7 | 1 | hammer |
| 4 | 8 | 3 | nails, box |
| 4 | 9 | 12 | bandage |
+--------+---------+------+-------------+
3 rows in set (0.00 sec)
• 插入 NULL 让 inv_id 自增生成（当前生成 5）
• CURDATE() 自动填入当前日期
mysql> INSERT INTO invoice (inv_id, date)
-> VALUES (NULL, CURDATE());
Query OK, 1 row affected (0.01 sec)
LAST_INSERT_ID() 获取刚插入的 inv_id 值（即 5）
存入用户变量 @inv_id 供后续使用
mysql> set @inv_id = last_insert_id();
Query OK, 0 rows affected (0.00 sec)
每个商品的 item_id 自动自增（从 10 开始）
通过 inv_id=5 关联到刚创建的发票
mysql> insert into inv_item (inv_id, qty, description)
-> values (@inv_id, 1, 'hammer');
Query OK, 1 row affected (0.01 sec)
mysql> insert into inv_item (inv_id, qty, description)
-> values (@inv_id, 3, 'nails, box');
Query OK, 1 row affected (0.01 sec)
mysql> insert into inv_item (inv_id, qty, description)
-> values (@inv_id, 12, 'bandage');
Query OK, 1 row affected (0.01 sec)
mysql> select * from inv_item;
+--------+---------+------+-------------+
| inv_id | item_id | qty | description |
+--------+---------+------+-------------+
| 4 | 7 | 1 | hammer | ← 之前测试数据
| 4 | 8 | 3 | nails, box |
| 4 | 9 | 12 | bandage |
| 5 | 10 | 1 | hammer | ← 本次新增数据
| 5 | 11 | 3 | nails, box |
| 5 | 12 | 12 | bandage |
+--------+---------+------+-------------+
• 发票ID 5 包含 3 个商品（item_id 10~12）
• item_id 自增连续（7~12），与 inv_id 无关
• 数据完整体现了 一对多关系（一张发票对应多个商品）
________________________________________
技术要点总结
1. 自增主键规则
o invoice 表的 inv_id 和 inv_item 表的 item_id 都是自增主键
o 插入 NULL 或省略时自动生成递增值
2. LAST_INSERT_ID()
o 获取 当前会话 最后插入的自增值
o 必须立即使用（其他插入操作会覆盖该值）
3. 用户变量 @inv_id
o 用 SET @var = value 存储临时数据
o 在整个会话期间有效
4. 外键逻辑
o 虽然未显式定义外键约束，但通过 inv_id 实现了逻辑关联
o 建议添加正式外键约束（如之前讨论的）
________________________________________
下一步建议
-- 查看完整的发票+商品信息
SELECT i.inv_id, i.date, it.item_id, it.qty, it.description
FROM invoice i
JOIN inv_item it ON i.inv_id = it.inv_id
WHERE i.inv_id = 5;
+--------+------------+---------+------+-------------+
| inv_id | date | item_id | qty | description |
+--------+------------+---------+------+-------------+
| 5 | 2025-07-03 | 10 | 1 | hammer |
| 5 | 2025-07-03 | 11 | 3 | nails, box |
| 5 | 2025-07-03 | 12 | 12 | bandage |
+--------+------------+---------+------+-------------+
3 rows in set (0.00 sec)
11.14 将序列生成器用作计数器
mysql> select * from booksales;
Empty set (0.01 sec)
mysql> describe booksales;
+--------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+--------------+------+-----+---------+-------+
| title | varchar(60) | NO | PRI | NULL | |
| copies | int unsigned | NO | | NULL | |
+--------+--------------+------+-----+---------+-------+
2 rows in set (0.01 sec)
mysql> insert into booksales(title, copies) values ('The Greater Trumps', 0);
Query OK, 1 row affected (0.01 sec)
mysql> update booksales set copies = copies+1 where title= 'The Greater Trumps';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> insert into booksales(title, copies) values ('The Greater Trumps', 1)
-> on duplicate key update copies = copies +1;
Query OK, 2 rows affected (0.01 sec)
mysql> select * from booksales;
+--------------------+--------+
| title | copies |
+--------------------+--------+
| The Greater Trumps | 2 |
+--------------------+--------+
1 row in set (0.00 sec)
mysql> insert into booksales(title, copies) values ('The Greater Trumps', last_insert_id(1))
-> on duplicate key update copies = last_insert_id(copies + 1);
Query OK, 2 rows affected (0.01 sec)
mysql> select last_insert_id();
+------------------+
| last_insert_id() |
+------------------+
| 3 |
+------------------+
1 row in set (0.00 sec)
import pandas as pd
import mysql.connector
# 数据库配置
config = {
'user': 'cbuser',
'password': 'cbpass',
'host': 'localhost',
'database': 'cookbook',
'charset': 'utf8mb4'
}
try:
# 1. 正确建立数据库连接
conn = mysql.connector.connect(**config)
cursor = conn.cursor()
# 2. 准备正确的插入语句
insert_query = """
INSERT INTO booksales(title, copies)
VALUES (%s, %s)
ON DUPLICATE KEY UPDATE copies = copies + VALUES(copies)
"""
# 3. 准备要插入的数据
data = ('The Greater Trumps', 1) # 示例数据
# 4. 执行插入操作
cursor.execute(insert_query, data)
conn.commit() # 提交事务
# 5. 获取插入ID的正确方法
count = cursor.lastrowid # 或者使用 conn.insert_id()
print(f"操作成功，影响行数: {cursor.rowcount}, 最后插入ID: {count}")
except mysql.connector.Error as e:
print(f"数据库操作失败: {e}")
if 'conn' in locals() and conn.is_connected():
conn.rollback()
except Exception as e:
print(f"发生其他错误: {e}")
finally:
# 6. 确保关闭连接
if 'cursor' in locals():
cursor.close()
if 'conn' in locals() and conn.is_connected():
conn.close()
操作成功，影响行数: 2, 最后插入ID: 0
11.15 创建循环序列
方法1：使用数字序列生成循环
sql
-- 生成1-100的序列，循环周期为5
SELECT
n,
(n-1) % 5 + 1 AS cycle_number -- 产生1-5的循环
FROM (
SELECT ROW_NUMBER() OVER () AS n
FROM information_schema.columns
LIMIT 100
) AS numbers;
+-----+--------------+
| n | cycle_number |
+-----+--------------+
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
| 4 | 4 |
| 5 | 5 |
。。。。。。
96 | 1 |
| 97 | 2 |
| 98 | 3 |
| 99 | 4 |
| 100 | 5 |
+-----+--------------+
100 rows in set (0.01 sec)
方法2：使用递归CTE生成自定义循环序列
sql
-- 生成1-20的序列，循环周期为3
WITH RECURSIVE sequence AS (
SELECT 1 AS n
UNION ALL
SELECT n + 1 FROM sequence WHERE n < 20
)
SELECT
n,
CASE (n-1) % 3
WHEN 0 THEN 'A'
WHEN 1 THEN 'B'
WHEN 2 THEN 'C'
END AS cycle_item
FROM sequence;
+------+------------+
| n | cycle_item |
+------+------------+
| 1 | A |
| 2 | B |
| 3 | C |
| 4 | A |
| 5 | B |
| 6 | C |
| 7 | A |
| 8 | B |
| 9 | C |
| 10 | A |
| 11 | B |
| 12 | C |
| 13 | A |
| 14 | B |
| 15 | C |
| 16 | A |
| 17 | B |
| 18 | C |
| 19 | A |
| 20 | B |
+------+------------+
20 rows in set (0.00 sec)
方法3：创建存储过程生成循环序列
sql
DELIMITER //
CREATE PROCEDURE generate_cyclic_sequence(
IN total_rows INT,
IN cycle_length INT
)
BEGIN
DECLARE i INT DEFAULT 0;
DROP TEMPORARY TABLE IF EXISTS temp_sequence;
CREATE TEMPORARY TABLE temp_sequence (
pos INT,
cycle_val INT
);
WHILE i < total_rows DO
INSERT INTO temp_sequence VALUES (i+1, i % cycle_length + 1);
SET i = i + 1;
END WHILE;
SELECT * FROM temp_sequence;
END //
Query OK, 0 rows affected (0.02 sec)
mysql> DELIMITER ;
-- 调用存储过程：生成15个元素，周期为4
CALL generate_cyclic_sequence(15, 4);
+------+-----------+
| pos | cycle_val |
+------+-----------+
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
| 4 | 4 |
| 5 | 1 |
| 6 | 2 |
| 7 | 3 |
| 8 | 4 |
| 9 | 1 |
| 10 | 2 |
| 11 | 3 |
| 12 | 4 |
| 13 | 1 |
| 14 | 2 |
| 15 | 3 |
+------+-----------+
15 rows in set (0.02 sec)
Query OK, 0 rows affected (0.10 sec)
方法4：使用日期生成循环序列
mysql> -- 生成30天的日期，按周循环（1-7）
mysql> SELECT
-> date_field,
-> DAYOFWEEK(date_field) AS day_of_week,
-> (DAYOFWEEK(date_field)+5) % 7 + 1 AS custom_cycle -- 调整为周一到周日为1-7
-> FROM (
-> SELECT CURDATE() + INTERVAL n DAY AS date_field
-> FROM (
-> SELECT ROW_NUMBER() OVER () - 1 AS n
-> FROM information_schema.columns
-> LIMIT 30
-> ) AS numbers
-> ) AS dates;
+------------+-------------+--------------+
| date_field | day_of_week | custom_cycle |
+------------+-------------+--------------+
| 2025-07-03 | 5 | 4 |
| 2025-07-04 | 6 | 5 |
| 2025-07-05 | 7 | 6 |
| 2025-07-06 | 1 | 7 |
| 2025-07-07 | 2 | 1 |
| 2025-07-08 | 3 | 2 |
| 2025-07-09 | 4 | 3 |
| 2025-07-10 | 5 | 4 |
| 2025-07-11 | 6 | 5 |
| 2025-07-12 | 7 | 6 |
| 2025-07-13 | 1 | 7 |
| 2025-07-14 | 2 | 1 |
| 2025-07-15 | 3 | 2 |
| 2025-07-16 | 4 | 3 |
| 2025-07-17 | 5 | 4 |
| 2025-07-18 | 6 | 5 |
| 2025-07-19 | 7 | 6 |
| 2025-07-20 | 1 | 7 |
| 2025-07-21 | 2 | 1 |
| 2025-07-22 | 3 | 2 |
| 2025-07-23 | 4 | 3 |
| 2025-07-24 | 5 | 4 |
| 2025-07-25 | 6 | 5 |
| 2025-07-26 | 7 | 6 |
| 2025-07-27 | 1 | 7 |
| 2025-07-28 | 2 | 1 |
| 2025-07-29 | 3 | 2 |
| 2025-07-30 | 4 | 3 |
| 2025-07-31 | 5 | 4 |
| 2025-08-01 | 6 | 5 |
+------------+-------------+--------------+
30 rows in set (0.00 sec)
分步解释
1. 内部查询：生成0-29的数字序列
sql
复制
下载
SELECT ROW_NUMBER() OVER () - 1 AS n
FROM information_schema.columns
LIMIT 30
2. 中间查询：生成从今天开始的30天日期
sql
复制
下载
SELECT CURDATE() + INTERVAL n DAY AS date_field
FROM (...) AS numbers
3. 外层查询：计算每周循环
o DAYOFWEEK() 返回1(周日)到7(周六)
o (DAYOFWEEK()+5)%7+1 转换为1(周一)到7(周日)
方法5：结合除法和模运算的复杂循环
sql
-- 生成1-24的序列，每6个元素为一个周期，每个周期内分3组
SELECT
n,
(n-1) % 6 + 1 AS cycle_pos, -- 周期内位置1-6
FLOOR((n-1)/6) % 3 + 1 AS group_id -- 组别1-3循环
FROM (
SELECT ROW_NUMBER() OVER () AS n
FROM information_schema.columns
LIMIT 24
) AS numbers;
+----+-----------+----------+
| n | cycle_pos | group_id |
+----+-----------+----------+
| 1 | 1 | 1 |
| 2 | 2 | 1 |
| 3 | 3 | 1 |
| 4 | 4 | 1 |
| 5 | 5 | 1 |
| 6 | 6 | 1 |
| 7 | 1 | 2 |
| 8 | 2 | 2 |
| 9 | 3 | 2 |
| 10 | 4 | 2 |
| 11 | 5 | 2 |
| 12 | 6 | 2 |
| 13 | 1 | 3 |
| 14 | 2 | 3 |
| 15 | 3 | 3 |
| 16 | 4 | 3 |
| 17 | 5 | 3 |
| 18 | 6 | 3 |
| 19 | 1 | 1 |
| 20 | 2 | 1 |
| 21 | 3 | 1 |
| 22 | 4 | 1 |
| 23 | 5 | 1 |
| 24 | 6 | 1 |
+----+-----------+----------+
24 rows in set (0.01 sec)
实际应用示例：循环分配任务
sql
-- 创建用户表
CREATE TABLE IF NOT EXISTS users (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(50)
);
-- 创建任务表
CREATE TABLE IF NOT EXISTS tasks (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(50),
assigned_to INT
);
-- 插入示例数据
INSERT INTO users (name) VALUES ('Alice'), ('Bob'), ('Charlie'), ('David');
INSERT INTO tasks (name)
VALUES ('Task1'), ('Task2'), ('Task3'), ('Task4'), ('Task5'), ('Task6'), ('Task7');
-- 循环分配任务给用户
UPDATE tasks t
JOIN (
SELECT
t.id AS task_id,
u.id AS user_id
FROM tasks t
JOIN users u ON (t.id-1) % (SELECT COUNT(*) FROM users) = u.id-1
) AS assignment ON t.id = assignment.task_id
SET t.assigned_to = assignment.user_id;
-- 查看分配结果
SELECT t.id, t.name, u.name AS assigned_to
FROM tasks t
LEFT JOIN users u ON t.assigned_to = u.id;