如何使用某些前缀使 MySQL 表主键自动递增
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17893988/
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
How to make MySQL table primary key auto increment with some prefix
提问by Vijay
I have table like this
我有这样的桌子
table
id Varchar(45) NOT NULL AUTO_INCREMENT PRIMARY KEY,
name CHAR(30) NOT NULL,
I want to increment my id field like 'LHPL001','LHPL002','LHPL003'... etc.
What should I have to do for that? Please let me know any possible way.
我想增加我的 id 字段，比如'LHPL001','LHPL002','LHPL003'......等等。我应该怎么做？请让我知道任何可能的方式。
回答by peterm
If you really need this you can achieve your goal with help of separate table for sequencing (if you don't mind) and a trigger.
如果你真的需要这个，你可以在单独的排序表（如果你不介意）和触发器的帮助下实现你的目标。
Tables
表
CREATE TABLE table1_seq
(
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY
);
CREATE TABLE table1
(
id VARCHAR(7) NOT NULL PRIMARY KEY DEFAULT '0', name VARCHAR(30)
);
Now the trigger
现在触发
DELIMITER $$
CREATE TRIGGER tg_table1_insert
BEFORE INSERT ON table1
FOR EACH ROW
BEGIN
INSERT INTO table1_seq VALUES (NULL);
SET NEW.id = CONCAT('LHPL', LPAD(LAST_INSERT_ID(), 3, '0'));
END$$
DELIMITER ;
Then you just insert rows to table1
然后你只需将行插入 table1
INSERT INTO Table1 (name)
VALUES ('Jhon'), ('Mark');
And you'll have
你会有
| ID | NAME | ------------------ | LHPL001 | Jhon | | LHPL002 | Mark |
Here is SQLFiddledemo
这是SQLFiddle演示
回答by Fluffy
Create a table with a normal numeric auto_increment ID, but either define it with ZEROFILL, or use LPADto add zeroes when selecting. Then CONCATthe values to get your intended behavior. Example #1:
创建一个带有普通数字 auto_increment ID 的表，但要么使用 定义它ZEROFILL，要么LPAD在选择时使用添加零。然后CONCAT是获得预期行为的值。示例#1：
create table so (
id int(3) unsigned zerofill not null auto_increment primary key,
name varchar(30) not null
);
insert into so set name = 'John';
insert into so set name = 'Mark';
select concat('LHPL', id) as id, name from so;
+---------+------+
| id | name |
+---------+------+
| LHPL001 | John |
| LHPL002 | Mark |
+---------+------+
Example #2:
示例#2：
create table so (
id int unsigned not null auto_increment primary key,
name varchar(30) not null
);
insert into so set name = 'John';
insert into so set name = 'Mark';
select concat('LHPL', LPAD(id, 3, 0)) as id, name from so;
+---------+------+
| id | name |
+---------+------+
| LHPL001 | John |
| LHPL002 | Mark |
+---------+------+
回答by yhAm
I know it is late but I just want to share on what I have done for this. I'm not allowed to add another table or trigger so I need to generate it in a single query upon insert. For your case, can you try this query.
我知道为时已晚，但我只想分享我为此所做的工作。我不允许添加另一个表或触发器，因此我需要在插入时在单个查询中生成它。对于你的情况，你可以试试这个查询。
CREATE TABLE YOURTABLE(
IDNUMBER VARCHAR(7) NOT NULL PRIMARY KEY,
ENAME VARCHAR(30) not null
);
Perform a select and use this select query and save to the parameter @IDNUMBER
执行选择并使用此选择查询并保存到参数@IDNUMBER
(SELECT IFNULL
(CONCAT('LHPL',LPAD(
(SUBSTRING_INDEX
(MAX(`IDNUMBER`), 'LHPL',-1) + 1), 5, '0')), 'LHPL001')
AS 'IDNUMBER' FROM YOURTABLE ORDER BY `IDNUMBER` ASC)
And then Insert query will be :
然后插入查询将是：
INSERT INTO YOURTABLE(IDNUMBER, ENAME) VALUES
(@IDNUMBER, 'EMPLOYEE NAME');
The result will be the same as the other answer but the difference is, you will not need to create another table or trigger. I hope that I can help someone that have a same case as mine.
结果将与其他答案相同，但不同之处在于，您不需要创建另一个表或触发器。希望能帮到和我一样情况的人。
