Im trying to keep track of points / stats by making a column auto increment. However, it's not working as I want it. I want it to auto increment if the row gets updated, not if a new row gets added. For example, if I run the update command it will just add one to the "count" column for the row I updated. If I add a new row it'll start at 0!
Here is my code to create a table:
statement = connection.prepareStatement(
"CREATE TABLE IF NOT EXISTS stats" +
"id varchar(100) not null," +
"count int not null auto_increment," +
"PRIMARY KEY (id)," +
"KEY (count)" +
connection = plugin.getHikari().getConnection();
statement = connection.prepareStatement("INSERT INTO stats (id) VALUES(?) ON DUPLICATE KEY UPDATE id=?");
1) Remove the AUTO_INCREMENT attribute from the `count` column.
AUTO_INCREMENT isn't a suitable mechanism for what you are trying to achieve.
2) Add a DEFAULT 1 to the definition of the `count` column
When a new row is inserted into the table, and a value is not supplied for the `count` column, the default value will be assigned to the column.
3) re-write the INSERT statement to increment the `count` column when an attempt is made to add a duplicate `id` value
INSERT INTO atickets_stats (id) VALUES ( ? ) ON DUPLICATE KEY UPDATE count = count + 1 ;