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/