如何使用某些前缀使MySQL表主键自动递增 [英] How to make MySQL table primary key auto increment with some prefix

问题描述

我有这样的桌子

table
id Varchar(45) NOT NULL AUTO_INCREMENT PRIMARY KEY,
name CHAR(30) NOT NULL,

我想增加我的id字段，例如'LHPL001','LHPL002','LHPL003' ...等. 我该怎么办?请让我知道任何可能的方式.

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.

推荐答案

如果您确实需要这样做，可以通过单独的排序表(如果您不介意)和触发器来实现您的目标.

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.

表格

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

现在触发

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 ;

然后您只需将行插入到table1

Then you just insert rows to table1

INSERT INTO Table1 (name) 
VALUES ('Jhon'), ('Mark');

您将拥有

|      ID | NAME |
------------------
| LHPL001 | Jhon |
| LHPL002 | Mark |

这里是 SQLFiddle 演示

Here is SQLFiddle demo

这篇关于如何使用某些前缀使MySQL表主键自动递增的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！