Mariadb ID 시퀀스가 깨질 때마다 min 및 max 변수를 재설정하는 방법

Mariadb ID 시퀀스가 깨질 때마다 min 및 max 변수를 재설정하는 방법

ID 시퀀스가 누락된 ID 목록이 있습니다. 다른 애플리케이션에서 사용할 최소 시퀀스와 최대 시퀀스를 생성하려고 합니다.

MariaDB [test]> select * from book_lists;
+----+-----------+
| id | name      |
+----+-----------+
|  1 | c++ for x |
|  2 | c++ for x |
|  3 | c++ for x |
|  7 | c++ for x |
|  9 | c++ for x |
| 10 | c++ for x |
| 12 | c++ for x |
| 13 | c++ for x |
| 14 | c++ for x |
| 15 | c++ for x |
+----+-----------+
10 rows in set (0.000 sec)

정규 최소 및 최대 생산량

MariaDB [test]> select min(id),max(id) from book_lists;
+---------+---------+
| min(id) | max(id) |
+---------+---------+
|       1 |      15 |
+---------+---------+
1 row in set (0.000 sec)

하지만 ID 시퀀스가 깨질 때마다 min과 max 변수를 리셋하고 아래 출력을 받고 싶습니다.

+---------+---------+
| min(id) | max(id) |
+---------+---------+
    1       3
    7       7
    9       10
    12      15
+---------+---------+

아래는 스키마 및 테스트 데이터입니다.

DROP TABLE IF EXISTS `book_lists`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `book_lists` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(30) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=utf8mb4;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `book_lists`
--

LOCK TABLES `book_lists` WRITE;
/*!40000 ALTER TABLE `book_lists` DISABLE KEYS */;
INSERT INTO `book_lists` VALUES (1,'c++ for x'),(2,'c++ for x'),(3,'c++ for x'),(7,'c++ for x'),(9,'c++ for x'),(10,'c++ for x'),(12,'c++ for x'),(13,'c++ for x'),(14,'c++ for x'),(15,'c++ for x');
/*!40000 ALTER TABLE `book_lists` ENABLE KEYS */;
UNLOCK TABLES;

아키나씨가 이미 말한 것처럼, 이것은 「갑과 섬」이라고 불리는 잘 알려진 문제입니다.

아일랜드는 일련의 행 그룹이고 간격은 숫자의 간격입니다.

이 문제를 해결하려면 특정 섬 하나에 대해 동일한 상태를 유지할 수 있는 무언가를 찾아야 합니다.이 경우 레코드 번호에 (row_number - id)를 사용할 수 있습니다.이것이 CTE(부품 포함)가 하는 일입니다.

"id - row_number()" 방정식의 결과는 아일랜드 내에서 동일하게 유지됩니다.내 말뜻을 이해하려면 혼자 CTE 쿼리를 수행하세요.

따라서 CTE가 완료되면 메인 쿼리와 마찬가지로 섬별로 그룹화하기만 하면 됩니다.

with gaps_and_islands as (
  select id - row_number() over(order by id) isle, 
         bl.* 
    from book_lists bl)

select min(id), max(id)
  from gaps_and_islands
 group by isle;

자, 여기 몇 가지 설명이 있습니다.

1. "with" 쿼리는 보통 "Common table expression" 또는 CTE로 불리며 쿼리를 저장하는 변수입니다.

이것이 없으면 내 쿼리는 다음과 같이 다시 작성될 수 있습니다.

select min(id), max(id)
  from (select id - row_number() over(order by id) isle, 
         bl.* 
    from book_lists bl) gaps_and_islands
 group by isle;

제 생각에는 읽기 힘드네요따라서 이 경우 쿼리를 읽을 수 있도록 하기 위한 하위 쿼리로 사용됩니다.

2. row_number() over(id로 정렬) - "over(order by)" 부분에 따라 모든 행을 순차적으로 계산합니다.데이터베이스에 대한 "창 기능"을 구글로 검색할 수 있습니다.

예를들면,

select row_number() over(order by id) isle, 
       bl.id 
    from book_lists bl;

반환:

아이디	row_number
1	1
2	2
3	3
7	4
9	5
10	6

바로 이 시점에서 문제가 해결됩니다.(id - row_number())의 서브섹션 결과에 따라 순차 ID의 모든 그룹에 동일한 값의 그룹이 제공됩니다.

아이디	row_number	id - row_number
1	1	0
2	2	0
3	3	0
7	4	3
9	5	4
10	6	4
12	7	5
13	8	5
14	9	5
15	10	5

그리고 그룹화할 것이 있기 때문에, 문제는 해결된다.

언급URL : https://stackoverflow.com/questions/69765800/mariadb-how-to-reset-min-and-max-variable-whenever-id-sequence-breaks