blocks|key|53330|text|创建一个具有普通数字auto_increment+ID的表，但可以使用ZEROFILL对其进行定义，或者在选择时使用LPAD添加零。然后CONCAT这些值以获得您想要的行为。示例#1：|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|53331|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;
%2B---------%2B------%2B
%7C+id++++++%7C+name+%7C
%2B---------%2B------%2B
%7C+LHPL001+%7C+John+%7C
%7C+LHPL002+%7C+Mark+%7C
%2B---------%2B------%2B|code-block|syntax|javascript|53332|示例#2：|53333|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;
%2B---------%2B------%2B
%7C+id++++++%7C+name+%7C
%2B---------%2B------%2B
%7C+LHPL001+%7C+John+%7C
%7C+LHPL002+%7C+Mark+%7C
%2B---------%2B------%2B|53334|entityMap^0|Z|8|1M|4|1W|6|0|0|0|0^^$0|@$1|2|3|4|5|6|7|Q|8|@$9|R|A|S|B|C]|$9|T|A|U|B|C]|$9|V|A|W|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|X|8|@]|D|@]|E|$I|J]]|$1|K|3|L|5|6|7|Y|8|@]|D|@]|E|$]]|$1|M|3|N|5|H|7|Z|8|@]|D|@]|E|$I|J]]|$1|O|3|-4|5|6|7|10|8|@]|D|@]|E|$]]]|P|$]]

Create a table with a normal numeric auto_increment ID, but either define it with <code>ZEROFILL</code>, or use <code>LPAD</code> to add zeroes when selecting. Then <code>CONCAT</code> the values to get your intended behavior. Example #1:

<pre><code>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 |
+---------+------+
</code></pre>

Example #2:

<pre><code>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 |
+---------+------+
</code></pre>

blocks|key|53386|text|我知道为时已晚，但我只想分享我为此所做的一切。不允许添加另一个表或触发器，因此需要在插入时在单个查询中生成它。对于您的情况，您可以尝试此查询。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|53387|CREATE+TABLE+YOURTABLE(
IDNUMBER+VARCHAR(7)+NOT+NULL+PRIMARY+KEY,
ENAME+VARCHAR(30)+not+null
);|code-block|syntax|javascript|53388|执行select并使用此select查询并保存到参数@IDNUMBER|53389|(SELECT+IFNULL
+++++(CONCAT('LHPL',LPAD(
+++++++(SUBSTRING_INDEX
++++++++(MAX(`IDNUMBER`),+'LHPL',-1)+%2B+1),+5,+'0')),+'LHPL001')
++++AS+'IDNUMBER'+FROM+YOURTABLE+ORDER+BY+`IDNUMBER`+ASC)|53390|然后Insert查询将是：|53391|INSERT+INTO+YOURTABLE(IDNUMBER,+ENAME)+VALUES+
(@IDNUMBER,+'EMPLOYEE+NAME');|53392|结果将与其他答案相同，但不同之处在于，您不需要创建另一个表或触发器。我希望我能帮助那些和我有相同情况的人。|53393|entityMap^0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|S|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|T|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|U|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|V|8|@]|9|@]|A|$E|F]]|$1|K|3|L|5|6|7|W|8|@]|9|@]|A|$]]|$1|M|3|N|5|D|7|X|8|@]|9|@]|A|$E|F]]|$1|O|3|P|5|6|7|Y|8|@]|9|@]|A|$]]|$1|Q|3|-4|5|6|7|Z|8|@]|9|@]|A|$]]]|R|$]]

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.

<pre><code>CREATE TABLE YOURTABLE(
IDNUMBER VARCHAR(7) NOT NULL PRIMARY KEY,
ENAME VARCHAR(30) not null
);
</code></pre>

Perform a select and use this select query and save to the parameter @IDNUMBER

<pre><code>(SELECT IFNULL
 (CONCAT('LHPL',LPAD(
 (SUBSTRING_INDEX
 (MAX(`IDNUMBER`), 'LHPL',-1) + 1), 5, '0')), 'LHPL001')
 AS 'IDNUMBER' FROM YOURTABLE ORDER BY `IDNUMBER` ASC)
</code></pre>

And then Insert query will be :

<pre><code>INSERT INTO YOURTABLE(IDNUMBER, ENAME) VALUES 
(@IDNUMBER, 'EMPLOYEE NAME');
</code></pre>

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.

blocks|key|53401|text|如果有人在PostgreSQL上需要触发器，下面是不带触发器的PostgreSQL示例：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|53402|CREATE+SEQUENCE+messages_seq;

+CREATE+TABLE+IF+NOT+EXISTS+messages+(
++++id+CHAR(20)+NOT+NULL+DEFAULT+('message_'+%7C%7C+nextval('messages_seq')),
++++name+CHAR(30)+NOT+NULL,
);

ALTER+SEQUENCE+messages_seq+OWNED+BY+messages.id;|code-block|syntax|javascript|53403|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Here is PostgreSQL example without trigger if someone need it on PostgreSQL:
<pre><code>CREATE SEQUENCE messages_seq;

 CREATE TABLE IF NOT EXISTS messages (
 id CHAR(20) NOT NULL DEFAULT ('message_' || nextval('messages_seq')),
 name CHAR(30) NOT NULL,
);

ALTER SEQUENCE messages_seq OWNED BY messages.id;
</code></pre>

I have table like this

<pre><code>table
id Varchar(45) NOT NULL AUTO_INCREMENT PRIMARY KEY,
name CHAR(30) NOT NULL,
</code></pre>

I want to increment my id field like <code>'LHPL001','LHPL002','LHPL003'</code>... etc.
What should I have to do for that? Please let me know any possible way.

How to make MySQL table primary key auto increment with some prefix

PostgreSQL

我有一张这样的桌子tableid Varchar(45) NOT NULL AUTO_INCREMENT PRIMARY KEY,name CHAR(30) NOT NULL,我想像'LHPL001','LHPL002','LHPL003'一样递增我的id字段...等等。我应该为此做些什么呢？请告诉我任何可能的方法。

问如何使MySQL表主键带前缀自动递增
EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何使MySQL表主键带前缀自动递增EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何使MySQL表主键带前缀自动递增
EN