0

MySQL has auto_increment which allows us to create unique primary keys automatically.

However, this only works for integer columns. There doesn't seem to be a way to combine auto_increment with binary columns, for example:

mysql > create table Users(Id binary(6) auto_increment, primary key(id));

#1063 - Incorrect column specifier for column 'Id'

How can we create or implement an auto-increment binary column?

(Finding a solution which maintains atomicity and have comparable performance to auto_increment integer columns.)

Pacerier
  • 86,231
  • 106
  • 366
  • 634
  • probably a before-insert trigger to calculate the new id value in a transaction-safe manner. – Marc B Jul 09 '12 at 19:59

1 Answers1

0

A trigger that is called before each insert operation should be safe for transactions and will accomplish what you want.

Checkout the manual page on triggers.

Pacerier
  • 86,231
  • 106
  • 366
  • 634
Lusitanian
  • 11,012
  • 1
  • 41
  • 38
  • I believe that triggers are fully supported on InnoDB tables, yes. – Lusitanian Jul 09 '12 at 20:03
  • Misunderstood you. No, I do not know if atomicity is guaranteed, though from brief googling it appears that the before update trigger is atomic. Having said that, if you are going to go this route, you should absolutely test on a small database before use. – Lusitanian Jul 09 '12 at 20:08
  • Hmm, but it doesn't seem to be acidly safe to use triggers as a replacement for auto-increment, from http://dba.stackexchange.com/a/3409/9405 and http://stackoverflow.com/questions/16881043/are-database-triggers-safe-for-cross-table-integrity-constraints/16881199#16881199 – Pacerier Dec 07 '14 at 17:44