CODEWITHSUNDEEP
javascriptmysqlsqlnode.js
Preshy Jones
Preshy Jones

Reputation: 29

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....')
    });

});

Upvotes: 0

Views: 286

Answers (2)

&#193;lvaro Gonz&#225;lez
&#193;lvaro Gonz&#225;lez

Reputation: 146588

You've increased the auto_increment_increment system variable from the default value of 1 to 10. Its scope is Global, Session so you can either set it for current session or change it for the entire server (the first option used to be restricted prior to MySQL/8.0.18).

If you don't need it at all I suggest you just find the directive in the settings file and comment it out.

Upvotes: 1

Sebastian B.
Sebastian B.

Reputation: 2211

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/

Upvotes: 0

Related Questions