MySQL自动递增加一列字母数字 [英] MySQL auto increment plus alphanumerics in one column

问题描述

我是来自Oracle的MySQL新手.我需要创建一个表，其中主键位于一列中，但格式如下.

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字母，注明原籍国，例如S对于西班牙，Z对于津巴布韦e.tc(我们只有五个来源国)

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-该格式的日期，
9999-4位办公室代码.
9999999-Oracle中一个序列的右7个填充序列号(MySQL中的自动递增)
这给了我像Z2010013150110000001这样的主键作为主键.

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.

我的问题是如何在MySQL中生成9999999的一部分.在Oracle中应该是

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;

推荐答案

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)
);

然后您可以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
);

然后，您可以使用SELECT CONCAT(country_code, DATE_FORMAT(sysdate, '%Y%m%d'), office_code, LPAD(id, 7, '0')) AS full_key FROM xxxxx甚至可以放入WHERE office_code = 1256 AND sysdate >= '2010-01-01'，而不必以某种方式解析该巨大的字符串.

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.

如果您确实需要大字符串作为单字段主键，则可以自己手动增加内容.我仍然不建议这样做，因为MySQL 真的喜欢它的PK是数字的.

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.

这篇关于MySQL自动递增加一列字母数字的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！