mysql table AUTO increment keeps incrementing in steps of instead of 1. Like 41,51,61

Even after deleting all rows in the table continues. It continues adding from the last inserted id. I used this to create my table

app.get('/createuserstable', (req, res) => {
    let sql = 'CREATE TABLE Users(id int AUTO_INCREMENT, name VARCHAR(255), username VARCHAR(255), email VARCHAR(255), PRIMARY KEY (id))';
    db.query(sql, (err, result) => {
        if (err) throw err;
        console.log(result);
        res.send('Users table created....');
    });
});

adding users with a signup route and it keeps incrementing in steps of 10

app.post('/signup', (req, res) => {
    let user = { name: req.body.name, username: req.body.username, email: req.body.email };
    db.query('INSERT INTO users SET?', user, (error, result) => {
        if (error) throw error;
        res.status(201).send(`User added with ID: ${result.insertId}`);
    });
});

this is the code that is used the 10 rows of json data

app.get('/populate', (req, res) => {
    request({
        url: "http://jsonplaceholder.typicode.com/users",
        json: true
    }, (err, resp, body) => {
        //res.send(typeof body);
        for (var i = 0; i < body.length; i++) {

            let post = { id: body[i].id, name: body[i].name, username: body[i].username, email: body[i].email };
            let sql = 'INSERT INTO users SET?';
            let query = db.query(sql, post, (err, result) => {
                if (err) throw err;
                console.log(result);
            });
        };
        res.send('users data added....')
    });

});

Answer

That’s normal behavior of MySQL and often intended, so that IDs are never re-used, even for deleted records.

If you really need to reset auto-increment counter, you could either:

• emptying whole table at once with TRUNCATE TABLE users
• drop and re-create the table with DROP TABLE users / CREATE TABLE users ...
• ALTER TABLE users AUTO_INCREMENT = 1;

See also https://www.mysqltutorial.org/mysql-reset-auto-increment/