MySQL auto increment plus alphanumerics in one column

Question

I am new to MySQL coming from Oracle. I have a requirement to create a table with the primary key in one column but with the following format.

X-A letter stating the country of origin e.g. S for Spain, Z for Zimbabwe e.tc (we have five countries of origins only)

YYYYMMDD-Date in that format

9999-4 digit office code.

9999999-7 right padded sequence number from a sequence in Oracle (auto increment in MySQL)

This gives me a primary key like Z2010013150110000001 as the primary key.

My question is how do I generate the part of 9999999 in MySQL. In Oracle it would have been

select 'Z'||to_char(sysdate, 'YYYYMMDD')|| 5011||cust_id.nextval from dual;

Answer 1

auto_increment can't be just part of a field, so a single column might be a wash. So how about:

CREATE TABLE xxxxx (
id int unsigned not null auto_increment,
rest_of_that_key char(xx) not null,
// other goodies
PRIMARY KEY (id)
);

Then you can SELECT CONCAT(rest_of_that_key, LPAD(id, 7, '0')) AS full_key FROM xxxxx.

---

Or even better, so you can use those office codes and dates to filter data with:

CREATE TABLE xxxxx (
id int unsigned not null auto_increment,
country_code char(1) not null,
sysdate date not null,
office_code smallint unsigned not null,
// other goodies
PRIMARY KEY (id),
KEY country_code (country_code)
// etc, index the useful stuff
);

Then you can use SELECT CONCAT(country_code, DATE_FORMAT(sysdate, '%Y%m%d'), office_code, LPAD(id, 7, '0')) AS full_key FROM xxxxx and even throw in a WHERE office_code = 1256 AND sysdate >= '2010-01-01' without having to somehow parse that huge string.

If you really need that huge string as a single-field primary key, you'll have manually increment things yourself. I still wouldn't recommend doing it though, MySQL really likes its PKs to be numeric.