Generare una serie di numeri in MySQL

Come posso generare una serie di numeri consecutivi (uno per riga) da una query MySQL in modo che io possa inserirli in una tabella?

Per esempio:

nr 1 2 3 4 5 

Mi piacerebbe usare solo MySQL per questo (non PHP o altre lingue).

Se hai bisogno dei record in una tabella e vuoi evitare problemi di concorrenza, ecco come farlo.

Per prima cosa crei una tabella in cui archiviare i tuoi record

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

In secondo luogo creare una stored procedure come questa:

 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 ; 

Chiama infine l’SP:

 CALL dowhile(); SELECT * FROM incr; 

Risultato

 Id 1 2 3 4 5 

Ecco un modo per farlo basato su set senza loop. Questo può anche essere trasformato in una vista per il riutilizzo. L’esempio mostra la generazione di una sequenza da 0 a 999, ma ovviamente può essere modificata per adattarsi.

 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 

Ecco la versione di un ingegnere hardware della soluzione DBA di Pittsburgh :

 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; 

Diciamo che vuoi inserire i numeri da 1 a 100 nel tuo tavolo. Finché hai qualche altra tabella con almeno tante righe (non importa il contenuto della tabella), allora questo è il mio metodo preferito:

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

Vuoi un intervallo che inizia con qualcosa di diverso da 1? Basta cambiare ciò che @ROW viene impostato sul join.

Come tutti voi capite, questo è piuttosto hacky, quindi usatelo con cura

 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; 

Il modo “più breve” che conosco (in MySQL) per creare una tabella con una lunga sequenza è quello di (incrociare) unire una tabella esistente con se stessa. Dal momento che qualsiasi server MySQL (comune) ha la tabella information_schema.COLUMNS , lo userei:

 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 

Di solito un join dovrebbe essere sufficiente per creare più di 1 milione di righe - Ma un altro join non farà male 🙂 - Basta non dimenticare di impostare un limite.

Se vuoi includere 0 , devi "rimuovere" la proprietà AUTO_INCEMENT .

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

Ora puoi inserire 0

 INSERT INTO seq (i) VALUES (0); 

e numeri negativi pure

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

È ansible convalidare i numeri con

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

L’idea che voglio condividere non è una risposta precisa per la domanda, ma può essere utile per alcuni, quindi mi piacerebbe condividerla.

Se hai spesso bisogno solo di un numero limitato di numeri, può essere utile creare una tabella con i numeri di cui potresti aver bisogno e usare sempre quella tabella ogni volta. Per esempio:

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

Questo può essere applicato solo se hai bisogno di numeri inferiori ad un certo limite ragionevole, quindi non usarlo per generare la sequenza 1 … 1 milione ma può essere usato per i numeri 1 … 10k, per esempio.

Se hai questa lista di numeri nella tabella _numbers , puoi scrivere query come questa, per ottenere i singoli caratteri di una stringa:

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

Se hai bisogno di numeri maggiori di 10k, puoi unirti a te stesso:

 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