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.
a letter indicating the country of origin, for example.
for Zimbabwe e.tc (we only have five countries of origin)
- Date in this format,
- 4-digit office code.
- 7 right padded sequential numbers from sequence in Oracle (auto-add in MySQL)
This gives me a primary key, for example
as a primary key.
My question is how can I generate the part
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