Auto increment a value on the fly with MySQL ~ Tech Blog

By assigning a value to a variable in MySQL and incrementing the variable as part of a select statement, it's possible to have a number auto-incremented on the fly. This number has no other reference to the table other than the order the data is selected. It is also possible to auto increment the variable when running an update statement so a new set of incrementing values could be stored into the table.

Example table

The examples presented in this post use a table called "fruit" defined as follows:

CREATE TABLE `fruit` (
  `fruit_id` int(11) NOT NULL auto_increment,
  `name` varchar(50) NOT NULL,
  `somevalue` int(11) NOT NULL default 0,
  PRIMARY KEY  (`fruit_id`)
) ENGINE=MyISAM

And some sample data:

INSERT INTO `fruit` (`name`)
VALUES ('Banana'), ('Orange'), ('Cherry'), ('Apple');

Doing "SELECT * FROM fruit" will return this:

+----------+--------+-----------+
| fruit_id | name   | somevalue |
+----------+--------+-----------+
|        1 | Banana |         0 |
|        2 | Orange |         0 |
|        3 | Cherry |         0 |
|        4 | Apple  |         0 |
+----------+--------+-----------+

SELECT with auto incrementing value

To have an auto incrementing value, first assign a variable like so:

SELECT @i:=0;

This can now be incremented in the SELECT query like so:

SELECT *, @i:=@i+1 AS i FROM fruit ORDER BY name;

This will return:

+----------+--------+-----------+------+
| fruit_id | name   | somevalue | i    |
+----------+--------+-----------+------+
|        4 | Apple  |         0 |    1 |
|        1 | Banana |         0 |    2 |
|        3 | Cherry |         0 |    3 |
|        2 | Orange |         0 |    4 |
+----------+--------+-----------+------+

Notice that for each record, i is one greater than the previous record.

UPDATE with auto incrementing value

The next example updates the "somevalue" column with an incrementing value. Note that @i needs to be reset otherwise it will continue in this example with 5 being the next number.

SELECT @i:=0;

As with the SELECT query above, we'll update ordering by name:

UPDATE fruit SET somevalue = @i:=@i+1 ORDER BY name;

And the result from "SELECT * FROM fruit"

+----------+--------+-----------+
| fruit_id | name   | somevalue |
+----------+--------+-----------+
|        1 | Banana |         2 |
|        2 | Orange |         4 |
|        3 | Cherry |         3 |
|        4 | Apple  |         1 |
+----------+--------+-----------+

or "SELECT * FROM fruit ORDER BY name"

+----------+--------+-----------+
| fruit_id | name   | somevalue |
+----------+--------+-----------+
|        4 | Apple  |         1 |
|        1 | Banana |         2 |
|        3 | Cherry |         3 |
|        2 | Orange |         4 |
+----------+--------+-----------+

Tech Blog

Saturday, 8 September 2018