Générer une gamme de nombres dans MySQL

Comment générer une série de nombres consécutifs (un par ligne) à partir d’une requête MySQL afin de pouvoir les insérer dans une table?

Par exemple:

nr 1 2 3 4 5 

Je voudrais utiliser uniquement MySQL pour cela (pas PHP ou d’autres langages).

Si vous avez besoin des enregistrements dans une table et que vous souhaitez éviter les problèmes de concurrence, voici comment procéder.

D’abord, vous créez une table dans laquelle stocker vos enregistrements.

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

Deuxièmement, créez une procédure stockée comme ceci:

 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 ; 

Appelez enfin le SP:

 CALL dowhile(); SELECT * FROM incr; 

Résultat

 Id 1 2 3 4 5 

Voici une façon de le faire basée sur des ensembles sans boucles. Cela peut également être transformé en vue de réutilisation. L’exemple montre la génération d’une séquence de 0 à 999, mais bien sûr, elle peut être modifiée pour convenir.

 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 

Voici la version de l’ingénieur hardware de la solution de Pittsburgh 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; 

Disons que vous voulez insérer les chiffres 1 à 100 dans votre tableau. Tant que vous avez une autre table qui a au moins autant de lignes (peu importe le contenu de la table), alors c’est ma méthode préférée:

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

Vous voulez une gamme qui commence par autre chose que 1? Changez simplement ce que @ROW a défini dans la jointure.

Comme vous le comprenez tous, ceci est plutôt pirate donc utilisez-le avec précaution

 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; 

La façon la plus courte que je connaisse (en MySQL) de créer une table avec une longue séquence est de (croiser) joindre une table existante avec elle-même. Comme tout serveur MySQL (commun) possède la table information_schema.COLUMNS , je l’utilise:

 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 

D'habitude, une jointure devrait suffire à créer plus de 1M de lignes - Mais une autre jointure ne vous fera pas de mal 🙂 - N'oubliez pas de définir une limite.

Si vous souhaitez inclure 0 , vous devez "supprimer" la propriété AUTO_INCEMENT .

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

Maintenant, vous pouvez insérer 0

 INSERT INTO seq (i) VALUES (0); 

et des nombres négatifs aussi

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

Vous pouvez valider les numéros avec

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

L’idée que je veux partager n’est pas une réponse précise à la question mais peut être utile pour certains, alors j’aimerais la partager.

Si vous n’avez souvent besoin que d’un nombre limité de nombres, il peut être utile de créer un tableau avec les nombres dont vous avez besoin et d’utiliser simplement ce tableau à chaque fois. Par exemple:

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

Ceci ne peut être appliqué que si vous avez besoin de nombres inférieurs à une certaine limite raisonnable, donc ne l’utilisez pas pour générer la séquence 1 … 1 million mais peut être utilisée pour les nombres 1 … 10k, par exemple.

Si vous avez cette liste de nombres dans la table _numbers , vous pouvez écrire des requêtes comme celle-ci, pour obtenir les caractères individuels d’une chaîne:

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

Si vous avez besoin d'un nombre plus grand que 10k, vous pouvez joindre la table à elle-même:

 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