MySQL as well as other SQL databases doesn’t return data in a predictable order without using order by clause. This can be a problem in some situations: for example user adding positions to an invoice can expect they’re returned in the same order from the server. The same expectation is possibly true for the most of other master-detail use cases. In this short example I’m going to show a simple trick how to achieve this.

In the medieval times everything was much simpler, because for MySQL we were using numeric AUTO_INCREMENT primary keys:

mysql> create table animal (id bigint(20) auto_increment primary key, name varchar(255));
mysql> insert into animal(name) values('lion'), ('tiger'), ('fish');

mysql> select * from animal;
+----+-------+
| id | name  |
+----+-------+
|  1 | lion  |
|  2 | tiger |
|  3 | fish  |
+----+-------+
3 rows in set (0.00 sec)

As you can see here, the simple select from this table returns data in the insertion order. This is because it seems MySQL by default uses a primary key order when no order by clause is provided. You can verify this fact with this example:

mysql> create table animal2 (id bigint(20) primary key, name varchar(255));
mysql> insert into animal2(id, name) values (3,'lion'), (2, 'tiger'), (1, 'fish');

mysql> select * from animal2;
+----+-------+
| id | name  |
+----+-------+
|  1 | fish  |
|  2 | tiger |
|  3 | lion  |
+----+-------+
3 rows in set (0.00 sec)

Recently, instead of numeric primary keys we frequently use UUID-s. This is because nowadays the application data is often split into multiple types of storage or kept in data clusters and we need to identify database rows with some unique identifiers. MySQL doesn’t provide a support for separate UUID column type (PostgreSQL does), but instead we can use binary(16) column type:

mysql> create table animal3 (id binary(16) primary key, name varchar(255));
mysql> insert into animal3(id, name) values 
    (UUID_TO_BIN('dce71b17-7ba3-456b-829d-d97feda7e5fe'), 'lion'), 
    (UUID_TO_BIN('c7c6d041-4c3c-4127-9a05-d59eafc0c2d5'), 'tiger'),
    (UUID_TO_BIN('8e76df35-7a12-4647-8e6d-7a2bfb5b21ee'), 'fish'); 

mysql> select BIN_TO_UUID(id), name from animal3;
+--------------------------------------+-------+
| BIN_TO_UUID(id)                      | name  |
+--------------------------------------+-------+
| 8e76df35-7a12-4647-8e6d-7a2bfb5b21ee | fish  |
| c7c6d041-4c3c-4127-9a05-d59eafc0c2d5 | tiger |
| dce71b17-7ba3-456b-829d-d97feda7e5fe | lion  |
+--------------------------------------+-------+
3 rows in set (0.00 sec)

That’s quite inconvenient, because we always need to convert back and forth the data from and to the binary form using BIN_TO_UUID() and UUID_TO_BIN() functions. We could use strings and char(36) column instead of binary(16) but that would be inefficient, because char(36) takes 36 bytes while binary(16) only 16. So, from the performance point of view it’s better to use binary version.

Regardless the above, the problem we have now with our UUID-s is that the data is still returned in the primary key order, what can be seen on the example above. To make it possible to sort the data in the insertion order we anyway need some AUTO_INCREMENT field that could be used in order by clause:

mysql> create table animal4 (id binary(16) primary key, seq bigint(20) auto_increment, name varchar(255));
ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key

Oops, the AUTO_INCREMENT field needs to be a key… But does it need to be a primary key? No, it looks it’s enought for it to be an unique key:

mysql> create table animal4 (id binary(16) primary key, seq bigint(20) auto_increment unique key, name varchar(255));
Query OK, 0 rows affected (0.05 sec)

mysql> insert into animal4(id, name) values 
    (UUID_TO_BIN('dce71b17-7ba3-456b-829d-d97feda7e5fe'), 'lion'), 
    (UUID_TO_BIN('c7c6d041-4c3c-4127-9a05-d59eafc0c2d5'), 'tiger'),
    (UUID_TO_BIN('8e76df35-7a12-4647-8e6d-7a2bfb5b21ee'), 'fish');

mysql> select BIN_TO_UUID(id), seq, name from animal4;
+--------------------------------------+-----+-------+
| BIN_TO_UUID(id)                      | seq | name  |
+--------------------------------------+-----+-------+
| 8e76df35-7a12-4647-8e6d-7a2bfb5b21ee |   2 | fish  |
| c7c6d041-4c3c-4127-9a05-d59eafc0c2d5 |   3 | tiger |
| dce71b17-7ba3-456b-829d-d97feda7e5fe |   1 | lion  |
+--------------------------------------+-----+-------+
3 rows in set (0.00 sec)

In such a scenario MySQL still sorts the data using primary key, but now we can apply our own (insertion) order with the order by seq clause:

mysql> select BIN_TO_UUID(id), seq, name from animal4 order by seq;
+--------------------------------------+-----+-------+
| BIN_TO_UUID(id)                      | seq | name  |
+--------------------------------------+-----+-------+
| dce71b17-7ba3-456b-829d-d97feda7e5fe |   1 | lion  |
| 8e76df35-7a12-4647-8e6d-7a2bfb5b21ee |   2 | fish  |
| c7c6d041-4c3c-4127-9a05-d59eafc0c2d5 |   3 | tiger |
+--------------------------------------+-----+-------+
3 rows in set (0.00 sec)

To avoid spinning out things here is the solution for JPA/Hibernate entity to achieve the same:

@Entity
public class MyEntity {

	@Id
	@GeneratedValue(generator = "uuid2")
	@Column(columnDefinition = "binary(16)")
	protected UUID id;

	@Column(insertable = false, updatable = false, columnDefinition = "BIGINT(20) NOT NULL AUTO_INCREMENT UNIQUE KEY")
	protected long seq = 0;
	
    // [...]

	public UUID getId() {
		return id;
	}

	public long getSeq() {
		return seq;
	}
}

Stay tuned for the next episode!