板凳-------Mysql cookbook学习 (十一--------3)

发布于:2025-07-10 ⋅ 阅读:(21) ⋅ 点赞:(0)

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 会自动增加到 112.	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;

网站公告

今日签到

点亮在社区的每一天
去签到