oracle MySQL自动增量加一列中的字母数字
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2380923/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySQL auto increment plus alphanumerics in one column
提问by tamla83
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.
我是来自 Oracle 的 MySQL 新手。我需要使用一列中的主键创建一个表，但格式如下。
X-Aletter stating the country of origin e.g. Sfor Spain, Zfor Zimbabwe e.tc (we have five countries of origins only)
X-A说明原产国的信件，例如S西班牙、Z津巴布韦等（我们只有五个原产国）
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 Z2010013150110000001as the primary key.
YYYYMMDD- 该格式的日期，9999- 4 位办公代码。9999999- 来自 Oracle 中序列的 7 个右填充序列号（MySQL 中的自动增量）
这给了我一个像主键一样Z2010013150110000001的主键。
My question is how do I generate the part of 9999999in MySQL. In Oracle it would have been
我的问题是如何9999999在 MySQL 中生成部分。在 Oracle 中它本来是
select 'Z'||to_char(sysdate, 'YYYYMMDD')|| 5011||cust_id.nextval from dual;
回答by tadamson
auto_incrementcan't be just part of a field, so a single column might be a wash. So how about:
auto_increment不能只是一个字段的一部分，所以单个列可能是一个清洗。那么怎么样：
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.
那么你可以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 xxxxxand even throw in a WHERE office_code = 1256 AND sysdate >= '2010-01-01'without having to somehow parse that huge string.
然后你可以使用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'而不必以某种方式解析那个巨大的字符串。
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 reallylikes its PKs to be numeric.
如果您真的需要那个巨大的字符串作为单字段主键，您将自己手动增加内容。我仍然不建议这样做，MySQL真的喜欢它的 PK 是数字。
