source

MySQL에서 번호 범위 생성

manycodes 2022. 11. 25. 20:53
반응형

MySQL에서 번호 범위 생성

MySQL 쿼리에서 연속된 번호 범위(한 줄에 하나씩)를 생성하여 테이블에 삽입하려면 어떻게 해야 합니까?

예를 들어 다음과 같습니다.

nr
1
2
3
4
5

이 경우 MySQL만 사용하고 싶습니다(PHP나 다른 언어는 사용하지 않습니다.

루프를 사용하지 않고 셋 베이스로 하는 방법을 다음에 나타냅니다.이것은 재사용할 수 있는 뷰로도 만들 수 있습니다.이 예에서는 0 ~999 의 시퀀스의 생성을 나타내고 있습니다만, 필요에 따라서 변경할 수도 있습니다.

INSERT INTO
    myTable
    (
    nr
    )
SELECT
    SEQ.SeqValue
FROM
(
SELECT
    (HUNDREDS.SeqValue + TENS.SeqValue + ONES.SeqValue) SeqValue
FROM
    (
    SELECT 0  SeqValue
    UNION ALL
    SELECT 1 SeqValue
    UNION ALL
    SELECT 2 SeqValue
    UNION ALL
    SELECT 3 SeqValue
    UNION ALL
    SELECT 4 SeqValue
    UNION ALL
    SELECT 5 SeqValue
    UNION ALL
    SELECT 6 SeqValue
    UNION ALL
    SELECT 7 SeqValue
    UNION ALL
    SELECT 8 SeqValue
    UNION ALL
    SELECT 9 SeqValue
    ) ONES
CROSS JOIN
    (
    SELECT 0 SeqValue
    UNION ALL
    SELECT 10 SeqValue
    UNION ALL
    SELECT 20 SeqValue
    UNION ALL
    SELECT 30 SeqValue
    UNION ALL
    SELECT 40 SeqValue
    UNION ALL
    SELECT 50 SeqValue
    UNION ALL
    SELECT 60 SeqValue
    UNION ALL
    SELECT 70 SeqValue
    UNION ALL
    SELECT 80 SeqValue
    UNION ALL
    SELECT 90 SeqValue
    ) TENS
CROSS JOIN
    (
    SELECT 0 SeqValue
    UNION ALL
    SELECT 100 SeqValue
    UNION ALL
    SELECT 200 SeqValue
    UNION ALL
    SELECT 300 SeqValue
    UNION ALL
    SELECT 400 SeqValue
    UNION ALL
    SELECT 500 SeqValue
    UNION ALL
    SELECT 600 SeqValue
    UNION ALL
    SELECT 700 SeqValue
    UNION ALL
    SELECT 800 SeqValue
    UNION ALL
    SELECT 900 SeqValue
    ) HUNDREDS
) SEQ

다음은 하드웨어 엔지니어의 피츠버그 DBA 솔루션 버전입니다.

SELECT
    (TWO_1.SeqValue + TWO_2.SeqValue + TWO_4.SeqValue + TWO_8.SeqValue + TWO_16.SeqValue) SeqValue
FROM
    (SELECT 0 SeqValue UNION ALL SELECT 1 SeqValue) TWO_1
    CROSS JOIN (SELECT 0 SeqValue UNION ALL SELECT 2 SeqValue) TWO_2
    CROSS JOIN (SELECT 0 SeqValue UNION ALL SELECT 4 SeqValue) TWO_4
    CROSS JOIN (SELECT 0 SeqValue UNION ALL SELECT 8 SeqValue) TWO_8
    CROSS JOIN (SELECT 0 SeqValue UNION ALL SELECT 16 SeqValue) TWO_16;

테이블 내의 레코드가 필요하고 동시성 문제가 발생하지 않도록 하려면 다음과 같이 하십시오.

먼저 레코드를 저장할 테이블을 만듭니다.

CREATE TABLE `incr` (
  `Id` int(11) NOT NULL auto_increment,
  PRIMARY KEY  (`Id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8;

다음으로 다음과 같은 저장 프로시저를 만듭니다.

DELIMITER ;;
CREATE PROCEDURE dowhile()
BEGIN
  DECLARE v1 INT DEFAULT 5;
  WHILE v1 > 0 DO
    INSERT incr VALUES (NULL);
    SET v1 = v1 - 1;
  END WHILE;
END;;
DELIMITER ;

마지막으로 SP에 문의합니다.

CALL dowhile();
SELECT * FROM incr;

결과

Id
1
2
3
4
5

1부터 100까지를 테이블에 삽입한다고 칩시다.적어도 행 수가 같은 다른 테이블이 있는 경우(테이블의 내용은 중요하지 않음)에는 다음과 같은 방법을 권장합니다.

INSERT INTO pivot100 
SELECT @ROW := @ROW + 1 AS ROW
 FROM someOtherTable t
 join (SELECT @ROW := 0) t2
 LIMIT 100
;

1이 아닌 다른 것으로 시작하는 범위를 원하십니까?가입 시 @ROW 설정 내용을 변경하기만 하면 됩니다.

여러분들도 아시겠지만, 이건 좀 엉성하니 주의해서 사용하세요.

SELECT
  id % 12 + 1 as one_to_twelve
FROM
  any_large_table
GROUP BY
  one_to_twelve
;

DECLARE i INT DEFAULT 0;

WHILE i < 6 DO
  /* insert into table... */
  SET i = i + 1;
END WHILE;

과 매우 응답 WITHmysql > =0 의 > = 8.0 입니다.

WITH DIGITS (N) AS (
  SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL
  SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL
  SELECT 8 UNION ALL SELECT 9)
SELECT 
  UNITS.N + TENS.N*10 + HUNDREDS.N*100 + THOUSANDS.N*1000 
FROM 
  DIGITS AS UNITS, DIGITS AS TENS, DIGITS AS HUNDREDS, DIGITS AS THOUSANDS;

다른 모든 답변은 양호하지만 MySQL이 모든 번호를 생성한 후 필터링하도록 강제하기 때문에 모두 더 큰 범위에 대한 속도 문제가 있습니다.

다음은 MySQL이 필요한 숫자만 생성하므로 더 빠릅니다.

set @amount = 55; # How many numbers from zero you want to generate

select `t0`.`i`+`t1`.`i`+`t2`.`i`+`t3`.`i` as `offset`
from
(select 0 `i` union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) `t0`,
(select 0 `i` union select 10 union select 20 union select 30 union select 40 union select 50 union select 60 union select 70 union select 80 union select 90) `t1`,
(select 0 `i` union select 100 union select 200 union select 300 union select 400 union select 500 union select 600 union select 700 union select 800 union select 900) `t2`,
(select 0 `i` union select 1000 union select 2000 union select 3000 union select 4000 union select 5000 union select 6000 union select 7000 union select 8000 union select 9000) `t3`
where `t3`.`i`<@amount
and `t2`.`i`<@amount
and `t1`.`i`<@amount
and `t0`.`i`+`t1`.`i`+`t2`.`i`+`t3`.`i`<@amount;

상기의 사용으로, 최대 10,000개의 번호(0 ~999)를 생성할 수 있습니다.또, 아무리 낮은 번호라도, 저속 오버헤드가 발생하지 않습니다.

(MySQL에서) 긴 시퀀스를 가진 테이블을 만드는 가장 짧은 방법은 기존 테이블을 직접 결합하는 것입니다.에는 ('') MySQL'이 information_schema.COLUMNS테이블 나는 그것을 사용합니다.

DROP TABLE IF EXISTS seq;
CREATE TABLE seq (i MEDIUMINT AUTO_INCREMENT PRIMARY KEY)
    SELECT NULL AS i
    FROM information_schema.COLUMNS t1
    JOIN information_schema.COLUMNS t2
    JOIN information_schema.COLUMNS t3
    LIMIT 100000; -- <- set your limit here

보통 1개의 조인으로 100만 개 이상의 행을 만들 수 있습니다.단, 1개의 조인은 문제가 없습니다:-)-제한을 설정하는 것을 잊지 마십시오.

「 」를 0를 해야 돼요AUTO_INCEMENT소유물.

ALTER TABLE seq ALTER i DROP DEFAULT;
ALTER TABLE seq MODIFY i MEDIUMINT;

이제 삽입할 수 있습니다.0

INSERT INTO seq (i) VALUES (0);

그리고 음수들도 마찬가지입니다.

INSERT INTO seq (i) SELECT -i FROM seq WHERE i <> 0;

다음 방법으로 숫자의 유효성을 확인할 수 있습니다.

SELECT MIN(i), MAX(i), COUNT(*) FROM seq;

이는 이전 답변(https://stackoverflow.com/a/53125278/2009581),에 기반하지만 MySQL 5.7과 호환됩니다.복제본 및 읽기 전용 사용자에게 작동합니다.

SELECT x1.N + x10.N*10 + x100.N*100 + x1000.N*1000
  FROM (SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) x1,
       (SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) x10,
       (SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) x100,
       (SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) x1000
 WHERE x1.N + x10.N*10 + x100.N*100 + x1000.N*1000 <= @max;

[0] 범위의 정수를 생성합니다.@max].

MySql 8 이상을 사용하는 경우 json_table을 사용하는 방법은 다음과 같습니다.

set @noRows = 100;
SELECT tt.rowid - 1 AS value
  FROM JSON_TABLE(CONCAT('[{}', REPEAT(',{}', @noRows - 1), ']'),
                  "$[*]" COLUMNS(rowid FOR ORDINALITY)
       ) AS tt;

(h/t - https://www.percona.com/blog/2020/07/27/generating-numeric-sequences-in-mysql/)

MariaDB에서는 다음 작업을 수행할 수 있습니다.

선택 * seq_i_to_n

예를 들어 다음과 같습니다.

선택 * seq_0_to_1000

선택 * seq_1_에서 1000000까지

참고 자료: https://www.percona.com/blog/2020/07/27/generating-numeric-sequences-in-mysql/

제가 공유하고 싶은 아이디어는 질문에 대한 정확한 답변은 아니지만, 몇몇 사람들에게 도움이 될 수 있기 때문에 공유하고자 합니다.

한정된 숫자의 집합만 필요한 경우가 많은 경우 필요한 숫자의 표를 작성하여 매번 그 표를 사용하는 것이 좋습니다.예를 들어 다음과 같습니다.

CREATE TABLE _numbers (num int);
INSERT _numbers VALUES (0), (1), (2), (3), ...;

이는 어느 정도 합리적인 제한 이하의 숫자가 필요한 경우에만 적용할 수 있으므로 시퀀스 100만 생성에는 사용하지 말고 숫자 1에 사용할 수 있습니다.예를 들어 10k입니다.

에 이 번호 리스트가 있는 경우_numbers그러면 다음과 같은 쿼리를 작성하여 문자열의 개별 문자를 얻을 수 있습니다.

SELECT number, substr(name, num, 1) 
    FROM users
    JOIN _numbers ON num < length(name)
    WHERE user_id = 1234
    ORDER BY num;

10k보다 큰 숫자가 필요한 경우 테이블을 직접 결합할 수 있습니다.

SELECT n1.num * 10000 + n2.num
    FROM _numbers n1
    JOIN _numbers n2
    WHERE n1 < 100 
    ORDER BY n1.num * 10000 + n2.num; -- or just ORDER BY 1 meaning the first column

재귀적 cte를 사용합니다.

  with recursive rnums as (
  select 1 as n
      union all
  select n+1 as n from rnums
      where n <10
  )
  select * from rnums
  ;

결과는 다음과 같습니다.+---+ | n | +---+ | 1 | 2 | 3 | 4 | | 5 | 6 | 7 | 8 | 9 | 10 | +---+ 10 행 세트(0.00초)

언급URL : https://stackoverflow.com/questions/186756/generating-a-range-of-numbers-in-mysql

반응형

'source' 카테고리의 다른 글

Python을 사용하여 시스템 호스트 이름을 가져오려면 어떻게 해야 합니까?  (0) 2022.11.25
mysql 도커 컨테이너가 자주 크래시됨  (0) 2022.11.25
kubernetes/docker 군집에 mariadb galera 클러스터 배포  (0) 2022.11.25
MySQL - 구조가 같지만 데이터가 다른 여러 테이블에서 데이터 선택  (0) 2022.11.25
무선상의 ADB  (0) 2022.11.25

'source'의 다른글

  • 현재글MySQL에서 번호 범위 생성

관련글

댓글

티스토리툴바