0

So before I make this completely in my main programming language I wanted to know, if there is any way to accomplish this with pure SQL? Also I think it would be a lot more performant.

MySQL Version 5.7.22-1-log

+----+-------+
| id | num   |
+----+-------+
| 19 | A1000 |
| 20 | A1001 |
| 21 | A1002 |
| 22 | A1003 |
| 23 | A1010 |
| 24 | A1011 |
| 25 | A1012 |
| 26 | A1013 |
| 27 | A1020 |
| 28 | A1030 |
| 29 | A1031 |
| 30 | A1032 |
| 31 | A1033 |
| 33 | A1034 |
+----+-------+
# structure
+-------+------------------+------+-----+---------+----------------+
| Field | Type             | Null | Key | Default | Extra          |
+-------+------------------+------+-----+---------+----------------+
| id    | int(11) unsigned | NO   | PRI | NULL    | auto_increment |
| num   | varchar(25)      | NO   | UNI |         |                |
+-------+------------------+------+-----+---------+----------------+

So there are so numbers available between the ranges. To make things tricky, they are prefixed.

So I want to generate/insert e.g. exactly 50 numbers with prefix A and it should fill the free numbers between like A1004,A1005,A1006,A1007 etc.

1 Answer 1

1

I often use a table containing integers from 1 to (let's say) 1.000.000 because it's always useful with mysql. Assuming such table exists and is defined as:

CREATE TABLE `integers` (
    `n` INT(10) UNSIGNED NOT NULL,
    PRIMARY KEY (`n`)
) ENGINE=InnoDB

Then with this setup:

CREATE TABLE dbat (
id INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
num VARCHAR(25) NOT NULL,
PRIMARY KEY (id),
UNIQUE KEY num (num)
);

INSERT INTO dbat (id, num) VALUES 
(19, 'A1000'),
(20, 'A1001'),
(21, 'A1002'),
(22, 'A1003'),
(23, 'A1010'),
(24, 'A1011'),
(25, 'A1012'),
(26, 'A1013'),
(27, 'A1020'),
(28, 'A1030'),
(29, 'A1031'),
(30, 'A1032'),
(31, 'A1033'),
(33, 'A1034');

SELECT t.num
FROM (SELECT CONCAT('A', LPAD(i.n, 4, '0')) AS num
        FROM mta_info.integers AS i
        WHERE i.n >= 1000 AND i.n <= 9999) AS t
LEFT JOIN dbat ON dbat.num = t.num
WHERE dbat.id IS NULL
ORDER BY t.num ASC
LIMIT 50
;

Yields

num
A1004
A1005
A1006
A1007
A1008
A1009
A1014
A1015
A1016
A1017
A1018
A1019
A1021
A1022
A1023
A1024
A1025
A1026
A1027
A1028
A1029
A1035
A1036
A1037
A1038
A1039
A1040
A1041
A1042
A1043
A1044
A1045
A1046
A1047
A1048
A1049
A1050
A1051
A1052
A1053
A1054
A1055
A1056
A1057
A1058
A1059
A1060
A1061
A1062
A1063

Instantly executed

Having such integers tables is something every DBA should have when dealing with MySQL!

4
  • Works awesome, thanks for this detailed answer!
    – CodeBrauer
    Jun 28, 2018 at 15:17
  • It works perfectly and very fast. But are there any chances to adjust the query and get random "available" numbers?
    – CodeBrauer
    Aug 27, 2018 at 8:27
  • 1
    @CodeBrauer I'm not sure I got your request but you can put it into a subselect a get what you want from it SELECT * FROM (...the select in my main answer...) AS t ORDER BY RAND() LIMIT 1. For such small sets, ORDER BY RAND() is fair enough.
    – Xenos
    Aug 27, 2018 at 8:37
  • So easy - didn't thought about just using a subquery... Thanks a lot! There execution time is like 5 times slower, but it's okay for this purpose.
    – CodeBrauer
    Aug 28, 2018 at 14:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.