Add Prefix to auto-increment in mysql db
No. Either add the prefix in the query, or use a view instead.
How to make MySQL table primary key auto increment with some prefix
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
INSERT INTO Table1 (name)
VALUES ('Jhon'), ('Mark');
And you'll have
| ID | NAME |
------------------
| LHPL001 | Jhon |
| LHPL002 | Mark |
Here is SQLFiddle demo
Is there a way to insert an auto-incremental primary id with a prefix in mysql database?
First of all it's unadvisable to do so, like others commented, you can have this id value generated on the fly.
But if nonetheless you want it your way there're at least two ways to do so:
More or less reliable way involves using a separate table for sequencing and a trigger
Schema:
CREATE TABLE Table1_seq
(
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY
);
CREATE TABLE Table1
(
`id` VARCHAR(10) NOT NULL PRIMARY KEY DEFAULT '',
...
);
Trigger:
DELIMITER $$
CREATE TRIGGER tg_bi_table1
BEFORE INSERT ON table1
FOR EACH ROW
BEGIN
INSERT INTO table1_seq() VALUES();
SET NEW.id = CONCAT('D', LPAD(LAST_INSERT_ID(), 4,'0'));
END$$
DELIMITER ;
Then you just insert your rows to table1
INSERT INTO Table1 () VALUES (),(),();
And you'll get
| ID |
---------
| D0001 |
| D0002 |
| D0003 |
Here is SQLFiddle demo
Unreliable way is to generate your new id on the fly in INSERT
statement itself
INSERT INTO Table1 (id, ...)
SELECT CONCAT('D', LPAD(COALESCE(SUBSTR(MAX(id), 2), 0) + 1, 4, '0')),
...
FROM table1
Here is SQLFiddle demo
The problems with this approach:
- Under heavy load two concurrent sessions can grab the same
MAX(id)
value and therefore generate the same new id leading to the failure of insert. - You can't use multi-insert statements
Define Prefix for Auto Increment in MySQL
You can use date() and rand() to achieve this
Just like this
$id='PO'.date('Ymd').rand(0000,9999);
It will produce output as
Modified:-
For unique key you can use time() like this
$id='PO'.date('Ymd').time(); //o/p is "PO201707061499326403"
It will never repeat the same id.
Related Topics
Any PHP Code to Detect the Browser with Version and Operating System
Interpreting Return Value of Function Directly as an Array
How to Disable or Encrypt "View Source" for My Site
Php: Change Color of Text Based on $Value
How to Access Element Attributes with Simplexml
MySQL Select a Piece of a String and Order by That Piece
Best Method for Converting a PHP Array to JavaScript
PHP Passing Parameters via Url
PHP and Regexp to Accept Only Greek Characters in Form
Show Hide Custom Woocommerce Checkout Field Based on Selected Payment Method
I18N with Gettext But Without the Locale Hassle
Php: "Short_Open_Tag = On" Not Working
Loop Through Wordpress Posts, and Wrap Each X Post in a Div
Generating a Random Code in PHP
Regex PHP: Find Everything in Div
Easiest Way to Grab Filesize of Remote File in PHP
Convert an Array of Strings, Each String Has Dot Separated Values, to a Multidimensional Array