如何使用PHP创建将插入数据库的字母数字自增字段?我使用的数据类型是varchar。
例如:
例如:
SD1
SD2
SD3
SD1
SD2
SD3
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`;
给予
+-----------+-------------+
| 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 |
+-----------+-------------+
编辑
如果您想在数字部分前面填充零,可以这样做:
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
;
提供
+--------------+-------------+
| 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 |
+--------------+-------------+
编辑 #2
如果您正在使用 MyISAM 或 DBD 引擎(不幸的是,对于 InnoDB 来说这不是一个选项),则可以创建按照您的前缀分组的自增列。
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
;
其中填充了
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')
;
提供
+--------------+---------------+
| 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 |
+--------------+---------------+