Insert a sequence of number where some numbers could exist already / fill gaps? 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.
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 &gt;= 1000 AND i.n &lt;= 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!