如何将文本放在mysql中的自动增量字段前面(例如:TTT00001)? [英] How to put text in front of auto increment field in mysql (like : TTT00001)?

问题描述

我想使用mysql在我的自动增量字段前面添加一些文本， 但是我仍然没有答案. 这是我要插入表中的示例数据

I want to put some text in front of my auto increment field with mysql, but I still have no answer to do this. here is the example data that I want to insert into table

"IDC00000001"

"IDC00000001"

现在我要做的只是自动填充具有零填充格式的整数， 结果只有00000001.

and what I do now just auto incremented integer with zerofill format, and the result just 00000001.

，我希望数字自动增加.因此，当再次插入一些数据时，它将像这样 "IDC00000002"，"IDC00000003"，........，"IDC00000022"等

and I want the number auto incremented. so when Insert some data again it will be like this "IDC00000002", "IDC00000003",........,"IDC00000022",etc

我的问题:

1. 如何解决此案?
我需要商店手续吗?
2. 什么是最佳做法，在插入之前先在php中准备呢，还是直接在mysql中准备呢?

1. how to solved this case?
2. am I need a store procedure?
3. what is best practice to do this, prepare this in php before insert or in mysql directly?

推荐答案

您需要的是触发器和另一个表.请在下面尝试此触发器；

What you need is trigger and one more table. Try this trigger below;

DELIMITER $$
CREATE TRIGGER tg_tableName_insert
BEFORE INSERT ON tableName
FOR EACH ROW
BEGIN
  INSERT INTO tableName_seq VALUES (NULL);
  SET NEW.id = CONCAT('IDC', LPAD(LAST_INSERT_ID(), 8, '0'));
END$$
DELIMITER ;

不要忘记创建序列表；

Dont forget to create sequence table;

CREATE TABLE tableName_seq
(
  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY
);
CREATE TABLE tableName
(
  id VARCHAR(11) NOT NULL PRIMARY KEY DEFAULT '0'
);

当您将数据插入到tableName表中时，您的第一个ID将是IDC00000001，第二个IDC00000002会像这样.希望对您有所帮助！

When you insert data to the tableName table your first id will be IDC00000001 and second IDC00000002 goes like that. I hope it helps!

这篇关于如何将文本放在mysql中的自动增量字段前面(例如:TTT00001)?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！