MySQL auto increment plus alphanumerics on one column
I am new to MySQL, coming from Oracle. I have a requirement to create a table with a primary key in one column, but with the following format.
X-A
a letter indicating the country of origin, for example. S
for Spain, Z
for Zimbabwe e.tc (we only have five countries of origin)
YYYYMMDD
- Date in this format, 9999
- 4-digit office code. 9999999
- 7 right padded sequential numbers from sequence in Oracle (auto-add in MySQL)
This gives me a primary key, for example Z2010013150110000001
as a primary key.
My question is how can I generate the part 9999999
in MySQL. In Oracle it would be
select 'Z'||to_char(sysdate, 'YYYYMMDD')|| 5011||cust_id.nextval from dual;
source to share
auto_increment cannot just be part of a field, so one column can be 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 these utility codes and dates to filter the 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 away WHERE office_code = 1256 AND sysdate >= '2010-01-01'
without having to parse that huge string in any way.
If you really want this huge string as a single field primary key, you'll manually increment everything yourself. I still don't recommend doing this, although MySQL really likes its PKs to be numeric.
source to share