How to create alphanumeric auto_increment which will be inserted into database using PHP? The datatype that I use is varchar.
For example:
SD1
SD2
SD3
-
2There's no alphanumeric auto-increment in MySQL by definition - it's allowed only on integer columns. You can partly emulate it via trigger, for example, but that will fail in terms of concurring transactionsAlma Do– Alma Do2014-01-15 18:29:52 +00:00Commented Jan 15, 2014 at 18:29
-
1What happens when you get to SD9? Is the next value SD10? Or is it SE0? What are the rules for your "numbering" system? The built-in auto increment is (AFAIK) only for integer columns which are also an index. For this, I believe you'll have to write an INSERT trigger.ThatBlairGuy– ThatBlairGuy2014-01-15 18:33:25 +00:00Commented Jan 15, 2014 at 18:33
-
1Rather than using triggers if you want to increment from SD9 to SD10, you can define two fields, one with the alpha prefix, and the second as an autoincrementing numeric, and have a combined key for lookupsMark Baker– Mark Baker2014-01-15 18:39:47 +00:00Commented Jan 15, 2014 at 18:39
-
it will continue. no changes in alpha, only the number change. SD is just for identifier which is fixed, cannot change.nabil– nabil2014-01-15 18:44:09 +00:00Commented Jan 15, 2014 at 18:44
-
@MarkBaker - I think you've nailed it. Just set up an auto increment column and do the prefix as needed. Just needs to be in answer so we can upvote it. :-)ThatBlairGuy– ThatBlairGuy2014-01-15 18:47:21 +00:00Commented Jan 15, 2014 at 18:47
|
Show 2 more comments
1 Answer
CREATE TABLE IF NOT EXISTS `Products` (
`prefix` varchar(2) NOT NULL DEFAULT 'SD',
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(100) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY (`prefix`, `id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1
;
INSERT INTO `Products` (`name`) VALUES
('Product #1'),
('Product #2'),
('Product #3'),
('Product #4'),
('Product #5'),
('Product #6'),
('Product #7'),
('Product #8'),
('Product #9'),
('Product #10'),
('Product #11'),
('Product #12')
;
SELECT CONCAT(`prefix`,`id`) AS 'productId',
`name`
FROM `Products`;
gives
+-----------+-------------+
| productId | name |
+-----------+-------------+
| SD1 | Product #1 |
| SD2 | Product #2 |
| SD3 | Product #3 |
| SD4 | Product #4 |
| SD5 | Product #5 |
| SD6 | Product #6 |
| SD7 | Product #7 |
| SD8 | Product #8 |
| SD9 | Product #9 |
| SD10 | Product #10 |
| SD11 | Product #11 |
| SD12 | Product #12 |
+-----------+-------------+
EDIT
If you want to pad out the numeric part with leading zeroes, you can do
CREATE TABLE IF NOT EXISTS `Products` (
`prefix` varchar(2) NOT NULL DEFAULT 'SD',
`id` int(10) unsigned ZEROFILL NOT NULL AUTO_INCREMENT,
`name` varchar(100) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY (`prefix`, `id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1
;
which gives
+--------------+-------------+
| productId | name |
+--------------+-------------+
| SD0000000001 | Product #1 |
| SD0000000002 | Product #2 |
| SD0000000003 | Product #3 |
| SD0000000004 | Product #4 |
| SD0000000005 | Product #5 |
| SD0000000006 | Product #6 |
| SD0000000007 | Product #7 |
| SD0000000008 | Product #8 |
| SD0000000009 | Product #9 |
| SD0000000010 | Product #10 |
| SD0000000011 | Product #11 |
| SD0000000012 | Product #12 |
+--------------+-------------+
EDIT #2
If you're working with the MyISAM or DBD engines (sadly not an option for innodb unfortunately) you can create an autoincrement grouped by your prefix
CREATE TABLE IF NOT EXISTS `Products` (
`prefix` varchar(2) NOT NULL DEFAULT 'SD',
`id` int(10) unsigned ZEROFILL NOT NULL AUTO_INCREMENT,
`name` varchar(100) NOT NULL,
PRIMARY KEY (`prefix`, `id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1
;
which, populated with
INSERT INTO `Products` (`prefix`, `name`) VALUES
('SD', 'Product SD #1'),
('SD', 'Product SD #2'),
('SD', 'Product SD #3'),
('SD', 'Product SD #4'),
('SD', 'Product SD #5'),
('SD', 'Product SD #6'),
('TE', 'Product TE #1'),
('TE', 'Product TE #2'),
('TE', 'Product TE #3'),
('TE', 'Product TE #4'),
('TE', 'Product TE #5'),
('TE', 'Product TE #6')
;
gives
+--------------+---------------+
| productId | name |
+--------------+---------------+
| SD0000000001 | Product SD #1 |
| SD0000000002 | Product SD #2 |
| SD0000000003 | Product SD #3 |
| SD0000000004 | Product SD #4 |
| SD0000000005 | Product SD #5 |
| SD0000000006 | Product SD #6 |
| TE0000000001 | Product TE #1 |
| TE0000000002 | Product TE #2 |
| TE0000000003 | Product TE #3 |
| TE0000000004 | Product TE #4 |
| TE0000000005 | Product TE #5 |
| TE0000000006 | Product TE #6 |
+--------------+---------------+
