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