0

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;
ann
  • 576
  • 1
  • 10
  • 19
tamla83
  • 368
  • 3
  • 13

1 Answers1

3

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.

tadamson
  • 8,581
  • 1
  • 18
  • 9